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

1.数据库主从分类：

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

 

2.mysql主从介绍由来

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

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

因此数据库主从诞生

 

3.主从作用

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

 

4.主从复制原理

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

Relay log：中继日志

1. 主库会将所有的写操作记录到binlog日志下生成一个log dump线程，将binlog日志传给从库的I/O线程。
2. 从库有俩个线程：
   I/O线程
   sql线程
3. 从库的I/O线程会请求主库得到binlog日志写到relay log（中继日志）中
4. 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

可看相关教程教程(超详细)：http://www.tuohang.net/article/195316.html

?

1 2 3

#二进制安装：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主数据库与从数据库数据一致

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

[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地址

?

1 2 3 4 5 6

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在从库上测试连接

?

1 2 3 4 5 6 7 8

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

 

5.5配置主数据库

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

[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配置从数据库

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[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从数据库上）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

[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 statusG;     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测试：

 

主库：

?

1 2 3 4 5 6 7 8 9

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

 

从库:

?

1 2 3 4 5 6 7 8 9

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

 

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

?

1 2 3 4 5

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)

 

从库中查看：

?

1 2 3 4 5 6 7 8 9

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

 

 

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

 

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

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

?

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

 

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

#先对主库进行全备 [root@mysql01 ~] # mysqldump -uroot -A > all-databases.sql #拷贝数据到从数据库上 [root@mysql01 ~] # ls /clq all - databases.sql [root@mysql01 ~] # scp /clq/all-databases.sql root@192.168.136.193:/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 root@ 192.168 . 136.193 '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在从库上查看主库有哪些库，确保一致

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

[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确保俩库的配置文件已经配置了相应的文件

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

[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之后一模一样！

小结：

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

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

?

1 2 3 4 5 6 7 8

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数据库主从复制的资料请关注服务器之家其它相关文章！

原文链接：https://blog.csdn.net/qq_47945825/article/details/119995132