(1)安装Percona XtraBackup8.0
注:在服务器能够上网的情况下
[root@manager ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@manager ~]# yum list | grep percona
[root@manager ~]# yum install percona-xtrabackup-80.x86_64
如果不能连互联网:
传入Percona XtraBackup8.0包
[root@manager ~]# tar -xzf percona-xtrabackup-8.0.22-15-Linux-x86_64.glibc2.17.tar.gz
[root@manager ~]# cd percona-xtrabackup-8.0.22-15-Linux-x86_64.glibc2.17/bin
(2)在主节点上备份
登录数据库,创建备份用户,并授予权限:
mysql>CREATE USER ‘xtrabk’@’127.0.0.1(允许哪些机子用此用户登录)’ IDENTIFIED BY ‘设置用户密码’;
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON . TO ‘xtrabk’@‘127.0.0.1’;
mysql> GRANT SELECT ON performance_schema.log_status TO ‘xtrabk’@‘127.0.0.1’;
mysql> FLUSH PRIVILEGES;
注:如果使用root备份,提示没有权限。需要进入数据库进行授权,如果是新创建的用户就不会报错!
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON . TO ‘root’@’%’;
GRANT SELECT ON performance_schema.log_status TO ‘root’@’%’;
FLUSH PRIVILEGES;
./xtrabackup --defaults-file=配置文件路径 --user=备份用户 --password=‘用户密码’ --port=数据库服务端口 --backup --target-dir=备份到哪个目录下
(最后显示:190802 15:52:55 completed OK!说明全量备份已成功完成)
(3)把备份的整个目录拷贝到从服务器上
[root@MHA_Node22 data]# systemctl stop mysqld
[root@MHA_Node22 data]# mkdir mysql 在从服务器的数据盘目录下创建一个mysql目录用于恢复数据
[root@MHA_Node22 data]# chown -R mysql:mysql mysql
[root@MHA_Node22 data]#./ xtrabackup --prepare --apply-log-only --target-dir=/data/backup
[root@MHA_Node22 data]#./ xtrabackup --prepare --target-dir=/data/backup
[root@MHA_Node22 data]# xtrabackup --host=192.168.1.64 --user= xtrabk --password=‘Sccin1qazCDE#’ --host=127.0.0.1 --port=3306 --datadir=/data/mysql/ --copy-back --target-dir=/data/mysql
[root@MHA_Node22 data]#chown -R mysql:mysql /data/mysql/
[root@MHA_Node22 data]#systemctl start mysqld
[root@MHA_Node22 data]# cat xtrabackup_binlog_pos_innodb (mysql-bin.000282 40070778)记录下主读取的binlog日志名和POS偏移量的值
[root@MHA_Node22 data]#mysql -uroot -p
mysql> change master to master_host = ‘192.168.10.61’, master_user = ‘repl’, master_port=3306, master_password=’’, master_log_file = ‘mysqld-bin.000282’, master_log_pos=40070778;
mysql> start slave;
mysql> show slave status\G
注意:如果显示Slave_IO_Running: NO Slave_SQL_Running: Yes 或者错误信息是Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’ 需要进行以下操作
mysql> stop slave;
mysql> reset slave;
mysql> start slave;
如果出现1032跟delete语句有关或者是1062错误主键重复的话可以执行以下操作
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
mysql> Show slave status\G
因为重做,可能有些sql语句有重复执行会执行不了,如果1032和1062的错误比较多可以在主配置文件添加slave-skip-errors=1062,1032 此参数跳过这2个错误