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

mysql -基于Gtid的主从复制_gtid主从复制原理图

作者：寸_铁 | 2024-08-10 16:11:58

赞

踩

gtid主从复制原理图

主从复制原理图

主从复制实验原理图

准备工作

①
准备两台服务器，都开启mysql服务
尽量两台服务器的数据库版本一致

②
msater服务器开启二进制日志
master服务器和slave服务器的server_id不能相同，必须唯一

步骤

master配置

1.修改mysql配置文件
  • 开启二进制日志
  • 配置server_id
vim /etc/my.cnf

  • 1

修改如下

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
slow_query_log=1
long_query_time = 0.001
general_log
log_bin
# 服务器的编号
server_id = 1



  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

2.创建用来复制二进制日志的用户
mysql>create user 'slave'@'192.168.186.138' identified by 'wp123456';
Query OK, 0 rows affected (1.05 sec)

  • 1
  • 2
3.授权 - - - 授权获取二进制日志的权限
root@(none) 12:33  mysql>grant replication slave on *.* to 'slave'@'192.168.186.138';
Query OK, 0 rows affected (0.00 sec)

  • 1
  • 2
4.创建完全备份
[root@localhost /]# mysqldump -uroot -p'wp123456' --all-databases >/backup/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

  • 1
  • 2
5.将完全备份传输到slave机器上
[root@localhost /]# scp /backup/all.sql root@192.168.186.138:/root
The authenticity of host '192.168.186.138 (192.168.186.138)' can't be established.
ECDSA key fingerprint is SHA256:0aDD3cub4HOR1gWHbgqPRYCLydWdpE0F1FljaTnQ+fc.
ECDSA key fingerprint is MD5:8f:8c:72:87:9d:3a:d6:40:5a:c3:35:11:96:af:af:05.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.186.138' (ECDSA) to the list of known hosts.
root@192.168.186.136's password: 
all.sql                                                                                                    100%  887KB  16.8MB/s   00:00

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
6.在master上关闭防火墙和selinux
  • 关闭防火墙
       临时关闭
service firewalld stop

  • 1

   永久关闭

[root@localhost backup]# systemctl disable firewalld

  • 1
  • 关闭selinux

   临时关闭

set enforcing 0

  • 1

   永久关闭

[root@localhost backup]# vim /etc/selinux/config

  • 1

slave配置

1.修改mysql配置文件
vim /etc/my.cnf

  • 1
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
log_bin
# 服务器编号
server_id = 2
# 开启gtid
gtid-mode=ON
enforce-gtid-consistency=ON

log_slave_updates=ON


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

slave服务器的二进制日志可开启或者不开启

2.在slave服务器上导入备份
[root@localhost ~]# mysql -uroot -p'wp123456' <all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

  • 1
  • 2
3.查看配置slave的相关信息
  • 查看二进制日志文件
root@(none) 13:28  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000005 |    38028 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 查看端口号

在mysql里使用system + command(linux)查看

root@(none) 12:34  mysql>system lsof -i:3306
COMMAND    PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  106388 mysql   36u  IPv6 548819      0t0  TCP *:mysql (LISTEN)

  • 1
  • 2
  • 3
4.slave上启用master的信息
CHANGE MASTER TO MASTER_HOST='192.168.186.139',
MASTER_USER='slave',
MASTER_PASSWORD='wp123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='localhost-bin.000005',
MASTER_LOG_POS=38028;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

MASTER_USER是先前创建获取二进制日志文件的用户

MASTER_PASSWORD是先前创建获取二进制日志文件的用户的密码

MASTER_PORT端口号

MASTER_LOG_FILE二进制日志文件名

MASTER_LOG_POS位置号

  • 可能出现的错误：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHANGE MASTER TO MASTER_HOST='192.168.186.139',
MASTER_USER='slave',
MASTER_P' at line 1

  • 1
  • 2
  • 3

   解决方法

   可以使用下列语句

CHANGE MASTER TO
MASTER_HOST='192.168.186.139',
MASTER_USER='slave',
MASTER_PASSWORD='wp123456',
MASTER_LOG_FILE='localhost-bin.000005',
MASTER_LOG_POS=38028;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

  • 1

   解决方法

   1.drop 备份的 ibd表

use mysql;
drop table if exists slave_master_info; 
drop table if exists slave_relay_log_info; 
drop table if exists slave_worker_info; 
drop table if exists innodb_index_stats; 
drop table if exists innodb_table_stats;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

   2.重建

mysql> source /usr/local/mysql/share/mysql_system_tables.sql

  • 1

   3.重启数据库

[root@localhost ~]# service mysql restart
Redirecting to /bin/systemctl restart mysql.service

  • 1
  • 2
5.在slave上关闭防火墙和selinux
  • 关闭防火墙

   临时关闭

service firewalld stop

  • 1

   永久关闭

[root@localhost backup]# systemctl disable firewalld

  • 1
  • 关闭selinux

   临时关闭

set enforcing 0

  • 1

   永久关闭

[root@localhost backup]# vim /etc/selinux/config

  • 1

https://gitee.com/wupei_w/photo/raw/master/wupei_w/photo/20210404204230.png

6.查看slave状态
mysql>show slave status\G;

  • 1

https://gitee.com/wupei_w/photo/raw/master/wupei_w/photo/20210404204501.png

7.开启slave线程
mysql>start slave;

  • 1
8.验证启动效果
mysql>show slave status\G;

  • 1

https://gitee.com/wupei_w/photo/raw/master/wupei_w/photo/20210404204555.png

修改配置文件

关闭slave服务

root@mysql 22:29  mysql>stop slave;
Query OK, 0 rows affected (0.38 sec)

  • 1
  • 2

slave清除master的信息

root@(none) 22:40  mysql>reset master;
Query OK, 0 rows affected (0.01 sec)

  • 1
  • 2

配置文件信息

  • 临时

master配置

  • 永久

master配置

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
slow_query_log=1
long_query_time = 0.001
general_log
# 开启二进制日志
log_bin
# 指定二进制日志名称前缀
log-bin=mysql-bin
# 服务器的编号
server_id = 1

# 开启gtid
gtid-mode=ON
enforce-gtid-consistency=ON

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

enforce_gtid_consistency 保证GTID安全的参数强制gtid一致性，开启后对于特定create table不被支持

slave配置

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8

# 二进制日志
log_bin

# 服务器编号
server_id = 3
log-bin=mysql-bin

# gtid
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

log_slave_updates=ON

master和slave都重启mysql服务

[root@localhost /]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL... SUCCESS!

  • 1
  • 2
  • 3

开启gtid

master创建授权复制的用户

新建用户

root@(none) 11:47  mysql>create user 'slave'@'%' identified by 'wp123456';
Query OK, 0 rows affected (0.00 sec)

  • 1
  • 2

授权

root@(none) 11:49  mysql>grant replication slave on *.* to 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

  • 1
  • 2

slave配置master信息

CHANGE MASTER TO
MASTER_HOST='192.168.186.128',
MASTER_USER='slave',
MASTER_PASSWORD='wp123456',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

slave服务器开启slave

mysql>start slave；

  • 1

验证

查看开启状态

root@(none) 14:02  mysql>show slave status\G;

  • 1

https://gitee.com/wupei_w/photo/raw/master/wupei_w/photo/20210408140444.png

https://gitee.com/wupei_w/photo/raw/master/wupei_w/photo/20210408140621.png

查看master的情况，对比master和slave的gtid号

root@(none) 14:04  mysql>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+----------------------+----------+--------------+------------------+------------------------------------------+
| localhost-bin.000009 |      194 |              |                  | b5115b39-88bf-11eb-afb0-000c292a5af9:1-7 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

验证主从数据是否一致

master

root@(none) 14:27  mysql>create database test1;
Query OK, 1 row affected (1.09 sec)

root@(none) 14:27  mysql>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+----------------------+----------+--------------+------------------+------------------------------------------+
| localhost-bin.000009 |      356 |              |                  | b5115b39-88bf-11eb-afb0-000c292a5af9:1-8 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

slave

查看databases

root@(none) 14:29  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TENNIS             |
| backup             |
| game               |
| mysql              |
| performance_schema |
| sanchuang          |
| sanchuang123       |
| sys                |
| test               |
| test01             |
| test1              |
| w                  |
| wp                 |
| wxj                |
+--------------------+
15 rows in set (0.00 sec)


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

查看slave status

root@(none) 14:29  mysql>show slave status\G;
					 Retrieved_Gtid_Set: b5115b39-88bf-11eb-afb0-000c292a5af9:1-8
            Executed_Gtid_Set: b5115b39-88bf-11eb-afb0-000c292a5af9:1-8
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

错误排查

  • mysql服务不能启动

    [root@master mysql]# service mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL.........

    • 1
    • 2
    • 3

    使用ps aux查看进程

    [root@master mysql]# ps aux |grep mysqld
root       1755  0.0  0.1  11824  1580 pts/0    S    10:38   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
mysql      1909  0.0 21.3 1545108 212112 pts/0  Sl   10:38   0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --open-files-limit=8192 --pid-file=/data/mysql/localhost.localdomain.pid --socket=/data/mysql/mysql.sock --port=3306
root       3747  0.0  0.1  11824  1584 pts/0    S    11:28   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/master.pid
mysql      3963  1.4 19.2 976416 191748 pts/0   Sl   11:28   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master.err --open-files-limit=8192 --pid-file=/data/mysql/master.pid --socket=/data/mysql/mysql.sock --port=3306
root       3993  0.0  0.0 112824   976 pts/0    R+   11:28   0:00 grep --color=auto mysqld

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    发现有两个mysqld_safe和basedir的进程在启动，找出不是自己指定PID路径的进程

    kill -9 进程号

    [root@master mysql]# kill -9 1755
[root@master mysql]# kill -9 1909

    • 1
    • 2

    查看进程

    [root@master mysql]# ps aux |grep mysqld
root       3747  0.0  0.1  11824  1584 pts/0    S    11:28   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/master.pid
mysql      3963  0.5 21.2 1479376 211308 pts/0  Sl   11:28   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=master.err --open-files-limit=8192 --pid-file=/data/mysql/master.pid --socket=/data/mysql/mysql.sock --port=3306
root       4011  0.0  0.0 112824   976 pts/0    R+   11:29   0:00 grep --color=auto mysqld

    • 1
    • 2
    • 3
    • 4

    重启mysql服务

    [root@master mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

    • 1
    • 2
    • 3

  • SQL线程没有启动

    https://gitee.com/wupei_w/photo/raw/master/wupei_w/photo/20210409163528.png

    查看最后一个错误

    https://gitee.com/wupei_w/photo/raw/master/wupei_w/photo/20210409163451.png

    https://gitee.com/wupei_w/photo/raw/master/wupei_w/photo/20210409172900.png

    如果错误提示如上示两图，错误原因都是master和slave的数据不一致，slave服务器有数据丢失的可能，导致master所作的操作没有同步到slave上

  • IO线程没有开启

    https://gitee.com/wupei_w/photo/raw/master/wupei_w/photo/20210409172541.png

    错误提醒

    Last_IO_Error: error connecting to master 'slave@192.168.186.128:3306' - retry-time: 60  retries: 1

    • 1

    此类的错误都是主库删除多余的用户名，导致从库没有此信息造成主从故障！

    解决方法

    mysql> grant  select on dbname *.* to "'select_user'"@"%" identified by "123456";

mysql> flush privileges;

mysql> stop slave;

mysql> start slave;

以上命令执行后，从库'select_user'也会sql线程也会自动删掉select_user用户，主从同步恢复正常。


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

https://blog.csdn.net/chj_1224365967/article/details/107915724

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

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

  

闽ICP备14008679号