云计算之mysql鲜为人知的秘密

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监控使用]