实验场景描述及目的:
GTID的主从模式,目前是一主一从,希望再加一个slave做成一主两从的模式。由于测试的数据库不大,通过mysqldump备份数据,如果数据库比较大的情况下,可以想办法通过其它slave的备份来搭建新的slave。
1.备份master
-
mysql> FLUSH TABLE WITH READ LOCK;
-
Query OK, 0 rows affected (0.01 sec)
[root@qht131 backup]# mysqldump -u root -p --lock-all-tables --master-data=2 --flush-logs --all-databases --triggers --routines --events > full.sql
[root@qht131 backup]# scp full.sql 172.17.61.133:/u01/backup
记录当前的gtid:
-
mysql> show global variables like 'gtid_%';
-
+----------------------------------+-------------------------------------------+
-
| Variable_name | Value |
-
+----------------------------------+-------------------------------------------+
-
| gtid_executed | 8d8746fb-2cc6-11e8-b1b6-000c295c63e0:1-11 |
-
| gtid_executed_compression_period | 1000 |
-
| gtid_mode | ON |
-
| gtid_owned | |
-
| gtid_purged | |
-
+----------------------------------+-------------------------------------------+
-
5 rows in set (0.09 sec)
如果是Xtrabackup的话,Xtrabackup_binlog_info文件中包含global.gtid_purged='XXXXXX:XXXX'的信息。
2.在新的slave上建好的数据库并恢复Master的备份
2.1.建库方法:https://blog.csdn.net/jolly10/article/details/79566640
2.2.恢复全备:
[root@qht133 backup]# mysql -uroot -p < full.sql
2.3新slave上修改/etc/my.cnf,开启gtid
-
[root@qht133 backup]# cat /etc/my.cnf
-
[mysqld]
-
socket = /usr/local/mysql/mysql.sock
-
character_set_server= utf8
-
init_connect= 'SET NAMES utf8'
-
basedir= /usr/local/mysql
-
datadir= /u01/mysql
-
socket = /u01/mysql/mysql.sock
-
log-error= /u01/log/mysql/mysql_3306.err
-
pid-file= /u01/mysql/mysqld.pid
-
lower_case_table_names = 1
-
sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
-
secure-file-priv = /u01/backup
-
gtid_mode=on #开启gtid
-
enforce_gtid_consistency=on #强制gtid一致性
-
server-id=10003 #server id
-
skip_slave_start=1 #开启备库后不自动应用恢复进程
-
#log_bin = /u01/mysql/mysql_bin
-
#skip-grant-tables
-
#innodb_flush_log_at_trx_commit=1
-
#sync_binlog=1
-
relay-log=/u01/mysql/slave_relay_bin
-
expire_logs_days=10
-
relay_log_recovery=on
-
relay_log_info_repository=TABLE
重启数据库:
-
[root@qht133 backup]# service mysql restart
-
Shutting down MySQL.. [ OK ]
-
Starting MySQL.. [ OK ]
3.为了检验新的slave复制的正确性,在master上做些操作,看slave能否正确的同步。
-
mysql> use l5m
-
Database changed
-
mysql> select count(*) from test_emp;
-
+----------+
-
| count(*) |
-
+----------+
-
| 1099000 |
-
+----------+
-
1 row in set (0.67 sec)
-
mysql> delete from test_emp limit 1000;
-
Query OK, 1000 rows affected (0.08 sec)
-
mysql> select count(*) from test_emp;
-
+----------+
-
| count(*) |
-
+----------+
-
| 1098000 |
-
+----------+
-
1 row in set (0.18 sec)
-
mysql> commit;
-
Query OK, 0 rows affected (0.00 sec)
-
mysql> show global variables like 'gtid_%';
-
+----------------------------------+-------------------------------------------+
-
| Variable_name | Value |
-
+----------------------------------+-------------------------------------------+
-
| gtid_executed | 8d8746fb-2cc6-11e8-b1b6-000c295c63e0:1-12 |
-
| gtid_executed_compression_period | 1000 |
-
| gtid_mode | ON |
-
| gtid_owned | |
-
| gtid_purged | |
-
+----------------------------------+-------------------------------------------+
-
5 rows in set (0.01 sec)
4.在新的slave上开启复制:
先检查一下当前的gtid:
-
mysql> show global variables like 'gtid_%';
-
+----------------------------------+-------------------------------------------+
-
| Variable_name | Value |
-
+----------------------------------+-------------------------------------------+
-
| gtid_executed | 8d8746fb-2cc6-11e8-b1b6-000c295c63e0:1-11 |
-
| gtid_executed_compression_period | 1000 |
-
| gtid_mode | ON |
-
| gtid_owned | |
-
| gtid_purged | 8d8746fb-2cc6-11e8-b1b6-000c295c63e0:1-11 |
-
+----------------------------------+-------------------------------------------+
-
5 rows in set (0.00 sec)
新的slave库是从master恢复过来的,gtid_purged现在自动就有了值1-11,并不需要手动的执行reset master; set global gtid_purged = 'xxxxx',看来mysql5.7这方面又加强了!(只有在@@global.gtid_executed为空的情况下,才可以动态设置@@global.gtid_purged。因此可以通过RESET MASTER的方式来清空@@global.gtid_executed。)
下面直接开启复制就行了:
-
mysql> change master to
-
-> master_host='172.17.61.131',
-
-> master_port=3306,
-
-> master_user='repl',
-
-> master_password='repl',
-
-> master_auto_position=1;
-
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
-
mysql> show global variables like 'gtid_%';
-
+----------------------------------+-------------------------------------------+
-
| Variable_name | Value |
-
+----------------------------------+-------------------------------------------+
-
| gtid_executed | 8d8746fb-2cc6-11e8-b1b6-000c295c63e0:1-12 |
-
| gtid_executed_compression_period | 1000 |
-
| gtid_mode | ON |
-
| gtid_owned | |
-
| gtid_purged | 8d8746fb-2cc6-11e8-b1b6-000c295c63e0:1-12 |
-
+----------------------------------+-------------------------------------------+
-
5 rows in set (0.01 sec)
验证一下全备之后master上修改的数据有没有同步过来:
-
mysql> use l5m;
-
Database changed
-
mysql> select count(*) from test_emp;
-
+----------+
-
| count(*) |
-
+----------+
-
| 1098000 |
-
+----------+
-
1 row in set (0.40 sec)
没有问题,数据都同步到了新的slave。
参考:
https://www.cnblogs.com/zejin2008/p/7705473.html
http://www.cnblogs.com/luckcs/articles/6295992.html