devbox
码创造者
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

mysql学习--binlog与gtid主从同步_binlog gtid

作者：码创造者 | 2024-08-10 16:09:17

赞

踩

binlog gtid

基础环境

基于centOS7-MySQL8.0.35版本

我们先准备一台主服务器两台从服务器来实现我们主从同步的诉求

Master：192.168.75.142

slave1:192.168.75.143

slave：192.168.75.145

binlog主从同步

主库配置

  1. #我们需要在主从库中都需要添加server_id，每个库的server_id都不唯一
  2. [root@localhost ~]# tail -1 /etc/my.cnf
  3. server_id=1
  4. #重启mysql服务让配置分件生效
  5. [root@localhost ~]# systemctl restart mysqld
  6. #备份：
  7. [root@localhost ~]# mysqldump --opt -B -u root -p school school1> school.sql
  8. #授权用户：
  9. mysql> create user rep@'192.168.75.%' identified with mysql_native_password by 'Mhn@2001';
  10. mysql> grant replication slave on *.* to rep@'192.168.75.%';

从库配置

  1. [root@localhost ~]# tail -1 /etc/my.cnf
  2. server_id=2
  3. [root@localhost ~]# systemctl restart mysqld
  4.  
  5. [root@localhost ~]# tail -1 /etc/my.cnf
  6. server_id=3
  7. [root@localhost ~]# systemctl restart mysqld
  8.  
  9. #还原主库备份，到从服务器家目录下：
  10. scp db.sql 192.168.75.143:/root/
  11. scp db.sql 192.168.75.145:/root/
  12.  
  13. #在两台从主机上将复制的备份文件导入数据库
  14. mysql -uroot -pMysql@123 < school.sql
  15.  
  16. #主库查看
  17. mysql> show master status;
  18. +------------------+----------+--------------+------------------+-------------------+
  19. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  20. +------------------+----------+--------------+------------------+-------------------+
  21. | mysql-bin.000001 |      679 |              |                  |                   |
  22. +------------------+----------+--------------+------------------+-------------------+
  23. 1 row in set (0.00 sec)
  24.  
  25. 从库配置
  26. change master to
  27. master_host='192.168.75.42',
  28. master_user='rep',
  29. master_password='Mhn@2001',
  30. master_log_file='mysql-bin.000001',
  31. master_log_pos=679,
  32. get_master_public_key=1;
  1. mysql> show slave status \G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for source to send event
  4.                   Master_Host: 192.168.75.142
  5.                   Master_User: rep
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: binlog.000002
  9.           Read_Master_Log_Pos: 694
  10.                Relay_Log_File: localhost-relay-bin.000002
  11.                 Relay_Log_Pos: 323
  12.         Relay_Master_Log_File: binlog.000002
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes

从这里可以查看到状态没有问题可以进行主从同步

其他可选配置

  1. #[可选] 0（默认）表示读写（主机），1表示只读（从机）
  2. read-only=0
  3. #设置日志文件保留的时长，单位是秒
  4. binlog_expire_logs_seconds=6000
  5. #控制单个二进制日志大小。此参数的最大和默认值是1GB
  6. max_binlog_size=200M
  7. #[可选]设置不要复制的数据库
  8. binlog-ignore-db=test
  9. #[可选]设置需要复制的数据库,默认全部记录。
  10. binlog-do-db=需要复制的主数据库名字
  11. #[可选]设置binlog格式
  12. binlog_format=STATEMENT

素材

  1. 数据库备份，数据库为school，素材如下
  2.  
  3. 1.创建student和score表
  4.  
  5. CREATE  TABLE  student (
  6. id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  ,
  7. name  VARCHAR(20)  NOT NULL ,
  8. sex  VARCHAR(4)  ,
  9. birth  YEAR,
  10. department  VARCHAR(20) ,
  11. address  VARCHAR(50) 
  12. );
  13.  
  14. 创建score表。SQL代码如下：
  15.  
  16. CREATE  TABLE  score (
  17. id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
  18. stu_id  INT(10)  NOT NULL ,
  19. c_name  VARCHAR(20) ,
  20. grade  INT(10)
  21. );
  22.  
  23. 2.为student表和score表增加记录
  24.  
  25. 向student表插入记录的INSERT语句如下：
  26.  
  27. INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
  28. INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
  29. INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
  30. INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
  31. INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
  32. INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
  33.  
  34. 向score表插入记录的INSERT语句如下：
  35. INSERT INTO score VALUES(NULL,901, '计算机',98);
  36. INSERT INTO score VALUES(NULL,901, '英语', 80);
  37. INSERT INTO score VALUES(NULL,902, '计算机',65);
  38. INSERT INTO score VALUES(NULL,902, '中文',88);
  39. INSERT INTO score VALUES(NULL,903, '中文',95);
  40. INSERT INTO score VALUES(NULL,904, '计算机',70);
  41. INSERT INTO score VALUES(NULL,904, '英语',92);
  42. INSERT INTO score VALUES(NULL,905, '英语',94);
  43. INSERT INTO score VALUES(NULL,906, '计算机',90);
  44. INSERT INTO score VALUES(NULL,906, '英语',85);
  1. create database school;
  2. use school;
  3. CREATE TABLE `Student` (
  4.  `Sno` int(10) NOT NULL COMMENT '学号',  `Sname` varchar(16) NOT NULL COMMENT '姓名',
  5.  `Ssex` char(2) NOT NULL COMMENT '性别',  `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
  6.  `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别',  PRIMARY KEY (`Sno`)
  7.   ) ;
  8. INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');

gtid主从同步

GTID的工作原理:


1.当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。

2.binlog传输到slave，并存储到salve的relaylog后，读取这个GTID的这个值设置GTID——next变量，即告诉slave，下一个要执行的GTID值。

3.sql线程从relaylog中获取GTID，然后对比slave端的binlog是否有该GTID。

4.如果有记录说明该GTID的事务已经执行，slave会忽略。

5.如果没有记录，slave会执行该GTID事务，并记录到该GTID到自身的binlog，在读取该事务前会检查其他session持有该GTID，确保不被重复执行。

6.在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描。

主库配置

  1. 主库配置
  2. [root@localhost ~]# vim /etc/my.cnf
  3. server_id=1  
  4. gtid_mode=ON   #开启gtid模式
  5. enforce-gtid-consistency=ON   #强制gtid一致性，开启后对特定的create table不支持
  6. log-bin=mysql-bin    #开启二进制日志
  7. log-slave-updates=1    #从库binlog记录主库同步的操作日志
  8. skip-slave-start=1   #跳过slave复制线程
  9.  
  10. #重启服务
  11. [root@localhost ~]# systemctl restart mysqld
  12. #查看服务状态
  13. [root@localhost ~]# systemctl status mysqld
  14. ● mysqld.service - MySQL Server
  15.    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
  16.    Active: active (running) since 四 2024-02-29 11:04:01 CST; 37min ago
  17.      Docs: man:mysqld(8)
  18.            http://dev.mysql.com/doc/refman/en/using-systemd.html
  19.   Process: 3740 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
  20.  Main PID: 3770 (mysqld)
  21.    Status: "Server is operational"
  22.     Tasks: 40
  23.    CGroup: /system.slice/mysqld.service
  24.            └─3770 /usr/sbin/mysqld
  25.  
  26. [root@localhost ~]# mysql -uroot -pMysql@123
  27. mysql> show variables like '%gtid%';
  28. +----------------------------------+------------------------------------------+
  29. | Variable_name                    | Value                                    |
  30. +----------------------------------+------------------------------------------+
  31. | binlog_gtid_simple_recovery      | ON                                       |
  32. | enforce_gtid_consistency         | ON                                       |
  33. | gtid_executed                    | ff800e4d-d478-11ee-83a4-000c29b35fbd:1-8 |
  34. | gtid_executed_compression_period | 0                                        |
  35. | gtid_mode                        | ON                                       |
  36. | gtid_next                        | AUTOMATIC                                |
  37. | gtid_owned                       |                                          |
  38. | gtid_purged                      |                                          |
  39. | session_track_gtids              | OFF                                      |
  40. +----------------------------------+------------------------------------------+
  41. 9 rows in set (0.01 sec)
  42.

从库配置

  1. #slave1
  2. server_id=2
  3. gtid_mode=ON
  4. enforce-gtid-consistency=ON
  5. log-bin=mysql-bin
  6. log-slave-updates=1
  7. skip-slave-start=1
  8. mysql> show variables like '%gtid%';
  9. +----------------------------------+------------------------------------------+
  10. | Variable_name                    | Value                                    |
  11. +----------------------------------+------------------------------------------+
  12. | binlog_gtid_simple_recovery      | ON                                       |
  13. | enforce_gtid_consistency         | ON                                       |
  14. | gtid_executed                    | ff800e4d-d478-11ee-83a4-000c29b35fbd:1-8 |
  15. | gtid_executed_compression_period | 0                                        |
  16. | gtid_mode                        | ON                                       |
  17. | gtid_next                        | AUTOMATIC                                |
  18. | gtid_owned                       |                                          |
  19. | gtid_purged                      |                                          |
  20. | session_track_gtids              | OFF                                      |
  21. +----------------------------------+------------------------------------------+
  22. 9 rows in set (0.00 sec)
  23.  
  24.  
  25. #slave2
  26. server_id=3
  27. gtid_mode=ON
  28. enforce-gtid-consistency=ON
  29. log-bin=mysql-bin
  30. log-slave-updates=1
  31. skip-slave-start=1
  32. mysql> show variables like '%gtid%';
  33. +----------------------------------+------------------------------------------+
  34. | Variable_name                    | Value                                    |
  35. +----------------------------------+------------------------------------------+
  36. | binlog_gtid_simple_recovery      | ON                                       |
  37. | enforce_gtid_consistency         | ON                                       |
  38. | gtid_executed                    | ff800e4d-d478-11ee-83a4-000c29b35fbd:1-8 |
  39. | gtid_executed_compression_period | 0                                        |
  40. | gtid_mode                        | ON                                       |
  41. | gtid_next                        | AUTOMATIC                                |
  42. | gtid_owned                       |                                          |
  43. | gtid_purged                      |                                          |
  44. | session_track_gtids              | OFF                                      |
  45. +----------------------------------+------------------------------------------+
  46. 9 rows in set (0.00 sec)

开启主从同步

  1. #主库
  2. mysql> stop slave;
  3. Query OK, 0 rows affected, 2 warnings (0.01 sec)
  4.  
  5. mysql>  create user rep@'192.168.75.%' identified with mysql_native_password by 'Mhn@2001';
  6. Query OK, 0 rows affected (0.04 sec)
  7.  
  8. mysql> grant replication slave on *.* to rep@'192.168.75.%';
  9. Query OK, 0 rows affected (0.00 sec)
  1. #从库，两个都一样
  2. mysql> CHANGE MASTER TO
  3.     -> MASTER_HOST = '192.168.75.142',
  4.     -> MASTER_USER = 'rep',
  5.     -> MASTER_PASSWORD = 'Mhn@2001',
  6.     -> MASTER_AUTO_POSITION = 1;
  7. Query OK, 0 rows affected, 7 warnings (0.01 sec)
  1. #主库建立数据库
  2. mysql> create database book;
  3. Query OK, 1 row affected (0.00 sec)
  4.  
  5. mysql> use book;
  6. Database changed
  7. mysql> CREATE TABLE books (name char(66),price int,pages int);
  8. '80','666');
  9. INSERT INTO books(name,price,pages) VALUES('Artificial Intelligence','166','666');Query OK, 0 rows affected (0.02 sec)
  10.  
  11. mysql> INSERT INTO books(name,price,pages) VALUES('Linux','30','666');
  12. Query OK, 1 row affected (0.01 sec)
  13.  
  14. mysql> INSERT INTO books(name,price,pages) VALUES('Cloud Computing','60','666');
  15. Query OK, 1 row affected (0.01 sec)
  16.  
  17. mysql> INSERT INTO books(name,price,pages) VALUES('Operation System','80','666');
  18. Query OK, 1 row affected (0.00 sec)
  19.  
  20. mysql> INSERT INTO books(name,price,pages) VALUES('Artificial Intelligence','166','666');
  21. Query OK, 1 row affected (0.00 sec)
  22.  
  23. mysql> select * from books;
  24. +-------------------------+-------+-------+
  25. | name                    | price | pages |
  26. +-------------------------+-------+-------+
  27. | Linux                   |    30 |   666 |
  28. | Cloud Computing         |    60 |   666 |
  29. | Operation System        |    80 |   666 |
  30. | Artificial Intelligence |   166 |   666 |
  31. +-------------------------+-------+-------+
  32. 4 rows in set (0.00 sec)
  1. #从库的状态查看
  2. #slave1
  3. mysql> show slave status \G
  4. *************************** 1. row ***************************
  5.                Slave_IO_State: Waiting for source to send event
  6.                   Master_Host: 192.168.75.142
  7.                   Master_User: rep
  8.                   Master_Port: 3306
  9.                 Connect_Retry: 60
  10.               Master_Log_File: mysql-bin.000001
  11.           Read_Master_Log_Pos: 680
  12.                Relay_Log_File: localhost-relay-bin.000002
  13.                 Relay_Log_Pos: 896
  14.         Relay_Master_Log_File: mysql-bin.000001
  15.              Slave_IO_Running: Yes
  16.             Slave_SQL_Running: Yes
  17. #slave2
  18. mysql> show slave status \G
  19. *************************** 1. row ***************************
  20.                Slave_IO_State: Waiting for source to send event
  21.                   Master_Host: 192.168.75.142
  22.                   Master_User: rep
  23.                   Master_Port: 3306
  24.                 Connect_Retry: 60
  25.               Master_Log_File: mysql-bin.000001
  26.           Read_Master_Log_Pos: 680
  27.                Relay_Log_File: localhost-relay-bin.000002
  28.                 Relay_Log_Pos: 896
  29.         Relay_Master_Log_File: mysql-bin.000001
  30.              Slave_IO_Running: Yes
  31.             Slave_SQL_Running: Yes
  1. #从库查看
  2. mysql> show databases;
  3. +--------------------+
  4. | Database           |
  5. +--------------------+
  6. | book               |
  7. | information_schema |
  8. | mysql              |
  9. | performance_schema |
  10. | sys                |
  11. +--------------------+
  12. 5 rows in set (0.01 sec)
  13.  
  14. mysql> use book;
  15. Reading table information for completion of table and column names
  16. You can turn off this feature to get a quicker startup with -A
  17.  
  18. Database changed
  19. mysql> show tables;
  20. +----------------+
  21. | Tables_in_book |
  22. +----------------+
  23. | books          |
  24. +----------------+
  25. 1 row in set (0.00 sec)
  26.  
  27. mysql> select * from book.books;
  28. +-------------------------+-------+-------+
  29. | name                    | price | pages |
  30. +-------------------------+-------+-------+
  31. | Linux                   |    30 |   666 |
  32. | Cloud Computing         |    60 |   666 |
  33. | Operation System        |    80 |   666 |
  34. | Artificial Intelligence |   166 |   666 |
  35. +-------------------------+-------+-------+
  36. 4 rows in set (0.00 sec)

主库

从库1

从库2

可以看到最后的gtid值都一样这就说明我们配置完成。

解决问题

在gtid配置中如果出现 Last_IO_Error: Error connecting to source 'root@192.168.75.142:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Host '192.168.75.143' is not allowed to connect to this MySQL server

可以尝试更改一下主库的mysql>  create user rep@'192.168.75.%' identified with mysql_native_password by 'Mhn@2001';
Query OK, 0 rows affected (0.04 sec)

首先我们需要将所有的库进行stop slave；操作，然后进行操作，修改完成后再次开启就可以使用。

在遇到问题可以使用翻译和查询日志来进行查看

如果我们是使用一段时间mysql后才配置的gtid主从同步需要注意gtid值需要开始的位置，不能从1开始

需要注入空事务，从而解决起始位置相同

stop slave;
set gtid_next='gtid值:开始位置';
begin;commit;
set gtid_next='AUTOMATIC';

初始化mysql

学习过程中因为gtid数据不同步可以进行初始化mysql

在初始化之前必须要将mysql数据目录所有内容全部清空

systemctl stop mysqld

mysql --initialize --uesr=mysql

然后重新启动mysql

systemctl restart mysqld

进入mysql，进入后需要刷新权限表

FLUSH PRIVILEGES；

最后再修改密码

alter user root@localhost identified by 'Mysql@123'；

最后需要在配置文件中删除--skip-grant-tables

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/码创造者/article/detail/959264
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号