MySQL主从复制

一、MySQL主从复制原理
- 1.1、MySQL 支持的复制类型
- 1.2、复制的工作过程
二、需要主从复制原因与改造方法
- 2.1、原因
- 2.2、改造办法
- 2.3、更高级解决办法
三、实验过程
- 3.1、实验环境
- 3.2、建立时间同步环境
- 3.3、主服务器、从服务器1、从服务器2均安装MySQL服务
- 3.4、主服务器配置
- 3.5、从服务器 1 配置
- 3.6、从服务器 2 配置
- 3.7、验证：MySQL主从服务器
- - 主服务器
  - 从服务器 1
  - 从服务器 2
- 3.8、实验小问题

一、MySQL主从复制原理

1.1、MySQL 支持的复制类型

基于语句的复制（默认）
在主服务器上执行的语句，从服务器执行同样的语句
基于行的复制
把改变的内容复制到从服务器
混合类型的复制
一旦发现基于语句无法精确复制时，就会采用基于行的复制

1.2、复制的工作过程

① 在每个事务更新数据完成之前，Master在二进制日志记录这些改变。写入二进制日志完成后，Master通知存储引擎提交事务
②Slave将Master的Binary log复制到其中继日志。首先，Slave开始一个工作线程——I/O线程，I/O线程在Master上打开一个普通的连接，然后开始Binlog dump process。Binlog dump process从Master的二进制日志中读取事件，如果已经跟上Master，他会睡眠并等待Master产生新的事件。I/O线程将这些事件写入中继日志。
③SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新Slave的数据，使其与Master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
复制过程有一个很重要的限制，即复制在Slave上是串行化的，也就是说Master上的并行更新操作不能再Slave上并行操作。

二、需要主从复制原因与改造方法

2.1、原因

■在企业网站中，后端MySQL数据库只有一台时，会有以下问题

单点故障，服务不可用
无法处理大量的并发数据请求
数据丢失——大灾难

2.2、改造办法

■改造办法

增加MySQL数据库服务器，对数据进行备份，形成主备
确保主备MySQL数据库服务器数据是一样的
主服务器宕机了，备份服务器继续工作，数据有保障

2.3、更高级解决办法

■更高级解决办法

通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力

三、实验过程

3.1、实验环境

服务器配置

  IP地址          主机              操作系统
20.0.0.25      master      centos-7.6-x86_64
20.0.0.24      Slave1      centos-7.6-x86_64
20.0.0.23      Slave2      centos-7.6-x86_64

三台MySQL服务器关闭防火墙，关闭核心防护

3.2、建立时间同步环境

在主服务器上安装配置NTP时间同步服务器（20.0.0.25）

[root@localhost~]# yum -y install ntp ###如果不是最小化安装，就不用yum安装ntp

[root@localhost~]# vi /etc/ntp.conf 
###删除下面四行###
server 0.centos.pool.ntp.org iburst
server 1.centos.pool.ntp.org iburst
server 2.centos.pool.ntp.org iburst
server 3.centos.pool.ntp.org iburst
###添加这两行###
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@localhost~]# systemctl restart ntpd
[root@localhost~]# systemctl enable ntpd
[root@localhost ~]# service ntpd restart
[root@localhost ~]# systemctl restart ntpd
[root@localhost ~]# systemctl enable ntpd

在从服务器1上配置NTP同步（20.0.0.24）

[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 20.0.0.25
[root@localhost ~]# crontab -e
*/2 * * * * /usr/sbin/ntpdate 20.0.0.24 >> /var/log/ntpdate.log
[root@localhost ~]# touch /var/log/ntpdate.log 
[root@localhost ~]# systemctl restart crond
[root@localhost ~]# systemctl enable crond
[root@localhost ~]# tail -f /var/log/ntpdate.log ###动态查看更新日志文件

在从服务器2上配置NTP同步（20.0.0.23）

[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 20.0.0.25
[root@localhost ~]# crontab -e
*/2 * * * * /usr/sbin/ntpdate 20.0.0.23 >> /var/log/ntpdate.log
[root@localhost ~]# touch /var/log/ntpdate.log 
[root@localhost ~]# systemctl restart crond
[root@localhost ~]# systemctl enable crond
[root@localhost ~]# tail -f /var/log/ntpdate.log ###动态查看更新日志文件

3.3、主服务器、从服务器1、从服务器2均安装MySQL服务

[root@localhost~]# yum -y install \
ncurses \
ncurses-devel \
bison \
cmake \
gcc \
gcc-c++
[root@localhost~]# useradd -s /sbin/nologin mysql
[root@localhost~]# cd /opt
[root@localhost opt]# tar xf mysql-boost-5.7.20.tar.gz
[root@localhost opt]# cd /opt/mysql-5.7.20/
[root@localhost mysql-5.7.20]# 
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
[root@localhost mysql-5.7.20]# make -j4
[root@localhost mysql-5.7.20]# make install

[root@localhost mysql-5.7.20]# chown -R mysql:mysql /usr/local/mysql/ 
 '//数据库目录进行权限调整'

[root@localhost mysql-5.7.20]# vi /etc/my.cnf 
 '//建立调整配置文件'
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

[root@localhost mysql-5.7.20]# chown mysql:mysql /etc/my.cnf
[root@localhost mysql-5.7.20]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@localhost mysql-5.7.20]# echo 'export PATH' >> /etc/profile 
     '//设置环境变量'
[root@localhost mysql-5.7.20]# source /etc/profile 
'//生效'

[root@localhost mysql-5.7.20]# cd /usr/local/mysql/

[root@localhost mysql]# bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@localhost mysql]# systemctl enable mysqld
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# systemctl status mysqld
[root@localhost mysql]# netstat -anpt | grep 3306
[root@localhost mysql]# mysqladmin -u root -p password
'//设置密码'
[root@localhost ~]# mysql -u root -p ## 验证是否可以登录数据库
Enter password: 
mysql>

3.4、主服务器配置

[root@localhost ~]# vi /etc/my.cnf ###在原来server-id=1的地方修改成11后面新增log_bin=master-bin log-slave-updates=true###
server-id = 11         ## 修改为 11，下面的从服务器也会改，3个server-id 不可相同
log_bin = master-bin   ##设置二进制日志名
log-slave-updates = true   ## 从服务器更新二进制日志

[root@localhost ~]# systemctl restart mysqld ###重启数据库

登录Master数据库给从服务器授权
[root@localhost ~]# mysql -uroot -p
## 让20.0.0.0端的从服务器拥有复制权限，可以使用myslave身份12345密码复制所有的库和表
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '12345';
mysql> flush privileges;   ## 刷新
mysql> show master status;  
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      599 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.5、从服务器 1 配置

[root@localhost ~]# vi /etc/my.cnf ###在原来server-id=1的地方修改成22 后面新增relay-log=relay-log-bin relay-log-index=slave-relay-bin.index###
server-id = 22     ## 修改为22，另一台从服务器的id设为33，三个id不可相同
relay-log = relay-log-bin          ## 设置二进制日志名
relay-log-index = slave-relay-bin.index      ## 服务器更新二进制日志
[root@localhost ~]# systemctl restart mysqld ###重启数据库
登录slave数据库配置同步 ###注意下这边的master_log_file='master-bin.000001'，master_log_pos=601;要和Master数据库信息不一致，不一致的话要更改先stop slave;然后更改同步信息
[root@localhost ~]# mysql -uroot -p12345
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 20.0.0.25
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 599
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes   ## 查看IO是正常的，这里显示 Yes 就可以了
            Slave_SQL_Running: Yes   ## 查看SQL是正常的，这里显示 Yes 就可以了
            ....................略........................
            1 row in set (0.00 sec)

3.6、从服务器 2 配置

[root@localhost ~]# vi /etc/my.cnf
server-id = 33
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p12345
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;

mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 20.0.0.25
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 599
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes   ## 这里显示 Yes 就可以了
            Slave_SQL_Running: Yes   ## 这里显示 Yes 就可以了
            ....................略........................
            1 row in set (0.00 sec)

3.7、验证：MySQL主从服务器

主服务器

mysql> create database School;  ## 创建 School库
Query OK, 1 row affected (0.00 sec)

mysql> show databases;   ## 查看主服务器有 School库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| School             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

从服务器 1

mysql> show databases;   ## 查看从服务器 1 有 School库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| School             |   ## 同步成功！
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

从服务器 2

mysql> show databases;   ## 查看从服务器 2 有 School库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| School             |   ## 同步成功！
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

3.8、实验小问题

主从复制没有成功，显示的参数是这样 Slave_IO_Running: Connecting 。
解决办法：

## 当我们输入这句代码完之后
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
            Slave_IO_Running: Connecting  ## 这里并没有改成 Yes
            Slave_SQL_Running: Yes

1、可能是因为 master_log_pos=599 这个值输入错了导致没有同步。
2、也可能是因为主服务器 IP 输入错了，或者是密码错了。

这时候我们需要 stop slave

mysql> stop slave;  ## 先关闭
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='20.0.0.25',master_user='myslave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=599;  ## 然后再给一次复制权限，把里面的参数改成正确的参数
mysql> start slave;  ## 在开启
mysql> show slave status\G
*************************** 1. row ***************************
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
## 再查看就已经设置成功了！
## 排障完成

实验做到这里就结束了！我们最后验证了是可以进行主从复制的。
    1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
    2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
    3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

下一篇博客我将介绍主从复制的读写分离！欢迎收看！

• 不愧是Alibaba技术官：程序员必会的架构知识清	• 【Redis】求求你，别再问跳表了
• Linux修改网卡命令规则和配置IP地址（简单易懂	• 大数据到底应该如何学？
• 架构师梳理4万字长篇PDF：程序员必备核心知识点	• 学生党白嫖服务器-不会吧不会吧，你还在买学生

• Esp8266天猫精灵_RGB灯_非点灯平台	• STM32F103 串口1和串口3对发数据配合蓝牙模块
• TMS570学习【1】了解什么是TMS570	• 新闻稿 \| Qt公司收购froglogic公司以巩固市场领
• [Java]SpringBoot2整合mqtt服务器EMQ实现消息订	• 苹果群控投屏同步操作原理及运用的平台APP分享

• Esp8266天猫精灵_RGB灯_非点灯平台	• STM32F103 串口1和串口3对发数据配合蓝牙模块
• TMS570学习【1】了解什么是TMS570	• 新闻稿 \| Qt公司收购froglogic公司以巩固市场领
• [Java]SpringBoot2整合mqtt服务器EMQ实现消息订	• 苹果群控投屏同步操作原理及运用的平台APP分享
• STM32查询式按键输入[直接用寄存器]	• Ubuntu系统 USB设备端口绑定
• 2021-04-14 第四次按键输入实验	• Flutter扫码功能完美实现

MySQL服务器主从复制同步——有这一篇就够了！我会主从复制了，你呢？