MySQL双主基于GTID复制方案

操作系统：CentOS Linux release 7.8.2003 (Core) master-1：192.168.200.50 master-2：192.168.200.51 ## 基本操作 **注: 所有节点都需要执行** ```bash // 关闭防火墙 systemctl stop firewalld // 关闭Selinux vim /etc/sysconfig/selinux SELINUX=disabled // 临时关闭Selinux setenforce 0 ``` ## 二进制安装数据库 **注: 所有节点都需要执行** ### 安装数据库 ```bash // 安装数据库 mkdir /usr/local/mysql // 解压缩 tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql --strip=1 // 创建用户及用户组 groupadd mysql useradd mysql -g mysql -s /sbin/nologin // 创建数据库数据目录 mkdir /usr/local/mysql/data // 授权 chown -R mysql.mysql /usr/local/mysql // 复制启动脚本 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld // 设置开机启动 chkconfig on mysqld ``` ### 初始化数据库 ```bash // 初始化数据库 此处会生成默认root密码 /usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --initialize // 会打印一下日志 记住保存好密码 2021-09-01T11:54:32.248336Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-09-01T11:54:33.099720Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-09-01T11:54:33.236031Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-09-01T11:54:33.310273Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5f55799d-0b1b-11ec-9475-005056a53fc6. 2021-09-01T11:54:33.313554Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-09-01T11:54:34.641583Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2021-09-01T11:54:34.641629Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2021-09-01T11:54:34.642431Z 0 [Warning] CA certificate ca.pem is self signed. 2021-09-01T11:54:34.944690Z 1 [Note] A temporary password is generated for root@localhost: **urG/hV3t0jo?** ``` ## 双主配置 ### **节点1配置数据库** ```bash // 打开/etc/my.cnf 将以下内容添加进去 vim /etc/my.cnf [mysqld] # 数据目录 datadir=/usr/local/mysql/data basedir=/usr/local/mysql/ socket=/usr/local/mysql/data/mysql.sock user=mysql # 端口 port=13306 character-set-server=utf8 default_storage_engine = InnoDB lower_case_table_names = 1 # 服务ID 唯一 不同节点分配不同ID server_id = 1 # 打开Mysql日志 格式为二进制 log-bin = mysql-bin # 开启基于GTID的复制 gtid_mode = on enforce_gtid_consistency = on binlog_format = row log-slave-updates = 1 skip_slave_start = 1 # 与节点数相同 auto-increment-increment = 2 # 自增 auto-increment-offset = 1 symbolic-links=0 [client] port = 13306 default-character-set=utf8 socket=/usr/local/mysql/data/mysql.sock [mysqld_safe] log-error=/usr/local/mysql/data/error.log pid-file=/usr/local/mysql/data/database.pid ``` **节点1启动数据库** ```go // 启动数据库 /etc/init.d/mysqld start ``` **节点1初始化密码** ```sql // 登陆数据库 /usr/local/mysql/bin/mysql -uroot -p --port=13306 // 修改root密码 mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; mysql>flush privileges; mysql>exit; ``` **节点1创建从库同步用户** ```sql // 登陆数据库 /usr/local/mysql/bin/mysql -uroot -p --port=13306 // 创建同步用户 mysql>GRANT REPLICATION SLAVE ON *.* TO master1@'%' IDENTIFIED BY '123456'; mysql>flush privileges; mysql>exit; ``` ### **节点2配置数据库** ```bash // 打开/etc/my.cnf 将以下内容添加进去 vim /etc/my.cnf [mysqld] # 数据目录 datadir=/usr/local/mysql/data basedir=/usr/local/mysql/ socket=/usr/local/mysql/data/mysql.sock user=mysql # 端口 port=13306 character-set-server=utf8 default_storage_engine = InnoDB lower_case_table_names = 1 # 服务ID 唯一 不同节点分配不同ID server_id = 2 # 打开Mysql日志 格式为二进制 log-bin = mysql-bin # 开启基于GTID的复制 gtid_mode = on enforce_gtid_consistency = on binlog_format = row log-slave-updates = 1 skip_slave_start = 1 # 与节点数相同 auto-increment-increment = 2 # 自增 auto-increment-offset = 2 symbolic-links=0 [client] port = 13306 default-character-set=utf8 socket=/usr/local/mysql/data/mysql.sock [mysqld_safe] log-error=/usr/local/mysql/data/error.log pid-file=/usr/local/mysql/data/database.pid ``` **节点2启动数据库** ```bash // 启动数据库 /etc/init.d/mysqld start ``` **节点2初始化密码** ```sql // 登陆数据库 /usr/local/mysql/bin/mysql -uroot -p --port=13306 // 修改root密码 mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; mysql>flush privileges; mysql>exit; ``` **节点2创建从库同步用户** ```sql // 登陆数据库 /usr/local/mysql/bin/mysql -uroot -p --port=13306 // 创建同步用户 mysql>GRANT REPLICATION SLAVE ON *.* TO master2@'%' IDENTIFIED BY '123456'; mysql>flush privileges; mysql>exit; ``` ## 双主同步配置 **节点2开启同步节点1** ```sql // 登陆数据库 /usr/local/mysql/bin/mysql -uroot -p --port=13306 // 创建同步 mysql>change master to master_host='192.168.200.50',master_user='master1',master_password='123456',master_port=13306,master_auto_position=1; // 开始同步 mysql>start slave; // 查看同步状态 mysql>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.200.50 Master_User: master1 Master_Port: 13306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 194 Relay_Log_File: work-02-relay-bin.000003 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes // 必须为Yes 表示当前线程会连接Master节点的Bin-Log 并同步到本地中继日志中 Slave_SQL_Running: Yes // 必须为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: 194 Relay_Log_Space: 670 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: 1 Master_UUID: 9e07b87e-0b1e-11ec-8609-005056a53fc6 Master_Info_File: /usr/local/mysql/data/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: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ``` **节点1开启同步节点2** ```go // 登陆数据库 /usr/local/mysql/bin/mysql -uroot -p --port=13306 // 创建同步 mysql>change master to master_host='192.168.200.51',master_user='master2',master_password='123456',master_port=13306,master_auto_position=1; // 开始同步 mysql>start slave; // 查看同步状态 mysql>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.200.51 Master_User: master2 Master_Port: 13306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 759 Relay_Log_File: work-01-relay-bin.000002 Relay_Log_Pos: 862 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: 759 Relay_Log_Space: 1071 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: 2 Master_UUID: e9ff5da2-0b1e-11ec-b665-005056a5c44c Master_Info_File: /usr/local/mysql/data/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: e9ff5da2-0b1e-11ec-b665-005056a5c44c:1-2 Executed_Gtid_Set: 9e07b87e-0b1e-11ec-8609-005056a53fc6:1-5, e9ff5da2-0b1e-11ec-b665-005056a5c44c:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ``` **对IP进行授权访问** ```sql // 在任意一个节点上执行 // 登陆数据库 /usr/local/mysql/bin/mysql -uroot -p --port=13306 mysql>grant all on *.* to 'root'@'192.168.200.50' identified by '123456' with grant option; mysql>grant all on *.* to 'root'@'192.168.200.51' identified by '123456' with grant option; mysql>flush privileges; mysql>exit; ``` ## 测试数据库 **注：任意一台服务器/2个节点其中一个 需要确保已经授权** ```sql // 登陆数据库 如果登陆成功 代表数据库集群正常运行 /usr/local/mysql/bin/mysql -uroot -p -h 192.168.200.50 --port=13306 ``` **完整数据库测试** ```sql // 测试数据库创建 create database test1; // 测试数据表创建 use test1; create table user( id int auto_increment primary key, user varchar(20) not null, sex varchar(20) not null, birthday datetime ) // 测试数据插入 不带ID 让Mysql自增 insert into user(user,sex,birthday) values('耀耀','男','1977-09-01'); // 查看数据 select * from user; +----+--------+-----+---------------------+ | id | user | sex | birthday | +----+--------+-----+---------------------+ | 2 | 张三 | 男 | 1977-09-01 00:00:00 | | 4 | 张三 | 男 | 1977-09-01 00:00:00 | | 6 | 张三 | 男 | 1977-09-01 00:00:00 | +----+--------+-----+---------------------+ 发现是2倍数递增 // 测试数据插入 携带ID insert into user(id,user,sex,birthday) values(7,'耀耀','男','1977-09-01'); ``` ## 参考资料 1. [https://keithlan.github.io/2016/06/23/gtid/](https://keithlan.github.io/2016/06/23/gtid/) 2. [http://mysql.taobao.org/monthly/2020/05/09/](http://mysql.taobao.org/monthly/2020/05/09/) 3. [https://bbs.huaweicloud.com/blogs/211180](https://bbs.huaweicloud.com/blogs/211180)