MySQL root access from all hosts

Question

Update:

As mentioned in the comments, since MySql 8 you need to first explicitly create the user, so the command will look like:

CREATE USER 'root'@'%' IDENTIFIED BY 'root'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Original answer:

There's two steps in that process:

a) Grant privileges. As root user execute with this substituting 'password' with your current root password :

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

b) bind to all addresses:

The easiest way is to comment out the line in your my.cnf file:

#bind-address = 127.0.0.1 

and restart mysql

service mysql restart

By default it binds only to localhost, but if you comment the line it binds to all interfaces it finds. Commenting out the line is equivalent to bind-address=*.

To check where mysql service has binded execute as root:

netstat -tupan | grep mysql

Update For Ubuntu 16:

Config file is (now)

/etc/mysql/mysql.conf.d/mysqld.cnf 

(at least on standard Ubuntu 16)

Run the following query:

use mysql;
update user set host='%' where host='localhost'

NOTE: Not recommended for production use.

MYSQL 8.0 - open mysql command line client

GRANT ALL PRIVILEGES ON \*.* TO 'root'@'localhost';  

use mysql

UPDATE mysql.user SET host='%' WHERE user='root';  

Restart mysql service

Sometimes

bind-address = 127.0.0.1

should be

bind-address = *