mysql 多端口备份_MySQL备份、迁移及开放远程访问3306端口备忘

今天把数据服务器和业务服务器进行了分离，其中涉及对MySQL开放远程访问的，作出记录如下以供备忘：

假设数据服务器IP地址为192.168.1.101，业务服务器IP地址为192.168.2.51。

首先在数据服务器192.168.1.101上安装好MySQL实例，然后使用root访问MySQL并建立好所需要的数据库和用户。

$ mysql -uroot -p

Enter Password:

> CREATE DATABASE business_db;

> GRANT ALL PRIVILEGES ON `business_db`.* TO 'business'@'192.168.2.51' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

> FLUSH PRIVILEGES;

$ mysql -uroot -p

Enter Password: > CREATE DATABASE business_db;

> GRANT ALL PRIVILEGES ON `business_db`.* TO 'business'@'192.168.2.51' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

> FLUSH PRIVILEGES;

这里以创建business_db数据库为例，授权用户为business，其中紧跟着@符号后面的是业务服务器的IP地址，这样就指定了business用户只能通过这个IP地址访问远程数据库。后面的'mypassword'为你想设置的密码。

对于已经存在的用户我们可以通过下面的指令对远程访问主机进行更新(以user1为例)。

$ 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';

$ 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';

大家可能注意到了这里指定了业务服务器192.168.2.51，实际上允许任何主机连接只需要将IP地址改为%即可。

最后还需要修改MySQL绑定的IP地址，还是在我们的数据服务器上，修改配置文件/etc/mysql/my.cnf找到[mysqld]节的bind-address。

[mysqld]

#

# * Basic Settings

#

....

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address=192.168.1.101

[mysqld]

#

# * Basic Settings

# .... # Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address= 192.168.1.101

最后不要忘记重新启动一下MySQL守护进程：

service mysql restart

service mysql restart

现在让我们回到业务服务器，接下来我们将数据从旧MySQL迁移到新的MySQL数据库服务器上。

使用mysqldump命令导出数据库，格式如下，请按实际要求对参数进行替换：

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

比如导出数据库business_db

mysqldump -uroot -p business_db > business_db.sql

mysqldump -uroot -p business_db > business_db.sql

然后将导出的备份文件business_db.sql复制到数据库服务器192.168.1.101上，在数据库服务器上用root登录并使用source指令：

$ mysql -uroot -p

Enter Password:

> use business_db;

> source ~/business_db.sql

$ mysql -uroot -p

Enter Password: > use business_db;

> source ~/business_db.sql

这里~/business_db.sql是指定的路径，具体为存放备份文件business_db.sql备份文件的路径。

对于业务服务器来说下面修改phpmyadmin的配置，让其使用我们新的数据库服务器，如果对phpmyadmin使用多个远程数据库管理感兴趣的话可以参考文章《修改phpMyAdmin使其能够管理多台远程MySQL服务器[原创]》：

修改位于phpmyadmin路径下的配置文件libraries/config.default.php：

/**

* MySQL hostname or IP address

*

* @global string $cfg['Servers'][$i]['host']

*/

$cfg['Servers'][$i]['host'] = '192.168.1.101'; // 数据库服务器地址

/**

* MySQL hostname or IP address

*

* @global string $cfg['Servers'][$i]['host']

*/

$cfg['Servers'][$i]['host'] = '192.168.1.101'; // 数据库服务器地址

当修改好所有使用MySQL的应用的配置文件后，我们可以让旧服务器上的MySQL服务器光荣退休了：

service mysql stop

update-rc.d mysql disable

service mysql stop

update-rc.d mysql disable

注意我这里只是将mysql停止并禁用，没有卸载，主要考虑到后续运行的问题，如果要卸载直接使用apt-get remove --purge mysql就可以了。