MySQL 5.7建立主从备份

发表于 更新于 分类于 Valine： 本文字数： 2.5k 阅读时长 ≈ 2 分钟

配置主服务器

vim /etc/mysql/mysql.conf.d/mysqld.cnf
找到server-id附近的代码，修改为

1
2
3
4
5
6
7
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = test  # 需要写binlog的数据库
binlog_ignore_db        = mysql # 不需要写binlog的数据库
# binlog_do_db和binlog_ignore_db可以仅指定一项，也可以都不指定，都不指定则全部写binlog

重启数据库
service mysql restart

防止出现打开文件数限制

ulimit -n 65535

备份数据库

xtrabackup --backup --host=127.0.0.1 --user=root --password=zrjm6E13RdhTPBmB --target-dir=/data/dbbackup/20221009
如果不存在xtrabackup命令
apt install percona-xtrabackup

复制到远程

rsync -avpP -e ssh /data/dbbackup/20221009 root@从库:/data/dbbackup/
或通过共享目录在从服务器挂载备份目录

配置从服务器

vim /etc/mysql/mysql.conf.d/mysqld.cnf
找到server-id附近的代码，修改为

1
2
3
4
5
6
7
8
9
10
server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
replicate-do-db         = test    #需要同步的数据库
replicate-ignore-db     = mysql    #不同步系统数据库
super_read_only         = 1
read_only               = 1

准备恢复

这步并没有连接数据库，只是对备份文件做一些设置，在主或从上执行都可以，安全一点应该再从库上执行，因为从库和主库的数据库版本未必完全一致。
xtrabackup --prepare --user=root --password=zrjm6E13RdhTPBmB --target-dir=/data/dbbackup/20221009

连上从数据库停止SLAVE

mysql -h127.0.0.1 -P3306 -uroot -p
STOP SLAVE;

停止从服务器数据库

service mysql stop

清空/var/lib/mysql目录，顺便备份

mv /var/lib/mysql /var/lib/mysql_backup
如果不需要备份直接删除也可以

还原数据库

xtrabackup --copy-back --target-dir=/data/dbbackup/20221009

修正文件权限

chown -R mysql:mysql /var/lib/mysql

查看并记下备份到的LOG_FILE和LOG_POS，后面设置用

1
2
cd /data/dbbackup/20221009
cat xtrabackup_binlog_info

显示类似
mysql-bin.000891 408016029

启动从服务器数据库

service mysql start

连上从数据库重设SLAVE

mysql -h127.0.0.1 -P3306 -uroot -p

修改其中的MASTER_LOG_FILE & MASTER_LOG_POS为前面查看得到的

1
2
3
4
5
6
7
8
CHANGE MASTER TO
MASTER_HOST='192.168.1.140',
MASTER_USER='repl',
MASTER_PASSWORD='4Bd9x25ERcacAqwX',
MASTER_LOG_FILE='mysql-bin.000891',
MASTER_LOG_POS=408016029;

START SLAVE;

查看本机作为从库的状态
SHOW SLAVE STATUS\G
关注下面3个字段

1
2
3
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

Slave_IO_Running & Slave_SQL_Running 应该都是Yes，Seconds_Behind_Master可能为一个正数表示从库落后主库的秒数，开启一段时间后应该会变成0。

PS：
1）mysql-bin文件在主服务器上是不断增加的，会保留一段时间旧的，一段时间后自动删除，在还原并配置从服务器时，要保证主服务器上的MASTER_LOG_FILE还存在才能成功，后面可以有新的文件会自动同步过来，所以在备份还原期间不用停止或锁住主数据库。
2）备份前、复制到远程前、还原前，都应该确保有足够的磁盘空间。
使用df - h来查看磁盘剩余空间，使用du -sh来查看当前文件夹占用空间。
3）如果还原时遇到 Error: copy_file() failed. 可以用md5sum校验复制的文件是否损坏，如果两边md5sum不一致或读取错误无法计算md5sum则说明损坏了。这时需要重新copy损坏的文件并重新执行恢复命令。
4）root在localhost的密码和在%的密码可能不同，连接mysql时分别尝试不指定-h或指定-h127.0.0.1来连接。
5）主从同步出错修复时跳过修改配置文件的步骤，确认备份点的MASTER_LOG_FILE是否存在，若存在也可跳过备份步骤，直接STOP、还原、START、STOP SLAVE; CHANGE MASTER……; START SLAVE;
6) 如果Seconds_Behind_Master不断增大，检查主库和从库的数据库版本，最好主次版本都完全一致，如果不一致，从库应该高于主库，而不能让主库高于从库。