使用innobackupex基于从库搭建mysql主从架构

现有的架构是一主一从，版本为Mysql5.6.37。实施要求是：利用从库，搭建第二个从库，版本为5.7.21

主库：192.168.1.21 

从库：192.168.1.22

从库2：192.168.1.23

1、备份从库1

[root@mysql02 ~]./innobackupex --user=root --password="xxxxxx" --slave-info --safe-slave-backup --parallel=3 /data/backup/

#参数slave-info 用于GTID

#复制my.cnf文件到新从库

[root@mysql02 ~]$scp /etc/my.cnf 192.168.1.23:/data/backup

2、主库授予从库2复制账户

master@MySQL> grant replication slave,replication client on *.* to repl@'192.168.1.23' identified by 'repl';

3、从库2：进行恢复

[root@mysql03 ~]$./innobackupex --user=root --password="xxxxxx" --apply-log --use-memory=2G /data/backup/2018-08-10_17-29-01

./innobackupex --defaults-file=/etc/my.cnf  --user=root --password="xxxxxx"  --socket=/data/mysql_33061/run/mysql.sock --copy-back  /data/backup/2018-08-10_17-29-01

或者：

[root@mysql03 ~]$mv /data/backup/2018-08-10_17-29-01 /data/mysql_3306

4、准备从库2配置文件my.cnf,根据需要修改相应参数，这里的修改如下，

skip-slave-start

datadir = /data/mysql_3306

port = 3306

server_id = 330623

socket = /data/mysql_3306/mysql.sock

pid-file=/data/mysql_3306/mysql3307.pid

log_error=/data/mysql_3306/mysql_error.log

5、启动从库2

# chown -R mysql:mysql /data/backup

# mysqld_safe --defaults-file=/etc/my.cnf &

6、运行mysql时，提示Table ‘performance_schema.session_variables’ doesn’t exist

./mysql_upgrade -hlocalhost -u root -p --force -P3306 --socket=/data/mysql_3306/mysql.sock

重启数据库

7、change master

mysql> system more /log/recover/xtrabackup_slave_info

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000658', MASTER_LOG_POS=925384099

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.1.22',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='repl',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='mysql-bin.000658',

-> MASTER_LOG_POS=925384099;

Query OK, 0 rows affected, 2 warnings (0.31 sec)

mysql> start slave;

在从库2，start slave时报：error:

ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

经分析，需要：

删除5张表，并重新导入脚本

use mysql

drop table slave_master_info;

drop table slave_relay_log_info;

drop table slave_worker_info;

drop table innodb_index_stats;

drop table innodb_table_stats;

检查表数据，为空表，重建表

source /backup/mysql_system_tables.sql

(2)重新启动数据库

mysql> start slave;

mysql> show slave status\G;

 一主一从一级联，搭建完成。

 

未开启GTID情况下，从 一主一从一级联，调整为一主两从

1：在从库一：停掉SQL线程、IO线程，并记录下此时从库1 的master status

mysql>stop slave;

mysql> show master status;

+------------------+-----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+-----------+--------------+------------------+-------------------+

| mysql-bin.000658| 925384099| | | |

+------------------+-----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

2：在从库2,show slave status；观察Master_Log_File  Exec_Master_Log_Pos与从库1中show master status;一致时，在从库1：show slave status\G;记录下Master_Log_File  Exec_Master_Log_Pos

3：然后在从库2，即指向主库：

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.1.21', 

-> MASTER_USER='repl', 

-> MASTER_PASSWORD='repl',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='mysql-bin.000100',

-> MASTER_LOG_POS=384099;

Query OK, 0 rows affected, 2 warnings (0.31 sec)

mysql> start slave;

4：开启从库1的SQL线程、IO线程

此时，一主两从搭建成功；