采用xtrabackup部署主从同步

1.停掉从库
/opt/mysql57_slave/bin/mysqladmin -h localhost -uroot -P13306 -pmysql -S /opt/mysql57_slave/mysql.sock shutdown

 

2.主库上创建同步账号

grant replication slave, replication client on *.*  to repl@'192.168.1.%' identified by 'mysql';

 

2.主库全备份

root账号下操作
cd /opt/xtrabackup247/bin
./innobackupex --defaults-file=/opt/mysql57/conf/my.cnf --user=root --socket=/tmp/mysql.sock  --password=ptesttest -P3306 /opt/xtrabackup_file/


备份完成后会有提示ok,如下:
190124 10:50:07 Executing UNLOCK TABLES
190124 10:50:07 All tables unlocked
190124 10:50:07 Backup created in directory '/opt/xtrabackup_file/2019-01-24_10-46-06/'
MySQL binlog position: filename 'binlog.000011', position '587445616'
190124 10:50:07 [00] Writing backup-my.cnf
190124 10:50:07 [00] ...done
190124 10:50:07 [00] Writing xtrabackup_info
190124 10:50:07 [00] ...done
xtrabackup: Transaction log of lsn (296778142671) to (296778142701) was copied.
190124 10:50:07 completed OK!

完成备份后会以日期生成备份目录,我们把该目录tar并scp到远程备份服务器
[root@dev-env-22 xtrabackup_file]# tar -cvf /home/xtrabackup0124.tar ./2019-01-24_10-46-06/
[root@dev-env-22 home]# scp xtrabackup0124.tar root@192.168.1.23:/home/

 

----------------------------备库恢复------------------------
1.备库解压缩
[root@dev-env23 home]# cd /home
[root@dev-env23 home]# tar -xvf xtrabackup0124.tar

2.清空原来的数据文件目录和日志目录
查看/etc/my.cnf配置文件找到数据文件目录

datadir=/home/mysql/data
relay_log=/home/mysql/logs/relay-bin

 

我这里是采用重建目录的方式
[root@dev-env23 mysql]# mv data bakdata
[root@dev-env23 mysql]# mkdir data
[root@dev-env23 mysql]# mv logs baklogs
[root@dev-env23 mysql]# mkdir logs

 

3.恢复(备库也事先安装好xtrabackup软件)
cd /opt/xtrabackup247/bin
./innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /home/2019-01-24_10-46-06
这步完成后也有ok提示
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 296778145738
190124 11:08:12 completed OK!

./innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back --rsync /home/2019-01-24_10-46-06
完成后有ok提示
190124 11:11:58 [01] ...done
190124 11:11:58 completed OK!

4.修改权限
[root@dev-env23 mysql]#cd /home/mysql
[root@dev-env23 mysql]#chown -R mysql:mysql ./data
[root@dev-env23 mysql]#chown -R mysql:mysql ./logs

 

5.启动数据库
[root@dev-env23 mysql]# service mysqld start
190124 11:14:14 mysqld_safe error: log-error set to '/home/mysql/logs/mysqld.log', however file don't exists. Create writable for user 'mysql'.
MySQL Daemon failed to start.
Starting mysqld: [FAILED]
[root@dev-env23 mysql]#

提示没有mysqld.log日志文件,创建即可
[root@dev-env23 mysql]# cd /home/mysql/logs
[root@dev-env23 logs]# echo>mysqld.log
[root@dev-env23 logs]# chown mysql:mysql mysqld.log

 

再次启动
[root@dev-env23 logs]# service mysqld start
Starting mysqld: [ OK ]
[root@dev-env23 logs]#

 

6.找到主从同步的参数值
master_log_file和master_log_pos
这两个参数可以在xtrabackup备份目录下的文件中查找到

[root@dev-env23 data]# cd /home/2019-01-24_10-46-06

[root@host02 2017-09-18_15-53-15]# more xtrabackup_binlog_info
[root@dev-env23 2019-01-24_10-46-06]# more xtrabackup_binlog_info
binlog.000011 587445616

7.执行主从复制
[root@dev-env23]# mysql -h localhost -uroot -p123456
change master to master_host='192.168.1.22',
master_port=3306,
master_user='repl',
master_password='repl',
master_log_file='binlog.000011',
master_log_pos=587445616;

 

8.启动复制
start slave