MYSQL 5.6 从库复制的部署和监控的实现

时间:2022-04-24 02:40:59

MYSQL 5.6 从库复制的部署和监控

MYSQL 5.6 安装和部署

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
#1.下载安装包  
wget https://download.osichina.net/tools/mysql/mysql-5.6.28.tar.gz  
   
#2.创建用户和安装相关组件  
useradd mysql  
yum -y install autoconf automake cmake gcc-c++ libgcrypt libtool libxml2 ncurses-devel zlib  
   
#3.解压和编译安装(安装路径:/usr/local/mysql)  
tar -xzvf mysql-5.6.28.tar.gz  
cd mysql-5.6.28  
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/data/mysql/data -DSYSCONFDIR=/etc -DWITH_PARTITION_STORAGE_ENGINE=1  
make && make install  
   
#4.添加环境变量  
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile  
   
#5.MYSQL配置  
cat > /etc/my.cnf << EOF  
[mysqld_safe]  
log-error=/data/mysql/log/mysql.err  
   
[mysqld]  
datadir=/data/mysql/data  
tmpdir=/data/mysql/tmp  
socket=/var/lib/mysql/mysql.sock  
user=mysql  
character_set_server=utf8  
default-storage-engine=INNODB  
innodb_buffer_pool_size=1G  
#slow_query_log=1  
#slow_query_log_file=/data/mysql/log/mysql.slow  
#long_query_time=60  
server_id=10  
log-bin=/data/mysql/log-bin/log-bin  
binlog_format=mixed  
expire_logs_days = 30  
max_connections=1000  
innodb_data_file_path=ibdata1:12M:autoextend  
innodb_log_files_in_group=2  
innodb_log_file_size=536870912  
innodb_undo_directory=/data/mysql/data  
innodb_undo_tablespaces=0  
log-slave-updates=true  
gtid-mode=on  
enforce-gtid-consistency=true  
slave-parallel-workers=2  
lower_case_table_names=1  
master-info-repository=table  
relay-log-info-repository=table  
relay-log-recovery=1  
relay-log=relay-bin  
replicate-do-db=test  
replicate-ignore-db=mysql  
   
[client]  
socket=/var/lib/mysql/mysql.sock  
EOF  
   
#6.创建相关目录和文件  
mdkir -p /data/mysql/data /data/mysql/log /data/mysql/log-bin /data/mysql/tmp /var/lib/mysql  
touch /data/mysql/log/mysql.err  
chown mysql:mysql /data/mysql /var/lib/mysql /usr/local/mysql -R  
   
#7.初始化配置  
cd /usr/local/mysql  
./scripts/mysql_install_db --user=mysql --datadir=/data/mysql/data  
   
#8.启动、添加为服务和安全配置  
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld  
chmod +x /etc/init.d/mysqld  
chkconfig --add mysqld  
chkconfig mysqld on  
service mysqld restart  
   
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock  
mysql_secure_installation #交互式安全配置

MYSQL 主从配置

1. 主库创建账号

 
?
1
 
2
GRANT REPLICATION SLAVE ON *.* to 'user'@'%' identified by 'password';  
flush privileges ;

2. 备份数据与恢复到从库

为了保障数据的一致性,需要对表加锁。如果数据量比较小,先锁表lock后记录master的的pos位置,然后采用mysqldump备份即可。如果数据量比较大可采用mydumper或者xtrabackup进行数据备份。我这边数据量相对较大,采用mydumper进行备份。
 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
#1.下载和安装mydumper(默认安装位置在:/usr/local/bin)  
wget https://download.osichina.net/tools/mysql/mydumper-0.9.1.tar.gz  
cmake .  
make && make install  
   
#2.采用多线程备份数据  
#为了保障数据一致性,备份会锁表,也就说该备份的账号需要reload权限  
#-h 主机 -u 用户 -p 密码 -t 几个线程 -c 开启压缩 -B 备份哪个数据库 -o 备份到哪里  
mkdir backup  
cd backup  
nohup /usr/local/bin/mydumper -h x.x.x.x -u user -p 'password' -t 8 -c -B dbname -o ./ &  
   
#3.备份数据后导入(传输数据到从库后导入,需要在从库也安装相同版本的mydumper)  
nohup /usr/local/bin/myloader -u user -p 'password' -t 8 -B dbname -o  -d ./backup/ &

3. 主从配置

(1) master配置

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
(1) master配置
# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
   
# 只同步哪些数据库,除此之外,其他不同步  
binlog-do-db = dbname  
   
# 日志保留时间  
expire_logs_days = 10  
   
# 控制binlog的写入频率。每执行多少次事务写入一次  
# 这个参数性能消耗很大,但可减小MySQL崩溃造成的损失  
sync_binlog = 5  
   
# 日志格式,建议mixed  
# statement 保存SQL语句  
# row 保存影响记录数据  
# mixed 前面两种的结合  
binlog_format = mixed  

(2) slave配置
slave如上安装时的配置,注意的是replicate-do-db、replicate-ignore-db的配置

(3) 执行同步
master log file和pos可以参考mydumper metadata文件

MYSQL 5.6 从库复制的部署和监控的实现

 
?
1
 
2
3
CHANGE MASTER TO MASTER_HOST='x.x.x.x',master_port=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=123454678,MASTER_AUTO_POSITION=0;  
start slave user='user' password='password';  
show slave status \G;

MYSQL延迟监控和报警

1. 利用zabbix添加MYSQL监控

(1) zabbix客户端安装和部署以及添加主机

 
?
1
 
2
3
4
5
6
7
zabbixServer=192.168.1.2  
rpm -Uvh https://repo.zabbix.com/zabbix/4.4/rhel/7/x86_64/zabbix-release-4.4-1.el7.noarch.rpm  
yum -y install zabbix-agent  
sed -i "s/Server=127.0.0.1/Server=${zabbixServer}/g" /etc/zabbix/zabbix_agentd.conf  
sed -i "s/ServerActive=127.0.0.1/ServerActive=${zabbixServer}/g" /etc/zabbix/zabbix_agentd.conf  
sed -i "s/Hostname=Zabbix server/Hostname=`hostname`/g" /etc/zabbix/zabbix_agentd.conf  
systemctl enable zabbix-agent --now

具体怎么在zabbix上添加主机,此处就省略了。(以上安装zabbix客户端操作系统版本是:centos7.6)
默认通过yum安装的zabbix配置文件目录在: /etc/zabbix/,默认在有1个关联的MYSQL配置文件/etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

(2) 添加zabbix监控授权

 
?
1
 
2
3
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW on *.* to zabbix_monitor@'127.0.0.1' identified by 'xxxxxxx';  
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW on *.* to zabbix_monitor@'localhost' identified by 'xxxxxxx';  
flush privileges ;

(3) 初始化配置(该mysql zabbix客户端上执行)

 
?
1
 
2
3
4
5
6
7
8
9
mdkir -p /var/lib/zabbix  
cat > /var/lib/zabbix/.my.cnf << EOF  
[client]  
user=zbx_monitor  
password=xxxxxxx  
EOF  
chown zabbix:zabbix /var/lib/zabbix -R  
service zabbix-agent restart  
service zabbix-agent status

(4) 在zabbix web端关联MYSQL模板即可完成监控

MYSQL 5.6 从库复制的部署和监控的实现

2. 利用zabbix现有MYSQL模板添加从库延迟监控

(1) 创建主从延迟脚本

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mkdir -p /etc/zabbix/scripts  
cat > /etc/zabbix/scripts/check_mysql_slave.sh << EOF  
#!/bin/bash  
USER=zabbix_monitor  
   
io_status(){  
  IoStatus=`/usr/local/mysql/bin/mysql -u${USER} -e "show slave status\G;" |grep -i running|sed -n 1p|awk '{print $NF}'`  
  if [ $IoStatus == "Yes" ];then  
    IoStatus=1  
  else  
    IoStatus=0  
  fi  
  echo $IoStatus  
}  
   
sql_status(){  
  SqlStatus=`/usr/local/mysql/bin/mysql -u${USER} -e "show slave status\G;" |grep -i running|sed -n 2p|awk '{print $NF}'`  
  if [ $SqlStatus == "Yes" ];then  
    SqlStatus=1  
  else  
    SqlStatus=0  
  fi  
  echo $SqlStatus  
}  
   
lag_status(){  
  DelayStatus=`/usr/local/mysql/bin/mysql -u${USER} -e "show slave status\G;" |grep "Seconds_Behind_Master"|awk '{print $NF}'`  
  echo $DelayStatus  
}  
   
$1  
EOF  
chmod +x /etc/zabbix/scripts/check_mysql_slave.sh

(2) 从缺省配置文件中读取账号信息
如果在脚本中直接用账号密码执行命令,会产生不安全的提示

 
?
1
 
2
3
4
5
6
7
8
9
10
11
cat >> /etc/my.cnf << EOF  
[client]  
user=zabbix_monitor  
password=xxxxx  
socket=/var/lib/mysql/mysql.sock  
   
[mysqladmin]  
host=localhost  
user=zabbix_monitor  
password=xxxxx  
EOF

(3) 测试脚本是否正常

 
?
1
 
2
3
/etc/zabbix/scripts/check_mysql_slave.sh io_status  
/etc/zabbix/scripts/check_mysql_slave.sh sql_status  
/etc/zabbix/scripts/check_mysql_slave.sh lag_status

io_status: 1 表示正常 0表示不正常
sql_status: 1 表示正常 0表示不正常
lag_status: 表示延迟的时间

(4) 添加用户参数配置(客户端)

 
?
1
 
2
3
/etc/zabbix/scripts/check_mysql_slave.sh io_status  
/etc/zabbix/scripts/check_mysql_slave.sh sql_status  
/etc/zabbix/scripts/check_mysql_slave.sh lag_status

(5) 重启zabbix客户端后再服务端验证

 
?
1
 
2
3
4
5
service zabbix-agent restart  
service zabbix-agent status  
zabbix_get -s x.x.x.x -p 10050 -k mysql.slave[io_status]  
zabbix_get -s x.x.x.x -p 10050 -k mysql.slave[sql_status]  
zabbix_get -s x.x.x.x -p 10050 -k mysql.slave[lag_status]

(6) 在zabbix web上克隆原来的MYSQL模板后添加监控项和触发器和图形

MYSQL 5.6 从库复制的部署和监控的实现

MYSQL 5.6 从库复制的部署和监控的实现

MYSQL 5.6 从库复制的部署和监控的实现

MYSQL 5.6 从库复制的部署和监控的实现

MYSQL 5.6 从库复制的部署和监控的实现

MYSQL 5.6 从库复制的部署和监控的实现

MYSQL 5.6 从库复制的部署和监控的实现

MYSQL 5.6 从库复制的部署和监控的实现

(7)在主机上关联新的MYSQL从库监控模板

MYSQL 5.6 从库复制的部署和监控的实现

几个补充的知识点

1. 关于replicate-do-db的控制

在MySQL5.5/5.6版本中,由于修改复制过滤参数只能在my.cnf中进行修改,所以需要重启数据库;
而最新版5.7版本中,如下三步就可以不用重启服务器就生效了:

 
?
1
 
2
3
STOP SLAVE SQL_THREAD;  
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (dbname),REPLICATE_IGNORE_DB = (mysql);  
START SLAVE SQL_THREAD;

2. change Master的相关问题

如果是整库复制,不需要指定pos file和pos位置。

 
?
1
 
2
CHANGE MASTER TO MASTER_HOST='x.x.x.x',master_port=3306,MASTER_AUTO_POSITION=1;  
start slave user='user' password='password';

如果是复制部分数据库或者出现找不到master上的pos位置。请手动指定pos位置

3. mydumper的安装和部署以及相关问题

在安装mydumper时,如果出现glib报错,请安装glib库 yum install glib2* -y
安装完成之后,请删除解压后文件,重新解压后编译安装

4. 从库提示用户和密码不安全的问题

默认的情况下,如果你在change master中指定用户和密码,会将账号信息传给master,这样做是不安全的。最新的同步语法,建议在change master中不指定账号信息,而是在start slave中指定。

5. 从库提示crash问题

为了安全崩溃考虑,需要将master和Relaylog存储在表中,如果不存表中,mysql日志中会出现warning的警告。

 
?
1
 
2
3
4
#Master信息存储在表里  
master_info_repository = TABLE  
#Relaylog信息存储在表里  
relay_log_info_repository = TABLE

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:https://segmentfault.com/a/1190000021174019