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监控配置与邮件告警的邮件告警章节

巴特西