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)
有数据,证明搭建成功!