Mysql: 利用Xtrabackup搭建从库

STEP 1: 生成备份并prepare

主库做一个全备，做好看到 completed OK 表明备份成功

# innobackupex --defaults-file=/etc/my.cnf --user root --password XXX --no-timestamp /home/ssd/ali_backup/full_xtra_3306_20160825_1
...
160825 14:57:41 completed OK!

为了保证备份的数据一致性，需要prepare

# innobackupex --defaults-file=/etc/my.cnf --user root --password XXX --apply-log /home/ssd/ali_backup/full_xtra_3306_20160825_1
。。。
160825 15:15:04 completed OK!

STEP 2: 把备份传到备机上

压缩打包

# zip -r full_xtra_3306_20160825_1.zip full_xtra_3306_20160825_1/*

把备份从master传到slave

# scp full_xtra_3306_20160825_1.zip db2:/home/oradata/ali_data_candicate

修改slave上datadir的权限

# chown -R mysql:mysql ali_data_candicate

STEP 3:master节点上创建复制用户

创建用于主从复制的用户，并赋予合适的权限

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'db2' identified by 'XXX';

在slave上校验创建的用户

# mysql -hdb1 -urepl -pXXX
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+-------------------------------------------------------------------------------------------+
| Grants for repl@192.168.201.99                                                            |
+-------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.201.99' IDENTIFIED BY PASSWORD <secret> |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

STEP 4: slave节点配置

拷贝一份配置文件到slave

# scp /etc/my.cnf db2:/etc/

在slave编辑配置文件，修改相关路径，注意server_id要与主节点不同

server_id=201993306

slave启动mysql

STEP 5: 开启复制

查看xtrabackup_binlog_info确定复制开始position

# more xtrabackup_binlog_info
3306-mysql-bin.000002   191     87fd24be-683d-11e6-ba97-1418774c98d8:49-53

在slave节点执行CHANGE MASTER语句

CHANGE MASTER TO
MASTER_HOST = 'db1',
MASTER_PORT = 3306,
MASTER_USER = 'repl',
MASTER_PASSWORD = 'repl',
MASTER_AUTO_POSITION = 1;

跳过已经执行过的事务

mysql> set global gtid_purged='87fd24be-683d-11e6-ba97-1418774c98d8:49-53';
Query OK, 0 rows affected (0.00 sec)

开始复制

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

STEP 6: 检查

mysql> show slave status\G;
。。。
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes