msyql主从复制(全网最详细的讲解)

msyql主从复制

一、原理

下面简单描述下 MySQ Replication 复制的原理及过程

1 ）在 slave 服务器上执行 start slave 命令，主从复制开始进行

2）此时，slave 服务器的 I/O 线程 会通过在 Master 上已经授权的用户请求连接 Master 服务器 ，并请求从指定 binlog 日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行 change master 命令指定的）之后开始发送 binlog 日志内容
3) Master 服务器接收到来自 slave 服务器的 I/O 线程的请求之后，其上负责复制的 binlog dump 线程会根据请求的信息，分批读取指定 binlog 日志文件所指定位置之后的 binlog 日志信息，然后返回给 Slave 端的 I/O 线程。 返回的信息中除了 binlog 日志内容之外，还包括在 Master 服务器端记录的新的 binlog 文件名称， 以及在新的 binlog 中的下一个指定的更新位置

4）当 slave 服务器的 I/O 线程获取到 Master 服务器上 I/O 线程发送的日志内容及 日志文件和位置点之后 ，会将 binlog 日志内容依次写入到 Slave 端自身的 Relay Log （即中继日志）文件（ MySQL-relay-bin.xxxxxx ）的最末端，并将新的 binlog 文件名和位置记录到 master info 文件中，以便下次读取 Master 端新 binlog 日志时，能够告诉 Master 服务器需要从新 binlog 日志的指定文件及位置开始请求新的 binlog 日志内容

5 ) Slave 服务器端的 SQL 线程会实时地检测本地 Relay Log 中 I/O 线程新增加的日志内容，然后及时地把 Relay Log 文件中的内容解析成 SQL 语句， 并在自身 slave 服务器上按解析 SQL 语句的位置顺序执行，并将当前应用中继日志的文件名及位置点记录在 relay- log.info

经过了上面的过程，就可以确保在 Master 端和 slave 端执行了同样的 SQL 语句 在复制状态正常的情况下， Master 端和 Slave 端的数据是完全一样 当然， MySQ 复制机制也包含一些特殊的情况。

二、实践

1、实验环境

主：centos 7.8 网络环境桥接

从：centos 7.8 网络环境桥接

数据库: Mysql 8.0

关闭selinux

关闭firewall（或开放相应端口） 具体可看之前的详细文档《防火墙firewall》

2、linux Mysql 8.0 的安装

1.下载mysql的yum仓库
[root@xingdian-mysql-1 /]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
2.安装mysql的yum仓库
[root@xingdian-mysql-1 /]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm
3.安装数据库
[root@xingdian-mysql-1 /]# yum -y install mysql-server mysql
4.查看数据库的初始密码
[root@localhost ~]# systemctl start mysqld
[root@xingdian-mysql-1 /]# grep 'password' /var/log/mysqld.log
2019-07-13T15:14:31.176905Z 1 [Note] A temporary password is generated for root@localhost:k12zPB1r;2Ta
5.修改数据库密码
[root@xingdian-mysql-1 /]# mysqladmin -u root -p'旧密码' password'Litaimin@123'
密码：大小写有特殊字符数字

3、实验步骤

3.1 主库部署

（1）设置server-id的值并开启binlog功能

1、
vim /etc/my.cnf

[mysqld]
...
server_id=132
log_bin=/application/mysql/logs/qianhui_bin      //后面可以不带等号部分， MySQL使用默认路径及日志名。
...

2、修改了配置信息需要重启mysql服务

3、登录数据库检查参数的更改情况
mysql> show variables like 'log_bin' ;     //如果显示下图所示，表示binlog功能开启

log_bin     | ON         


查看server_id
mysql> show variables like 'server_id';

查看主库状态
mysql> show master status;

​

（2）创建用于主从复制的账号，在主库上给从库提供一个账号

create user 'slave'@'192.168.80.%' identified by 'password';
grant all privileges on *.* to 'slave'@'192.168.80.%';
FLUSH PRIVILEGES;

（3）对主数据库锁表只读（当前窗口不要关掉）的命令如下：

 mysql> flush table with read lock;
 
 #查看解锁时间
 mysql> show variables like '%timeout%';
 
 | interactive_timeout               | 28800    |

3.2 数据备份并传送

（1）逻辑备份

mysqldump -uroot -p -A >./bak_$(date +%F).sql.gz

（2）传输文件

scp bak_2020-11-18.sql.gz root@ip:~/

3.3 从库部署

（1）修改配置文件，添加server_id（从库不能开启log_bin功能）

# vim /etc/my.cnf

...
server_id =215
...


重启服务

（2）将备份数据导入

# zcat ./bak_2020-11-18.sql.gz |mysql -uroot -p     

（3）登录从库配置复制参数

mysql> change master to master_host='192.168.3.48',master_port=3306,master_user='slave',master_password='Litaimin@123',master_log_file='qianhui_bin.000003',master_log_pos=1676;

#参数详解
MASTER_HOST  //这里是主库的工
MASTER_PORT      //这里是主库的端口，从库的端口可以与主库的不同
MASTER_USER     //这里是主库上建立的用于复制的用户 rep
MASTER_PASSWORD  //这里是 rep 用户的密码
MASTER_LOG_FILE   //这里是 show master status 时查看到的二进制日志文件名称，注意不能多出空格
MASTER_LOG_POS      //这里是 show master status 时查看到的二进制日志偏移量，注意不能多出空格

（4）启动从库主从复制开关

start slave ;

（5）查看复制状态

show slave status \G;

主从复制是否配置成功了，最关键的是查看下面3项状态参数：
Slave IO_Running       : Yes
Slave_SQL_Running     : Yes
Seconds_Behind_Master : O           // 从库比主库延迟的秒数

(6) 解锁表

mysql> unlock tables;