Mysql主从热备份+数据迁移+授权迁移实战

• 服务器信息
• 一、原理，流程
• 二、master端操作：配置信息，导出mysqldump出.sql文件，授权给slave 账号
• • 三、slave 端操作

服务器信息

服务器：master10.197.10.1 ，slave 10.197.10.2

mysql版本：5.7 迁移至5.5会出错，

比如

1,主键的长度超出，此处采用版本一样。

2,master 用的 mysql5.6 , binlog_checksum 默认设置的是 crc32。 如果slave用的 5.5 或者更早的版本，请将master的 binglog_checksum设置为 none。

一、原理，流程

1，配置两台服务器中 /etc/my.cnf 文件中 server-id=1,server-id=2,务必检查，避免出错

检查方法：进入mysql后，查看生效的变量值：

show variables like "%server%";。
           

2，在master端，mysqldump出.sql文件，并且获取master在备份时间点的logbin文件 和pos位置。

3，导入mysql < xx.sql 文件后，在slave端 开启日志追索。

4，追索完成后 ，checksum核对，并且开始修复差异数据

二、master端操作：配置信息，导出mysqldump出.sql文件，授权给slave 账号

1，需要迁移若干个库，非全量库，可以指定

mysqldump -u xx -p xx --databases db1 db2 >/data/xxx.sql
           

注意：查看目录是否有足够空间df -sh，再确定导出至哪个目录

2,迁移全量库，

(1)innodb库,无需锁表， 可记录时间快照信息

mysqldump -uroot -p --default-character-set=utf8 --opt --master-data=2 --single-transaction --all-databases > all_date.sql
           

注：–master-data=2，导出快照日志文件和位置信息，并且注释（2值）

–single-transaction参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，

(2)myisam,需锁表

mysqldump -uroot -p --default-character-set=utf8 -opt --master-data=2 --lock-all-tables --all-databases > all_data.sql
           

(3)导出后，查看sql文件信息

tail all_data.sql文件，查看是否有completed，完成提示。

head -n 50 all_data.sql，查看日志

MASTER_LOG_FILE=‘mysql-bin.000009’ MASTER_LOG_POS=2108

后续热备时，从次处追索同步。

注意避免踩坑：mysqldump操作后，切勿对库再次进行mysqldump操作，否则后续热备时，日志位置会报错。

3, 导出grants信息

I #!/bin/bash
expgrants()
{
  mysql -B -uroot -padmin -N [email protected] -e "SELECT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql -uroot -padmin  [email protected] | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}
expgrants > ./grants.sql
           

本地生成了grants.sql文件

4，授权给slave同步账号

GRANT REPLICATION SLAVE ON *.* to 'rep'@'10.197.10.2' identified by  '123456'
           

5，文件传输，master–>slave端,

免输入密码传输，方便导出后，定时操作

推送

yum -y install sshpass
sshpass -p "pssword" scp /data/all_data.sql [email protected].197.10.2:/data/
           

也可拉取

yum -y install sshpass
sshpass -p "pssword" scp  [email protected].197.10.2:/data/ /data/all_data.sql
           

三、slave 端操作

注意踩坑点：

如果slave中有的库表已存在，或者某次迁移失败后，再次导入迁移。会导致速度很慢，插入后需重新根据键处理表，个别表耽误时间很长，建议先清除这些库，空白后再导入。

1. 导入sql文件

   mysql -uroot -p --default-character-set=utf8 < all_data.sql

   避免终端掉线，需要后台挂起导入

nohup mysql < all_data.sql &
 nohup mysql -padmin -f <all_data.sql &
           

1. 查看库的导入进度，大小

   (1)进入sql后

USE information_schema;
 SELECT TABLE_SCHEMA, SUM(DATA_LENGTH)/1024 FROM TABLES GROUP BY TABLE_SCHEMA;
           

(2)查看是否有，正在执行的插入操作,

(3)查看迁移过来的目录下，文件数量，大小等信息

cd /data/mysqldata/3306/data/
 ls -l |grep "^-"|wc -l
 du -sh *
           

1. 增加了 开启slave‘ ；

CHANGE MASTER TO
MASTER_HOST='10.197.10.1',
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=2108;
           

start slave ;
show slave status;
           

Slave_IO_Running: Yes //此状态必须YES

Slave_SQL_Running: Yes //此状态必须YES

4、常见错误

（1）日志追索之前，切勿在slave端操作建表 drop表等，否则显示 exist table 类。

解决方法：设定跳过错误的数量，1个不够，多设定几个，切勿太大，否则 需要在master端，多建几个表，多删几次，slave才能看到变化。

mysql>slave stop;

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务

mysql>slave start

主从同步测试

在master中建立一个test数据库，并在这个库中建表插入一条数据，查看slave是否同步到。