If you’re like most MySQL administrators, you want to be able to log on to any host in your network without needing root privileges. With MySQL 5.6, you can give the root user login permission from any host by setting the root_login variable to ‘yes’. To set the root_login variable, open your MySQL server configuration file (usually located at /etc/mysql/my.cnf) and add the following line: root_login=yes ..
To configure this feature, you’ll need to update the mysql user table to allow access from any remote host, using the % wildcard.
Open the command-line mysql client on the server using the root account.
Then you will want to run the following two commands, to see what the root user host is set to already:
Here’s an example of the output on my database, which is pretty much the default settings. Note that ubuntuserv is the hostname of my server.
Now I’ll update the ubuntuserv host to use the wildcard, and then issue the command to reload the privilege tables. If you are running this command, substitute the hostname of your box for ubuntuserv.
mysql> select host,user from user; +—————+——————+ | host | user | +—————+——————+ | ubuntuserv | root | | localhost | debian-sys-maint | | localhost | root | +—————+——————+ 3 rows in set (0.00 sec)
That’s all there is to it. Now I was able to connect to that server from any other machine on my network, using the root account.
Again, note this isn’t very secure, and you should at least make sure that you’ve set a root password.
RELATED: How to Export a CSV file from MySQL Command Line
