By default remote access to MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server.
You need type the following commands which will allow remote connections.
Once connected you need to edit the MySQL server configuration file my.cnf.
* If you are using Debian Linux file is located at /etc/mysql/my.cnf location
* If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
* If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf
# vi /etc/my.cnf
For example, if your MySQL server IP is 192.168.1.100 then entire block should be look like as follows:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 192.168.1.100
* bind-address : IP address to bind to.
* skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.
Restart the mysql server, enter:
# /etc/init.d/mysql restart
Grant access to remote IP address
Connect to mysql server:
$ mysql -u root -p mysql
Grant access to a new database
GRANT ALL PRIVILEGES <— States the user will have all the privileges
ON *.* <— all databases
TO ‘user’@'%’ <— to user @ any connecting hostname or ipaddress
IDENTIFIED BY ‘some_pass’ <— with the password you changed ‘some_pass’ to
WITH GRANT OPTION; <— gives the user the option to grant more privileges (aka administrative permissions) and closes out the command with the ;
