环境基于MySQL5.7之后版本
什么是GTID
从MYSQL5.6 开始,mysql开始支持GTID复制。基于日志点复制的缺点:从那个二进制日志的偏移量进行增量同步,如果指定错误会造成遗漏或者重复,导致数据不一致。
1.从服务器会告诉主服务器已执行的事务的GTID值。
2.主库会告诉从哪些GTID事务没有被执行。
同一个事务在指定的从库执行一次。
模拟图
步骤
1,配置主数据库参数
bin_log=/var/lib/mysql/mysql-bin
server_id=100
gtid_mode=on
enforce-gtid-consistency=on #配置后不能用create table ... select。强制事务一致性,保证事务的安全
log-slave-updates=on #MySQL5.7默认开启,之前的版本需要打开
2,配置从数据库参数
server_id=101
relay_log=/var/lib/mysql/relay_log
gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=on
read_only=on #建议
master_info_repository=TABLE #建议
relay_log_info_repository=TABLE #建议
3,在主DB服务器上建立复制帐号
在主库执行命令
create user repl@'192.168.31.%' identified by 'repl';
grant replication slave on *.* to repl@'192.168.31.%';
4,创建测试数据库
mysql> desc tt;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(120) | NO | | NULL | |
| phone | char(20) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
5,备份测试数据库和拷贝到从服务器
[root@localhost ~]# mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot > /home/test1/test.sql
--single-transaction
对innodb引擎进行热备,必须加(一个事务中导出数据,确保产生一致性的备份数据)
–master-data
告诉你备份后时刻的binlog位置
如果等于1,则将其打印为CHANGE MASTER命令,记录对应二进制位置; 如果等于2,那么该命令将以注释符号为前缀,单纯的想获取二进制日志文件的位置。
简单理解就是可以自动帮我们锁表和识别binlog临界文件,就不需要我们锁表。再看临界文件编号,再执行CHANGE MASTER填写binglong位置信息到从库master.info文件中了,提高了从库部署效率。
--triggers
备份触发器数据
--routines, -R
导出存储过程以及自定义函数。
[root@192 ~]# scp /home/test1/test.sql 192.168.143.129:/home/love/
6,配置主从复制
mysql> change master to master_host='192.168.143.154',
-> master_user='repl',
-> master_password='123',
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
7,开启复制链路
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.143.154
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1453
Relay_Log_File: mysql_relay_log.000002
Relay_Log_Pos: 943
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1453
Relay_Log_Space: 1151
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 69132615-8186-11ea-9c53-000c292c0c18
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 69132615-8186-11ea-9c53-000c292c0c18:4-5
Executed_Gtid_Set: 69132615-8186-11ea-9c53-000c292c0c18:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
8,测试在主库插入一条数据,看从库是否链路复制成功
主库插入;
mysql> insert into tt values(2,'xiao',33333333);
Query OK, 1 row affected (0.00 sec)
从库查看
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from tt;
+----+-------+----------+
| id | name | phone |
+----+-------+----------+
| 1 | huang | 88888888 |
| 2 | xiao | 33333333 |
+----+-------+----------+
2 rows in set (0.00 sec)
其他
设置多线程
1,停止链路
stop slave
2,设置基于逻辑时钟(mysql5.7)
mysql> show variables like 'slave_parallel_type';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.30 sec)
mysql> set global slave_parallel_type='logical_clock';
Query OK, 0 rows affected (0.00 sec)
3,查看默认是多少个并发线程并设置成4个线程
mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> set global slave_parallel_workers=4;
Query OK, 0 rows affected (0.00 sec)
4,启动链路
start slave