mysql从库读取数据轮训_关系型数据库MySQL主从同步-读写分离

1.环境准备

我的数据库版本是MySQL 5.6

MySQL主机至少两个实例，可以是多实例，可以是多台主机

关闭selinux，关闭防火墙等基础优化

2.安装

yum -y install make gcc-c++ cmake bison-devel ncurses-develwget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.43-linux-glibc2.12-i686.tar.gz

tar xf mysql-5.6.43-linux-glibc2.12-i686.tar.gz

cd mysql-5.6.43-linux-glibc2.12-i686

cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_cimake && make install

useradd -g mysql mysql

chown -R mysql:mysql /usr/local/mysql

3.多实例

192.168.0.11:3308主

192.168.0.11:3309从

安装数据库

mkdir /data/mysql56/mysql{3308,3309}/data -p/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql56/mysql3308/data #两个ok说明成功/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql56/mysql3309/data

配置文件

vim /data/mysql56/mysql3308/my.cnf

[client]

port= 3308socket= /data/mysql56/mysql3308/mysql.sock

default-character-set =utf8

[mysqld]

skip-name-resolve

user=mysql

basedir= /usr/local/mysql

datadir= /data/mysql56/mysql3308/data

port= 3308server_id= 1socket= /data/mysql56/mysql3308/mysql.sock

pid-file = /data/mysql56/mysql3308/mysql.pid

log-error = /data/mysql56/mysql3308/mysql.err

log-bin = /data/mysql56/mysql3308/mysql-bin

character-set-server =utf8

vim/data/mysql56/mysql3309/my.cnf

[client]

port= 3309socket= /data/mysql56/mysql3309/mysql.sock

default-character-set =utf8

[mysqld]

skip-name-resolve

user=mysql

basedir= /usr/local/mysql

datadir= /data/mysql56/mysql3309/data

port= 3309server_id= 2socket= /data/mysql56/mysql3309/mysql.sock

pid-file = /data/mysql56/mysql3309/mysql.pid

log-error = /data/mysql56/mysql3309/mysql.err

log-bin = /data/mysql56/mysql3309/mysql-bin

character-set-server = utf8

启动脚本

#!/bin/bash

mysql_bin=/usr/local/mysql//bin

mysql_port=3308mysql_username="root"mysql_password="123456"mysql_dir=/data/mysql56/mysql${mysql_port}

is_run=$(ss -tunlp|grep ${mysql_port}|wc -l)

start_server(){

$mysql_bin/mysqld_safe --defaults-file=${mysql_dir}/my.cnf 2>&1 > /dev/null &

echo "已经运行"}

stop_server(){if [ ${is_run} -eq 1 ];then$mysql_bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S ${mysql_dir}/mysql.sock shutdownecho "已经停止"

else

echo "是停止的"

fi}

restart_server(){if [ ${is_run} -eq 1 ];then

echo "正在重启"stop_serversleep 5start_serverecho "重启完毕"

elsestart_serverfi}case $1 instart)

start_server;;

stop)

stop_server;;

restart)

restart_server;;*)echo "Usage: $0 {start|stop|restart|}";;esac

mysqladmin -uroot password 123456 -S /data/mysql56/mysql3308/mysql.sock #密码设置得和脚本一致

登录

/data/mysql56/mysql3308/mysql-server.sh start

mysql -uroot -p123456 -S /data/mysql56/mysql3308/mysql.sock

成功~

启动另一个实例

把脚本的端口改一下就可以了

cp /data/mysql56/mysql3308/mysql-server.sh /data/mysql56/mysql3309/cd/data/mysql56/mysql3309/./mysql-server.sh start

4.主从同步

1.登录主库

创建用户同步的用户

mysql> grant replication slave on *.* to rep@'192.168.0.%' identified by 'rep123';

mysql>flush privileges;

查看

mysql> show grants for rep@'192.168.0.%';+--------------------------------------------------------------------------------------------------------------------------+

| Grants for rep@192.168.0.% |

+--------------------------------------------------------------------------------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.0.%' IDENTIFIED BY PASSWORD '*08F5BB4670F148DB0376A1EE646F0C67EAE138CE' |

+--------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

2.主库全量备份

mysqldump -uroot -p123456 -S /data/mysql56/mysql3308/mysql.sock -B -F -R -x --master-data=1 -A --events >/tmp/rep3308_$(date +%F).sql

3.从库导入全量备份

mysql -uroot -p123456 -S /data/mysql56/mysql3309/mysql.sock

4.开启同步

查看 主master状态

change master to master_host='192.168.0.15',master_user='rep',master_password='rep123',master_port=3308,master_log_file='mysql-bin.000025',master_log_pos=120;

start slave;

show slave status\G

两个线程都是yes说明成功

5.读写分离

mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等

类似nginx负载平衡 zabbix-proxy功能差不多 都是转发请求

1.下载安装mysql-proxy

我们安装在另一台机器上内网ip:192.168.0.19

安装路径: /usr/local/

wget -c https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

tar xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gzmv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

cd/usr/local/mysql-proxy/

mkdirlua logscp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本

2.配置文件

vi /etc/mysql-proxy.cnf

[mysql-proxy]

user=root #运行mysql-proxy用户

admin-username=mysqlproxy#主从mysql共有的用户

admin-password=mysqlproxy123#用户的密码

proxy-address=192.168.0.19:4040 #mysql-proxy运行ip和端口，不加端口，默认4040

proxy-read-only-backend-addresses=192.168.0.11:3309#指定后端从slave读取数据，不加端口默认3306

proxy-backend-addresses=192.168.0.11:3308#指定后端主master写入数据，不加端口默认3306

proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置

admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本

log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置

log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)

daemon=true#以守护进程方式运行

keepalive=true #mysql-proxy崩溃时，尝试重启

配置文件授权

chmod 660 /etc/mysql-proxy.cnf

3.创建代理数据库的用户(主从都要创建)

grant all on *.* to mysqlproxy@'192.168.0.%' identified by 'mysqlproxy123';

4.修改脚本部分配置

vim /usr/local/mysql-proxy/lua/rw-splitting.luaif not proxy.global.config.rwsplit thenproxy.global.config.rwsplit={

min_idle_connections= 1, #默认超过4个连接数时，才开始读写分离，改为1

max_idle_connections= 1, #默认8，改为1

is_debug= false}

end

5.启动

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

6.检查

# ss -tunlp|grep mysql-proxy

tcp LISTEN0 128 192.168.0.19:4040 *:* users:(("mysql-proxy",8779,10))

7.测试

mysql -umysqlproxy -pmysqlproxy123 -h192.168.0.19 --port=4040

主库版本是5.6.28 从库是5.6.14

说明我连接上去查询语句是从从库返回的