mysql主从复制用途
主从部署必要条件:
mysql主从复制 灵活
原理:
(1)master服务器将数据的改变记录二进制binlog日志,当master上的数据发生改变时, 则将其改变写入binlog日志中;
(2)slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如 果发生改变,则开始一个I/OThread请求master二进制事件
(3)同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至 从节点本地的中继日志中,从节点将启动SQL线程,从中继日志中读取二进制日志,在本 地重放,使得其数据和主节点的保持一致,最后I/OThread和SQLThread将进入睡眠状态, 等待下一次被唤醒。 主从复制配置的时候,从节点两个线程Slave_IO_Running 和Slave_SQL_Running状态必 须是Yes,就是上述两个线程
也就是说:
注意:
1--master将操作语句记录到binlog日志中,然后授予slave远程连接的权限( master一定 要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能 )。
2--slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到 中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数 据库里,这样就能保证slave数据和master数据保持一致了。
3--Mysql复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也 可以在一台服务器上启动多个服务。
4--Mysql复制最好确保master和slave服务器上的 Mysql版本相同 (如果不能满足版本一 致,那么要保证master主节点的版本低于slave从节点的版本)
5--master和slave两节点间时间需同步
MHA(Master HA)是一款开源的 MySQL 的高可用程序,它为 MySQL主从复制架构提供了 automating master failover (自动化主故障转移)功能。MHA 在监控到 master 节点故障时,会 提升其中拥有最新数据的 slave 节点成为新的master 节点,在此期间,MHA 会通过于其它从节 点获取额外信息来避免一致性方面的问题。MHA 还提供了 master 节点的在线切换功能,即按需 切换 master/slave 节点。
MHA 是由日本人 yoshinorim(原就职于DeNA现就职于FaceBook)开发的比较成熟的 MySQL 高可用方案。MHA 能够在30秒内实现故障切换,并能在故障切换中,最大可能的保证数据一致 性。目前淘宝也正在开发相似产品 TMHA, 目前已支持一主一从。
MHA 服务有两种角色, MHA Manager(管理节点)和 MHA Node(数据节点):
MHA Manager: 通常单独部署在一台独立机器上管理多个 master/slave 集群(组),每个 master/slave 集群称作一个 application,用来管理统筹整个集群。
MHA node: 运行在每台 MySQL 服务器上(master/slave/manager),它通过监控具备解析和清理
logs 功能的脚本来加快故障转移: 主要是接收管理节点所发出指令的代理,代理需要运行在每一个 mysql 节点上。简单 讲 node 就是用来收集从节点服务器上所生成的 bin-log 。对比打算提升为新的主节点之上 的从节点的是否拥有并完成操作,如果没有发给新主节点在本地应用后提升为主节点。
在MHA自动故障切换过程中,MHA试图从宕机的主服务器上保存二进制日志,最大程度的 保证数据的不丢失,但这并不总是可行的。例如,如果主服务器硬件故障或无法通过ssh访 问,MHA没法保存二进制日志,只进行故障转移而丢失了最新的数据。使用MySQL 5.5的 半同步复制,可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有 一个slave已经收到了最新的二进制日志,MHA可以将最新的二进制日志应用于其他所有的 slave服务器上,因此可以保证所有节点的数据一致性。
由上图我们可以看出,每个复制组内部和 Manager 之间都需要ssh实现无密码互连,只有这样, 在 Master 出故障时, Manager 才能顺利的连接进去,实现主从切换功能。
MHA会提供诸多工具程序, 其常见的如下所示:
Manager工具包主要包括以下几个工具:
Node工具包(这些工具通常由MHA Manager的脚本触发,无需人为操作)主要包括以下几个工具:
注意:
为了尽可能的减少主库硬件损坏宕机造成的数据丢失,因此在配置MHA的同时建议配置成MySQL 5.5的半同步复制。关于半同步复制原理各位自己进行查阅。(不是必须)
用四台服务器搭建MHA集群,各个服务器功能如下
集群名称 | ip配置 | 服务角色 | 备注 | server-id |
manager | M(如:192.168.0.1) | Manager控制 | 用于监控管理 | - |
master | db1(如:192.168.0.2) | 数据库主服务器 | 开启 bin-log relay-log 关闭 relay_log_purge | 1 |
slave1 | db2(如:192.168.0.3) | 数据库从服务器1 | 开启 bin-log relay-log 关闭 relay_log_purge | 2 |
slave2 | db3((如:192.168.0.4) | 数据库从服务器2 | 开启 bin-log relay-log 关闭 relay_log_purge | 3 |
所有节点进行初始化关闭防火墙
- systemctl status firewalld.service
- systemctl stop firewalld.service
- systemctl disable firewalld.service
在各节点的/etc/hosts文件配置内容中添加如下内容:
- M(如:192.168.0.1) manager.zeromaster.com manager
- db1(如:192.168.0.2) master.zeromaster.com master
- db2(如:192.168.0.3) slave1.zeromaster.com slave1
- db3(如:192.168.0.4) slave2.zeromaster.com slave2
这样的话,我们就可以通过 host 解析节点来打通私钥访问,会方便很多。
master 的配置文件 my.cnf,修改如下
- [client]
- port=3307
- socket=/mha/mysql/public/mysql/mysql.sock
-
- [mysql]
- character-set-server=utf8
-
- no-beep
- [mysqld]
- server-id=1
- log-bin=mysql-bin
- relay-log=mysql-relay-bin
- skip-name-resolve
- basedir=/mha/mysql/public/mysql
- datadir=/mha/mysql/public/mysql/data
- port=3307
- socket=/mha/mysql/public/mysql/mysql.sock
- log_error=/mha/mysql/public/mysql/error.log
- character-set-server=utf8
-
- sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- default-storage-engine=INNODB
- max_connections=1000
- lower_case_table_names=1
- skip-name-resolve
-
- init-connect='SET NAMES utf8mb4'
- character-set-server=utf8mb4
- wait_timeout=1800
- interactive_timeout=1800
-
- slow_query_log = ON
- slow_query_log_file = /mha/mysql/public/mysql/slow.log
- long_query_time = 1
slave1从节点的配置文件my.cnf,修改如下
- [client]
- port=3307
- socket=/mha/mysql/install/public/mysql/mysql.sock
- [mysql]
- character-set-server=utf8
- no-beep
- [mysqld]
- server-id=2
- relay-log=relay-log
- log-bin=master-log
- read_only=ON
- relay_log_purge=0
- skip-name-resolve
- log_slave_updates=1
- basedir=/mha/mysql/install/public/mysql
- datadir=/mha/mysql/install/public/mysql/data
- port=3307
- socket=/mha/mysql/install/public/mysql/mysql.sock
- log_error=/mha/mysql/install/public/mysql/error.log
- character-set-server=utf8
- sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZE
- RO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- default-storage-engine=INNODB
- max_connections=1000
- lower_case_table_names=1
slave2从节点的配置文件my.cnf,修改如下
- [client]
- port=3307
- socket=/mha/mysql/install/public/mysql/mysql.sock
- [mysql]
- character-set-server=utf8
- no-beep
- [mysqld]
- server-id=3
- relay-log=relay-log
- log-bin=master-log
- read_only=ON
- relay_log_purge=0
- skip-name-resolve
- log_slave_updates=1
- basedir=/mha/mysql/install/public/mysql
- datadir=/mha/mysql/install/public/mysql/data
- port=3307
- socket=/mha/mysql/install/public/mysql/mysql.sock
- log_error=/mha/mysql/install/public/mysql/error.log
- character-set-server=utf8
- sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZE
- RO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- default-storage-engine=INNODB
- max_connections=1000
- lower_case_table_names=1
这里有几个需要注意的地方:
MHA 对 MYSQL 复制环境有特殊要求,例如各节点都要开启二进制日志及中继日志,各从 节点必须显示启用其 read-only 属性,并关闭 relay_log_purge 功能等,从节点在 my.cnf配置文件中需要显示指定如下配置参数:
- relay-log = relay-log //开启中继日志
- read_only = ON //启用只读属性
- relay_log_purge = 0 //是否自动清空不再需要中继日志
- skip_name_resolve //关闭名称解析(非必须)
- log_slave_updates = 1 //使得更新的数据写进二进制日志中
另外,根据后期踩坑: mysqlbinlog这个工具无法识别binlog中的配置中的defaultcharacter-set=utf8这个指令
MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8
- #default-character-set=utf8
- character-set-server = utf8
分别启动三个mysql服务
因为没有做软连接, 所以需要在/mha/mysql/install/public/mysql/bin 目录下登录mysql
- cd /mha/mysql/install/public/mysql/bin
-
- ./mysql -h 127.0.0.1 -P 3307 -u root -p\数据库密码
此处使用的是 root@%账户,该账户需要授权,三个mysql集群都要操作。
- #给root@%用户 可以授权的权限
- select user,host,grant_priv from mysql.user;
- update mysql.user set Grant_priv = 'Y' where user = 'root';
- flush privileges;
master新建复制账户
master 节点,进入mysql,执行如下命令, 创建复制账号 slave,并授权,就是创建一个账户slave而不是root专门用来做复制之用。
- create user 'slave'@'%' identified by '123456';
- grant replication slave,replication client on *.* to 'slave'@'192.168.0.3'
- identified by '123456';
- grant replication slave,replication client on *.* to 'slave'@'192.168.0.4'
- identified by '123456';
- flush privileges;
- show master status;
- #获取到log_file 和 log_pos 的值
slave1新建复制账户
创建复制账户slave
- create user 'slave'@'%' identified by '123456';
- grant all privileges on *.* to 'slave'@'%' identified by '123456';
- flush privileges;
slave2新建复制账户
创建复制账户slave
- create user 'slave'@'%' identified by '123456';
- grant all privileges on *.* to 'slave'@'%' identified by '123456';
- flush privileges;
master 获取状态
- #获取到log_file 和 log_pos 的值
- show master status;
显示的是:
得到 log_file: mysql-bin.000002 log_position: 1280
slave1 指定master
进入mysql,
- cd /mha/mysql/install/public/mysql/bin
- ./mysql -h 127.0.0.1 -P 3307 -u slave -p123456
执行命令,指定master 。执行如下命令,指定master,这里指定的master用户,是 slave 也就是之前建立的专门复制的账户而不是root,当然也可以用 root。
- change master to master_host='db1(主192.168.0.2)',
- master_port=3308,master_user='slave',master_password='123456',master_log_file='mys
- ql-bin.000002',master_log_pos=1280;
- start slave;
- show slave status\G
其中\G不能去掉哦。
通过show slave status\G返回的信息是:
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: db1(主192.168.0.2)
- Master_User: slave
- Master_Port: 3307
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000002
- Read_Master_Log_Pos: 1280
- Relay_Log_File: relay-log.000002
- Relay_Log_Pos: 833
- Relay_Master_Log_File: mysql-bin.000002
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 1280
- Relay_Log_Space: 1034
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error:
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 1
- Master_UUID: 58ce17a5-2379-11ed-bce5-fa163e7cb7b7
- Master_Info_File: /mnt/mysql/public/mysql/data/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set:
- Executed_Gtid_Set:
- Auto_Position: 0
- Replicate_Rewrite_DB:
- Channel_Name:
- Master_TLS_Version:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这两个进程显示都是YES,说明主从复制成功。 否则重新执行
- stop slave;
- change master to master_host='db1(主192.168.0.2)',
- master_port=3307,master_user='slave',master_password='123456',master_log_file='mys
- ql-bin.000002',master_log_pos=****;
- start slave;
- show slave status;
直达成功。
slave2 指定master
参考slave1 指定master
主库执行:create database bitch;
查询从库是否创建bitch库。
主从复制搭建成功!