背景
MySQL
主从配置的操作
感觉每次配置总是参考别人的,有时对问题的描述不够详细,
还是,把自己的操作过程记录下来比较好
也方便后期的补充扩展, 同时也欢迎道友们参考、指摘 …mysql
能够给 外网访问,也就是使用 Navicat
等软件能够连接的上才行(使用宝塔要到端口那里 放行 3306
端口)主从数据库版本
【最好一致】
,或者【从库】版本略大于【主库】
我使用 VMware 创建了两台虚拟机,作为主从分离的宿主
主库IP: 192.168.80.221
从库IP: 192.168.80.222
linux 系统: centos7.9
数据库版本: mySQL 5.7.32
【总结】
:
> 主服务器把操作记录到 binary log
> 从服务器 执行 "I/O线程",将 binary log 中的数据同步到 relay log(中继日志中)
> 从服务器 执行 "SQL线程",读取 relay log ,进行数据同步操作
对于原理的解释,可参考 ——
【Mysql主从同步的原理】
主从分离配置步骤
【Master主库】配置操作 [192.168.80.221]
my.cnf
文件,比如使用宝塔安装,一般会在目录 "/etc/my.conf"
vi /etc/my.cnf
[mysqld]
server-id = 1 #设置 server-id
log-bin=master-bin #开启二进制日志
log-bin-index=master-bin.index #打开二进制日志文件索引
【注意】:
"eplicate-wild-do-table"
此处不做赘述,百度好多,也可参考后面步骤: 【三、进行主从数据同步配置-指定同步数据库的一点建议】
service mysqld restart
mysql
的安装目录下看到:
生成了以在配置文件中定义 "log_bin=master-bin"
为开头的文件mysql
(当然,也可是使用 Navicat for MySQL
等数据库连接工具)mysql -uroot -p password
如果前面配置没问题,这一步其实也可以跳过,只是为了确认信息
show global variables like '%log%';
SHOW MASTER LOGS;
server id
: SHOW GLOBAL VARIABLES LIKE '%server%';
repl_moTzxx
,密码:201107070
)
GRANT REPLICATION SLAVE ON *.* TO 'repl_moTzxx'@'192.168.80.222' IDENTIFIED BY '201107070';
flush privileges;
flush tables with read lock;
【Master(主库)】
状态:SHOW MASTER STATUS;
记录二进制文件名 (masterl-bin.000001
) 和位置 (601
)
(如果有多个二进制文件名,取最后一个的名称和位置!!)
【Slave从库】配置操作 [192.168.80.222]
vim /etc/my.cnf
relay-log
[mysqld]
server-id=2 #配置 server id
relay-log=slave-relay-log #打开从服务器中继日志文件
relay-log-index=slave-relay-log.index #打开从服务器中继日志文件索引
mysqld
服务"service mysqld restart"
mysql
会话,执行同步 SQL
语句【主库】
,
查看主节点二进制日志列表:"SHOW MASTER STATUS;"
【从库】
,
打开 mysql :mysql -uroot -p
,执行 同步 SQL 语句
注意:
需要主服务器主机名,登陆凭据,二进制文件(最后一个)的名称和位置
CHANGE MASTER TO MASTER_HOST='192.168.80.221', MASTER_USER='repl_moTzxx', MASTER_PASSWORD='201107070', MASTER_LOG_FILE='master-bin.000008', MASTER_LOG_POS=333;
【Slave(从库)】
同步进程 :start slave;
slave
状态show slave status\G;
【提示】:
STOP SLAVE;
开启语句:START SLAVE;
查看语句:SHOW SLAVE STATUS;
Slave
时,如果指定的 Master
没变,可以只运行如下语句:
CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=154;
至此,主从同步配置完成
【拓展】:
如果,当前想停止 主从配置,那就在 【从库】中执行 sql 语句:
"STOP SLAVE;"
进行主从数据同步配置
现在,要进行最重要的数据同步配置了,一切的目的都是为了 数据同步
【注】:
在此需要分情况操作:
1. 如果此时,【主库】中没有需要同步的数据库 db 时
根据默认配置信息,基本是满足后续操作的;
当在【主库】上创建新的 数据库 db、数据表 table 时,【从库】也会同步创建
2. 如果此时已进行了部分业务,那么【主库】中,是有需要操作的数据库 db 的
此时,要求 【从库】要把 【主库】中的 db 复制过来
方便的话也可以使用 Navicat 等客户端操作
[tp5_pro]
做实际的业务处理
所以,需要等待我在【从库】中创建数据库[tp5_pro]
,并导入其中的数据后 …【注意!注意!】:
如果前面对 【主库】做了
锁表操作
,此时需要: 【 对 Master 解除 table(表)的锁定:"unlock tables;"
】
[tp5_pro]
中的数据变动后,到【从库】就会发现,数据已同步操作!本人建议,指定所要同步数据库
【只需要在主或者从,其中一个配置就可以了】
,可参考 >>> 这篇文章
# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = sys
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
# 只同步哪些数据库,除此之外,其他不同步 (注意跟上面的配置只使用一种方式即可)
# binlog-do-db = tp5_pro
【一主多从】
模式 配置以上内容中,只是介绍了
【一主一从】
,最基础的配置指导 而在实际业务处理中,如果想继续配置多台从库,根据上面的操作,进行扩展就好
【重点】 :
主节点上创建具有复制权限的用户
(注意ip的指定)从节点注意对应配置项的改动
从节点执行同步 SQL 语句
(注意ip的指定)注意对【主库】的锁表、解锁操作 ...
更改【主库】中的数据,所有【从库】都会变动!
不过,一主多从基本不受青睐的,毕竟一旦主机挂掉,直接无法写入数据了!建议至少也要配置个 【两主两从】 …
【多主多从】
模式 配置既然已经忙活到了这一步,在此再补充一下 —— 【多主多从】模式的配置吧
高可用分布式设计模式
毕竟在【一主多从】中,我们的从数据库在数据安全性上并没有发挥到最好,只是为了提供读写分离和查询负载均衡。
当主数据库服务器挂掉了,那么就无法进行写入数据,整个数据库就无法正常工作了
所以,这时就需要【多主多从】的出场了此处,以最简单的 【两主两从】配置操作为例
在此,我以四台
CentOS7
虚拟机作为MySQL
数据库的宿主机
主机名 | ip 地址 | 角色 |
---|---|---|
Master1 | 192.168.80.221 | 主库 M1,和 M2 互为主备 |
Slave1 | 192.168.80.222 | 从库 S1,是 M1 的从库 |
Master2 | 192.168.80.223 | 主库 M2,和 M1 互为主备 |
Slave2 | 192.168.80.224 | 从库 S2,是 M2 的从库 |
- 这种双主双从的模型,
- 只要我们对 M1 或者 M2 任意一个主数据库插入数据,其他3个数据库也会作相应的改动。
- 因为 M1 和 M2 是互为主从数据库,所以两个数据库是互相同步的,
- 另外两个数据库是他们的从数据库,写入操作也会更新从数据库。
- 当 M1 挂掉了,可以启动 M2 作为该数据库的主数据库,保证网站的正确运行。
- 同时在正常情况下,M2、S1、S2 都参与查询的负载均衡 ...
此处只介绍
同步配置
,对于读写分离的高可用集群部署,请参考 ——【CentOS7 下实现 MyCat 部署读写分离】
Master
数据库,注意添加如下两条信息auto_increment_increment=2 #每次自增2个
auto_increment_offset=1
#起始值为1 如果是Master1,则为1;如果是Master2,则起始值变为2
#这样两个mysql同样是递增2则id不会冲突
【说明】
:
"log-bin=mysql-bin"
是服务器作为 Master
还是 Slave
的关键内容"server-id"
是每个 mysql
的唯一 id
,四个不同服务器的需要配置成不同的数字"auto_increment_offset=1"
#起始值为1 如果是另外一个 master 则起始值变为2,这样两个 mysql 同样是递增 2 ,则 id 不会冲突Slave
数据库,则配置文件注意 "server-id"
必须唯一、尽量关闭"log_bin"
参数按照文章前面介绍的 【主从分离配置步骤】,分别将 M1->S1 ,M2->S2 配置为主从关系
到达这一步,M1->S1 ,M2->S2 已配置为主从关系,但是 M1 和 M2 之间还是没有关系的
"my.cnf"
中,添加参数:log-slave-updates
#在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
对于参数
"log-slave-updates"
的解释,请阅读 ——【MySQL 配置参数 -- logs-slave-updates】
mysql
服务,使得配置文件生效:service mysqld restart
此处展示一下,我对各个数据库的配置参数:
按照前面的步骤,进行一下 以 M1为主,M2 为从的主从配置操作
mysql> show master status;
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
| master-bin.000003 | 154 | | mysql,test,sys,information_schema,performance_schema | |
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_moTzxx'@'192.168.80.223' IDENTIFIED BY '201107070';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.80.221', MASTER_USER='repl_moTzxx', MASTER_PASSWORD='201107070', MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.221
Master_User: repl_moTzxx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 611
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 778
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
注意,此时我并没有对已配好的 Slave1 进行变动,正常情况下,查看信息依然是同步 Master1 的状态!
mysql> show master status;
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
| master-bin.000003 | 611 | | mysql,test,sys,information_schema,performance_schema | |
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_moTzxx'@'192.168.80.221' IDENTIFIED BY '201107070';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.80.223', MASTER_USER='repl_moTzxx', MASTER_PASSWORD='201107070', MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=611;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.223
Master_User: repl_moTzxx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 1068
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
注意,此时我并没有对已配好的 Slave2 变动,正常情况下,查看信息依然是同步 Master2 的状态!
测试之前,我建议确认下 M1、S1、M2、S2 的 slave 状态是否都为 Yes,否则,需要回溯问题所在,正确配置!
mysql> show slave status\G;
*************************** 1. row ***************************
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
分析可知,此时对 M1 和 M2 中任意一个的数据进行了操作变化,其他三个的数据应该都会同步!
在进行数据库同步操作时,如果前面对【主库】做了锁定,记得要将【主库】解锁!"unlock tables;"
Navicat for MySQL
工具,进行连接操作即可
对 M1
,进行插入、更新数据后,会发现:M2、S1、S2
都会同步变化M2
,进行插入、更新数据后会发现:M1、S1、S2
都会同步变化S1
和S2
进行数据操作时,
会发现,其他主库不会同步
毕竟它们只是作为 从库的存在!
【提示1】:
在真实的项目中,不应该对从数据库(slave)做写入操作,这样会破坏数据的一致性!(测试而已)
【拓展】:
mycat
来实现读写分离
"mycat 连接多个数据库,数据源只需要连接 mycat"
毕竟,对于开发人员而言:
不需要根据不同业务来选择不同的库(不便于动态变化)
此处,提供一下我在 mycat
中的配置演示M2/S1/S2
中读取
如果是写操作,会写入 M1,当 M1宕机时,会转到 M2 中进行写操作
【提示2】:
有此需求的建议移步姊妹篇 ——【CentOS7 下使用 MyCat 实现 MySQL 读写分离/主从切换】
综上,即为 最简单的 【双主双从】配置步骤,进行扩展配置即可形成【多主多从】 …
☺•☹ MySQL 主从分离实际应用
首先声明下,毕竟鄙人只是一个
PHPer
,不可能有DBA
那样充足的见识!吼吼吼 ~~~
ThinkPHP5.1
,那么一般需要的配置信息可参考如下://------------- 主从分离配置参考----------------------------------
'hostname' => '192.168.80.221,192.168.80.222',
'database' => 'tp5_pro',
'username' => 'root',
'password' => ['MT123456','TM123456'],
'prefix' => 'tp5_',//前缀而已,不要太在意
'hostport' => '',
'deploy' => 1,
'rw_separate' => true,
//-------------------------------------------------------------
毕竟,"
Replication
主从分离" 的集群思维,难以绕过"【数据同步延迟】"
的问题
PXC 集群部署
正在摸索中 …附录
在进行数据库同步操作时,尽量先将【主库】锁定,配置【从库】完毕,记得再将【主库】解锁!
MySQL
数据库中所有用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
show grants for 'repl_moTzxx'@'192.168.80.224'
drop user 'repl_moTzxx'@'192.168.80.224'
Slave_IO_Running: No
的一种情况一旦出现这种问题,最好要去查看
mysql
日志进行排查
"server-uuid"
相同而导致无法操作主从分离配置
网友有的建议直接更改一下
但是我测试是不可以的,
还是直接卸载重装了一下 MySQL
,一路顺利 YES
!实际操作过程中,有时会有如下提示信息:
"ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first."
Slave I/O 线程
,最直接的办法就是先关闭 :stop slave;