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

1.环境准备

我的数据库版本是MySQL 5.6

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

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

2.安装

yum -y install make gcc-c++ cmake bison-devel ncurses-devel
wget 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_ci
make && 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

配置文件

3308
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
 
3309
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=3308
mysql_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 shutdown 
echo "已经停止"
else
 echo "是停止的"
fi
}
restart_server(){
if [ ${is_run} -eq 1 ];then
 echo "正在重启" stop_server sleep 5 start_server echo "重启完毕"
else start_server
fi
}
case $1 in
start) 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 -A -B --master-data=1 --events >/tmp/rep3308_$(date +%F).sql

3.从库导入全量备份

mysql -uroot -p123456 -S /data/mysql56/mysql3309/mysql.sock </tmp/rep3308_$(date +%F).sql

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.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
cd /usr/local/mysql-proxy/
mkdir lua logs
cp 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和端口，不加端口，默认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崩溃时，尝试重启

配置文件授权

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.lua
if not proxy.global.config.rwsplit then proxy.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-proxytcp LISTEN 0 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

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