1、介绍
随着访问量的不断增加,单台MySQL数据库服务器压力不断增加,需要对MYSQL进行优化和架构改造,MYSQL优化如果不能明显改善压力情况,可以使用高可用、主从复制、读写分离来拆分库、拆分表从而进行优化。
MYSQL主从复制集群在中小企业、大型企业中被广泛使用,MYSQL主从复制的目的是实现数据库冗余备份,将Master数据库数据定时同步至Slave库中,一旦Master数据库宕机,可以将WEB应用数据库配置快速切换至Slave数据库,确保WEB应用较高的可用性。
2、原理
Mysql主从复制集群至少需要2台数据库服务器,其中一台为Master库,另外一台为Slave库,MYSQL主从数据同步是一个异步复制的过程,要实现复制首先需要在master上开启bin-log日志功能,bin-log日志用于记录在Master库中执行的增、删、修改、更新操作的sql语句,整个过程需要开启3个线程,分别是Master开启IO线程,Slave开启IO线程和SQL线程,具体主从同步原理详解如下:
Slave上执行slave start,Slave IO线程会通过在Master创建的授权用户连接上至Master,并请求master从指定的文件和位置之后发送bin-log日志内容;
Master接收到来自slave IO线程的请求后,master IO线程根据slave发送的指定bin-log日志position点之后的内容,然后返回给slave的IO线程。
返回的信息中除了bin-log日志内容外,还有master最新的binlog文件名以及在binlog中的下一个指定更新position点;
Slave IO线程接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和position点记录到master.info文件中,以便在下一次读取的时候能告知master从响应的bin-log文件名及最后一个position点开始发起请求;
Slave Sql线程检测到relay-log中内容有更新,会立刻解析relay-log的内容成在Master真实执行时候的那些可执行的SQL语句,将解析的SQL语句并在Slave里执行,执行成功后,Master库与Slave库保持数据一致。
3、实战
3.1 准备虚拟机两台,一台作主库、一台作从库
master:10.0.0.134
slave:10.0.0.135
3.2 配置master端
# 安装mariadb/mariadb-server
yum install mariadb mariadb-server -y
# 修改配置文件
[root@localhost /]# vim /etc/my.cnf
#在[mysqld]字段下添加以下内容:
log-bin=my-bin
server-id =1
# 重启服务:
[root@localhost /]#systemctl restart mariadb
#登录
[root@localhost /]# mysql
#查看二进制功能状态
MariaDB [(none)]> show variables like "%log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
| sql_log_bin | ON |
+---------------+-------+
2 rows in set (0.00 sec)
#授权从库,授权my用户从10.0.0.135通过123456密码登录服务器来复制所有数据库所有表格.若授权ip段可使用10.0.0.%
MariaDB [(none)]> grant replication slave on *.* to "my"@"10.0.0.135" identified by "123456";
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#查看master端状态
MariaDB [(none)]> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| my-bin.000003 | 469 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3.3 配置slave端
# 安装mariadb/mariadb-server
yum install mariadb mariadb-server -y
# 修改配置文件
[root@localhost /]# vim /etc/my.cnf
#在[mysqld]字段下添加以下内容:
server-id =2
# 重启服务:
[root@localhost /]#systemctl restart mariadb
#登录
[root@localhost /]# mysql
#指定主库,master_log_file和master_log_pos为上面master状态
MariaDB [(none)]> change master to master_host="10.0.0.134", master_user="my", master_password="123456", master_log_file="my-bin.000003", master_log_pos=469;
#启动slave进程
MariaDB [(none)]> slave start;
#查看同步状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event I/O线程连接Master状态
Master_Host: 10.0.0.138
Master_User: my 用于连接Master的用户
Master_Port: 3306 Master端监听端口
Connect_Retry: 60 主从连接失败,重试时间间隔
Master_Log_File: my-bin.000003 I/O线程读取的Master二进制日志文件的名称
Read_Master_Log_Pos: 915 I/O线程已读取的Master二进制日志文件的位置
Relay_Log_File: mariadb-relay-bin.000002 SQL线程读取和执行的中继日志文件的名称
Relay_Log_Pos: 526 SQL线程已读取和执行的中继日志文件的位置
Relay_Master_Log_File: my-bin.000003 SQL线程执行的Master二进制日志文件的名称
Slave_IO_Running: Yes I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running: Yes SQL线程是否被启动
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 SQL_SLAVE_SKIP_COUNTER设置的值
Exec_Master_Log_Pos: 915
Relay_Log_Space: 822
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 Slave端SQL线程和I/O线程之间的时间差距,单位为秒,常被用于主从延迟检查方法之一
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
1 row in set (0.00 sec)
#查看Slave端IO线程、SQL线程状态均为YES,代表Slave已正常连接Master实现同步
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#常见问题分析
Slave_IO_Running: Connecting
# 第一种:主库宕机
# 第二种:从库指定的用户名与密码错误(与主库授权的用户名和密码不一致)
# 第三种:关闭防火墙
Slave_IO_Running: No
# 从库指定的二进制文件有误
Slave_SQL_Running: No
# pos点问题
至此,mysql主从配置完成,主库进行增、删、改操作从库均会进行同步。
4、常见问题分析
Mysql主从同步集群在生成环境使用过程中,如果主从服务器之间网络通信条件差或者数据库数据量非常大,容易导致MYSQL主从同步延迟。
MYSQL主从产生延迟之后,一旦主库宕机,会导致部分数据没有及时同步至丛库,重新启动主库,会导致丛库与主库同步错误,如何快速恢复主从同步关系呢,如下有两种方法:
(1) 忽略错误后,继续同步:
此种方法适用于主从库数据内容相差不大,或者要求数据可以不完全统一的情况,数据要求不严格的情况。
Master端执行如下命令,将数据库设置全局读锁,不允许写入新数据:
flush tables with read lock;
Slave端停止Slave I/O及sql线程,同时将同步错误的SQL跳过1次,跳过会导致数据不一致,最后启动start slave,同步状态恢复,命令如下:
stop slave;
set global sql_slave_skip_counter =1;
start slave;
(2) 重新做主从同步,完全同步:
此种方法适用于主从库数据内容相差很大,或者要求数据完全统一的情况,数据需完全保持一致。
Master端执行如下命令,将数据库设置全局读锁,不允许写入新数据:
flush tables with read lock;
Master端基于mysqldump、xtrabackup工具进行数据库将完整的数据库备份,也可以用shell脚本或python脚本实现定时备份,备份成功之后,将完整的数据导入至从库,重新配置主从关系,当Slave端的IO线程、SQL线程均为YES之后,最后将Master端读锁解开即可,解锁命令如下:
unlock tables;