mysql

使用Xtrabackup在线配置MySQL主从复制

Posted by Sunday on 2019-02-19

使用percona-xtrabackup备份数据库，实现不停机不锁表配置MySQL主从复制

安装

1
2
3
dpkg -i https://mirrors.tuna.tsinghua.edu.cn/percona/apt/percona-release_latest.xenial_all.deb 
sed -i 's#http://www.percona.com#https://mirrors.tuna.tsinghua.edu.cn#g' /etc/apt/sources.list.d/percona-release.list
apt-get install percona-xtrabackup-24

备份

备份指定库 （多库用空格隔开）

1
2
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root \
--password=xxx --parallel=4 --databases="db1 db2" /root/backup

备份所有库

1
2
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root \
--password=xxx --parallel=4  /root/backup
1
2
3
ls -l /root/backup/
total 4
drwxr-x--- 6 root root 4096 Jan 30 01:01 2019-01-30_00-57-25

保持事务一致性

1
innobackupex --apply-log /root/backup/2019-01-30_00-57-25/

传输

1
scp -r -P 25680 2019-01-30_00-57-25 sunday@192.168.1.91:/home/sunday/

从库恢复

1
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password xxx --datadir=/data/mysql/3306/data --copy-back /tmp/backup/2019-01-30_00-57-25/

修复权限

1
chown -R mysql.mysql /data/mysql/3306

重启从数据库

1
/etc/init.d/mysql restart

主从复制配置

主库授权

1
grant replication slave on *.* to slave@'192.168.1.9%' identified by 'slave';

从库查看xtrabackup_binlog_info gtid_purged

1
2
3
cat /data/mysql/3306/xtrabackup_binlog_info 
mysql-bin.000033    71601523    4d83ee2d-11ad-11e9-953c-1866dae7c89c:1-4522979,
6c679363-11a5-11e9-8b86-1866dae7c89c:1-2

从库修改GTID_PURGE

1
2
3
mysql > reset slave all;
mysql > reset master;
mysql > SET GLOBAL GTID_PURGED='4d83ee2d-11ad-11e9-953c-1866dae7c89c:1-4522979,6c679363-11a5-11e9-8b86-1866dae7c89c:1-2'

从库连接主库

1
2
3
change master to master_host='192.168.1.91',master_user='slave',master_password='slave',master_port=3306,master_auto_position=1;
start slave;
show slave status\G;

从库注意修改参数

如忽略指定库和开启只读模式

1
2
3
4
5
replicate_wild_ignore_table = sys.%
replicate_wild_ignore_table = information_schema.%
replicate_wild_ignore_table = performance_schema.%
read_only=on
super_read_only=on

https://wsgzao.github.io/post/xtrabackup/

CATALOG
    FEATURED TAGS
    mysql
    FRIENDS