mysql -基于Gtid的主从复制

最新推荐文章于 2023-10-27 20:37:29 发布

Vivant-W

最新推荐文章于 2023-10-27 20:37:29 发布

阅读量161

点赞数

分类专栏： mysql 文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/weixin_54470937/article/details/115433308

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

主从复制原理图

主从复制实验原理图

准备工作

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

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

步骤

master配置

1.修改mysql配置文件

开启二进制日志
配置server_id

vim /etc/my.cnf

修改如下

[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

2.创建用来复制二进制日志的用户

mysql>create user 'slave'@'192.168.186.138' identified by 'wp123456';
Query OK, 0 rows affected (1.05 sec)

3.授权 - - - 授权获取二进制日志的权限

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

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.

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

6.在master上关闭防火墙和selinux

关闭防火墙
临时关闭

service firewalld stop

永久关闭

[root@localhost backup]# systemctl disable firewalld

关闭selinux

临时关闭

set enforcing 0

永久关闭

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

slave配置

1.修改mysql配置文件

vim /etc/my.cnf

[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

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

2.在slave服务器上导入备份

[root@localhost ~]# mysql -uroot -p'wp123456' <all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

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)

查看端口号

在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)

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;

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

解决方法

可以使用下列语句

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;

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.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;

2.重建

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

3.重启数据库

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

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

关闭防火墙

临时关闭

service firewalld stop

永久关闭

[root@localhost backup]# systemctl disable firewalld

关闭selinux

临时关闭

set enforcing 0

永久关闭

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

6.查看slave状态

mysql>show slave status\G;

7.开启slave线程

mysql>start slave;

8.验证启动效果

mysql>show slave status\G;

修改配置文件

关闭slave服务

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

slave清除master的信息

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

配置文件信息

临时

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

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

log_slave_updates=ON

master和slave都重启mysql服务

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

开启gtid

master创建授权复制的用户

新建用户

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

授权

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

slave配置master信息

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

slave服务器开启slave

mysql>start slave；

验证

查看开启状态

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

查看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)

验证主从数据是否一致

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)

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)

查看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)

错误排查

mysql服务不能启动

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

使用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

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

kill -9 进程号

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

查看进程

[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

重启mysql服务

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

SQL线程没有启动

查看最后一个错误

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

IO线程没有开启

错误提醒

Last_IO_Error: error connecting to master 'slave@192.168.186.128:3306' - retry-time: 60  retries: 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用户，主从同步恢复正常。

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

Vivant-W

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql -基于Gtid的主从复制

准备工作①准备两台服务器，都开启mysql服务尽量两台服务器的数据库版本一致0.配置master和slave服务器msater服务器和slave服务器都需要开启二进制日志master服务器和slave服务器的编号不能相同master配置[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=ut
复制链接

扫一扫