mysql主从复制、GTID、半同步复制

最新推荐文章于 2024-08-24 02:04:49 发布

我去买个橘子_

最新推荐文章于 2024-08-24 02:04:49 发布

阅读量710

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/whdccdja/article/details/123796932

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL的主从复制机制，包括基于binlog的复制过程，主从复制的IO线程和SQL线程作用。还讨论了主从复制带来的延迟问题以及解决方案。进一步讲解了GTID（全局事务ID）的引入，如何保证主备一致性，并分析了半同步复制在提升数据安全性方面的角色及其在网络延迟下的性能影响。

摘要由CSDN通过智能技术生成

mysql复制

1、MySQL的复制是基于binlog的。
2、MySQL复制包括两部分，IO线程和 SQL线程。
3、 IO线程主要是用于拉取接收Master传递过来的binlog，并将其写入到relay log
4、SQL线程主要负责解析relay log，并应用到slave中
5.、不管怎么说，IO和SQL线程都是单线程的，然后master却是多线程的，所以难免会有延迟，为了解决这个问题，多线程应运而生了

主从复制

将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上，然后将这些日志重新执行，从而使得从数据库中的数据与主数据库中的数据保持一致。

server4  安装mysql的虚拟机作为Master（主数据库）
server1  新的虚拟机作为slave端（从数据库）

#将主数据库中的mysql目录和/etc/my.cnf文件复制给从数据库
[root@server4 ~]# scp -r /usr/local/mysql/ server1:/usr/local/
[root@server4 ~]# scp /etc/my.cnf server1:/etc/

在这里插入图片描述

#在从数据库中
[root@server1 ~]# cd /usr/local/mysql/support-files
#拷贝启动脚本
[root@server1 support-files]# cp mysql.server /etc/init.d/mysqld
#添加环境变量
[root@server1 support-files]# cd
[root@server1 ~]# vim .bash_profile 
[root@server1 ~]# tail -n 3 .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH
[root@server1 ~]# source .bash_profile
[root@server1 ~]# which mysql
/usr/local/mysql/bin/mysql
[root@server1 ~]# useradd -M -d /data/mysql -s /sbin/nologin mysql
#创建数据目录并给予权限
[root@server1 ~]# mkdir /data/mysql -p
[root@server1 ~]# chown mysql.mysql /data/mysql

在这里插入图片描述

#初始化数据库，得到一个初始化密码
[root@server1 ~]# mysqld --initialize --user=mysql
#开启mysql
[root@server1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysqld.log'.
 SUCCESS! 
 #修改数据库密码
[root@server1 ~]# mysql_secure_installation

在这里插入图片描述
在主数据库master端开启binlog

#修改配置文件
[root@server4 ~]# vim /etc/my.cnf
[root@server4 ~]# head -5 /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
#添加如下内容
log-bin=mysql-bin##表示启用binlog,log-bin决定了msyql 的binlog的名字，⽣成的binlog名字为mysql-bin.000001
server_id=1	##正整数，可以不是递增，但必须为正
#重启数据库
[root@server4 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@server4 ~]# cd /data/mysql/
[root@server4 mysql]# ls
---->mysql-bin.000001 mysql-bin.index
##mysql-bin.000001数据库的二进制操作日志，每重启一次会生成新的;mysql-bin.index，二进制日志的索引文件，记录了所有的日志

在这里插入图片描述

#进入数据库添加授权用户并进行授权，从数据库slave端可以通过该授权用户进入master端数据库
[root@server4 mysql]# mysql -p
Enter password: 
mysql> create user 'repl'@'%' identified by 'westos';
mysql> grant replication slave on *.* to 'repl'@'%';

在这里插入图片描述
在slave端进行测试

mysql -h 192.168.230.147 -u repl -p		##此时并没有什么权限
> show databases;
> exit								##退出

在这里插入图片描述
在master（server4）端可以查看主库状态可以看到二进制操作日至的名称和位置

> show master status;

在这里插入图片描述

#在slave端修改配置文件，设定server-id为2，重启服务。进入数据库，设定并查看是否设定完成
[root@server1 ~]# vim /etc/my.cnf
server_id=2
[root@server1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@server1 ~]# mysql -p
Enter password: 
mysql> set global server_id=2;
mysql> show variables like 'server_id';

在这里插入图片描述

[注意] 主从复制之前，主从主机数据一定要同步，此时因为主从库数据不同步，所以需要将主库数据备份并传给从库。

##同步主从数据库

#在server4上创建数据库数据库表并写入数据
mysql> show variables like 'server_id';
mysql> create database westos;
mysql> show databases;
mysql> use westos;
Database changed
mysql> create table linux;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table linux(
    -> name varchar(255),
    -> age int,
    -> id int);
mysql> insert into linux values('zhangsan',18,001);
Query OK, 1 row affected (0.01 sec)

mysql> select * from linux;
+----------+------+------+
| name     | age  | id   |
+----------+------+------+
| zhangsan |   18 |    1 |
+----------+------+------+
1 row in set (0.00 sec)
mysql> quit
Bye
#查看日志
[root@server4 mysql]# cd /data/mysql/
[root@server4 mysql]# mysqlbinlog mysql-bin.000001 
#备份数据和所有操作过程
[root@server4 mysql]# mysqldump -p westos > dump.sql 
Enter password: 
#将备份的数据传给从数据库
[root@server4 mysql]# scp dump.sql server1:

在这里插入图片描述

#以管理员身份创建westos库
[root@server1 ~]# mysqladmin -p create westos
Enter password: 
#将备份数据导入数据库
[root@server1 ~]# mysql -p westos < dump.sql 
Enter password: 
#登录数据库即可查看备份的数据
[root@server1 ~]# mysql -pwestos
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)

mysql> select * from linux;
+----------+------+------+
| name     | age  | id   |
+----------+------+------+
| zhangsan |   18 |    1 |
+----------+------+------+
1 row in set (0.00 sec)

mysql>

在这里插入图片描述

#重启数据库
[root@server1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@server1 ~]# mysql -p
Enter password: 
mysql> change master to master_host='192.168.230.147', master_user='repl', master_password='westos', master_log_file='mysql-bin.000001', master_log_pos=595;#595代表固定位置，可以更改为其他位置
##slave从某台主机的某个日志，某个位置开始同步，此位置与master主机二进制日志文件的位置相同。
mysql> start slave;
mysql> show slave status\G;      
--->Slave_IO_Running: Yes 
	Slave_SQL_Running: Yes
mysql> select * from westos.linux;

出现过的错误：
Slave_IO_Running: Yes
Slave_SQL_Running: No

Last_SQL_Error: Error ‘Table ‘linux’ already exists’ on query. Default database: ‘westos’. Query: ‘create table linux(
name varchar(255),
age int,
id int)’
解决办法

1 stop slave;

2 set global sql_slave_skip_counter=1;

这样并不会造成数据丢失

3 start slave;

在这里插入图片描述

主从复制+GTID

从MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
GTID (Global Transaction ID)是全局事务ID,当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务，
从架构设计的角度，GTID是一种很好的分布式ID实践方式，通常来说，分布式ID有两个基本要求：

1）全局唯一性
2）趋势递增

这个ID因为是全局唯一，所以在分布式环境中很容易识别，因为趋势递增，所以ID是具有相应的趋势规律，在必要的时候方便进行顺序提取，行业内适用较多的是基于Twitter的ID生成算法snowflake,所以换一个角度来理解GTID，其实是一种优雅的分布式设计。

master（server4）—>slave(server1)(master)----->slave(server3)
文字理解就是1从4上同步数据而3从1上同步数据

在master(server4)和slave(server1)中都执行以下操作

#编辑配置文件开启gtid并重启mysql
[root@server4 ~]# vim /etc/my.cnf
--------------------------
gtid_mode=ON
enforce-gtid-consistency=ON
--------------------------
[root@server4 ~]# /etc/init.d/mysqld restart 
Shutting down MySQL........... SUCCESS! 
Starting MySQL. SUCCESS!

在这里插入图片描述

#slave同样开启gtid并重启mysql
[root@server1 ~]# vim /etc/my.cnf
[root@server1 ~]# /etc/init.d/mysqld restart
#登录数据库并停止slave 
[root@server1 ~]# mysql -pwestos
mysql> stop slave;
#设定以repl用户自动获取master端的事务，自动位置为1
mysql> change master to master_host='192.168.230.147', master_user='repl', master_password='westos', master_auto_position = 1;
#重新开启slave并查看slave状态
mysql> start slave;
mysql> show slave status\G;
----------------------
#io线程和sql线程都为运行状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在这里插入图片描述

在master端给数据库插入数据
[root@server4 ~]# mysql -p westos
Enter password: 
mysql> show databases;
mysql> use westos;
mysql> show tables;
mysql> select * from linux;
mysql> insert into linux value('lisi','20','2');
mysql> select * from linux;

在这里插入图片描述

设定server3为server1主机的slave端

#在server1上可以查看到master端插入的数据
mysql> select westos.linux;
#查看gtid系统表可以看到已经复制成功的资源id
mysql> use mysql;
mysql> select * from gtid_executed;
#将mysql目录复制给server3
scp -r /usr/local/mysql/ server3:/usr/local/

在这里插入图片描述

#在master端将配置文件和启动项复制给server3
[root@server4 ~]# scp /etc/my.cnf server3:/etc
[root@server4 ~]# scp /etc/init.d/mysqld server3:/etc/init.d/

在这里插入图片描述

#编辑server3的配置文件，设置服务id为3
[root@server3 ~]# vim /etc/my.cnf
[root@server3 ~]# head -n 10 /etc/my.cnf
--------------------------------
server_id=3	
gtid_mode=ON
enforce-gtid-consistency=ON
---------------------------------

在这里插入图片描述

添加环境变量并应用
[root@server3 ~]# vim .bash_profile 
[root@server3 ~]# source .bash_profile 
创建目录和用户
[root@server3 ~]# mkdir -p /data/mysql
[root@server3 ~]# chown mysql.mysql /data/mysql
chown: invalid user: ‘mysql.mysql’
[root@server3 ~]# useradd mysql
[root@server3 ~]# chown mysql.mysql /data/mysql
#初始化数据库密码
[root@server3 ~]# mysqld --initialize --user=mysql
root@localhost: cHd/NaBHQ7Fo
[root@server3 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysqld.log'.
 SUCCESS! 
 #修改数据库密码
[root@server3 ~]# mysql_secure_installation
NO NO YES YES YES YES

在这里插入图片描述

[root@server1 ~]# vim /etc/my.cnf
-------------------------
log-bin=mysql-bin##启用binlog
log_slave_updates=ON##作为slave的同时又作为master，则记录日志
-------------------------
[root@server1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@server1 ~]# mysql -p
Enter password: 
mysql> show slave status\G;
-----------------------------
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-----------------------------
##在主服务器建立一个专门用于复制数据的用户
mysql> grant replication slave on *.* to repl@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.01 sec)

在这里插入图片描述

#备份数据库并复制给server3
[root@server4 ~]# mysqldump -p westos > dump.sql
[root@server4 ~]# scp dump.sql server3:

备份数据库时出现警告Warning: A partial dump from a server that has GTIDs will by
default include the GTIDs of all transactions, even those that changed
suppressed parts of the database. If you don’t want to restore GTIDs,
pass --set-gtid-purged=OFF. To make a complete dump, pass
–all-databases --triggers --routines --events. 关于GTID是5.6以后，加入了全局事务
ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。官方给的：A global transaction
identifier (GTID) is a unique identifier created and associated with
each transaction committed on the server of origin (master).
所以可能是因为在一个数据库里面唯一，但是当导入其他的库就有可能重复。所有会有一个提醒。

可以通过添加–set-gtid-purged=off 或者–gtid-mode=OFF这两个参数设置。
个人认为是在导入库中重新生产GTID，而不用原来的。

在这里插入图片描述

#导入数据库，登录后可以查看到数据库中的数据
[root@server3 ~]# ls
anaconda-ks.cfg  dump.sql  original-ks.cfg
[root@server3 ~]# mysql -p westos < dump.sql 
Enter password: 
[root@server3 ~]# mysql -pwestos
mysql> select * from westos.linux;
+----------+------+------+
| name     | age  | id   |
+----------+------+------+
| zhangsan |   18 |    1 |
| lisi     |   20 |    2 |
+----------+------+------+
2 rows in set (0.00 sec)
#设定以repl用户自动获取master端的事务，开启slave端，查看状态。
mysql> change master to master_host='192.168.230.147', master_user='repl', master_password
='westos', master_auto_position = 1;
mysql> start slave;
mysql> show slave status\G;

在这里插入图片描述

#在master端插入数据
[root@server4 ~]# mysql -p
Enter password: 
mysql> use westos;
mysql> select * from linux;
mysql> insert into linux values('wangwu','19','3');
mysql> select * from linux;

在这里插入图片描述
在server3中可以查看到同步的数据

半同步复制（对io进行设定）

半同步复制（Semisynchronous replication）

默认创建的MySQL复制是异步的，意味着主库将数据库修改事件写入到自己的bin log，而并不知道从库是否获取了这些事件并应用在自己身上。所以当主库崩溃要主从切换时，有可能从库上的数据不是最新的。

从5.7版本开始MySQL通过扩展的方式支持了半同步复制。当主库执行一个更新操作事物时，提交操作会被阻止直到至少有一个半同步的复制slave确认依据接收到本次更新操作并写到relay log中，主库的提交操作才会继续。半同步复制的slave发送确认消息只会在本次更新操作已经记录到本地的relay log之后。如果没有任何slave发送确认消息而导致超时时，半同步复制会转换成异步复制。半同步复制会对MySQL性能产生影响，因为主库的提交动作只有在收到至少一个从库的确认消息之后才能执行。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

在server4（master）和server1（slave）主机开启mysql，安装其对应的半同步插件。

###server4
/etc/init.d/mysqld start
mysql -p
> install plugin rpl_semi_sync_master soname 'semisync_master.so';		##安装半同步插件

###server1
/etc/init.d/mysqld start
mysql -pwestos
> show slave status\G;
> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

在这里插入图片描述

在这里插入图片描述在server4主机验证插件安装状态，显示有关服务器插件的信息，启用半同步。

mysql> select plugin_name, plugin_status
-> from information_schema.plugins
-> where plugin_name like '%semi%';				##验证插件安装状态
mysql> show plugins;
mysql> set global rpl_semi_sync_master_enabled=1;

在这里插入图片描述 server1同样启用半同步

mysql> set global rpl_semi_sync_slave_enabled=1;

在这里插入图片描述在server4上查看系统变量半同步为打开状态，超时时间默认10s（生产环境下为无穷大，半同步挂掉则切换为异步，所以需要将等待时间设为无穷大），模式为AFTER_SYNC

mysql> show variables like 'rpl%';

在这里插入图片描述

在server1上同样查看系统变量与状态

mysql> show variables like 'rpl%';
rpl_semi_sync_slave_enabled               | ON 
mysql> show status like 'rpl%';
Rpl_semi_sync_slave_status                 | OFF

在这里插入图片描述

server4上rpl_semi_sync_master_clients为0表示没有slave端

mysql> show status like 'rpl%';

在这里插入图片描述
server1上关闭io线程并重新打开用以刷新设定

mysql> stop slave IO_THREAD;
mysql> start slave IO_THREAD;
mysql> show status like 'rpl%';
Rpl_semi_sync_slave_status                 | ON

在这里插入图片描述

server4上查看，此时rpl_semi_sync_master_clients为1，表示有slave端，插入数据，重新查看，rpl_semi_sync_master_yes_tx为1，表示同步成功1次。

mysql> show status like 'rpl%';
mysql> use westos;
mysql> insert into linux values ('zhangqiling','25','4');
mysql> show status like 'rpl%';
Rpl_semi_sync_master_yes_tx                | 1

在这里插入图片描述

在server1上查看数据库，可以查看到同步过来的数据，再次关闭io线程

mysql> select * from westos.linux;
+-------------+------+------+
| name        | age  | id   |
+-------------+------+------+
| zhangsan    |   18 |    1 |
| zhangsan    |   18 |    1 |
| lisi        |   20 |    2 |
| wangwu      |   19 |    3 |
| zhangqiling |   25 |    4 |
+-------------+------+------+
5 rows in set (0.00 sec)
mysql> stop slave IO_THREAD;

在这里插入图片描述 server4上插入数据，查看状态可以看到同步失败数量为1次

mysql> insert into linux values ('zql','23','5');
mysql> show status like 'rpl%';
Rpl_semi_sync_master_no_tx                 | 1

在这里插入图片描述
server1上无法查看到同步数据，开启io线程后重新查看数据，数据同步成功

mysql> select * from westos.linux;
+-------------+------+------+
| name        | age  | id   |
+-------------+------+------+
| zhangsan    |   18 |    1 |
| zhangsan    |   18 |    1 |
| lisi        |   20 |    2 |
| wangwu      |   19 |    3 |
| zhangqiling |   25 |    4 |
+-------------+------+------+
5 rows in set (0.00 sec)
mysql> start slave IO_THREAD;
mysql> select * from westos.linux;

在这里插入图片描述

server4上查看开启io后的状态并插入新数据，同步成功，同步次数改为2

mysql> show status like 'rpl%';
mysql> insert into linux values ('qwe','24','6');
mysql> show status like 'rpl%';
mysql> show processlist;

在这里插入图片描述

在这里插入图片描述 server1上查看数据，可以看到全部同步完成

我去买个橘子_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录