- A+
所属分类：linux技术
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 |应用与系统版本 |有无数据 |
| :--- | :----|:---|
|主数据库 |192.168.222.250 |centos8/redhat8 mysql-5.7 | 有数据 |
|从数据库 |192.168.222.251 | centos8/redhat8 mysql-5.7 | 无数据|
3.1 mysql安装
分别在主从两台服务器上安装mysql-5.7版本，此处略过安装步骤，若有疑问请参考《mysql进阶》
3.2 mysql主从配置
3.2.1 确保从数据库与主数据库里的数据一样
为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中
先查看主库有哪些库: [root@master ~]# mysql -uroot -plnh123 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | lnh | | mysql | | performance_schema | | sys | | tushanbu | +--------------------+ 再查看从库有哪些库: [root@slave ~]# mysql -uroot -plnh123 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 全备主库: //全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致 [root@master ~]# mysql -uroot -plnh123 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 8 Server version: 5.7.38 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> flush tables with read lock; Query OK, 0 rows affected (0.05 sec) //此锁表的终端必须在备份完成以后才能退出 因为主库已经锁住，我们可以再开一个主库的终端进行操作 [root@master ~]# mysqldump -uroot -plnh123 --all-databases > /opt/all-$(date '+%Y%m%d%H%M%S').sql //全量备份主库 mysqldump: [Warning] Using a password on the command line interface can be insecure [root@master ~]# ll /opt/ total 864 -rw-r--r--. 1 root root 878669 Aug 2 00:42 all-20220802004207.sql drwxr-xr-x. 7 mysql mysql 4096 Jul 27 22:04 xbz [root@master ~]# scp /opt/all-20220802004207.sql root@192.168.222.251:/opt/ The authenticity of host '192.168.222.251 (192.168.222.251)' can't be established. ECDSA key fingerprint is SHA256:y11UDaNXs3AnvVUnZQfAim2VHAplF09YOvQp2NemHyk. Are you sure you want to continue connecting (yes/no/[fingerprint])? y Please type 'yes', 'no' or the fingerprint: yes Warning: Permanently added '192.168.222.251' (ECDSA) to the list of known hosts. root@192.168.222.251's password: Permission denied, please try again. root@192.168.222.251's password: all-20220802004207.sql 100% 858KB 91.7MB/s 00:00 //将主库的备份数据传送到从库里面去 mysql> quit Bye //在主里面退出锁定 在从库里面： [root@slave ~]# ll /opt/ total 864 -rw-r--r--. 1 root root 878669 Aug 2 00:45 all-20220802004207.sql drwxr-xr-x. 5 mysql mysql 4096 Aug 1 23:45 xbz //可以查看到有数据备份的文件 [root@slave ~]# mysql -uroot -plnh123 < /opt/all-20220802004207.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@slave ~]# mysql -uroot -plnh123 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | lnh | | mysql | | performance_schema | | sys | | tushanbu | +--------------------+ //将主库里面的数据成功全部备份到从库里面实现了主库和从库里面的数据一致 3.2.2 在主数据库里创建一个同步账号授权给从数据库使用
[root@master ~]# mysql -uroot -plnh123 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 10 Server version: 5.7.38 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> grant replication slave on *.* to 'repl'@'192.168.222.251' identified by 'repl123'; Query OK, 0 rows affected, 1 warning (0.01 sec) //这里是授权从库的主机ip mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> quit Bye 3.2.3 配置主数据库
[root@master ~]# vim /etc/my.cnf [root@master ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/xbz socket = /tmp/mysql.sock port = 3306 pid-file = /opt/xbz/mysql.pid user = mysql skip-name-resolve sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION log-bin=mysql_bin //启用binlog日志 server-id = 10 //数据库服务器唯一标识符，主库的server-id值必须比从库的大 //添加这两行 10的目的是因为后面可能会有主库什么的 [root@master ~]# systemctl restart mysqld.service //重启服务 [root@master ~]# 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 128 *:80 *:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 80 *:3306 *:* [root@master ~]# mysql -uroot -plnh123 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.38-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 master status; //查看状态 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql_bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 3.2.4 配置从数据库
[root@slave ~]# vim /etc/my.cnf [root@slave ~]# cat /etc/my.cnf [mysqld] basedir = /usr/local/mysql datadir = /opt/xbz socket = /tmp/mysql.sock port = 3306 pid-file = /opt/xbz/mysql.pid user = mysql skip-name-resolve sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION server-id = 20 //设置从库的唯一标识符，从库的server-id值必须大于主库的该值 relay-log = myrelay //启用中继日志relay-log [root@slave ~]# systemctl restart mysqld.service //重启服务 [root@slave ~]# 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 128 [::]:22 [::]:* LISTEN 0 80 *:3306 *:* [root@slave ~]# mysql -uroot -plnh123 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.38 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> change master to //配置并启动主从复制 -> master_host='192.168.222.250', -> master_user='repl', -> master_password='repl123', -> master_log_file='mysql_bin.000001', -> master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.02 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.222.250 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000001 Read_Master_Log_Pos: 603 Relay_Log_File: myrelay.000002 Relay_Log_Pos: 769 Relay_Master_Log_File: mysql_bin.000001 Slave_IO_Running: Yes //这里必须是yes(这里如果没有yes那么就是找不到指定东西) Slave_SQL_Running: Yes //这里必须是yes(这里如果没有yes那么就是查询不了) Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 603 Relay_Log_Space: 968 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10 Master_UUID: 09b3989b-0d96-11ed-b83a-000c2905f428 Master_Info_File: /opt/xbz/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) 测试验证
在主服务器的lnh库的xbz表中插入数据：
mysql> use lnh; 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> create table xbz(id int not null,name varchar(20),age int); Query OK, 0 rows affected (0.03 sec) mysql> desc xbz; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into xbz values (1,'dad',20),(2,'tom',22),(3,'jerry',12); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from xbz; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | dad | 20 | | 2 | tom | 22 | | 3 | jerry | 12 | +----+-------+------+ 3 rows in set (0.00 sec) 在从数据库中查看数据是否同步：
[root@slave ~]# mysql -uroot -plnh123 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 6 Server version: 5.7.38 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> use lnh; 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 xbz; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | dad | 20 | | 2 | tom | 22 | | 3 | jerry | 12 | +----+-------+------+ 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在一组复制中，全局唯一.
如图：server1（master）宕机了，这个时候server2（slave）已经同步到了server1的全部数据，而server3（slave）只是同步到了一半，这时要是把Server2提升为主，Server3变成Server2的从。这时在Server3上执行change的时候需要做一些计算。这个问题在5.6的GTID出现后，就显得非常的简单。由于同一事务的GTID在所有节点上的值一致，那么根据Server3当前停止点的GTID就能定位到Server2上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现，我们都不需要知道GTID的具体值，直接使用CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令就可以直接完成failover的工作。
====== 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文件呢？
假设有3个binlog: bin.001,bin.002,bin.003,
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
假设现在我们要找GTID=$A，那么MySQL的扫描顺序为:
从最后一个binlog开始扫描（即: bin.003）
bin.003的Previous-GTIDs=1-80，如果$A=100 > Previous-GTIDs,那么肯定在bin.003中
bin.003的Previous-GTIDs=1-80，如果$A=79 包含在Previous-GTIDs中,那么继续对比上一个binlog文件 bin.002,然后再循环前面2个步骤，直到找到为止.
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工作原理
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.222.250 | centos8/redhat8 mysql-5.7 |
| 从数据库 | 192.168.222.251 | centos8/redhat8 mysql-5.7 |
我这里是直接进行yum/dnf下载mysql的
详细操作可以看我的《mysql基础里面》
做之前要确保主从库的里面数据一样
主库配置。vim /etc/my.cnf，添加以下配置，重启mysql。
[root@lnh ~]# vim /etc/my.cnf //添加下面这几行 log-bin=mysql_bin server-id=10 gtid_mode=on enforce-gtid-consistency=true log-slave-updates=on [root@lnh ~]# systemctl restart mysqld.service //重启服务 从库配置。vim /etc/my.cnf, 添加以下配置，重启mysql。
[root@slave ~]# vim /etc/my.cnf //添加下面这几行 server-id=20 relay-log=myrelay gtid_mode=on enforce-gtid-consistency=true log-slave-updates=on read_only=on master-info-repository=TABLE relay-log-info-repository=TABLE [root@slave ~]# systemctl restart mysqld.service //重启服务 主库授权复制用户。
[root@lnh ~]# mysql -uroot -plnh123 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.39-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> 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> grant replication slave on *.* to 'repl'@'%' identified by 'repl123!'; Query OK, 0 rows affected, 1 warning (0.01 sec) 从库设置要同步的主库信息，并开启同步。
mysql> change master to master_host='192.168.222.250',master_port=3306,master_user='repl',master_password='repl123!',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 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.222.250 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000001 Read_Master_Log_Pos: 603 Relay_Log_File: myrelay.000002 Relay_Log_Pos: 769 Relay_Master_Log_File: mysql_bin.000001 Slave_IO_Running: Yes //这里必须是yes(这里如果没有yes那么就是找不到指定东西) Slave_SQL_Running: Yes //这里必须是yes(这里如果没有yes那么就是查询不了) Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 603 Relay_Log_Space: 968 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10 Master_UUID: 09b3989b-0d96-11ed-b83a-000c2905f428 Master_Info_File: /opt/xbz/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) 配置完之后，通过查看slave的状态，可以看是否配置成功。同时可以在主库进行一些操作，提交一些事务(insert,update)，之后数据就会自动同步到从库。
