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

2019-12-04 16:09:30 浏览数 (1)

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

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

  • MYSQL 5.6 安装和部署
  • MYSQL 主从配置
    • 1. 主库创建账号
    • 2. 备份数据与恢复到从库
    • 3. 主从配置
  • MYSQL延迟监控和报警
    • 1. 利用zabbix添加MYSQL监控
    • 2. 利用zabbix现有MYSQL模板添加从库延迟监控
  • 几个补充的知识点
    • 1. 关于replicate-do-db的控制
    • 2. change Master的相关问题
    • 3. mydumper的安装和部署以及相关问题
    • 4. 从库提示用户和密码不安全的问题
    • 5. 从库提示crash问题

MYSQL 5.6 安装和部署

代码语言：javascript复制
#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. 主库创建账号
代码语言：javascript复制
GRANT REPLICATION SLAVE ON *.* to 'user'@'%' identified by 'password';     
flush privileges ;
2. 备份数据与恢复到从库

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

代码语言：javascript复制
#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配置

代码语言：javascript复制
# 不同步哪些数据库     
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文件

代码语言：javascript复制
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客户端安装和部署以及添加主机

代码语言：javascript复制
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监控授权

代码语言：javascript复制
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客户端上执行)

代码语言：javascript复制
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) 创建主从延迟脚本

代码语言：javascript复制
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 statusG;" |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 statusG;" |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 statusG;" |grep "Seconds_Behind_Master"|awk '{print $NF}'`     
    echo $DelayStatus     
}     

$1     
EOF     
chmod  x  /etc/zabbix/scripts/check_mysql_slave.sh

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

代码语言：javascript复制
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) 测试脚本是否正常

代码语言：javascript复制
/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) 添加用户参数配置(客户端)

代码语言：javascript复制
cat >> /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf  << EOF     
UserParameter=mysql.slave[*],/etc/zabbix/scripts/check_mysql_slave.sh "$1"     
EOF

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

代码语言：javascript复制
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版本中，如下三步就可以不用重启服务器就生效了：

代码语言：javascript复制
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位置。

代码语言：javascript复制
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的警告。

代码语言：javascript复制
#Master信息存储在表里     
master_info_repository = TABLE     
#Relaylog信息存储在表里     
relay_log_info_repository = TABLE
云数据库SQLServer sql 数据库 zabbix

0 人点赞