MySQL数据库主从复制原理及作用分析

OStack程序员社区-中国程序员成长平台 › 门户 › 编程›数据库

原作者: [db:作者] 来自: [db:来源] 收藏邀请

1.数据库主从分类：

主从分为俩种：传统主从/GTID主从

2.mysql主从介绍由来

现实生活中，数据极其重要，存储数据库的方式很多，但是数据库存在着一种隐患。
隐患：

用一台数据库存放数据，若数据库服务器宕机了导致数据丢失数据多了，访问量大了，一台服务器无法保证服务质量

因此数据库主从诞生

3.主从作用

故障切换，实现预备读写分离，提供查询服务数据库管理系统备份（DBSM），避免影响业务

4.主从复制原理

bin log：二进制日志，记录写操作（增删改查）

Relay log：中继日志

主库会将所有的写操作记录到binlog日志下生成一个log dump线程，将binlog日志传给从库的I/O线程。
从库有俩个线程：
I/O线程
sql线程
从库的I/O线程会请求主库得到binlog日志写到relay log（中继日志）中
sql线程，会读取relay log日志文件中的日志，并解析具体操作，来实现主从的操作一样，达到数据一致

5.主从复制配置（数据一致时）

步骤：

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

环境需求：

俩台mysql服务器，一台主服务器（写功能），一台从服务器（读功能）

主数据库（centos8） ip地址：192.168.136.145 centos8.0/mysql5.7 相同数据
第六节：数据不相同（可能在公司之前有数据的情况）
从数据库（centos8） ip地址：192.168.136.191 centos7.0/mysql5.7 相同数据

5.1主从服务器分别安装mysql5.7

可看相关教程教程(超详细)：https://www.ogeek.net/article/221946.htm

#二进制安装：https://blog.csdn.net/qq_47945825/article/details/116848970?spm=1001.2014.3001.5501
#或者网络仓库安装：（一般二进制安装）
https://blog.csdn.net/qq_47945825/article/details/116245442?spm=1001.2014.3001.5501

5.2主数据库与从数据库数据一致

[root@mysql01 ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@mysql02 ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

5.3在主数据库里创建一个同步账号授权给从数据库使用

replication：复制 slave：从 192.168.136.191：从数据库ip地址

mysql> create user 'vvv'@'192.168.136.191' identified by 'vvv0917';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.*to 'vvv'@'192.168.136.191';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5.4在从库上测试连接

[root@mysql02 ~]# mysql -uvvv -vvv0917 -h192.168.136.145
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

5.5配置主数据库

[root@mysql01 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql_bin #启动binlog日志
server-id=10   #数据库服务器唯一标识，id必须比从数据库小
#重启服务 （此重启方式，前提已配置mysqld.service文件）
[root@mysql01 ~]# systemctl restart mysqld
观察主数据库状态：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000004 |      962 |              |                  |                   |
+------------------+----------+--------------+------------------+---

5.6配置从数据库

[root@mysql02 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql 
datadir = /opt/data 
socket = /tmp/mysql.sock 
port = 3307
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#skip-grant-tables 
server-id=20               #服务器id，大于主数据库id
relay-log=mysql_relay_log  #启动中继日志
#log-bin=mysql-bin 
#重启服务：
[root@mysql02 ~]# systemctl restart mysqld

5.7配置并启动主从复制的功能（mysql02从数据库上）

[root@slave02 ~]# mysql -uroot -p
mysql> change master to
    -> master_host='192.168.136.145',
    -> master_user='vvv',
    -> master_password='vvv0917',
    -> master_log_file='mysql_bin.000004',
    -> master_log_pos=962;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;   #stop slave为关闭
Query OK, 0 rows affected (0.01 sec)
#查看配置状态：
mysql> show slave status\G; 
   Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.145
                  Master_User: vvv
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000004
          Read_Master_Log_Pos: 962
               Relay_Log_File: mysql_relay_log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            #此处必须俩个都是yes，就是配置成功，否则失败

5.8测试：

主库：

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

从库:

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

主库创建数据库clq并且加入数据：

mysql> create database clq;
Query OK, 1 row affected (0.00 sec)
mysql> create table clq01(id int(11)not null primary key auto_increment,name varchar(100)not null,age tinyint(4)); 
mysql> insert clq01(name,age) values('A',20),('B',21),('C',22);
Query OK, 3 rows affected (0.00 sec)

从库中查看：

mysql> select * from clq01;
+----+------+------+s
| id | name | age  |
+----+------+------s+
|  1 | A    |   20 |
|  2 | B    |   21 |
|  3 | C    |   22 |
+----+------+------+
                              #主从复制完成！

6.主从配置（数据不一致时）

6.1一般全备主库需要另开一个终端，给数据库加上读锁（只读不写）

避免其他人在写入数据导致不一样

flush tables with read lock：
quit：退出即可为解锁（备份完之后才能解锁）

6.2确保主主数据库与从数据库的数据一样

#先对主库进行全备
[root@mysql01 ~]# mysqldump -uroot -A > all-databases.sql 
#拷贝数据到从数据库上
[root@mysql01 ~]# ls /clq
all-databases.sql
[root@mysql01 ~]# scp /clq/all-databases.sql [email protected]:/clq/
The authenticity of host '192.168.136.193 (192.168.136.193)' can't be established.
ECDSA key fingerprint is SHA256:XIAQEoJ+M0vOHmCwQvhUdw12u5s2nvkN0A4TMKLaFiY.
Are you sure you want to continue connecting (yes/no/[fingerprint])yes
[email protected]'s password: 
all-databases.sql                                                 100%  853KB 115.4MB/s   00:00  
[root@mysql02 clq]# ll
总用量 896                       #从库上查看
-rw-r--r--. 1 root root 873266 5月  17 19:36 all-databases.sql

6.3在从库上查看主库有哪些库，确保一致

[root@mysql02 clq]# mysql -uroot -pHuawei0917@ < all-databases.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql02 clq]# mysql -uroot -pHuawei0917@ -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| clq                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
主库：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| clq                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

6.4确保俩库的配置文件已经配置了相应的文件

[root@mysql01 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql_bin     #日志文件
server-id=10          #唯一标识服务id
[root@mysql02 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql 
datadir = /opt/data 
socket = /tmp/mysql.sock 
port = 3307
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#skip-grant-tables 
server-id=20                #唯一标识服务id（大于主库）
relay-log=mysql_relay_log     #中继日志
#log-bin=mysql-bin

此后步骤和5.5之后一模一样！

小结：

主库修改数据，从库的数据随之改变！
反之，从库修改数据，主库的数据不会发生改变

查看数据库运行的命令进程

mysql> show processlist;
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host                  | db   | Command     | Time | State                                                         | Info             |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  5 | repl | 192.168.136.219:39788 | NULL | Binlog Dump | 1575 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  7 | root | localhost             | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

以上就是MySQL数据库主从复制原理及作用分析的详细内容，更多关于MySQL数据库主从复制的资料请关注极客世界其它相关文章！