MySQL主从复制

复制概述

复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

复制原理

MySQL主从复制原理如下：

从上层来看，复制分成三步：
1）Master主库在事物提交时，会把数据变更作为时间Events记录在二进制日志文件Binlog中
2）主库推送二进制日志文件Binlog中的日志事件到从库的中继日志Relay Log
3）slave从库中继日志的事件，将改变反应它的数据

复制优势

MySQL复制优点主要包括以下三个方面：
1）主库出现问题，可以快速切换到从库提供服务
2）可以在从库上执行查询操作，从库中更新，实现读写分离，降低主库访问的压力
3）可以在从库中执行备份，以避免备份期间影响主库的服务

集群搭建

主节点配置

1）在master的配置文件（/etc/my.cnf）中，配置如下内容：

# mysql服务ID，保证整个集群环境中唯一
server-id=1
# mysql binlog日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin
#是否只读，1代表只读，0代表读写
read-only=0
#忽略的数据，指不需要同步的数据库，mysql是自己管理的数据，不需要读
binlog-ignore-db=mysql
1
2
3
4
5
6
7
8

2）重启mysql服务

systemctl restart mysqld
1

3）创建同步数据的账户，并且进行授权操作：

#创建用户
CREATE USER `slave01`@'%' IDENTIFIED BY 'slave123456';
#授权slave
GRANT REPLICATION SLAVE ON *.* TO `slave01`@'192.168.14.167【从机数据库IP】' IDENTIFIED BY 'slave123456';
grant replication slave on *.* to `slave01`@'121.41.103.116' identified by 'slave123456';
#更新权限
flush privileges;
1
2
3
4
5
6
7

4）查询master的状态

#查询master的状态
show master status;
1
2

字段含义：

File：从哪个日志文件开始推送日志文件
Positon：从哪个位置开始推送日志
Binlog_Ignore_DB：指定不需要同步的数据库
1
2
3

从节点配置

1）在slave端配置文件中，配置如下内容：

vi /etc/my.cnf

#mysql服务端ID，唯一
server-id=2
#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin
1
2
3
4
5
6

2）重启mysql服务

systemctl restart mysqld
1

3）执行如下命令，配置参数参考主库master的show master status;
指定当前从库对应主库的IP地址，用户名，密码，从哪个日志文件开始的哪个位置开始同步推送日志

stop slave;
change master to master_host='106.52.36.65',master_user='slave01',master_password='slave123456',master_log_file='mysqlbin.000011',master_log_pos=5143;
1
2

4）开启同步操作

start slave;
show slave status\G;
1
2

5）停止同步操作

stop slave;
1

验证同步操作

1）在主库中创建数据库，创建表，并插入数据：

create database test;

use test;
CREATE TABLE `city` (
  `city_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city_name` varchar(56) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`),
  UNIQUE KEY `idx_city_name` (`city_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `city` VALUES (1, '西安市', 1);
INSERT INTO `city` VALUES (2, 'NewYork', 2);
INSERT INTO `city` VALUES (3, '北京', 1);
INSERT INTO `city` VALUES (4, '上海', 1);
INSERT INTO `city` VALUES (5, '深圳', 1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2）在从库中查询验证