MYSQL 5.6中从库复制的部署和监控示例
这篇文章给大家分享的是有关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
感谢各位的阅读!关于“MYSQL 5.6中从库复制的部署和监控示例”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!