MySQL GTID主从复制

一、GTID(Global Transaction ID)

1、概述

GTID是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。
GTID = server_uuid : transaction_id

核心特性：全局唯一，具备幂等性。 GTID优势：在主从复制中开启GTID可以实现多事务并行传输，多线程复制。

#UUID
[root@db03 ~]# cat /data/mysql/3306/data/auto.cnf 
[auto]
server-uuid=3386190b-cedd-11e9-a862-000c2936acca

2、核心参数

gtid-mode=on #启用GTID类型主从架构(不开启为普通主从复制架构) enforce-gtid-consistency=true #强制GTID一致性 log-slave-updates=1 #从库更新将GTID写入日志(MHA必要参数)

3、基于GTID的主从复制部署流程

step 1 清理系统环境

pkill mysqld #停止mysql服务进程 rm -rf 数据目录/* #清空数据目录 rm -rf 日志目录/* #清空日志目录

[root@db03 ~]# pkill mysqld;
[root@db03 ~]# ps -ef | grep mysqld
root       8327   8069  0 14:05 pts/0    00:00:00 grep --color=auto mysqld
[root@db03 ~]# rm -rf /data/mysql/3306/data/*
[root@db03 ~]# ll /data/mysql/3306/data/
total 0
[root@db03 ~]# rm -rf /data/mysql/3306/logs/*
[root@db03 ~]# ll /data/mysql/3306/logs/
total 0

step 2 准备配置文件

[mysqld] basedir=服务目录 datadir=数据目录 socket=socket文件 server_id=id port=端口号 autocommit=0 log_bin=二进制日志 binlog_format=二进制日志格式 log_error=错误日志 slow_query_log=ON slow_query_log_file=慢日志 long_query_time=时间 log_queries_not_using_indexes=ON innodb_flush_log_at_trx_commit=1 innodb_buffer_pool_size=1G innodb_flush_method=O_DIRECT #transaction_isolation=READ-COMMITTED gtid-mode=on enforce-gtid-consistency=true log-slave-updates=1 [mysql] socket=/tmp/mysql.sock

[root@db03 ~]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=153
port=3306
autocommit=0
log_bin=/data/mysql/3306/logs/sql-binlog
binlog_format=row
log_error=/data/mysql/3306/logs/db_err.log
slow_query_log=ON
slow_query_log_file=/data/mysql/3306/logs/db_slow.log
long_query_time=1
log_queries_not_using_indexes=ON
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_buffer_pool_size=1G
innodb_flush_method=O_DIRECT
#transaction_isolation=READ-COMMITTED
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
socket=/tmp/mysql.sock

[root@db04 ~]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=154
port=3306
autocommit=0
log_bin=/data/mysql/3306/logs/sql-binlog
binlog_format=row
log_error=/data/mysql/3306/logs/db_err.log
slow_query_log=ON
slow_query_log_file=/data/mysql/3306/logs/db_slow.log
long_query_time=1
log_queries_not_using_indexes=ON
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_buffer_pool_size=1G
innodb_flush_method=O_DIRECT
#transaction_isolation=READ-COMMITTED
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
socket=/tmp/mysql.sock

[root@db05 ~]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=155
port=3306
autocommit=0
log_bin=/data/mysql/3306/logs/sql-binlog
binlog_format=row
log_error=/data/mysql/3306/logs/db_err.log
slow_query_log=ON
slow_query_log_file=/data/mysql/3306/logs/db_slow.log
long_query_time=1
log_queries_not_using_indexes=ON
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_buffer_pool_size=1G
innodb_flush_method=O_DIRECT
#transaction_isolation=READ-COMMITTED
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
socket=/tmp/mysql.sock

step 3 初始化数据库

mysqld --initialize-insecure --user=mysql --basedir=服务目录 --datadir=数据目录

[root@db03 ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/3306/data/
[root@db03 ~]# ll /data/mysql/3306/data/
total 110628
-rw-r----- 1 mysql mysql       56 Sep  6 15:39 auto.cnf
-rw-r----- 1 mysql mysql      423 Sep  6 15:39 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Sep  6 15:39 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep  6 15:39 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep  6 15:39 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Sep  6 15:39 mysql
drwxr-x--- 2 mysql mysql     8192 Sep  6 15:39 performance_schema
drwxr-x--- 2 mysql mysql     8192 Sep  6 15:39 sys

[root@db04 ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/3306/data/
[root@db04 ~]# ll /data/mysql/3306/data/
total 110628
-rw-r----- 1 mysql mysql       56 Sep  6 15:43 auto.cnf
-rw-r----- 1 mysql mysql      423 Sep  6 15:43 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Sep  6 15:43 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep  6 15:43 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep  6 15:43 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Sep  6 15:43 mysql
drwxr-x--- 2 mysql mysql     8192 Sep  6 15:43 performance_schema
drwxr-x--- 2 mysql mysql     8192 Sep  6 15:43 sys

[root@db05 ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/3306/data/
[root@db05 ~]# ll /data/mysql/3306/data/
total 110628
-rw-r----- 1 mysql mysql       56 Sep  6 15:44 auto.cnf
-rw-r----- 1 mysql mysql      423 Sep  6 15:44 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Sep  6 15:44 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep  6 15:44 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep  6 15:44 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Sep  6 15:44 mysql
drwxr-x--- 2 mysql mysql     8192 Sep  6 15:44 performance_schema
drwxr-x--- 2 mysql mysql     8192 Sep  6 15:44 sys

step 4 启动数据库

systemctl start mysqld

[root@db03 ~]# cat /etc/systemd/system/mysqld.service 
[unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
[root@db03 ~]# ps -ef | grep mysqld
root       8224   7327  0 15:41 pts/0    00:00:00 grep --color=auto mysqld
[root@db03 ~]# systemctl start mysqld
[root@db03 ~]# ps -ef | grep mysqld
mysql      8231      1  9 15:41 ?        00:00:00 /application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root       8263   7327  0 15:42 pts/0    00:00:00 grep --color=auto mysqld

[root@db04 ~]# cat /etc/systemd/system/mysqld.service 
[unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
[root@db04 ~]# systemctl start mysqld.service 
[root@db04 ~]# ps -ef |grep mysqld
mysql      7832      1  1 15:46 ?        00:00:00 /application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root       7864   7321  0 15:46 pts/0    00:00:00 grep --color=auto mysqld

[root@db05 ~]# cat /etc/systemd/system/mysqld.service 
[unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
[root@db05 ~]# systemctl start mysqld.service 
[root@db05 ~]# ps -ef | grep mysqld
mysql      7819      1  2 15:47 ?        00:00:00 /application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root       7851   7309  0 15:47 pts/0    00:00:00 grep --color=auto mysqld

step 5 构建主从

MASTER_AUTO_POSITION=1

[root@db03 ~]# mysql -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '456';"
[root@db03 ~]# mysql -e "select user,host from mysql.user;"
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| repl          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

[root@db04 ~]# mysql -e "CHANGE MASTER TO MASTER_HOST='10.0.0.153',MASTER_USER='repl',MASTER_PASSWORD='456',MASTER_PORT=3306,MASTER_AUTO_POSITION=1,MASTER_CONNECT_RETRY=10;"
[root@db04 ~]# mysql -e "start slave;"
[root@db04 ~]# mysql -e "show slave status\G;" | grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
[root@db04 ~]# mysql -e "show slave status\G;"|grep -E '(^[ ]+Master_(Host|User|Port|Log))|(Connect_Retry)|(Read_Master_Log_Pos)'
                  Master_Host: 10.0.0.153
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: sql-binlog.000002
          Read_Master_Log_Pos: 444

[root@db05 ~]# mysql -e "CHANGE MASTER TO MASTER_HOST='10.0.0.153',MASTER_USER='repl',MASTER_PASSWORD='456',MASTER_PORT=3306,MASTER_AUTO_POSITION=1,MASTER_CONNECT_RETRY=10;"
[root@db05 ~]# mysql -e "start slave;
[root@db05 ~]# mysql -e "show slave status\G;" | grep -E '(^[ ]+Slave_(IO|SQL)_Running:)|(^[ ]+Last_(IO|SQL)_Err(or|no):)'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:
[root@db05 ~]# mysql -e "show slave status\G;"|grep -E '(^[ ]+Master_(Host|User|Port|Log))|(Connect_Retry)|(Read_Master_Log_Pos)'
                  Master_Host: 10.0.0.153
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: sql-binlog.000002
          Read_Master_Log_Pos: 444

附：思维导图