安装xtrabackup工具
yum install https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.18/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm -y
/usr/bin/innobackupex --version
xtrabackup: recognized server arguments: --datadir=/data/mysql/data/ --server-id=6
innobackupex version 2.4.18 Linux (x86_64) (revision id: 29b4ca5)
使用xtrabackup备份数据
innobackupex --defaults-file=/etc/opt/rh/rh-mysql56/my.cnf --user=root --password='123456' --port=3306 --socket=/usr1/mysql/mysqltmp/mysql.sock --stream=tar /usr1/Data_Backup/DATA/bak/ |gzip - > 127.16.18.8.tar.gz
把备份数据导入到从库
scp -r root@127.16.18.8:/usr1/Data_Backup/DATA/127.16.18.8.tar.gz /data/bak/
tar zxvf 127.16.18.8.tar.gz -C /data/bak/bak/
关闭数据库并修改数据库文件
systemctl stop mysqld
mv mysql mysql_bak
恢复数据库
innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /data/bak/bak/
innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /data/bak/bak/
数据目录授权
chown -R mysql.mysql mysql
启动数据库
systemctl start mysqld
主库创建用于创建备份的账号
/usr/local/mysql/bin/mysql -uroot -e 'grant all privileges on *.* to 'slave_admin'@'%' identified by '123456' with grant option;'
在从库中设置同步复制
change master to master_host='127.16.18.8',master_user='slave_admin',master_password='123456',master_port = 3306,master_log_file='mysql-bin.000009',master_log_pos=232249604;
备注
1.注意主从数据库的server-id号,从库必须不能和主库的ID号相同
2.master_log_file,master_log_pos记录在文件xtrabackup_info,xtrabackup_binlog_info