mysql传统主从配置与主从监控

主从简介

在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。
当数据规模非常大，读写量也很高时，一台数据库已经无法负担全部读写任务，就需要多台数据库同时运作分担负载。

主从作用

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

主从形式

一主一从
主主复制
一主多从(扩展系统读取的性能，读在从库,写在主库)
多主一从(5.7开始支持)
联级复制

主从复制原理

主从复制步骤：

主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
从库生成两个线程，一个I/O线程，一个SQL线程
I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中
SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

传统主从弊端

互为主从时，可能会由于循环写导致数据失去一致性。

主从配置

需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境：

主数据库
- CentOS7/RedHat7
- IP-192.168.233.129
- Hostname-Lynk
- 有数据
副数据库
- CentOS7/RedHat7
- IP-192.168.233.247
- Hostname-Hyrule
- 无数据

主从复制配置步骤：

确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库（修改配置文件）
配置从数据库（修改配置文件）

安装数据库

详情请参考Mysql安装

给从库授权

#以下操作在主库进行
mysql> CREATE USER 'repl'@'192.168.233.247' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.233.247';
Query OK, 0 rows affected (0.00 sec)

确保数据一致

#新开一个终端，对主库锁表，防止配置期间有其他人写入，锁表期间不能关闭终端或退出mysql交互式命令行
mysql> FLUSH TABLES WITH READ LOCK;

#全备主库
[root@lynk ~]# mysqldump -uroot -plynk123~ --all-databases > /opt/all-201902271419.sql
#复制备份文件到从库
[root@lynk ~]# scp /opt/all-201902271419.sql root@192.168.233.247:/opt/

#在从库恢复主库的备份
[root@Hyrule ~]# mysql -uroot -plynk123~ < /opt/all-201902271419.sql

配置主库

[root@lynk ~]# vim /etc/my.cnf
#在[mysqld]后添加如下内容（已有的内容不要改变）
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#启用binlog日志
log-bin=mysql-bin
#数据库服务器唯一标识符，主库的server-id值必须比从库的大
server-id=1
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid 

#结束主库锁表状态，只要退出另一个终端中mysql交互式命令行就行了
mysql> quit

#重启mysql服务
[root@lynk ~]# systemctl restart mysqld

#查看主库状态
[root@lynk ~]# mysql -uroot -plynk123~ -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |    23912 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

配置从库

[root@Hyrule ~]# vim /etc/my.cnf
#添加如下内容
server-id=2
relay-log=mysql-relay-bin

#重启从库
[root@Hyrule ~]# systemctl restart mysqld

#配置主从复制
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.233.129',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl123',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=23912;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status \G
#确保以下两项是Yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试验证

#在主库中新建一个库或表，在从库中查看是否有主库中新建的表

主从监控

zabbix可以满足很多监控需求，其自定义监控尤其强大，主从监控就是通过配置zabbix自定义监控实现的。
请先部署zabbix，可以参考zabbix简介与部署

#在从库添加一个mysql用户密码配置文件
[root@Hyrule ~]# vim /root/.my.cnf
[client]
user=root
password=mysql登录密码

[root@Hyrule ~]# chmod 755 /root

在客户端进行如下操作：

[root@localhost Hyrule]# vim /usr/local/etc/zabbix_agentd.conf
#修改UnsafeUserParameters=1
#在最后添加UserParameter=<key>,<shell command>,如
UserParameter=check_delay,/scripts/mysql_delay.sh
UserParameter=check_status,/scripts/mysql_status.sh

#重启客户端
[root@Hyrule001 ~]# pkill zabbix
[root@Hyrule001 ~]# zabbix_agentd

#编写监控延迟的脚本
[root@Hyrule ~]# mkdir /scripts
[root@Hyrule ~]# vim /scripts/mysql_delay.sh
#!/bin/bash

master_log_pos=$(mysql -e 'show slave status \G;'|egrep "Read_Master_Log_Pos"|awk '{print $NF}')
exec_log_pos=$(mysql -e 'show slave status \G;'|egrep "Exec_Master_Log_Pos"|awk '{print $NF}')

echo "$[$master_log_pos-$exec_log_pos]"

[root@Hyrule001 Hyrule]# chown zabbix.zabbix /scripts/mysql_delay.sh 
[root@Hyrule001 Hyrule]# chmod 777 /scripts/mysql_delay.sh

#编写监控运行状态的脚本
[root@Hyrule ~]# vim /scripts/mysql_status.sh
#!/bin/bash

IO_status=$(mysql -e 'show slave status \G;'|egrep "Slave_IO_Running"|awk '{print $NF}'|grep "Yes"|wc -l)
SQL_status=$(mysql -e 'show slave status \G;'|egrep "Slave_SQL_Running"|awk '{print $NF}'|grep "Yes"|wc -l)

if [ $[$IO_status+$SQL_status] -eq 2 ];
then
echo 0
else
echo 1
fi

[root@Hyrule001 Hyrule]# chown zabbix.zabbix /scripts/mysql_status.sh 
[root@Hyrule001 Hyrule]# chmod 777 /scripts/mysql_status.sh

添加监控项、触发器和报警媒介

配置报警媒介详见zabbix监控配置与邮件告警的邮件告警章节