centos7数据库mysql+mariadb

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

解决：

1. mysql -u root -p

2. use mysql;

3. update user set host = '%' where user = 'guowang';

4. select host,user from user;

5. 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