centos7数据库mysql+mariadb

xxqyzsc

　　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
　　

　　8.