MySQL database backup, migration, and open remote access 3306 ports

Today, the data server and Business Server are separated, which involves the open remote access to MySQL, record as follows for memo:

Suppose the data server IP address is 192.168.1.101, and the Business Server IP address is 192.168.2.51.

First install the MySQL instance on the data server 192.168.1.101, then use root to access MySQL and set up the necessary database and user.

The code is as follows	Copy Code
$ mysql-uroot-p Enter Password: > CREATE DATABASE business_db; > Grant all privileges in ' business_db '. * to ' business ' @ ' 192.168.2.51 ' identified by ' MyPassword ' with GRANT OPTION; > FLUSH privileges;

Here, for example, to create a business_db database, the authorized user is business, and the IP address of the Business Server follows the @ symbol, which specifies that the business user can access the remote database only through this IP address. The back ' mypassword ' is the password you want to set.

For existing users we can update the remote access host with the following instructions (take User1 as an example).

The code is as follows	Copy Code
$ mysql-uroot-p Enter Password: > Use MySQL; > SELECT ' host ' from user WHERE user= ' user1 '; > UPDATE user SET host = ' 192.168.2.51 ' WHERE user = ' User1 '; > FLUSH privileges; > SELECT ' host ' from user WHERE user= ' user1 ';

You may have noticed that this specifies the Business Server 192.168.2.51, which actually allows any host connection to be made only by changing the IP address to%.

Finally also need to modify the MySQL binding IP address, or on our data server, modify the configuration file/etc/mysql/my.cnf Find [mysqld] section of the

The code is as follows	Copy Code
Bind-address. [Mysqld] # # * Basic Settings # .... # Instead of skip-networking The default is now to listen only # localhost which are more compatible and are not less secure. Bind-address = 192.168.1.101

Finally, don't forget to restart the MySQL daemon:

Service MySQL Restart
Now let's go back to the business Server, and then we'll migrate the data from the old MySQL to the new MySQL database server.

Use the mysqldump command to export the database in the following format, replacing the parameters as you actually want:

Mysqldump-u user name-p database name > exported file name
Like exporting a database business_db

Mysqldump-uroot-p business_db > Business_db.sql
Then copy the exported backup file Business_db.sql to the database server 192.168.1.101, log on to the database server with root and use the source directive:

The code is as follows	Copy Code
$ mysql-uroot-p Enter Password: > Use business_db; > Source ~/business_db.sql

Here ~/business_db.sql is the path specified, specifically for storing backup files Business_db.sql backup files.

For the Business Server, the following modify the phpMyAdmin configuration, let it use our new database server, if the phpMyAdmin use more than one remote database management is interested in the site to search OH.

Modify the configuration file libraries/config.default.php located under the phpMyAdmin path:

The code is as follows	Copy Code
/** * MySQL hostname or IP address * * @global string $cfg [' Servers '] [$i] [' Host '] */ $cfg [' Servers '] [$i] [' host '] = ' 192.168.1.101 '; Database server address

Once you have modified all of the configuration files that use MySQL, we can retire the MySQL server on the old server honorably:

Service MySQL Stop
UPDATE-RC.D MySQL Disable
Notice me here just will MySQL stop and disable, no uninstall, mainly take into account the subsequent operation of the problem, if you want to uninstall the direct use of Apt-get remove--purge MySQL can be.