写点什么

阿里 P7:每个码农都应该知道的 MySQL 主从复制方法,看这篇就够了

发布于: 2021 年 05 月 10 日
阿里P7:每个码农都应该知道的MySQL主从复制方法,看这篇就够了

上一篇是分享的是《OpenJDK》,这篇给大家分享《MySQL 主从复制》。


目录:

  • MySQL 主从复制

  • MySQL 主从复制过程

  • MySQL 多实例数据库搭建

  • MySQL 多实例数据库启动与关闭

  • MySQL 一主多从架构

  • MySQL 双主双从配置开启二进制日志

  • MySQL 一主多从环境重启及 MySQL 双主双从数据复制验证

  • MySQL 多实例数据库配置

  • MySQL 双主双从重置主从状态

MySQL 主从复制

若数据库仅有一台服务器,那么单点问题比较容易产生,若服务器访问响应者崩溃,那么服务器就不能使用了,若硬盘坏了,则整个数据就丢失了,将是一个重大的安全事故。

想要避免数据的丢失,最起码准备两台或多台的服务器,经数据多分复制部署不同服务器上,若某一台出现故障,则其他服务器提供工作。

MySQL 提供了主从复制功能以提高服务的可用性与数据的安全可靠性;

主从复制是指服务器分为主服务器和从服务器,主服务器负责读和写,从服务器只负责读,主从复制也叫 master/slave,master 是主,slave 是从;

MySQL 主从复制架构

MySQL 一主多从架构

MySQL 双主双从架构

MySQL 主从复制过程

如果探测到 master 主服务器的二进制日志发生了改变,则开始出现一个 I/O Thread 请求 master 二进制日志;

同时 master 主服务器为每个人 I/O Thread 启动一个 dump Thread,用于向其发送二进制日志;

slave 服务器将接收到的二进制日志保存至自己本地的中继日志文件中;

salve 从服务器将启动 SQL Thread 从中继日志中读取二进制日志,在本地重放,使得其数据和主服务器保持一致;

最后 I/O Thread 和 SQL Thread 将进入睡眠状态,等待下一次被唤醒;

MySQL 主从复制原理图:

MySQL 多实例数据库搭建

1、在 MySQL 安装主目录下创建/data/3307, /data/3308, /data/3309, /data/3310 四个目录;

2、执行数据库初始化,在 MySQL 的/usr/local/mysql-5.7.18/bin 目录下执行命令:

./mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.18 --datadir=/usr/local/mysql-5.7.18/data/3307 --user=mysql./mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.18 --datadir=/usr/local/mysql-5.7.18/data/3308 --user=mysql./mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.18 --datadir=/usr/local/mysql-5.7.18/data/3309 --user=mysql./mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.18 --datadir=/usr/local/mysql-5.7.18/data/3310 --user=mysql
复制代码

其中 initialize-insecure 表示不生成 MySQL 数据库 root 用户的随机密码,即 root 密码为空;

3、在/data/3307, /data/3308, /data/3309, /data/3310 四个目录下分别创建一个 my.cnf 文件;

4、配置四个 MySQL 数据库服务的 my.cnf 文件

[client]port        = 3307socket      = /usr/local/mysql-5.7.18/data/3307/mysql.sockdefault-character-set=utf8
[mysqld]port = 3307socket = /usr/local/mysql-5.7.18/data/3307/mysql.sockdatadir = /usr/local/mysql-5.7.18/data/3307log-error = /usr/local/mysql-5.7.18/data/3307/error.logpid-file = /usr/local/mysql-5.7.18/data/3307/mysql.pid
character-set-server=utf8lower_case_table_names=1autocommit = 1
复制代码

至此多数据库实例就搭建好了;

MySQL 多实例数据库启动与关闭

MySQL 多实例数据库启动

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3307/my.cnf &./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3308/my.cnf &./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3309/my.cnf &./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3310/my.cnf &
复制代码

其中 --defaults-file 是指定配置文件,& 符合表示后台启动;

MySQL 多实例数据库关闭

切换到/usr/local/mysql-5.7.18/bin 目录下,使用 mysqladmin 命令 shutdown

方式一:

./mysqladmin -uroot -p -S /usr/local/mysql-5.7.18/data/3307/mysql.sock shutdown

方式二:

./mysqladmin -uroot -p -P3307 -h127.0.0.1 shutdown

方式三: 如果已经进入到了 MySQL 的命令行,可以直接执行 shutdown;

退出 MySQL 命令行,执行: exit

MySQL 一主多从架构

主(3307)里面加入

log-bin=mysql-bin #表示启用二进制日志

server-id=3307 #表示 server 编号,编号要唯一

从(3308)里面加入

server-id=3308 #表示 server 编号,编号要唯一

从(3309)里面加入

server-id=3309 #表示 server 编号,编号要唯一

从(3310)里面加入

server-id=3310 #表示 server 编号,编号要唯一

MySQL 一主多从环境重启

进入/usr/local/mysql-5.7.18/bin 目录,重启四个 MySQL 服务,启动时指定配置文件启动:

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3307/my.cnf &./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3308/my.cnf &./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3309/my.cnf &./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3310/my.cnf &
复制代码

MySQL 一主多从设置主从关系

1、在主服务器上创建复制数据的账号并授权:

grant replication slave on *.* to 'copy'@'%' identified by '123456';

2、建议重置一下主服务状态,执行命令:reset master;

3、在主服务器上执行命令,获取主服务器二进制 binlog 文件名及坐标(二进制文件名和坐标值在后面会用到):

show master status;

在从服务器上执行命令,设置从服务器的 master

4、重置一下从的状态:(在重置之前可以查看一下从的状态: show slave status)

stop slave;

reset slave;

5、change master to master_host='192.168.199.128',master_user='copy',

master_port=3307,master_password='123456',

master_log_file='mysql-bin.000001',master_log_pos=154;

6、再从机器上执行开始复制命令:start slave; (作用:启动 IO 线程和 SQL 线程)

MySQL 一主多从数据复制验证

检查从服务器复制功能状态

mysql> show slave status \G

Slave_IO_Running 和 Slave_SQL_Running 为 YES,就是正常状态。

在主服务器上创建数据库、表、数据,然后在从服务器上查看是否已经复制

以上操作过程显示正常,则主从服务器配置完成;

查看主从复制 binlog 日志文件内容:show binlog events in 'mysql-bin.000001';

MySQL 双主双从配置开启二进制日志

主 master 3307 ---> 从 slave 3309

主 master 3308 ---> 从 slave 3310

3307 <---> 3308 互为主从

2 个写节点,每个写节点下又是 2 个读节点;

MySQL 双主双从配置开启二进制日志

1、配置每一台 MySQL 服务器的配置文件 my.cnf

3307

3308

3309

3310

2、与一主多从一样,所有主服务器配置文件加上 binlog 配置

3307

3308

3309

3310

log-bin=mysql-bin

server-id=实例的端口(server-id 要唯一)

3、第一台主服务器 3307 的 my.cnf 文件增加如下配置:(Master 3307)

auto_increment_increment=2
auto_increment_offset=1
log-slave-updates
sync_binlog=1
复制代码

4、第二台主服务器 3308 的 my.cnf 文件增加如下配置:(Master 3308)

auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1
复制代码

配置项说明

auto_increment_increment,控制主键自增的自增步长,用于防止 Master 与 Master 之间复制出现重复自增字段值,通常 auto_increment_increment=n,有多少台主服务器,n 就设置为多少;

auto_increment_offset=1 设置自增起始值,这里设置为 1,这样 Master 的 auto_increment 字段产生的数值是:1, 3, 5, 7, …等奇数 ID

注意 auto_increment_offset 的设置,不同的 master 设置不应该一样,否则就容易引起主键冲突,比如 master1 的 offset=1,则 master2 的 offset=2,master3 的 offset=3

在双主模式中,log-slave-updates 配置项一定要配置,否则在 master1(3307)上进行了更新数据,在 master2(3308)和 slave1(3309)上会更新,但是在 slave2(3310)上不会更新

sync_binlog 表示每几次事务提交,MySQL 把 binlog 缓存刷进日志文件中,默认是 0,最安全的是设置为 1;

MySQL 一主多从环境重启及 MySQL 双主双从数据复制验证

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3307/my.cnf &./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3308/my.cnf &./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3309/my.cnf &./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3310/my.cnf &
复制代码

MySQL 双主双从数据复制验证

检查从服务器复制功能状态,执行命令:

mysql> show slave status \G

如果 Slave_IO_Running 和 Slave_SQL_Running 的值为 Yes,即表示复制功能配置正常;

设置好双主双从复制后:

1. 一个主出现问题,可以切换到另外的主进行写数据,新主同步数据给它的从;

2. 待问题旧主恢复服务后,新的主会同步数据给它,它再同步数据给自己的从,这样不会出现数据的不同步和服务的不可用。

MySQL 多实例数据库配置

其中 -p 是指定密码,如果没有密码则可以不写 -p,-S 是指定 sock 文件,mysql.sock 文件是服务器与本机客户端进行通信的 ip 与端口文件;

或者使用用端口、主机登录 ./mysql -uroot -p -P3307 -h127.0.0.1 登录进入 MySQL

2、修改 mysql 的密码,执行:

alter user 'root'@'localhost' identified by '123456';(其中 123456 是我们设置的密码)

3、授权远程访问,执行命令:(这样远程客户端才能访问)

grant all privileges on *.* to root@'%' identified by '123456';

其中*.* 的第一个*表示所有数据库名,第二个*表示所有的数据库表;

root@'%' 中的 root 表示用户名,%表示 ip 地址,%也可以指定具体的 ip 地址,比如 root@localhost,root@192.168.10.129

4、执行以下如下命令刷新权限:

flush privileges;

以上步骤一次性执行:

alter user 'root'@'localhost' identified by '123456';

grant all privileges on *.* to root@'%' identified by '123456';

flush privileges;

MySQL 双主双从重置主从状态

./mysql -uroot -p -P3307 -h127.0.0.1
./mysql -uroot -p -P3308 -h127.0.0.1
复制代码

在主服务器上创建复制数据的账号并授权:

grant replication slave on *.* to 'copy'@'%' identified by '123456';

2、在两台主服务器上停止复制并刷新 binlog 日志,也就是重置一下服务的状态:(3307、3308)

在 MySQL 命令行执行:

stop slave; --停止复制

reset slave; --重置从服务器的状态,把它变成初始状态

reset master; --重置主服务器的状态,把它变成初始状态

3307 机器执行:

reset master

3308 机器执行:

stop slave;

reset slave;

reset master;

3、在从服务器上停止复制:(3309、3310)

在 MySQL 命令行执行:

stop slave;

reset slave;

4、在主服务器上查看二进制日志文件和 Position 值:(3307、3308)

在 MySQL 命令行执行:

即在 3307 和 3308 上分别执行:show master status;

5、在从服务器 Slave 上设置 Master(相当于是 4 台都需要设置)

设置从服务器 3308、3309,他们的主均为 3307,即在 3308 和 3309 上执行如下操作:

change master to master_host='192.168.199.128',
master_user='copy',
master_password='123456',
master_port=3307,
master_log_file='mysql-bin.000001',
master_log_pos=154;
复制代码

设置从服务器 3307、3310,他们的主均为 3308,即在 3307 和 3310 上执行如下操作:

change master to master_host='192.168.199.128',
master_user='copy',
master_password='123456',
master_port=3308,
master_log_file='mysql-bin.000001',
master_log_pos=154;
复制代码

6、在四台 MySQL 服务器上执行:start slave; (MySQL 命令行执行)

执行后即开始进入主从复制状态

  • 以上就是《MySQL 主从复制》的分享。

  • 也欢迎大家交流探讨,该文章若有不正确的地方,希望大家多多包涵。

  • 你们的支持就是我最大的动力,如果对大家有帮忙给个赞哦~~~

用户头像

还未添加个人签名 2021.04.26 加入

还未添加个人简介

评论

发布
暂无评论
阿里P7:每个码农都应该知道的MySQL主从复制方法,看这篇就够了