Access the MySQL server database in Linux

By default, for security reasons, the MySQL database in Linux shields remote access. However, in many cases, you need to access remote data at home or from web programs.

By default, for security reasons, the MySQL database in Linux shields remote access. However, in many cases, you need to access remote data at home or from web programs.

By default, for security reasons, the MySQL database in Linux shields remote access. However, in many cases, you need to access the remote database server at home or from a web application, which is quite troublesome.

Step 1: Activate network settings
You need to edit the mysql configuration file my. cnf.

In general, my. cnf is placed in the following directory:
/Etc/mysql/my. cnf (Debian linux)
/Etc/my. cnf (Red Hat Linux/Fedora Linux)
/Var/db/mysql/my. cnf (FreeBSD)

# Vi/etc/my. cnf
Then, use vi to edit my. cnf and modify the content from the following lines:

[Mysqld]

What you need:
1. Make sure that the skip-networking is deleted or blocked. Otherwise, TCP/IP access is not supported.
2. Add bind-address = 65.55.55.2 to replace 65.55.55.2 with your server address.
After modification, the configuration is as follows:
[Mysqld]
User = mysql
Pid-file =/var/run/mysqld. pid
Socket =/var/run/mysqld. sock
Port = 3306
Basedir =/usr
Datadir =/var/lib/mysql
Tmpdir =/tmp
Language =/usr/share/mysql/English
Bind-address = 65.55.55.2
# Skip-networking
....
..

Save and close the configuration file
Restart the mysql server: #/etc/init. d/mysql restart

Step 2: grant access permissions to remote ip addresses
1. log on to mysql # mysql-u root-p as root
2. authorize new database
If you have a new database named foo. The user bar must be accessed at the client address 202.54.10.20.

Execute (authorization) on the server ):
Mysql> create database foo;
Mysql> grant all on foo. * TO bar @ '2014. 54.10.20 'identified by 'Password ';

How can I authorize an existing database?

If the user's webadmin often accesses the webd database at the client address 202.54.10.20, the Command executed on the server should be ):

Mysql> update db set Host = '2014. 54.10.20 'where Db = 'webdb ';
Mysql> update user set Host = '2014. 54.10.20 'where user = 'webadmin ';

Step 3: run the exit command to exit the mysql environment,

Step 4 Test
From remote system type command:
$ Mysql-u webadmin-h 65.55.55.2-p

Annotation;
-U webadmin: username for mysql
-H IP or domain name: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
-P: password prompt
You can also use telnet to connect to remote port 3306 $ telnet 65.55.55.2 3306

Note:

This article solves the problem of mysql remote access in the SUSE environment. However, in practice, my. cnf does not seem to be particularly important. In my operations, my. cnf creation and modification were not performed (ignore the first step ). The most important step is step 4.
Grant all on B2C. * TO root @ '5. 179.201.86 'IDENTIFIED '';
B2C is the name of the remote database to be accessed, and 5.179.201.86 is the IP address of the mysql client host, that is, the IP address of your machine.
There is a small detail that requires attention. If your server is installed with a firewall, You need to enable the 3306 access. Otherwise, the connection will fail.