MySQL实战：主从复制

MySQL主从复制

一、复制概述

将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行，从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

二、复制原理

MySQL主从复制原理：
(1) Master的更新事件(update、insert、delete)会按照顺序写入bin-log中。当Slave连接到Master的后,Master机器会为Slave开启binlog dump线程,该线程会去读取bin-log日志

(2) Slave连接到Master后,Slave库有一个I/O线程 通过请求binlog dump thread读取bin-log日志,然后写入从库的relay log日志中。

(3) Slave还有一个 SQL线程,实时监控 relay-log日志内容是否有更新,解析文件中的SQL语句,在Slave数据库中去执行。


总结
(1) 既然是要把事件记录到bin-log日志，那么对于Master就必须开启bin-log功能。

(2) 整个Mysql主从复制一共开启了3个线程。Master开启 IO线程，Slave开启 IO线程 和 SQL线程。

(3) 这点也很重要那就是Master和Slave交互的时候，记住这里是Slave去请求Master,而不是Master主动推给Slave。Slave通过IO线程连接Master后发起请求,Master服务器收到Slave IO线程发来的日志请求信息，io线程去将bin-log内容返回给slave IO线程。

三、复制优点

(1) 主库出现问题，可以快速切换到从库提供服务

(2) 在从库上执行查询操作，在主库上执行更新操作，实现读写分离，降低主库的访问压力

(3) 在从库上执行备份，以避免备份期间影响主库的服务

四、搭建主从复制

master-主库

1.修改配置文件

在master 的配置文件 /etc/my.cnf 中，添加如下内容:

cat >>/etc/my.cnf<<'EOF'
[mysqld]
log-bin=mysql-bin   # 启用二进制日志，数据间复制必不可少
server-id=100       # 服务器唯一ID
EOF

2.重启MySQL

systemctl restart mysqld.service

3.创建同步数据的用户并授权

1.mysql8授权用户需要先创建，创建和授权同一条语句的话会报错
create user slave@'%' identified by 'fxx123';
2.授权
GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%'WITH GRANT OPTION;
3.刷新权限
flush privileges;

注:上面SQL的作用是创建一个用户slave，密码为fxx123，并且给slave用户授予REPLICATION SLAVE权限。常用于建立复制时所需要用到的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过该用户复制。

4.登录Mysql数据库

登录Mysql数据库，执行下面SQL，记录下结果中File和Position的值

show master status;

注:上面SQL的作用是查看Master的状态，执行完此SQL后不要再执行任何操作

slave-从库

1.修改配置文件

在slave 的配置文件 /etc/my.cnf 中，添加如下内容:

cat >>/etc/my.cnf<<'EOF'
[mysqld]
server-id=101       # 服务器唯一ID
EOF

2.重启MySQL

systemctl restart mysqld.service

3.登录Mysql数据库

登录Mysql数据库，执行下面SQL

1.执行前先停下slave
stop slave;

2.这是从库和主库连接的关键一步，host是主库的ip，user是前面创建的slave用户，file和pos是主库show master status的信息
change master to master_host='10.0.0.51',master_user='slave',master_password='fxx123',master_log_file='mysql-bin.000002',master_log_pos=874;

3.执行后启动slave
start slave;

4.登录Mysql数据库，执行下面SQL，查看从数据库的状态
show slave status\G;

ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'fxx123';

五、验证同步操作

1.主库操作

主库中创建数据库，创建表，并插入数据

create database db01;

use db01;

create table user(
    id int(11) not null auto_increment,
    name varchar(50) not null,
    sex varchar(1),
    primary key (id)
)engine=innodb default charset=utf8;

insert into user(id,name,sex) values(null,'Tom','1');
insert into user(id,name,sex) values(null,'Trigger','0');
insert into user(id,name,sex) values(null,'Dawn','1');

2.从库操作

从库查取数据
show databases；
+--------------------+
| Database           |
+--------------------+
| db01               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use db01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 1    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 1    |
+----+---------+------+
3 rows in set (0.00 sec)

mysql> 

此时主库的内容一旦有变更，就会同步到从库。