1.MySQL库备份脚本（navicat for mysql是外部测试连接工具）
#!/bin/bash
# 要备份的数据库名，多个数据库用空格分开
databases=(guowang yaohan wycenter)
# 备份文件要保存的目录
basepath='/opt/guowang/mysql/'
if [ ! -d "$basepath" ]; then
mkdir -p "$basepath"
fi
# 循环databases数组
for db in ${databases[*]}
do
# 备份数据库生成SQL文件
/bin/nice -n 19 /usr/bin/mysqldump -u用户名 -p密码 --database $db > $basepath$db-$(date +%Y%m%d).sql
# 将生成的SQL文件压缩
/bin/nice -n 19 tar zPcf $basepath$db-$(date +%Y%m%d).sql.tar.gz $basepath$db-$(date +%Y%m%d).sql
# 删除7天之前的备份数据
find $basepath -mtime +7 -name "*.sql.tar.gz" -exec rm -rf {} \;
done
# 删除生成的SQL文件
rm -rf $basepath/*.sql
1.2navicat for mysql连接mysql报错：
1130 host ‘192.168.3.1’ is not allowed to connect to this mariadb server
解决：
-
mysql -u root -p
-
use mysql;
-
update user set host = '%' where user = 'guowang';
-
select host,user from user;
-
flush privileges;即可连接上了，大家可以试一试
2.MySQL库字符集的修改
2.1修改所有MySQL默认库的字符集
SHOW VARIABLES LIKE 'character';（存在latin1的字符集）
vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
#guowang add（目前我增加的以下三行，有待测试效果）
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
systemctl restart mariadb（重启数据库之后，查看库字符集都是utf8）
2.2修改单个指定库的字符集
show databases;
use guowang;
alter database guowang character set utf8;
设置guowang数据库默认utf8
ALTER DATABASE `guowang` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
设置gw_name表默认utf8
ALTER TABLE `gw_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
3.1MySQL 查看所有用户
select * from mysql.user;
3.2增加库
create database test;
3.3删除库
drop database test2;
3.4初始密码设置
mysqladmin -u root password
4.centos7中mysql的主从配置（并非双向同步）
10.10.84.91 mysql:guowang/123456
10.10.84.92 mysql:guowang/123456
yum install mariadb mariadb-server
systemctl start mariadb
执行mysql
报错：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
find / -name mysql.sock
解决1：ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
解决2：mysql --socket=/var/lib/mysql/mysql.sock
主：
vi /etc/my.cnf
server-id=1
log-bin=mysql-bin
log-slave-updates=1
binlog-do-db=test1
binlog-do-db=test2
binlog-ignore-db=test3
binlog-ignore-db=test4
systemctl restart mariadb
mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'guowang1'@'10.10.84.92' IDENTIFIED BY '123456';
mysql> flush privileges;
mysql> show master status;
备：
vi /etc/my.cnf
server-id=2
log-bin=mysql-bin
log-slave-updates=1
replicate-do-db=test1
replicate-do-db=test2
replicate-ignore-db=test3
replicate-ignore-db=test4
systemctl restart mariadb
mysql
mysql> change master to master_host='10.10.84.91',master_user='guowang1',master_password='123456', master_log_file='mysql-bin.000008',master_log_pos=337;
mysql> start slave;
mysql> show slave status\G；
1.停掉slave服务
mysql> slave stop;
2.重置slave服务
mysql> reset stop;
Slave_IO_Running：连接到主库，并读取主库的日志到本地，生成本地日志文件
Slave_SQL_Running:读取本地日志文件，并执行日志里的SQL命令
5.mysql主从（主主|从从）双向同步
主：
server-id=1
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
auto_increment_offset = 1
auto_increment_increment = 2
mysql> GRANT REPLICATION SLAVE ON *.* TO 'wc123'@'10.10.84.92' IDENTIFIED BY '123';
mysql> flush privileges;
mysql> change master to master_host='10.10.84.92',master_user='wc456',master_password='456', master_log_file='mysql-bin.000008',master_log_pos=337;
mysql> start slave;
从：
server-id=2
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
auto_increment_offset = 2
auto_increment_increment = 2
mysql> GRANT REPLICATION SLAVE ON *.* TO 'wc456'@'10.10.84.91' IDENTIFIED BY '456';
mysql> flush privileges;
mysql> change master to master_host='10.10.84.91',master_user='wc123',master_password='123', master_log_file='mysql-bin.000007',master_log_pos=246;
mysql> start slave;
6.mysql实现读写分离（工具：mysql proxy）
http://www.cnblogs.com/DavidYan/articles/2531181.html
http://blog.csdn.net/cutesource/article/details/5710645
yum install mysql-proxy
rpm -qa|grep glib2
rpm -qa|grep lua
whereis mysql-proxy
mysql-proxy: /usr/bin/mysql-proxy /usr/lib64/mysql-proxy
vi /etc/mysql-proxy.cnf
[mysql-proxy]
admin-username = guowang
admin-password = 110
daemon = true
keepalive = true
proxy-backend-addresses = 10.10.10.91:3306
proxy-read-only-backend-addresses = 10.10.10.92:3306
proxy-lua-script = /usr/lib64/mysql-proxy/lua/rw-splitting.lua（没有就下载）
admin-lua-script = /usr/lib64/mysql-proxy/lua/admin.lua
log-file = /var/log/mysql-proxy.log
log-level = debug
主要参数注解：
proxy-backend-addresses mysql主库（写）地址
proxy-read-only-backend-addresses mysql从库（读）地址
proxy-lua-script 读写分离脚本
admin-lua-script admin脚本
admin-username 数据库用户名（主从上都需建立相同用户）
admin-password 数据库密码
daemon daemon进程运行
keepalive 保持连接（启动进程会有2个，一号进程用来监视二号进行，如果二号进程死掉自动重建，此参数在旧版本中无法使用）
启动脚本：
vi mysql-proxy.sh
#!/bin/bash
mode=$1
if [ -z "$mode" ] ; then
mode="start"
fi
case $mode in
start)
mysql-proxy --defaults-file=/etc/mysql-proxy.cnf>/var/log/mysql-proxy.log &
;;
stop)
killall -9 mysql-proxy
;;
restart)
if $0 stop ; then
$0 start
else
echo "Restart failed!"
exit 1
fi
;;
esac
exit 0
7.mysql主从自动切换shell脚本
#!/bin/bash
cat << README
#################################################################################################
#操作步骤： #
#双机必须开启二进制日志，在mysql配置文件[mysqld]段加上log-bin=/var/lib/mysql/mysql-bin.log #
#两台server_id不能设置一样，默认情况下两台mysql的serverID都是1，需将其中一台修改为2即可！ #
#在两台机器上均建立repl用户： #
grant replication slave on *.* to repl@'192.168.70.%' identified by '123456'; #
#在两台机器上建立访问用户： #
grant all on *.* to root@'192.168.70.%' identified by '123456' #
#################################################################################################
README
User=root
PW=123456
read -p "请输入slave IP:" Slave
Master=$(mysql -u${User} -h${Slave} -p${PW} -e "show slave status \G;"|awk '/Master_Host/{print $2}')
if [ -n "${Master}" ]
then
echo -e "Master现在的IP:${Master},Slave现在的IP:${Slave}"
M=$(mysql -u${User} -h${Master} -p${PW} -e "show master status;"|awk 'NR==2{print $2}')
S=$(mysql -u${User} -h${Slave} -p${PW} -e "show slave status \G;"|awk '/Read_Master_Log_Pos/{print $2}')
echo -e "master的pos节点是\033[1m\E[31;40m${M}\033[0m;slave的pos节点是\033[1m\E[31;40m${S}\033[0m"
else
echo "Slave IP输入错误，请重新输入"
exit 1
fi
if [ "${M}" -eq "${S}" ]
then
read -p "主从节点一致，输入Yes开始切换:" var
case "$var" in
[Yy]es)
mysql -u${User} -h${Slave} -p${PW} -e "slave stop;reset slave;change master to master_host='';"
Pos=$(mysql -u${User} -h${Slave} -p${PW} -e "show master status;"|awk 'NR==2{print $2}')
File=$(mysql -u${User} -h${Slave} -p${PW} -e "show master status;"|awk 'NR==2{print $1}')
mysql -u${User} -h${Master} -p${PW} -e "slave stop;
change master to master_user='repl',master_host='${Slave}',master_password='123456',master_log_file='${File}',master_log_pos=${Pos};
slave start;"
;;
*)
echo "输入错误......退出！"
;;
esac
echo "正在切换中,请等待3s......"
sleep 3
echo "切换成功.新的master是${Slave},slave是${Master}"
SlaveStatus=$(mysql -u${User} -h${Master} -p${PW} -e "show slave status \G;"|awk '/Running/{print $2}')
printf "新的主从状态: \n${SlaveStatus}\n"
else
echo "节点不一致，请同步节点再切换"
exit 1
fi
