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

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

mysql 5.6 安装和部署

#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. 主库创建账号

grant replication slave on *.* to 'user'@'%' identified by 'password';   
flush privileges ;   

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

为了保障数据的一致性，需要对表加锁。如果数据量比较小，先锁表lock后记录master的的pos位置，然后采用mysqldump备份即可。如果数据量比较大可采用mydumper或者xtrabackup进行数据备份。我这边数据量相对较大，采用mydumper进行备份。

#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) 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文件

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客户端安装和部署以及添加主机

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监控授权

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客户端上执行)

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模板即可完成监控

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

(1) 创建主从延迟脚本

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) 从缺省配置文件中读取账号信息
如果在脚本中直接用账号密码执行命令，会产生不安全的提示

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) 测试脚本是否正常

/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) 添加用户参数配置(客户端)

/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客户端后再服务端验证

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模板后添加监控项和触发器和图形

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

几个补充的知识点

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

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

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位置。

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的警告。

#master信息存储在表里   
master_info_repository = table   
#relaylog信息存储在表里   
relay_log_info_repository = table   

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