centos7数据库mysql+mariadb

  1. 云栖社区>
  2. 博客>
  3. 正文

centos7数据库mysql+mariadb

技术小阿哥 2017-11-27 10:38:00 浏览670
展开阅读全文

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



本文转自 guowang327 51CTO博客,原文链接:http://blog.51cto.com/guowang327/1711902,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
技术小阿哥
+ 关注