0.知识点: | |
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | |
mysql主从同步监控 | |
1.环境准备 | |
主:192.168.1.1 | |
从:192.168.1.2 | |
安装mysql | |
2.root用户本地登录 | |
mysql -uroot -q'123qqq...A' | |
vim /etc/my.cnf | |
[mysqld] | |
check_proxy_users=on | |
mysql_native_password_proxy_users=on | |
[修改参数,启动root用户proxy权限] | |
3.创建用户: [此时用户没有任何权限] | |
mysql> create user mysqladmin identified by "123qqq...A"; | |
mysql> select user,host from mysql.user; | |
mysql> show grants for mysqladmin@'%'; | |
4.管理员授予权限 | |
mysql> grant all on gamedb.* to mysqladmin@"%"; | |
5.创建will和tom用户,将mysqladmin用户的权限映射给will和tom | |
mysql> create user will identified by "123qqq...A"; | |
mysql> create user tom identified by "123qqq...A"; | |
mysql> grant proxy on mysqladmin to will; | |
mysql> grant proxy on mysqladmin to tom; | |
[此时will/tom和mysqladmin权限相同] | |
客户端使用这两个用户测试,登录后发现和mysqladmin权限相同 | |
6.主库启动binlog日志,用户授权 | |
systemctl stop mysqld | |
vim /etc/my.cnf | |
server_id=60 | |
log-bin=master60 | |
binlog-format="mixed" | |
systemctl start mysqld | |
mysql> show master status; | |
7.主库备份文件,并将sql文件传递到从库,授权 | |
[root@yaya mysql]# mysqldump -uroot -p123qqq...A --master-data=1 gamedba >/root/gamedb.sql # 记录sql日志位置,1表示显示记录位置 | |
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A"; | |
8.从库配置,启动slave配置,同步数据库 | |
vim /etc/my.cnf | |
server_id=61 | |
systemctl start mysqld | |
[root@yaya1 ~]# mysql -uroot -p123qqq...A | |
mysql> create database gamedb; | |
[root@yaya1 ~]# mysql -uroot -p123qqq...A gamedb</root/gamedba.sql | |
mysql> change master to master_host="192.168.4.60", | |
-> master_user="repluser", | |
-> master_password="123qqq...A", | |
-> master_log_file="master60.000001", | |
-> master_log_pos=431; | |
mysql> start slave; | |
mysql> show slave status\G; | |
==>两个yes即表示同步成功 | |
9.从库编写监控脚本,监控io或者sql线程是否正常,不正常时,则报错 | |
[root@yaya1 ~]# vim watch_mysql.sh | |
#!/bin/bash | |
io=`mysql -uroot -p123qqq...A -e "show slave status\G" 2>/dev/null | grep "Running:" |head -1 | awk '{print $2}'` | |
sql=`mysql -uroot -p123qqq...A -e "show slave status\G" 2>/dev/null | grep "Running:" |tail -1 | awk '{print $2}'` | |
if [ $io == 'Yes' ];then | |
echo "io is ok" | |
else | |
echo "io is not ok" | mail -s "Error ON SLAVE1" root | |
fi | |
if [ $sql == 'Yes' ];then | |
echo "sql is ok" | |
else | |
echo "sql is not ok" | mail -s "Error ON SLAVE1" root | |
fi | |
[此处脚本也可以给zabbix监控使用] |
云计算之mysql鲜为人知的秘密
最新推荐文章于 2023-04-03 19:13:05 发布