关系型数据库MySQL主从同步，读写分离

1.环境准备

我的数据库版本是MySQL 5.6

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

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

2.安装

yum-yinstallmakegcc-c++ cmake bison-devel ncurses-devel

wgethttps://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.43-linux-glibc2.12-i686.tar.gz

tarxf 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_ci

make&&makeinstall

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

配置文件

3308

vim /data/mysql56/mysql3308/my.cnf

[client]port=3308socket= /data/mysql56/mysql3308/mysql.sockdefault-character-set =utf8[mysqld]skip-name-resolveuser=mysqlbasedir= /usr/local/mysqldatadir= /data/mysql56/mysql3308/dataport=3308server_id=1socket= /data/mysql56/mysql3308/mysql.sockpid-file= /data/mysql56/mysql3308/mysql.pidlog-error = /data/mysql56/mysql3308/mysql.errlog-bin = /data/mysql56/mysql3308/mysql-bincharacter-set-server =utf8

3309

vim/data/mysql56/mysql3309/my.cnf

[client]port=3309socket= /data/mysql56/mysql3309/mysql.sockdefault-character-set =utf8[mysqld]skip-name-resolveuser=mysqlbasedir= /usr/local/mysqldatadir= /data/mysql56/mysql3309/dataport=3309server_id=2socket= /data/mysql56/mysql3309/mysql.sockpid-file= /data/mysql56/mysql3309/mysql.pidlog-error = /data/mysql56/mysql3309/mysql.errlog-bin = /data/mysql56/mysql3309/mysql-bincharacter-set-server = utf8

启动脚本

#!/bin/bashmysql_bin=/usr/local/mysql/bin

mysql_port=3308

mysql_username="root"

mysql_password="123456"

mysql_dir=/data/mysql56/mysql$is_run=$(ss -tunlp|grep$|wc-l)start_server(){ $mysql_bin/mysqld_safe --defaults-file=$/my.cnf2>&1> /dev/null&

echo"已经运行"}

stop_server(){

if[ $ -eq1];then$mysql_bin/mysqladmin -u $ -p$ -S $/mysql.sock shutdown

echo"已经停止"

elseecho"是停止的"

fi

}

restart_server(){

if[ $ -eq1];thenecho"正在重启"stop_serversleep5start_serverecho"重启完毕"

elsestart_server

fi

}

case$1in

start)start_server;;stop)stop_server;;restart)restart_server;;

*)echo"Usage: $0 ";;

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

4.主从同步

1.登录主库

创建用户同步的用户

mysql> grant replication slave on *.* to rep@'192.168.0.%'identified by'rep123';mysql>flush privileges;查看mysql> show grantsforrep@'192.168.0.%';+--------------------------------------------------------------------------------------------------------------------------+| Grantsforrep@192.168..% |+--------------------------------------------------------------------------------------------------------------------------+| GRANT REPLICATION SLAVE ON *.* TO'rep'@'192.168.0.%'IDENTIFIED BY PASSWORD'*08F5BB4670F148DB0376A1EE646F0C67EAE138CE'|+--------------------------------------------------------------------------------------------------------------------------+1rowinset (0.00sec)

2.主库全量备份

mysqldump -uroot -p123456 -S /data/mysql56/mysql3308/mysql.sock -A -B --master-data=1--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

tarxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

mvmysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

cd/usr/local/mysql-proxy/

mkdir lua logs

cpshare/doc/mysql-proxy/rw-splitting.lua ./lua#复制读写分离配置文件

cpshare/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和端口，不加端口，默认4040proxy-read-only-backend-addresses=192.168.0.11:3309#指定后端从slave读取数据，不加端口默认3306proxy-backend-addresses=192.168.0.11:3308#指定后端主master写入数据，不加端口默认3306proxy-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崩溃时，尝试重启

配置文件授权

chmod660/etc/mysql-proxy.cnf

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

grantallon*.*tomysqlproxy@'192.168.0.%'identifiedby'mysqlproxy123';

4.修改脚本部分配置

vim /usr/local/mysql-proxy/lua/rw-splitting.lua

ifnot proxy.global.config.rwsplitthenproxy.global.config.rwsplit={min_idle_connections=1,#默认4个连接数时，才开始读写分离，改为1max_idle_connections=1,#默认8，改为1is_debug=false }end

5.启动

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

6.检查

#ss -tunlp|grepmysql-proxytcp LISTEN128192.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

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