MySQL allow remote root login in Ubuntu and CentOS
Allowing MySQL Root Login from All IP Addresses :
Mysql remote root access is disabled by default. Now in this tutorial, we will look into how to enable remote MySQL root access.
First of all, open the
/etc/mysql/my.cnf file using any of editor. then find the line bind-address. Then comment out that line something like below.
1 |
vim /etc/mysql/my.cnf |
Change line
1 |
bind-address = 127.0.0.1 |
to
1 |
#bind-address = 127.0.0.1 |
Now, we need to change the permission in MySQL using following command.
Please run the following command in MySQL CLI.
1 |
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; |
1 |
FLUSH PRIVILEGES; |
Note: Above command allows root access from all IP Address.
Allowing Root Access from Specific IP address:
Now we need to add our public IP address ( your desired IP Address ) to the MySQL whitelisted IP address. Please run the following command to enable the root login from IP Address 2.2.2.2.
1 |
GRANT ALL PRIVILEGES ON *.* TO 'root'@'2.2.2.2' IDENTIFIED BY 'password' WITH GRANT OPTION; |
Note: Please replace IP Address 2.2.2.2 with your desired IP Address.
Then run the following command to make all your changes to affect.
1 |
FLUSH PRIVILEGES; |
then restart the MySQL server.
On ubuntu:
1 |
service mysql restart |
On centos 7:
1 |
service mariadb restart |
Thank u very much it worked
If your Using MYSQL 5.7 on Ubuntu 16.04, Then bind-address can be found in the following configuration file.
vim /etc/mysql/mysql.conf.d/mysqld.cnf
change the Bind-address to 0.0.0.0 or whatever IP you want.