MySQL主从复制原理剖析与搭建

MySQL主从复制原理介绍

  MySQL主从复制集群在中小型企业、大型企业中被广泛应用，MySQL主从复制的目的是实现数据库冗余备份，将master数据库数据定时同步到slave库中，一旦master数据库宕机，可以将web应用数据库配置快速切换到slave数据库，确保web应用有较高的可用率。
  MySQL主从复制集群至少需要2台数据库服务器，其中一台为master，另一台为slave，MySQL主从数据同步是一个异步复制的过程，要实现复制首先需要在master上开启bin-log日志功能，bin-log日志用于记录在master中执行增、删、修改、更新操作的SQL语句，整个过程需要开启3个线程，分别是master开启I/O线程，slave开启I/O线程和SQL线程，具体主从复制原理详解如下：

• slave上执行start slave，slave I/O线程会通过在master创建的授权用户连接上至master，并请求master从指定的文件和位置之后发送bin-log日志内容；
• master接收到来自slave I/O线程的请求后，master I/O线程根据slave发送的指定bin-log日志position点之后的内容，然后返回给slave的I/O线程；
• 返回的信息中除了bin-log日志内容外，还有master最新的bin-log文件名以及在bin-log中的下一个指定更新position点；
• slave I/O线程接收到信息后，将接收到的日志内容依次添加到slave端的relay-log文件的最末端，并将读取到的master端的bin-log的文件名和position点记录到master.info文件中，以便在下一次读取的时候能告知master从相应的bin-log文件名及最后一个position点开始发起请求；
• slave SQL线程检测到relay-log中内容有更新，会立刻解析relay-log日志的内容，将解析后的SQL语句在slave里执行，执行成功后slave库与master库数据保持一致。
    MySQL主从复制架构图如下：
  
    MySQL主从复制集群有以下情景：
• 单向主从同步模式或一主两从模式，此架构只能在master上写入数据
  
• 双向主从同步模式，此架构可以在master1或master2上进行数据写入，或两端同时写入
  
• 线性级联单向双主复制，此架构只能在 Master1 端进行数据写入，工作场景中，Master1 和 Master2 作为主主互备，Slave1 作为从库，中间的 Master2 需要做特殊的设置。
  
• 环状级联单向多主同步模式，任意一个点都可以写入数据，此架构比较复杂，属于极端环境下的“作品”，一般场景应慎用。
  

MySQL主从复制实战

环境准备

环境：centos7.6
MySQL版本：5.7.27
master：192.168.90.171
slave：192.168.90.172

master配置

1.修改MySQL的my.cnf配置文件

[root@db ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html


[client]
default-character-set=utf8
port=3306
socket=/var/lib/mysql/mysql.sock

[mysql]
default-character-set=utf8
no-auto-rehash


#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

[mysqld]
character-set-server=utf8
port=3306 
log-bin=mysql-bin #添加的内容
server-id=1 #添加的内容


datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet=16MB

2.重启MySQL，并进入MySQL命令行创建用户账号，查看master状态，记录二进制文件名

[root@db ~]# systemctl restart mysqld
[root@db ~]# mysql -u root -p
................
mysql> create user '用户名'@'%' identified by '密码';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to '用户名'@'%';
Query OK, 0 rows affected (0.00 sec)

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     2142 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

salve配置

1.修改MySQL的配置文件

[root@web01 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html


[client]
default-character-set=utf8
port=3306
socket=/var/lib/mysql/mysql.sock


[mysql]
default-character-set=utf8
no-auto-rehash



#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

[mysqld]
character-set-server=utf8
port=3306
server-id=2 #添加的内容


datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet=16MB

2.重启MySQL，进入MySQL命令行执行以下SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)

[root@web01 ~]# systemctl restart mysqld
[root@web01 ~]# mysql -u root -p
.......................
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='192.168.90.171',
    -> master_user='用户名',
    -> master_password='密码',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

mysql> show slave status\G;

当Slave_IO_Running：yes
Slave_SQL_Running：yes表示slave成功连接master实现同步。

3.测试，在master创建mydb数据库和t0表，slave查看是否有mydb数据库和t0表，然后向t0表插入数据，再查看slave的t0表中是否有相同的数据

master端：
mysql> create database mydb charset=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed
mysql> create table t0(id varchar(20),name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t0 values("001","xiaoli");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t0 values("002","zhangsan");
Query OK, 1 row affected (0.00 sec)

salve端：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use mydb
mysql> select * from t0;
+------+----------+
| id   | name     |
+------+----------+
| 001  | xiaoli   |
| 002  | zhangsan |
+------+----------+
2 rows in set (0.01 sec)

有数据，证明搭建成功！