posts - 210, comments - 61, trackbacks - 0, articles - 0
   :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

MySQL 5.7 建立主从备份

Posted on 2020-01-13 14:47 魔のkyo 阅读(84) 评论(0)  编辑 收藏 引用
MySQL 5.7 建立主从备份
主服务器上编辑配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
找到server-id附近的代码,修改为
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
主服务器锁表(mysql命令行中)
连上数据库
mysql -h127.0.0.1 -P3306 -uroot -p
flush tables with read lock;
这一步不是必须要做
备份数据库
xtrabackup --backup --host=127.0.0.1 --user=root --password=****** --target-dir=/home/dbbackup/20200111
复制到远程
注意源路径写的是文件或文件夹
rsync -avpP -e ssh /home/dbbackup/20200111 root@dai.dsxqp.com:/home/dbbackup/
在从服务器上
vim /etc/mysql/mysql.conf.d/mysqld.cnf
找到server-id附近的代码,修改为
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 #不同步系统数据库
read_only #设只读权限
准备恢复(这步并没有连接数据库,只是对备份文件做一些设置,在主或从上执行都可以)
xtrabackup --prepare --user=root --password=****** --target-dir=/home/dbbackup/20200111
停止从服务器数据库
service mysql stop
清空/var/lib/mysql目录,顺便备份
mv /var/lib/mysql /home/dbbackup/var_lib_mysql_backup
还原数据库
xtrabackup --copy-back --target-dir=/home/dbbackup/20200111
修正权限
chown -R mysql:mysql /var/lib/mysql
查看并记下备份到的LOG_FILE和LOG_POS,后面设置用
cd /home/dbbackup/20200111
cat xtrabackup_binlog_info
显示类似
mysql-bin.000891 408016029
启动从服务器数据库
service mysql start
连上数据库
mysql -h127.0.0.1 -P3306 -uroot -p
停止SLAVE
STOP SLAVE;
重设SLAVE参数
修改其中的MASTER_LOG_FILE & MASTER_LOG_POS为前面查看得到的
CHANGE MASTER TO
MASTER_HOST='192.168.1.140',
MASTER_USER='repl',
MASTER_PASSWORD='******',
MASTER_LOG_FILE='mysql-bin.000891',
MASTER_LOG_POS=408016029;
开启SLAVE
START SLAVE;
查看从库状态
SHOW SLAVE STATUS\G
关注下面3个字段
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Slave_IO_Running & Slave_SQL_Running 应该都是Yes,Seconds_Behind_Master可能为一个正数表示从库落后主库的秒数,开启一段时间后应该会变成0。
回到主服务器解锁表(如果前面锁过的话)
unlock tables;
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;
只有注册用户登录后才能发表评论。