MySQL服务器合并

不中断web服务的前提下合并多个MySQL服务器到单独的一台服务器上

 

目标描述:
公司内部生产环境中有多个MySQL服务器系统配置过剩、管理复杂,计划将三台MySQL数据库服务器A1(A2从)、B1(B2从)、C1(C2从)合并到配置稍高Mysql服务器D1(D2从)上。前提要求是不中断web服务,不能丢失数据。
由于A1、B1、C1的数据库database_name并不重复,数据引擎为INNODB,合并的难度就小了很多。
A1中有DBA1、DBA2、DBA3,IP 172.16.1.101
B1中有DBB1,IP 172.16.1.102
C1中有DBC1,IP 172.16.1.103
D1 IP 172.16.1.104

合并思路:
1、在D1上创建账号并赋予权限;A1、B1、C1创建同步账号(曾创建)、开启二进制文件(曾开启)
2、设置D1为A1的从库:A1数据,导入D1、设置同步,切换web代码中的数据库连接文件。
3、导出B1数据,导入到D1中,重设D1为B1的从库,同步完成后切换web代码中数据库连接文件。
4、同理导出C1数据,导入到D1中,重设D1为C1的从库,同步完成后切换web代码中数据库连接文件。
5、重设D1为主服务器,创建同步账号,导出D1数据库并导入到D2,设置主从和备份。

mysqldump关键参数:
–master-data=2:记录数据导出时的数据库使用的日志文件名和position
–single-transaction:导出时不锁表
–databases/–B:同时导出多个数据库
具体释义请man mysqldump

实施过程(本例数据库名、IP、权限等已替换为非无意义单词,仅展示原理和步骤):

1、在D1服务器安装同版本的MySQL数据库,并修改MySQL的配置文件/etc/my.cnf,本例中直接在A1复制/etc/my.cnf,并修改server-id=value中value不和A1、B1、C1冲突
2、确认A1、B1、C1中存在同步用的账号,并已赋予同步权限,以A1为例:
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| DBA1 |
| DBA2 |
| DBA13 |
| mysql |
| performance_schema |
+——————–+
6 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+———–+————–+
| user | host |
+———–+————–+
| root | 127.0.0.1 |
| sync_user | 172.16.1.% |
| DBAU1 | 172.16.1.1 |
| root | localhost |
+———–+————–+
4 rows in set (0.00 sec)
mysql> show grants for ‘sync_user’@’172.16.1.%’;
+——————————————————————————————————————————-+
| Grants for sync_user@172.16.1.% |
+——————————————————————————————————————————-+
| GRANT REPLICATION SLAVE ON *.* TO ‘sync_user’@’172.16.1.%’ IDENTIFIED BY PASSWORD ‘*****************************************’ |
+——————————————————————————————————————————-+
1 row in set (0.00 sec)

GRANT REPLICATION SLAVE [ … ] 说明已经赋予sync_user账号在172.16.1.%的同步权限。

3、在D1服务器上创建DBAU1账号和权限与A1服务器的权限相同。
在A1服务器执行下列语句查看DBAU1的账号权限:
mysql> show grants for ‘DBAU1’@’172.16.1.1’;
+—————————————————————————————————————–+
| Grants for DBAU1@172.16.1.19 |
+—————————————————————————————————————–+
| GRANT USAGE ON *.* TO ‘DBAU1’@’172.16.1.19’ IDENTIFIED BY PASSWORD ‘*****************************************’ |
| GRANT ALL PRIVILEGES ON `DBA1`.`hl_city_exchange_record` TO ‘DBAU1’@’172.16.1.1’ WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `DBA2`.`hl_community_detail` TO ‘DBAU1’@’172.16.1.1’ WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `DBA3`.`hl_city_price_trend` TO ‘DBAU1’@’172.16.1.1’ WITH GRANT OPTION |
+—————————————————————————————————————–+
4 rows in set (0.00 sec)
在D1中执行如下命令赋权限:
mysql>GRANT ALL PRIVILEGES ON `DBA1`.`hl_city_exchange_record` TO ‘DBAU1’@’172.16.1.1’ IDENTIFIED BY ‘YOUR PASSWORD’ WITH GRANT OPTION;
mysql>GRANT ALL PRIVILEGES ON `DBA2`.`hl_community_detail` TO ‘DBAU1’@’172.16.1.1’ WITH GRANT OPTION;
mysql>GRANT ALL PRIVILEGES ON `DBA3`.`hl_city_price_trend` TO ‘DBAU1’@’172.16.1.1′ WITH GRANT OPTION;

4、导出A1中的DBA1、DBA2、DBA3数据库,并修改导出时使用的二进制文件和日志位置;
shell>mysqldump -uroot -p –master-data=2 –single-transaction –database DBA1 DBA2 DBA3 >A1_DBA123.sql

将数据文件传送到D1上:
scp A1_DBA123.sql 172.17.1.104L/root/

–master-data=2:参数值为2时日志文件和日志位置时被注释的状态,需要根据实际情况修改
查找记录位置的行并增加主库信息:
cat -n A1_DBA123.sql | grep “CHANGE MASTER TO”,找到如下
22 — MASTER_LOG_FILE=’mysql-bin.000044′, MASTER_LOG_POS=627859448; 修改为下行(n为行数,file和pos值每个文件不同)
shell> sed -i “N,22a\CHANGE MASTER TO MASTER_HOST=’172.16.1.101′, MASTER_USER=’sync_user’, MASTER_PASSWORD=’SYNC USER PASSWORD’, MASTER_LOG_FILE=’mysql-bin.000044′, MASTER_LOG_POS=627859448;”
导入数据:在D1上执行下行中的命令将修改后的SQL导入到D1中。
shell> mysql -uroot -p <A1_DBA123.sql

然后开启D1的slave.
mysql>start slave;
之后做一些必要的检查:
mysql>show slave status\G;

[ … ]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[ … ]
Seconds_Behind_Master: 0
[ … ]

显示中如果包含以上三列代码值为YES、YES、0则说明同步成功,如果不为0需要等待从库同步完毕。

5、确认数据库同步成功后修改web代码中数据库连接模块,将数据库IP指向D1服务器,并观察数据运行情况;
查看mysql运行状态
mysql> show processlist;
至此A1数据库已无缝迁移完毕。
测试部门确认页面访问正常后停掉D1的slave(后续导入B1数据库时会提示停掉slave)
mysql> stop slave;

6、B1数据库导入的情况大体一致,根据步骤2、3、4、5做一些相应修改后导入到D1中,做必要检查数据库前已完毕后切换web代码中数据库连接模块。
7、C1数据库导入的情况大体一致,根据步骤2、3、4、5做一些相应修改后导入到D1中,做必要检查数据库前已完毕后切换web代码中数据库连接模块。

8、数据库导入完毕后等测试部门确认前台无误。进行下一步。

9、重设D1为主库;
重设D1为主库;
mysql> reset master;
(服务器端执行reset master会重设master,并清理掉作为master的二进制日志)
mysql> quit;
重启数据库
shell> service mysqld restart
测试查看D1的slave状态如下:
mysql> show slave status\G;
Empty set (0.00 sec)

ERROR:
No query specified
10、导出D1的所有数据库,并设置D2为D1的从库;
shell>mysqldump -uroot -p –master-data=2 –single-transaction –all-databases >D1_all-databases.sql
查找记录位置的行并增加主库信息:
cat -n D1_all-databases.sql | grep “CHANGE MASTER TO”,找到如下
22 — MASTER_LOG_FILE=’mysql-bin.00001′, MASTER_LOG_POS=627859448; 修改为下行(n为行数,file和pos值每个文件不同)
shell> sed -i “N,22a\CHANGE MASTER TO MASTER_HOST=’172.16.1.101′, MASTER_USER=’sync_user’, MASTER_PASSWORD=’SYNC USER PASSWORD’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=627859448;”
(“N,22a…” 此中22为行数,和cat中的行数对应)
导入数据:在D2上执行下行中的命令将修改后的SQL导入到D2中。
然后开启D2的作为从库:
shell> start slave;
并做一些必要的检查。

至此,三台MySQL数据库服务器A1(A2从)、B1(B2从)、C1(C2从)成功合并到D1(D2从)。

在D1上设置备份(略写)
shell>echo “password of backup user” > backup.pass
shell> chown 600 backup.pass
这样设置目的是只有当前用户能够读取到backup_user的密码。
备份的关键部分是:
mysqldump -uroot -p`cat backup.pass` –master-data –single-transaction –all-databases > BackupDate`date +%Y%m%d-%H%M%S`.sql
也可以扩展比如清理mtime大于30天的文件等。不再详述。

发表评论

电子邮件地址不会被公开。 必填项已用*标注