mysql主从复制

随便投投

已于 2022-10-11 15:11:18 修改

阅读量85

点赞数 1

分类专栏： MySQL Linux服务文章标签： mysql 数据库服务器

于 2022-08-02 10:31:11 首次发布

本文链接：https://blog.csdn.net/ZJunWhite/article/details/126117248

版权

Linux服务同时被 2 个专栏收录

20 篇文章 0 订阅

订阅专栏

MySQL

7 篇文章 0 订阅

订阅专栏

主从复制与GTID主从

文章目录

主从复制

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. 主从复制配置

主从复制配置步骤：

1.确保从数据库与主数据库里的数据一样
2.在主数据库里创建一个同步账号授权给从数据库使用
3.配置主数据库（修改配置文件）
4.配置从数据库（修改配置文件）

需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.159.158	centos8/redhat8mysql-5.7	有数据
从数据库	192.168.159.162	centos8/redhat8mysql-5.7	无数据

3.1 mysql安装

分别在主从两台服务器上安装mysql-5.7版本，此处略过安装步骤，若有疑问请参考二进制格式mysql安装

3.2 mysql主从配置

主库配置

//主库创建一个用于同步的用户授权给从库使用
mysql> create user 'zhang'@'192.168.159.162' identified by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'zhang'@'192.168.159.162';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@master ~]# vim /etc/my.cnf 
[root@master ~]# cat /etc/my.cnf
[mysqld] 
basedir = /usr/local/mysql 
datadir = /opt/mysqldata 
socket = /tmp/mysql.sock 
port = 3306 
pid-file = /opt/mysqldata/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  //设置服务器唯一标识符，主库的id值必须比从库的大

[root@master ~]# systemctl restart mysqld.service  //重启服务生效配置文件

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)

从库配置

[root@slave ~]# vim /etc/my.cnf 
[root@slave ~]# cat /etc/my.cnf
[mysqld] 
basedir = /usr/local/mysql 
datadir = /opt/mysqldata 
socket = /tmp/mysql.sock 
port = 3306 
pid-file = /opt/mysqldata/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 = mysql-relay-bin  //启用中继日志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                    *:*      

//配置并启动主从复制
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.159.158',
    -> MASTER_USER='zhang',
    -> MASTER_PASSWORD='123',
    -> 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.159.158
                  Master_User: zhang
                  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
            Slave_SQL_Running: Yes

测试验证

在主服务器的student库的bj2表中插入数据：

mysql> select * from bj2;
Empty set (0.01 sec)

mysql> 
mysql> insert into bj2 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.01 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 bj2;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | sean  |  20 |
|  2 | tom   |  23 |
|  3 | jerry |  30 |
+----+-------+-----+
3 rows in set (0.00 sec)

GTID主从

GTID工作原理

简而言之，GTID的工作流程为：

master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
如果有记录，说明该GTID的事务已经执行，slave会忽略。
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

GTID主从配置

环境说明：

数据库角色	IP	应用与系统版本
主数据库	172.16.12.128	centos8/redhat8mysql-5.7
从数据库	172.16.12.129	centos8/redhat8mysql-5.7

主库配置。vi /etc/my.cnf，添加以下配置，重启mysql。

//主库创建一个同步授权从库的用户
mysql> create user 'zhang'@'192.168.159.162' identified by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'zhang'@'192.168.159.162';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@master ~]# vim /etc/my.cnf 
[root@master ~]# cat /etc/my.cnf
[mysqld] 
basedir = /usr/local/mysql 
datadir = /opt/mysqldata 
socket = /tmp/mysql.sock 
port = 3306 
pid-file = /opt/mysqldata/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
server-id = 10
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on

从库配置。vi /etc/my.cnf, 添加以下配置，重启mysql。

[root@slave ~]# vim /etc/my.cnf 
[root@slave ~]# cat /etc/my.cnf
[mysqld] 
basedir = /usr/local/mysql 
datadir = /opt/mysqldata 
socket = /tmp/mysql.sock 
port = 3306 
pid-file = /opt/mysqldata/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
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

//配置并启动主从复制
mysql> change master to
    -> master_host='192.168.159.158',
    -> master_user='zhang',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.159.158
                  Master_User: zhang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: myrelay.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试主从

//master
mysql> create database run;
Query OK, 1 row affected (0.00 sec)

mysql> use run;
Database changed
mysql> create table gent(id int,name varchar(25) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| run                |
| student            |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

//slave
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| run                |
| student            |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use run;
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_run |
+---------------+
| gent          |
+---------------+
1 row in set (0.00 sec)

mysql> desc gent;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(25) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)