mysql主从
mysql主从
1.主从简介
在现代企业中,数据显得尤为重要,而存储数据的数据库选择又五花八门,但无论是何种数据库,均存在着一种隐患。
1.1 主从作用
- 实时灾备,用于故障切换
- 读写分离,提供查询服务
- 备份,避免影响业务
1.2主从形式
- 一主一从
- 主主复制
- 一主多从---扩展系统读取的性能,因为读是在从库读取的
- 多主一从---5.7开始支持
- 联级复制
2. 主从复制原理
主从复制步骤:
- 主库将所有的写操作记录到binlog日志中并生成一个log dump线程,将binlog日志传给从库的I/O线程
- 从库生成两个线程,一个I/O线程,一个SQL线程
- I/O线程去请求主库的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中
- SQL线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,达到最终数据一致的目的
3. 主从复制配置
主从复制配置步骤:
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库(修改配置文件)
- 配置从数据库(修改配置文件)
需求:
搭建两台MySQL
服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作
环境说明:
数据库角色 | IP | 应用与系统版本 | 有无数据 |
---|---|---|---|
主数据库 | 172.16.12.128 | centos8/redhat8 mysql-5.7 | 有数据 |
从数据库 | 172.16.12.129 | centos8/redhat8 mysql-5.7 | 无数据 |
3.1 mysql安装
分别在主从两台服务器上安装mysql-5.7
版本
[root@localhost ~]# wget https://repo.mysql.com/mysql57-community-release-el7-10.noarch.rpm
[root@localhost ~]# ls
anaconda-ks.cfg mysql57-community-release-el7-10.noarch.rpm
/查看文件是否下载成功
安装
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm
[root@localhost ~]# rm -f mysql57-community-release-el7-10.noarch.rpm
//已经在第一步安装完成所以这里需要删掉
[root@localhost ~]#rpm -e mariadb-connector-c-config --nodeps
//删除原有的 mariadb 包,因为不做这个操作的话某些情况下会报冲突错误
[root@localhost ~]# yum -y install *.rpm //安装/root目录下的所有.rpm结尾的包
[root@localhost ~]# systemctl disable --now firewalld.service
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@localhost ~]# setenforce 0
[root@localhost ~]# vi /etc/selinux/config
[root@localhost ~]# systemctl status mysqld
[root@localhost ~]# ss -anlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]# grep 'password' /var/log/mysqld.log
2022-08-02T00:47:36.174483Z 1 [Note] A temporary password is generated for root@localhost: 3g>q.OtoQY:W //使用密码进行登录
[root@localhost ~]# mysql -uroot -p'3g>q.OtoQY:W'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set global validate_password_policy=0; //修改临时密码为永久密码
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> exit
Bye
[root@localhost ~]#
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
3.2 mysql主从配置
3.2.1确保从数据库与主数据库里的数据一样
主从复制配置步骤:
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库(修改配置文件)
- 配置从数据库(修改配置文件)
需求:
搭建两台MySQL
服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作
数据库角色 | ip | 应用与系统版本 | 有无数据 |
---|---|---|---|
主数据库 | 192.168.10.145 | centos8/redhat8 mysql-5.7 | 有数据 |
从数据库 | 192.168.10.149 | centos8/redhat8 mysql-5.7 | 无数据 |
为确保从数据库与主数据库里的数据一样,先全备主数据库并还原到从数据库中
查看主库有哪些库;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
查看从库有哪些库
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.14 sec)
mysql>
#将数据库从主库传到从库
[root@localhost ~]# scp all-20220802093343.sql 192.168.10.145:/root/
The authenticity of host '192.168.10.145 (192.168.10.145)' can't be established.
ECDSA key fingerprint is SHA256:n0Dkwe1M34tJ4a5o5+F7/QfqfwCkHlPmjNl+BKqWjgY.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.10.145' (ECDSA) to the list of known hosts.
root@192.168.10.145's password:
all-20220802093343.sql 100% 855KB 405.6MB/s 00:00
[root@localhost ~]#
[root@localhost ~]# scp all-20220802093343.sql 192.168.10.145:/root/
The authenticity of host '192.168.10.145 (192.168.10.145)' can't be established.
ECDSA key fingerprint is SHA256:n0Dkwe1M34tJ4a5o5+F7/QfqfwCkHlPmjNl+BKqWjgY.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.10.145' (ECDSA) to the list of known hosts.
root@192.168.10.145's password:
all-20220802093343.sql 100% 855KB 405.6MB/s 00:00
[root@localhost ~]# mysql -uroot -p123456 < all-20220802093324.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost ~]#
3.2.2 在主数据库里创建一个同步账号授权给从数据库使用
mysql> CREATE USER 'repl'@'192.168.10.149' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.00 sec)
mysql>
3.2.3 配置主数据库
[root@localhost ~]# vi /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin //启用binlog日志
server-id=1 //数据库服务器唯一标识符,主库的server-id值必须小于从库
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]#
//查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
3.2.4 配置从数据库
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2 //设置从库的唯一标识符,从库的server-id值必须小于主库的该值
relay-log=mysql-relay-bin //启用中继日志relay-log
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
//重启从库的mysql服务
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
配置并启动主从复制
#从库配置
mysql> change master to
-> master_host='192.168.10.149', //主库IP
-> master_user='repl',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.149
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此处必须为yes
Slave_SQL_Running: Yes //此处必须为yes
Replicate_Do_DB:
Replicate_Ignore_DB:
3.2.5 测试验证
在主服务器的student库的nie表中插入数据:
mysql> select * from nie;
Empty set (0.00 sec)
mysql> insert into nie values (1,'sean',20),(2,'tom',23),(3,'jerry',30);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from bj2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | sean | 20 |
| 2 | tom | 23 |
| 3 | jerry | 30 |
+----+-------+------+
3 rows in set (0.00 sec)
在从数据库中查看数据是否同步
mysql> use student;
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> select * from nie;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | sean | 20 |
| 2 | tom | 23 |
| 3 | jerry | 30 |
+----+-------+------+
3 rows in set (0.00 sec)
4. GTID主从
4.1 GTID概念介绍
GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。GTID最初由google实现,官方MySQL在5.6才加入该功能。mysql主从结构在一主一从情况下对于GTID来说就没有优势了,而对于2台主以上的结构优势异常明显,可以在数据不丢失的情况下切换新主。使用GTID需要注意: 在构建主从复制之前,在一台将成为主的实例上进行一些操作(如数据清理等),通过GTID复制,这些在主从成立之前的操作也会被复制到从服务器上,引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始,即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作,接着在server2上执行change的操作,会使得server2也进行server1的清理操作。
GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf),是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增,所以GTID能够保证每个MySQL实例事务的执行(不会重复执行同一个事务,并且会补全没有执行的事务)。GTID在一组复制中,全局唯一。 下面是一个GTID的具体形式 :
mysql> show master status;
+-----------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------------------------------+
| on.000003 | 187 | | | 7286f791-125d-11e9-9a9c-0050568843f8:1-362|
+-----------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
GTID:7286f791-125d-11e9-9a9c-0050568843f8:1-362
UUID:7286f791-125d-11e9-9a9c-0050568843f8
transactionId:1-362
在整个复制架构中GTID 是不变化的,即使在多个连环主从中也不会变。
例如:ServerA --->ServerB ---->ServerC
GTID从在ServerA ,ServerB,ServerC 中都是一样的。
了解了GTID的格式,通过UUID可以知道这个事务在哪个实例上提交的。通过GTID可以极方便的进行复制结构上的故障转移,新主设置,这就很好地解决了下面这个图所展现出来的问题。
====== GTID和Binlog的关系 ======
- GTID在binlog中的结构
- GTID event 结构
- Previous_gtid_log_event
Previous_gtid_log_event 在每个binlog 头部都会有每次binlog rotate的时候存储在binlog头部Previous-GTIDs在binlog中只会存储在这台机器上执行过的所有binlog,不包括手动设置gtid_purged值。换句话说,如果你手动set global gtid_purged=xx; 那么xx是不会记录在Previous_gtid_log_event中的。 - GTID和Binlog之间的关系是怎么对应的呢? 如何才能找到GTID=? 对应的binlog文件呢?
假设有4个binlog: bin.001,bin.002,bin.003,bin.004
bin.001 : Previous-GTIDs=empty; binlog_event有: 1-40
bin.002 : Previous-GTIDs=1-40; binlog_event有: 41-80
bin.003 : Previous-GTIDs=1-80; binlog_event有: 81-120
bin.004 : Previous-GTIDs=1-120; binlog_event有: 121-160
假设现在我们要找GTID=$A,那么MySQL的扫描顺序为: - 从最后一个binlog开始扫描(即: bin.004)
- bin.004的Previous-GTIDs=1-120,如果$A=140 > Previous-GTIDs,那么肯定在bin.004中
- bin.004的Previous-GTIDs=1-120,如果$A=88 包含在Previous-GTIDs中,那么继续对比上一个binlog文件 bin.003,然后再循环前面2个步骤,直到找到为止.
====== GTID 重要参数的持久化 =======
- GTID相关参数
参数 | comment |
---|---|
gtid_executed | 执行过的所有GTID |
gtid_purged | 丢弃掉的GTID |
gtid_mode | GTID模式 |
gtid_next | session级别的变量,下一个gtid |
gtid_owned | 正在运行的GTID |
enforce_gtid_consistency | 保证GTID安全的参数 |
====== 开启GTID的必备条件 ======
gtid_mode=on (必选)
enforce-gtid-consistency=1 (必选)
log_bin=mysql-bin (可选) #高可用切换,最好开启该功能
log-slave-updates=1 (可选) #高可用切换,最好打开该功能
4.2 GTID工作原理
从服务器连接到主服务器之后,把自己执行过的GTID (Executed_Gtid_Set: 即已经执行的事务编码) 、获取到的GTID (Retrieved_Gtid_Set: 即从库已经接收到主库的事务编号) 发给主服务器,主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候,找出同步最成功的那台从服务器,直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主, 先change到同步最成功的那台从服务器, 等把GTID全部补全了,就可以把它提升为主了。
GTID是MySQL 5.6的新特性,可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识一个事务。当事务提交时,MySQL Server在写binlog的时候,会先写一个特殊的Binlog Event,类型为GTID_Event,指定下一个事务的GTID,然后再写事务的Binlog。主从同步时GTID_Event和事务的Binlog都会传递到从库,从库在执行的时候也是用同样的GTID写binlog,这样主从同步以后,就可通过GTID确定从库同步到的位置了。也就是说,无论是级联情况,还是一主多从情况,都可以通过GTID自动找点儿,而无需像之前那样通过File_name和File_position找点儿了。
简而言之,GTID的工作流程为:
- master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。
- slave端的i/o 线程将变更的binlog,写入到本地的relay log中。
- sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录。
- 如果有记录,说明该GTID的事务已经执行,slave会忽略。
- 如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog。
- 在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描。
4.3 GTID主从配置
环境说明:
数据库角色 | IP | 应用与系统版本 |
---|---|---|
主数据库 | 192.168.10.145 | centos8/redhat8 mysql-5.7 |
从数据库 | 192.168.10.150 | centos8/redhat8 mysql-5.7 |
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysqldump -uroot -p'654321' --all-databases > /opt/all-20220802.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
[root@localhost ~]# cd /opt/
[root@localhost opt]# ls
all-20220802.sql
[root@localhost opt]# scp /opt/all-20220802.sql root@192.168.10.150:/opt/
The authenticity of host '192.168.10.150 (192.168.10.150)' can't be established.
ECDSA key fingerprint is SHA256:n0Dkwe1M34tJ4a5o5+F7/QfqfwCkHlPmjNl+BKqWjgY.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.10.150' (ECDSA) to the list of known hosts.
root@192.168.10.150's password:
all-20220802.sql 100% 0 0.0KB/s 00:00
[root@localhost opt]#
从库操作
[root@localhost ~]# mysql -uroot -p123456 < /opt/all-20220802.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost ~]#
主库配置
[root@localhost ~]# vi /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin //启用binlog日志
server-id=1 //数据库服务器唯一标识符,主库的server-id值必须小于从库
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]#
从库配置
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2 //设置从库的唯一标识符,从库的server-id值必须小于主库的该值
relay-log=mysql-relay-bin //启用中继日志relay-log
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
//重启从库的mysql服务
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
主库授权复制用户
mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
从库设置要同步的主库信息,并开启同步。
mysql> change master to
-> master_host='192.168.10.145',
-> master_user='repl',
-> master_password='123456',
-> master_port=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.145
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 437
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 650
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
检测
#主库创建名为school的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
从库查看
主从同步可以实现
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| test |
+--------------------+
6 rows in set (0.01 sec)
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了