MySQL 5.7 主从复制&读写分离

1、概念介绍

1.1、MySQL复制应用场景

读写分离，提高查询访问性能，有效减少主数据库访问压力。
实时灾备，主数据库出现故障时，可快速切换到从数据库。
数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。

1.2、MySQL主从复制原理

1.2.1、MySQL异步和半同步复制

蓝色箭头表示在不同 server 之间或者 server 与 client 应用之间的信息交互。

传统的 MySQL 复制提供了一种简单的主–从复制方法。有一个主，以及一个或多个从。主节点执行和提交事务，然后将它们（异步地）发送到从节点，以重新执行（在基于语句的复制中）或应用（在基于行的复制中）。这是一个shared-nothing 的系统，默认情况下所有 server 成员都有一个完整的数据副本。

半同步复制，它在协议中添加了一个同步步骤。这意味着主节点在提交时需要等待从节点确认它已经接收到事务。只有这样，主节点才能继续提交操作。

1.2.2、MySQL主从复制过程

开启binlog日志，通过把主库的 bin log 传送到从库，从新解析应用到从库。
复制需要3个线程（dump、io、sql）完成。
复制是异步的过程。主从复制是异步的逻辑的SQL语句级的复制。

1.2.3、MySQL主从复制前提

主服务器一定要打开二进制日志
必须两台服务器（或者是多个实例）
从服务器需要一次数据初始化
如果主从服务器都是新搭建的话，可以不做初始化
如果主服务器已经运行了很长时间了，可以通过备份将主库数据恢复到从库。
主库必须要有对从库复制请求的用户。
从库需要有relay-log设置，存放从主库传送过来的二进制日志 show variables like '%relay%';
在第一次的时候，从库需要change master to 去连接主库。
change master信息需要存放到 master.info 中 show variables like '%master_info%';
从库怎么知道，主库发生了新的变化?通过relay-log.info记录的已经应用过的relay-log信息。
在复制过程中涉及到的线程
- 从库会开启一个IO thread(线程)，负责连接主库，请求binlog，接收binlog并写入relaylog。
- 从库会开启一个SQL thread(线程)，负责执行relay-log中的事件。
- 主库会开启一个dump thrad(线程)，负责响应从IO

1.3.4、MySQL主从复制的实现

通过二进制日志
至少两台（主、从）
主服务器的二进制日志“拿”到从服务器上再运行一遍。
通过网络连接两台机器，一般都会出现延迟的状态。也可以说是异步的。
从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件 user、password、port、ip
并且让从库知道，二进制日志的起点位置（fifile名 position号）
启动从库同步服务 start slave
从库的IO和主库的dump线程建立连接
从库根据change master to 语句提供的fifile名和position号，IO线程向主库发起binlog的请求
主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程
从库IO线程接收binlog evnets，并存放到本地relay-log中，传送过来的信息，会记录到master.info中。
从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info,默认情况下，已经应用过的relay会自动被清理purge。

1.3、MySQL复制三种核心格式

复制的工作原理是数据库修改记录到bin log日志并传递到slave，然后slave在本地还原的过程。而时间记录到bin log的格式会有所不同。

1.3.1、基于语句的复制（Statement based replication）

基于主库将SQL语句写入bin log中完成复制

1.3.2、基于行数据的复制（Row based replication）

基于主库将每一行的数据变化信息作为时间写入到bin log中完成日志。

1.3.3、混合复制（Mixed based replication）

上述两者结合，默认情况下优先使用基于语句的复制，只有当部分语句如果是基于语句复制不完全的情况下才会自动切换为基于行数据的复制

#查看默认复制格式
mysql> SHOW VARIABLES LIKE '%BINLOG_FORMAT%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)
#修改为基于行复制
mysql> SET binlog_format='ROW';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%BINLOG_FORMAT%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

1.2、普通主从复制

普通主从复制主要是基于二进制日志文件位置的复制，因此主必须启动二进制日志记录并建立唯一的服务器ID，复制组中的每个服务器都必须配置唯一的服务器ID。如果您省略server-id（或者明确地将其设置为其默认值0），则主设备将拒绝来自从设备的任何连接。

1.3、GTID模式（全局事务标识符）

1.3.1、基本概念

MySQL 5.6 的新特性之一，全局事务标识符（GTID）是创建的唯一标识符，并与在源（主）服务器上提交的每个事务相关联。此标识符不但是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有一对一的映射关系。它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器上唯一，在所有存在主从关系的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠。一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。一个GTID被表示为一对坐标，用冒号（:）分隔，如下所示：GTID = source_id:transaction_id，source_id标识的源服务器。通常情况下，服务器 server_uuid用于这个目的。这transaction_id是一个序列号，由在此服务器上提交事务的顺序决定。 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 在传统的主从复制slave端，binlog是不用开启的，但是在GTID中slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）。GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。 mysql的主从复制是十分经典的一个应用，但是主从之间总会有数据一致性（data consistency ）的问题，一般情况从库会落后主库几个小时，而且在传统一主多从(mysql5.6之前)的模型中当master down掉后，我们不只是需要将一个slave提成master就可以，还要将其他slave的同步目的地从以前的master改成现在master，而且bin-log的序号和偏移量也要去查看，这是十分不方便和耗时的，但mysql5.6引入gtid之后解决了这个问题。

1.3.2、GTID优缺点

优点

GTID相对于行复制数据安全性更高，故障切换更简单。
简单的实现 failover，不用以前那样在需要找 log_file 和 log_pos。
更简单的搭建主从复制，确保每个事务只会被执行一次。
比传统的复制更加安全，一个 GTID 在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
GTID是连续的没有空洞的，保证数据的一致性，零丢失
GTID 用来代替classic的复制方法，不再使用 binlog+pos 开启复制。而是使用 master_auto_postion=1 的方式自动匹配 GTID 断点进行复制。
GTID 的引入，让每一个事务在集群事务的海洋中有了秩序，使得 DBA 在运维中做集群变迁时更加方便
根据 GTID 可以快速的确定事务最初是在哪个实例上提交的。

缺点

主从库的表存储引擎必须是一致的

主从库的表存储引擎不一致，就会导致数据不一致。如果主从库的存储引擎不一致，例如一个是事务存储引擎，一个是非事务存储引擎，则会导致事务和 GTID 之间一对一的关系被破坏，结果就会导致基于 GTID 的复制不能正确运行； master：对一个innodb表做一个多sql更新的事物，效果是产生一个GTID。 slave：假设对应的表是MYISAM引擎，执行这个GTID的第一个语句后就会报错，因为非事务引擎一个sql就是一个事务。当从库报错时简单的stop slave; start slave;就能够忽略错误。但是这个时候主从的一致性已经出现问题,需要手工的把slave差的数据补上,这里要将引擎调整为一样的，slave也改为事务引擎。

不允许一个SQL同时更新一个事务引擎和非事务引擎的表事务中混合多个存储引擎，就会产生多个 GTID。当使用 GTID 时，如果在同一个事务中，更新包括了非事务引擎（如 MyISAM）和事务引擎（如 InnoDB）表的操作，就会导致多个 GTID 分配给了同一个事务。
在一个复制组中，必须要求统一开启GTID或是关闭GTID;
不支持create table….select 语句复制（主库直接报错）;create table xxx as select的语句，其实会被拆分为两部分，create语句和insert语句，但是如果想一次搞定，MySQL会抛出如下的错误。 ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT. create table xxx as select 的方式可以拆分成两部分，如下。 create table xxxx like data_mgr;insert into xxxx select *from data_mgr;
对于create temporary table 和 drop temporary table语句不支持；使用GTID复制模式时，不支持create temporary table 和 drop temporary table。但是在autocommit=1的情况下可以创建临时表，Master端创建临时表不产生GTID信息，所以不会同步到slave，但是在删除临时表的时候会产生GTID会导致，主从中断。
不支持sal_slave_skip_counter。mysql在主从复制时如果要跳过报错，可以采取以下方式跳过SQL（event）组成的事务，但GTID不支持以下方式。 set global SQL_SLAVE_SKIP_COUNTER=1;start slave sql_thread。

1.3.3、GTID复制原理

当一个事务在主库端执行并提交时，产生 GTID，一同记录到 binlog 日志中。
binlog 传输到 slave,并存储到 slave 的 relaylog 后，读取这个 GTID 的这个值设置 gtid_next 变量，即告诉 Slave，下一个要执行的 GTID 值。
sql 线程从 relay log 中获取 GTID，然后对比 slave 端的 binlog 是否有该 GTID。
如果有记录，说明该 GTID 的事务已经执行，slave 会忽略。
如果没有记录，slave 就会执行该 GTID 事务，并记录该 GTID 到自身的 binlog;
在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

2、环境准备

平台：parallels desktop 17 系统：CentOS Linux release 7.9.2009 (AltArch) MySQL：5.7.27, for Linux (aarch64)

10.211.55.100	master	主
10.211.55.101	slave	从
10.211.55.102	mycat	调度

3、普通主从复制

wget http://mirrors.aliyun.com/repo/Centos-altarch-7.repo -O /etc/yum.repos.d/CentOS-Base.repo
yum clean all && yum makecache

3.1、安装MySQL 5.7

3.1.1、下载安装包

隔离mysql进程

groupadd -r mysql && useradd -r -g mysql -s /sbin/nologin -M mysql

下载、解压

yum install -y libaio*
wget https://obs.cn-north-4.myhuaweicloud.com/obs-mirror-ftp4/database/mysql-5.7.27-aarch64.tar.gz
tar -xvf mysql-5.7.27-aarch64.tar.gz -C /usr/local/

3.1.2、配置

mv /usr/local/mysql-5.7.27-aarch64 /usr/local/mysql
mkdir -p /usr/local/mysql/logs
chown -R mysql:mysql /usr/local/mysql
ln -sf /usr/local/mysql/my.cnf /etc/my.cnf
cp -rf /usr/local/mysql/extra/lib* /usr/lib64/
mv /usr/lib64/libstdc++.so.6 /usr/lib64/libstdc++.so.6.old
ln -s /usr/lib64/libstdc++.so.6.0.24 /usr/lib64/libstdc++.so.6
cp -rf /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld

3.1.3、添加环境变量

[root@master ~]# vim /etc/profile
export  MYSQL_HOME
MYSQL_HOME=/usr/local/mysql
export  PATH=$PATH:$MYSQL_HOME/lib:$MYSQL_HOME/bin
[root@master ~]# source /etc/profile

3.1.4、初始化MySQL

mkdir /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
systemctl start mysqld
systemctl status mysqld

3.2、MySQL配置

[root@master ~]# mysql_secure_installation

VALIDATE PASSWORD PLUGIN can be used to test passwordsand improve security. It checks the strength of passwordand allows the users to set only those passwords which aresecure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: N

New password:
Re-enter new password:

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

测试登录

[root@master ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.27-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

3.3、MySQL 创建主从同步账号

在主库创建一个专门用来复制的数据库用户，所有从库都用这个用户来连接主库，确保这个用户只有复制的权限。

授权访问

grant all privileges on *.* to 'slave'@'10.211.55.%' identified by '123456' with grant option;
flush privileges;

授权复制

grant replication slave,replication client on *.* to 'rep'@'10.211.55.%' identified by '123456';

3.4、编辑主从配置文件

master

[root@master ~]# vim /etc/my.cnf
log_bin = mysql-bin
server-id = 1
systemctl restart mysql

slave

[root@slave ~]# vim /etc/my.cnf
log_bin = mysql-bin
server-id = 2
systemctl restart mysql

3.5、测试

slave节点连接master测试

[root@slave ~]# mysql -urep -h master -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.27-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

3.6、从库配置

查看master状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1824 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

从库开启slave

mysql> change master to
    -> master_host='10.211.55.100',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=1824;
    
mysql> start slave;

看到 Slave_IO_Running: Yes Slave_SQL_Running: Yes 即可。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.211.55.100
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1824
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.7、主库创建测试

#主库：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
#从库：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

4、GTD模式

4.1、准备好基础数据库环境

略

4.2、MySQL创建主从同步账号。

在主库创建一个专门用来复制的数据库用户，所有从库都用这个用户来连接主库，确保这个用户只有复制的权限。

mysql> grant all privileges on *.* to 'root'@'10.211.55.%' identified by '123456' with grant option;

mysql> create user 'slave'@'10.211.55.101' identified by "123456";

mysql> grant replication slave on *.* to 'slave'@'10.211.55.101';

mysql> flush privileges;

4.3、修改my.cnf文件【master】

[root@master ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@master ~]# vim /etc/my.cnf

server-id = 1
#开启GTID模式
gtid-mode = on
#开启二进制日志
log-bin = mysql-bin
#默认为mixed混合模式，更改成row复制，为了数据一致性
binlog_format = row
#跳过slave复制线程
skip-slave-start = 1
#从库binlog才会记录主库同步的操作日志
log-slave-updates = 1
#强制gtid一致性，开启后对特定的create table不被支持
enforce-gtid-consistency = on

[root@master ~]# systemctl restart mysql

查看master状态、gtid状态发现多了一项"Executed_Gtid_Set "

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

mysql> show  variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+

确认binlog日志功能打开

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

4.4、修改my.cnf文件【slave】

[root@slave ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@slave ~]# vim /etc/my.cnf

server-id = 2
binlog_format = row
log_bin = mysql-bin
binlog_format = row
log-slave-updates = 1
skip-slave-start = 1
gtid-mode = on
enforce-gtid-consistency = on

[root@slave ~]# systemctl restart mysql

4.5、配置slave授权

检查gtid模式状态

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='10.211.55.100',
    -> master_user='slave',
    -> master_password='123456',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.211.55.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 773
               Relay_Log_File: slave-relay-bin.000004
                Relay_Log_Pos: 454
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4.6、验证

master：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> create database test;
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      932 |              |                  | 99b9a980-1bd1-11ed-bc29-001c42dae94e:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql>  show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | b0771516-1bd1-11ed-9ded-001c4232c47f |
+-----------+------+------+-----------+--------------------------------------+

slave

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

4.7、手动创建数据库、表测试

启动配置之前，同样需要对从服务器进行初始化。对从服务器初始化的方法基本和基于日志点是相同的，只不过在启动了GTID模式后，在备份中所记录的就不是备份时的二进制日志文件名和偏移量了，而是记录的是备份时最后的GTID值。需要先在主数据库机器上把目标库备份一下，假设这里目标库是forwl，为了测试效果，下面手动创建

mysql> create database forwl character set utf8 collate utf8_general_ci;

mysql> use forwl;
mysql> create table if not exists demo (id int(10) primary key auto_increment,name varchar(50) not null);

mysql> insert into forwl.demo values(1,"test001"),(2,"test002"),(3,"test003");

mysql> select * from forwl.demo;
+----+---------+
| id | name    |
+----+---------+
|  1 | test001 |
|  2 | test002 |
|  3 | test003 |
+----+---------+
3 rows in set (0.00 sec)

从库查看

mysql> use forwl;
mysql> select * from forwl.demo;
+----+---------+
| id | name    |
+----+---------+
|  1 | test001 |
|  2 | test002 |
|  3 | test003 |
+----+---------+
3 rows in set (0.00 sec)

4.8、备份forwl库

mysql5.6使用mysqldump备份时，指定备份的具体库，使用--database mysql5.7使用mysqldump备份时，指定备份的具体库，使用--databases

[root@master ~]# mysqldump --single-transaction --master-data=2 --triggers --routines --databases forwl -uroot -p'123456' > /root/forwl.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
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.

[root@master ~]# ls | grep forwl
forwl.sql

警告：默认情况下，来自具有 GTID 的服务器的部分转储将包括所有事务的 GTID，即使是那些更改了数据库的抑制部分的事务。如果您不想恢复 GTID，请传递 --set-gtid-purged=OFF。要进行完整转储，请传递 --all-databases --triggers --routines --events。

查看备份数据库

[root@master ~]# cat forwl.sql
-- MySQL dump 10.13  Distrib 5.7.27, for Linux (aarch64)
--
-- Host: localhost    Database: forwl
-- ------------------------------------------------------
-- Server version	5.7.27-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED='99b9a980-1bd1-11ed-bc29-001c42dae94e:1-7';

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1669;

--
-- Current Database: `forwl`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `forwl` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `forwl`;

--
-- Table structure for table `demo`
--

DROP TABLE IF EXISTS `demo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `demo` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `demo`
--

LOCK TABLES `demo` WRITE;
/*!40000 ALTER TABLE `demo` DISABLE KEYS */;
INSERT INTO `demo` VALUES (1,'test001'),(2,'test002'),(3,'test003');
/*!40000 ALTER TABLE `demo` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database 'forwl'
--
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-08-14 22:38:17

4.8、同步备份到从库

主从需安装rsync：yum install -y rsync

[root@master ~]# rsync -e "ssh -p22" -avpgolr /root/forwl.sql root@slave:/root/
root@slave's password:
sending incremental file list
forwl.sql

sent 2,589 bytes  received 35 bytes  749.71 bytes/sec
total size is 2,494  speedup is 0.95

[root@slave ~]# ls | grep forwl
forwl.sql

与主服务器配置除了server_id不一致外，从服务器还可以在配置文件里面添加："read_only＝on"，使从服务器只能进行读取操作，此参数对超级用户无效，并且不会影响从服务器的复制。

5、读写分离

5.1、Mycat介绍

官网：http://www.mycat.org.cn/

Mycat是什么？

Mycat是一个开源数据库中间件，是一个实现了MySQL协议的的数据库中间件服务器，我们可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问Mycat，而Mycat再使用用MySQL原生（Native）协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，包括SQL Server、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。

Mycat能做什么？

读写分离
- 通过Mycat可以自动实现写数据时操作主数据库，读数据时操作从数据库，这样能有效地减轻数据库压力，也能减轻IO压力。
- 当主出现故障后，Mycat自动切换到另一个主上，进而提供高可用的数据库服务，当然我们需要部署多主多从的模式
分库分表
- 水平拆分（分库）：根据表中数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库服务器上面
- 垂直拆分（分表）：按照不同的表来拆分分到不同的数据库服务器之上，根据表的类型不同来拆分

5.2、准备Mycat环境

5.2.1、准备MySQL 5.7主从复制

主机名	IP	角色
master	172.16.0.9/12	主
slave	172.16.0.10/12	从
mycat	172.16.0.11/12	调度

5.2.2、确认配置是否正确

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

mysql> show  variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

5.2.3、创建授权

mysql> create user 'slave'@'172.16.0.%' identified by "Forwl@123";
mysql> grant replication slave on *.* to 'slave'@'172.16.0.%';
mysql> flush privileges;

5.3、下载mycat、Java

wget http://dl.mycat.org.cn/1.6.7.5/2020-4-10/Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz
yum install java-1.8.0-openjdk.x86_64

[root@mycat ~]# java -version
openjdk version "1.8.0_342"
OpenJDK Runtime Environment (build 1.8.0_342-b07)
OpenJDK 64-Bit Server VM (build 25.342-b07, mixed mode)

声明Java环境变量（若YUM安装的也可不声明）

[root@mycat ~]# vim /etc/profile

#set java environment
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.342.b07-1.el7_9.x86_64
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/jre/lib/rt.jar
export PATH=$PATH:$JAVA_HOME/bin

[root@mycat ~]# source /etc/profile

解压

[root@mycat ~]# tar -zxvf Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz -C /usr/local/

查看mycat配置文件

[root@mycat ~]# cd /usr/local/mycat/
[root@mycat mycat]# ls
bin  catlet  conf  lib  logs  version.txt
[root@mycat mycat]# cd ./conf/
[root@mycat conf]# ls
autopartition-long.txt       rule.xml
auto-sharding-long.txt       schema.xml
auto-sharding-rang-mod.txt   sequence_conf.properties
cacheservice.properties      sequence_db_conf.properties
ehcache.xml                  sequence_distributed_conf.properties
index_to_charset.properties  sequence_time_conf.properties
log4j2.xml                   server.xml
migrateTables.properties     sharding-by-enum.txt
myid.properties              wrapper.conf
partition-hash-int.txt       zkconf
partition-range-mod.txt      zkdownload

server.xml：用于配置mycat所需要的服务器信息。
- 配置序列生成方式
- 配置mycat逻辑数据库
- 配置mycat的访问账户和密码
schema.xml：用于配置的逻辑数据库的映射、表、分片规则、数据结点及真实的数据库信息
- 配置逻辑库映射
- 配置垂直切分的表
- 配置真实的数据库
- 配置读写结点
rule.xml：分片规则（分库分表）

5.4、MySQL授权

mysql> create database forwl;
mysql> grant all on forwl.* to slave@'172.16.0.%' identified by 'Forwl@123';
mysql> flush privileges;

5.5、编辑mycat配置文件

修改server.xml

[root@mycat ~]# vim /usr/local/mycat/conf/server.xml




        
        0
        0
        2
        0
        384m
        true
        
        
                Forwl@123
                forwl

修改schema.xml

[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml





        
        
               
                    
        
                select user()

dataNode定义了Mycat中的数据节点，也就是我们通常说所的数据分片，一个dataNode标签就是一个独立的数据分片，通俗理解，一个分片就是一个物理数据库

name属性：定义数据节点的名字，这个名字需要是唯一的，这个名字在schema里面会使用到

dataHost属性：用于定义该分片属于哪个数据库实例的，属性值是引用dataHost标签上定义的name属性

database属性：用于对应真实的数据库名，必须是真实存在的

dataHost标签的作用：定义具体的数据库实例、读写分离配置和心跳语句

heartbeat标签：用于和后端数据库进行心跳检查的语句，检测MySQL数据库是否正常运行 writeHost标签：将后端的数据库的相关配置给mycat,用于实例化后端连接池，写实例 readHost标签：将后端的数据库的相关配置给mycat,用于实例化后端连接池，读实例

switchType指的是切换的模式，目前的取值也有4种：

switchType=’-1’ 表示不自动切换；
switchType=‘1’ 默认值，表示自动切换；
switchType=‘2’ 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status；

switchType='3’基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like ‘wsrep%’。

balance：负载均衡类型，目前的取值有4种

balance="0"：读请求仅发送到writeHost上(不开启读写分离)。

balance="1"：读请求随机分发到当前writeHost对应的readHost和standby的writeHost上。

balance="2"：读请求随机分发到当前dataHost内所有的writeHost和readHost上。

balance="3"：读请求随机分发到当前writeHost对应的readHost上。

在一个dataHost内可以定义多个writeHost和readHost。但是，如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去

5.6、启动mycat

[root@mycat ~]# cd /usr/local/mycat/bin/
[root@mycat ~]# jps
13203 Jps
[root@mycat ~]# java -version
openjdk version "1.8.0_342"
OpenJDK Runtime Environment (build 1.8.0_342-b07)
OpenJDK 64-Bit Server VM (build 25.342-b07, mixed mode)
[root@mycat ~]# cd /usr/local/mycat/bin/
[root@mycat bin]# ./mycat start
Starting Mycat-server...
[root@mycat logs]# jps
13831 Jps
13741 WrapperSimpleApp
[root@mycat logs]# ss -ntpl | grep java
LISTEN     0      1      127.0.0.1:32000                    *:*                   users:(("java",pid=13741,fd=4))
LISTEN     0      50        [::]:42835                 [::]:*                   users:(("java",pid=13741,fd=74))
LISTEN     0      50        [::]:1984                  [::]:*                   users:(("java",pid=13741,fd=71))
LISTEN     0      100       [::]:8066                  [::]:*                   users:(("java",pid=13741,fd=101))
LISTEN     0      50        [::]:34402                 [::]:*                   users:(("java",pid=13741,fd=70))
LISTEN     0      100       [::]:9066                  [::]:*

若配置正确则可以看到8066端口

5.7、连接MyCat节点测试

[root@master ~]# mysql -u slave -pForwl@123 -h mycat -P8066

mysql> show databases;
+----------+
| DATABASE |
+----------+
| forwl    |
+----------+
1 row in set (0.01 sec)

mysql>

5.8、测试新建查询

新建字段ID、Name

添加记录

master查询

mysql> show tables;
+-----------------+
| Tables_in_forwl |
+-----------------+
| admin           |
+-----------------+
1 row in set (0.01 sec)

mysql> select * from admin;
+------+--------+
| ID   | Name   |
+------+--------+
| 001  | 张三   |
| 002  | 李四   |
+------+--------+
2 rows in set (0.01 sec)

slave查询

mysql> show tables;
+-----------------+
| Tables_in_forwl |
+-----------------+
| admin           |
+-----------------+
1 row in set (0.01 sec)

mysql> select * from admin;
+------+--------+
| ID   | Name   |
+------+--------+
| 001  | 张三   |
| 002  | 李四   |
+------+--------+
2 rows in set (0.00 sec)

七月

七月

MySQL 5.7 主从复制&读写分离

MySQL 5.7 主从复制&读写分离

1、概念介绍

1.1、MySQL复制应用场景

1.2、MySQL主从复制原理

1.2.1、MySQL异步和半同步复制

1.2.2、MySQL主从复制过程

1.2.3、MySQL主从复制前提

1.3.4、MySQL主从复制的实现

1.3、MySQL复制三种核心格式

1.3.1、基于语句的复制（Statement based replication）

1.3.2、基于行数据的复制（Row based replication）

1.3.3、混合复制（Mixed based replication）

1.2、普通主从复制

1.3、GTID模式（全局事务标识符）

1.3.1、基本概念

1.3.2、GTID优缺点

1.3.3、GTID复制原理

2、环境准备

3、普通主从复制

3.1、安装MySQL 5.7

3.1.1、下载安装包

3.1.2、配置

3.1.3、添加环境变量

3.1.4、初始化MySQL

3.2、MySQL配置

3.3、MySQL 创建主从同步账号

3.4、编辑主从配置文件

3.5、测试

3.6、从库配置

3.7、主库创建测试

4、GTD模式

4.1、准备好基础数据库环境

4.2、MySQL创建主从同步账号。

4.3、修改my.cnf文件【master】

4.4、修改my.cnf文件【slave】

4.5、配置slave授权

4.6、验证

4.7、手动创建数据库、表测试

4.8、备份forwl库

4.8、同步备份到从库

5、读写分离

5.1、Mycat介绍

5.2、准备Mycat环境

5.2.1、准备MySQL 5.7主从复制

5.2.2、确认配置是否正确

5.2.3、创建授权

5.3、下载mycat、Java

5.4、MySQL授权

5.5、编辑mycat配置文件

5.6、启动mycat

5.7、连接MyCat节点测试

5.8、测试新建查询

分享