benrobb
thoughts that don't fit in
Subscribe to RSSHowto: Remote Root Access to MySql
Very quickly, another thing that I typically like to do on my server boxes is allow root access to my Mysql database from remote computers. I don’t forward the port through my router and I use a very secure password (doesn’t everyone?). I don’t want to create a security risk, I just want to connect to the database from other computers around my network – particularly from my laptop.
Again (like most of my instructions) these instructions are for Ubuntu – currently Edgy Eft.
sudo apt-get install mysql-server
Ubuntu installs Mysql at /etc/mysql/ by default. Now we need to set a root password.
mysql -u root
mysql> SET PASSWORD FOR 'ROOT'@'LOCALHOST"
> = PASSWORD('new_password');
Now while we’re still here, we’ll create a new HOST for root and allow root to login from anywhere.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
> IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit
Thanks to Thom for the Flush Privileges comment. I think it depends on other settings in your MySQL setup, since I didn’t have to, but just in case, I’ve added it here. We’re almost done now. We just have to tell Mysql to allow remote logins.
sudo vi /etc/mysql/my.cnf
Out-of-the-box, MySQL only allows connections from the localhost identified by the IP Address of 127.0.0.1. We need to remove that restriction, so find the line that says
bind-address = 127.0.0.1
and comment it out. That’s all there is to it! Now get your favorite MySql client and start developing.
Thanks a lot for sharing. it takes less then 1 min but learn whatever i was looking for.
Thank you. You help me to resolve my trouble in a seconds.
Thanks, this helped.
Fantastic!!!
Accidently set ‘password’ first time, without realising that’s where I should put my own password.
Works on Ubuntu 8.04…
Adam.
This should be in every mysql faq
What is the syntax for commenting out a line in a my.conf file?
Further, my.cnf did not exist on my system yet. I installed 5.0.67 from source. It lists the –data-dir as /usr/local/mysql/var. Could it go here? If it is empty, will adding that line and commenting it out do anything?
Thanks
Thanks!!!
Thanks for the help, the information is accurate
Don’t you need to run FLUSH PRIVILEGES; after the GRANT ALL?
Thanks
Thom
Thansk for the help. good job
Seriously, do not forget the FLUSH PRIVILEGES…I had done all the other steps several times to no avail…
The syntax for commenting is simply to add a # in front of the line, so it would say:
#bind-address = 127.0.0.1
If your my.conf file is not in that location you’ll have to find it because there are all sorts of settings in there that tell MySQL how to run. If the line doesn’t exist in your my.conf file when you find it, then you can skip this step. Adding a commented out line will not change anything.
thanks! very helpful