mysql基于GTID的主从复制

1.一主一从

在主库上授权用户

mysql> grant replication slave on *.* to 'akl'@'192.168.237.168' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置主从库主配置文件

//主数据库
[root@master ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

# replication config
server-id = 10
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_bin
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1
[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//从数据库
[root@slave ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

# replication config
server-id = 20
log-bin = mysql_bin
binlog-format = row
gtid-mode = on
skip-slave-start = 1
log-slave-updates = 1
enforce-gtid-consistency = on
[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

配置slave从主机

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.01 sec)

mysql> change master to 
    -> master_host='192.168.237.167',
    -> master_user='akl',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.237.167
                  Master_User: akl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

验证

//主库创建数据库和表
mysql> create database wys;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> create table student(id int not null,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert student(id,name,age) values(1,'zhangsan',25),(2,'lisi',27),(3,'wangwu',24);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   25 |
|  2 | lisi     |   27 |
|  3 | wangwu   |   24 |
+----+----------+------+
3 rows in set (0.01 sec)

//从库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from wys;
+---------------+
| Tables_in_wys |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> select * from wys.student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   25 |
|  2 | lisi     |   27 |
|  3 | wangwu   |   24 |
+----+----------+------+
3 rows in set (0.00 sec)

2.一主两从

在上面一主一从基础上加一台从主机

//主库上授权用户
mysql> grant replication slave on *.* to 'akl'@'192.168.237.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//配置从库主配置文件
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
port = 3306
skip-name-resolve

# replication config
server-id = 21
log-bin = mysql_bin
binlog-format = row
gtid-mode = on
skip-slave-start = 1
log-slave-updates = 1
enforce-gtid-consistency = on
[root@localhost ~]# service mysqld restart 	//重启
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//配置slave主机
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

mysql> change master to 
    -> master_host='192.168.237.167',
    -> master_user='akl',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.237.167
                  Master_User: akl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 1700
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 1913
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

//主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |     1700 |              |                  | 0d87363c-0a60-11ec-ac06-000c29500dfa:1-7 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

//在新建的从库上查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |     1692 |              |                  | 0d87363c-0a60-11ec-ac06-000c29500dfa:1-7 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

//查看从库数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> show tables from wys;
+---------------+
| Tables_in_wys |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> select * from wys.student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   25 |
|  2 | lisi     |   27 |
|  3 | wangwu   |   24 |
+----+----------+------+
3 rows in set (0.00 sec)

3.两主一从

//master1主配置
[root@master1 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

# replication config
server-id = 10
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_bin
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

//master2主配置
[root@master2 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

# replication config
server-id = 11
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_bin
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

//slave主配置
[root@slave ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

# replication config
server-id = 15
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_bin
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

//master1上授权用户
mysql> grant replication slave on *.* to 'test1'@'192.168.237.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//master2上授权用户
mysql> grant replication slave on *.* to 'test2'@'192.168.237.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//将从库存储库设置为table格式
mysql> set global master_info_repository='table';
Query OK, 0 rows affected (0.00 sec)

mysql> set global relay_log_info_repository='table';
Query OK, 0 rows affected (0.00 sec)

//从库上配置两个主库GTID复制
mysql> change master to
    -> master_host='192.168.237.167',
    -> master_user='akl',
    -> master_password='123',
    -> master_auto_position=1 for channel 'master1';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> change master to 
    -> master_host='192.168.237.169',
    -> master_user='test',
    -> master_password='123',
    -> master_auto_position=1 for channel 'master2';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

//开启slave
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.237.167
                  Master_User: test1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 602
               Relay_Log_File: slave-relay-bin-master1.000002
                Relay_Log_Pos: 815
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.237.169
                  Master_User: test2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 602
               Relay_Log_File: slave-relay-bin-master2.000002
                Relay_Log_Pos: 815
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试

//master1创建数据库和表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from test1;
+-----------------+
| Tables_in_test1 |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test1.student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   24 |
|  2 | lisi     |   26 |
|  3 | wangwu   |   27 |
+----+----------+------+
3 rows in set (0.00 sec)

//master2创建数据库和表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from test2;
+-----------------+
| Tables_in_test2 |
+-----------------+
| teacher         |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test2.teacher;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ww   |   35 |
|  2 | vv   |   40 |
|  3 | ll   |   39 |
+----+------+------+
3 rows in set (0.00 sec)

//slave从库查看是否同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql> show tables from test1;
+-----------------+
| Tables_in_test1 |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)

mysql> show tables from test2;
+-----------------+
| Tables_in_test2 |
+-----------------+
| teacher         |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test1.student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   24 |
|  2 | lisi     |   26 |
|  3 | wangwu   |   27 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from test2.teacher;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ww   |   35 |
|  2 | vv   |   40 |
|  3 | ll   |   39 |
+----+------+------+
3 rows in set (0.00 sec)