MySQL: How to allow remote connection to mysql

mysql mysql-workbench
Leo picture Leo · Feb 8, 2013 · Viewed 903.4k times · Source

I have installed MySQL Community Edition 5.5 on my local machine and I want to allow remote connections so that I can connect from external source.

How can I do that?

Answer

mjuarez picture mjuarez · Feb 8, 2013

That is allowed by default on MySQL.

What is disabled by default is remote root access. If you want to enable that, run this SQL command locally:

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

And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf on Unix/OSX systems. In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf).

If it's a Windows system, you can find it in the MySQL installation directory, usually something like C:\Program Files\MySQL\MySQL Server 5.5\ and the filename will be my.ini.

Change line

 bind-address = 127.0.0.1

to

 #bind-address = 127.0.0.1

And restart the MySQL server (Unix/OSX, and Windows) for the changes to take effect.

Related questions

MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More
Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

I am continuously receiving this error. I am using mySQL Workbench and from what I am finding is that root's schema privileges are null. There are no privileges at all. I am having troubles across platforms that my server is …

Read More
Authentication plugin 'caching_sha2_password' cannot be loaded

I am connecting MySQL - 8.0 with MySQL Workbench and getting the below error: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found I have tried with other client …

Read More