MYSQL主从同步详细教程

=======================================================================

• 随着访问量的不断增加，单台MySQL数据库服务器压力不断增加，需要对MYSQL进行优化和架构改造，MYQSL优化如果不能明显改善压力情况，可以使用高可用、主从复制、读写分离来、拆分库、拆分表来进行优化。

• MYSQL主从复制集群在中小企业、大型企业中被广泛使用，MYSQL 主从复制的目的是实现数据库冗余备份，将Master数据库数据定时同步至Slave库中，一旦Master数据库宕机，可以将WEB应用数据库配置快速切换至Slave数据库，确保WEB应用较高的可用率，如图所示，为MYSQL主从复制结构图:

简介

---

MYSQL 主从复制至少需要2台数据库服务器，其中一台为Master库，另外一台为Slave 库，mysql 主从同步是一个异步的过程。要实现复制，首先需要在master 上开启server-id和bin-log日志。

bin-log日志主要用来记录库中执行的增、删、修改、更新操作的sql语句，整个过程需要开启3个线程，分别是master上的IO线程， Slave 上的IO线程 和SQL线程。

同步原理详解

---

具体主从同步原理详解：

Slave 上执行start slave，Slave IO线程会 通过master创建的授权用户连接上master,并请求master从指定的文件和位置之后发送bin-log日志类容。

Master 接收到来自slave IO线程的请求后，master IO 线程会根据 Slave 指定的bin-log日志和 position点之后的内容返回给Slave IO线程。

Slave IO线程接收到信息后，将接收到的日志内容一次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件和position 点记录到master.info文件中。以便在下一次读取的时候能告知master从响应的bin-log文件名及最后一个position点开始发起请求。

Slave Sql线程检测到relay-log日志中日志又更新，会立刻解析relay-log的内容，成为在Master真实执行时候的那些可执行的SQL语句，将解析的语句并在Slave里执行，执行成功后，，Master 和Slave 库保持数据一致性。

MYSQL主从同步形式

---

• 一主一从

• 主主复制

• 一主多从(扩展系统读取的性能)

• 多主(5.7开始)

MYSQL 主从主要作用

---

• 实时灾备，用于故障切换

• 读写分离，提供查询服务

主从复制配置步骤

---

• 搭建二进制数据库

• 配置主数据库（修改配置文件）

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

• 配置从数据库（修改配置文件）

MYSQL主从搭建

---

1、实验环境

| 数据库角色 | IP | 系统版本 | mysql版本 | 数据 |

| :-: | :-: | :-: | :-: | :-: |

| Master | 192.168.10.11 | CentOS 7.4 | 5.7.30 | 有数据 |

| Slave | 192.168.10.12 | CentOS 7.4 | 5.7.30 | 无数据 |

2、两台主机安装相同的mysql

搭建教程

3、配置主从数据库

• master 库配置

• 开启日志(server-id=1，log-bin=xx)

[root@localhost ~]# cat /etc/my.cnf

[mysqld]

basedir = /usr/local/mysql

datadir = /data/mysql

socket = /tmp/mysql.sock

port = 3306

pid-file = /data/mysql/mysql.pid

user = mysql

skip-name-resolve

server-id=1

log-bin=hebao_log.bin

[root@localhost ~]#

• 创建授权用户，授权( grant replication slave on *.* to 'liuaoni'@'%' identified by '123456')

[root@localhost ~]# mysql -uroot -proot

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright © 2000, 2020, 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> grant replication slave on . to ‘liuaoni’@‘%’ identified by ‘123456’;

Query OK, 0 rows affected, 1 warning (0.01 sec) replication 复制权限

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

±-----------------±---------±-------------±-----------------±------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

±-----------------±---------±-------------±-----------------±------------------+

| hebao_log.000001 | 592 | | | |

±-----------------±---------±-------------±-----------------±------------------+

1 row in set (0.00 sec)

mysql>

• Slave 数据库配置

mysql> change master to master_host=‘192.168.10.11’,master_user=‘liuaoni’,master_password=‘123456’,master_log_file=‘hebao_log.000001’,master_log_pos=592;

Query OK, 0 rows affected, 2 warnings (0.14 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.11

Master_User: liuaoni

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: xx_log.000004

Read_Master_Log_Pos: 836

Relay_Log_File: localhost-relay-bin.000004

Relay_Log_Pos: 317

Relay_Master_Log_File: xx_log.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

看到 Slave_IO_Running: Yes

Slave_SQL_Running: Yes 都为yes就好

• 测试：

主上创建数据库：

mysql> create database Master_db;

Query OK, 1 row affected (0.14 sec)

mysql>

从上查看：

mysql> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| Master_db |

| mysql |

| mysql_back |

| performance_schema |

| sys |

±-------------------+

6 rows in set (0.00 sec)

mysql常用配置文件参数：

| 参数 | 说明 |

| :-: | :-- |

| port = 3306 | 设置监听端口 |

| socket = /tmp/mysql.sock | 指定套接字文件位置 |

| basedir = /usr/local/mysql | 指定MySQL的安装路径 |

| datadir = /data/mysql | 指定MySQL的数据存放路径 |

| pid-file = /data/mysql/mysql.pid | 指定进程ID文件存放路径 |

| user = mysql | 指定MySQL以什么用户的身份提供服务 |

| skip-name-resolve | 禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求 |

MYSQL 备份与恢复

---

• 数据库备份方案

• 全量备份：某一个时间点上的所有数据或应用进行一个完全的拷贝，备份时间长，数据恢复快

• 增量备份：是指在一次完全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加 或者被修改的文件。没有重复数据，备份时间短，恢复数据必须按一定的顺序进行

• 差异备份：备份上一次的完全备份后发生变化的所有文件。差异备份是指 在一次全备份后到进行差异备份的这段时间内对那些以增加或者修改文件的备份。在恢复时，只需要对第一次全量备份和最后一次差异备份进行恢复

• MYSQL 备份工具mysqldump

语法：

mysqldump [OPTIONS] database [tables …]

mysqldump [OPTIONS] --all-databases [OPTIONS]

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]

常用的OPTIONS：

-uUSERNAME 指定数据库用户名

-hHOST 指定服务器主机，请使用ip地址

-pPASSWORD 指定数据库用户的密码

-P# 指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

• 备份整个数据库(全备)

[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-202104202203.sql

Enter password: