关系型数据库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
配置文件
vim /data/mysql56/mysql3308/my.cnf [client] port = 3308 socket = /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 = 3308 server_id = 1 socket = /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 = 3309 socket = /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 = 3309 server_id = 2 socket = /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 -B -F -R -x --master-data=1 -A --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和端口，不加端口，默认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.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-proxy tcp 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
说明我连接上去查询语句是从从库返回的
