mysql主从复制和读写分离实战
目录 mysql主从复制和读写分离实战mysql主从复制Master配置Slave1配置Slave2配置主从复制测试mysql读写分离mysql_proxy配置读写分离测试mysql主从复制和读写分离实战mysql主从复制mysql主从复制模型：(本次是一主二从模型)Master：172.25.254.7slave1：172.25.254.8slave2：172.25.254.9mysql_pr.
mysql主从复制和读写分离实战
一般情况下，mysql都是通过主从复制实现数据同步，再通过读写分离实现数据库高可用能力。当然主从复制和读写分离的作用不仅仅是这些。
这里提供了一个mysql主从复制和读写分离模型：(本次是一主二从模型)
Master：172.25.254.7
slave1：172.25.254.8
slave2：172.25.254.9
mysql_proxy：172.25.254.10
所有服务都是在rhel7.0操作系统上运行，
Java web app：是客户端请求，会对数据库发起读写操作请求，具体是发送SQL指令
Mysql Proxy：对读写操作请求的SQL指令进行路由，使得读写分离
direct：一个负载分发引擎，对Mysql Proxy分发得读操作，按照一定得算法进行分发至后端得从服务器
master：主服务器，主要接受用户的写操作，并且负责将二进制日志同步给从服务器
slave-n：从服务器，主要负责用户的读操作(分担主服务器的读写压力)，并且负责重放master的写操作，还能实现容灾能力，保证高可用(如果主服务器挂掉，slvae顶上去)
mysql主从复制
mysql主从复制的思路：
配置主服务器，即Msater，使之具备一下能力
记录二进制日志
为从服务提供一个用户(设置密码)，提高二进制日志同步得安全性
配置从服务器，即slave，使之具备一下能力
记录中继日志
连接到mysql可以启动SLAVE功能，并且设置Master信息，通过配置信息，开启IO_THREAD和SQL_THREAD线程
mysql主动复制的意义：
为mysql服务提供高可用能力
通过远程主从复制，提供容灾能力
如果主服务器挂掉，从服务器可以快速顶替，并成为新的主服务器
Master配置
下载安装mysql
[root@mysql_master ~]# yum install -y mariadb* ***下载过程***
修改mysql配置文件
修改目的：
开启二进制日志记录，包括二进制日志格式、名前缀、index文件名等
log_bin：二进制日志名前缀
log_bin_index：二进制日志index文件名
binlog_format：二进制日志格式
修改InnoDB存储模式，修改为每表存储文件
innodb_file_per_table：InnoDB存储每表一个文件
设置ServerId，这在mysql主从复制是一个很重要的标识
server_id：ServerID
同步二进制日志，用于事务安全
sync_binlog：同步二进制日志
vim /etc/my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log_bin=master-bin log_bin_index=master-bin.index innodb_file_per_table=ON binlog_format=MIXED server_id=1 sync_binlog=1
上面内容仅仅是mysqld模块内容，包括添加内容
为从服务器创建复制数据的用户等信息
首先要启动mysql服务器：
[root@mysql_master ~]# systemctl start mariadb然后登陆mysql，添加信息：
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'copyuser'@'172.25.254.%' IDENTIFIED BY 'qq123.456'; Query OK, 0 rows affected (0.17 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.08 sec) #这里创建了一个名为copyuser的用户，设置密码为qq123.456，并且对172.25.254.0网段开放
Slave1配置
下载安装mysql
[root@mysql_slave1 ~]# yum install -y mariadb* ***下载过程***
修改mysql配置文件
修改目的：
开启中继日志，包括中继日志名前缀、index文件名
relay_log
relay_log_index
修改InnoDB存储模式，修改为每表存储文件
innodb_file_per_table
设置ServerID
server_id
设置从服务只能进行读操作
read_only
vim /etc/my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 innodb_file_per_table=ON relay_log = relay-log relay_log_index = relay-log.index server_id=2 read_only=ON
上面内容仅仅是mysqld模块内容，包括添加内容
添加登陆主服务器的登陆信息，包括复制的二进制日志文件名，位置信息等
首先要启动mysql服务器：
[root@mysql_slave1 ~]# systemctl start mariadb登陆mysql，添加登陆复制相关信息：
[root@mysql_slave1 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.25.254.7',MASTER_USER='copyuser',MASTER_PASSWORD='qq123.456',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=245; Query OK, 0 rows affected (0.32 sec) #这里定义使用Master的copyuser用户，并指定其密码；还有开始同步的二进制日志文件名，和起始位置 #可以使用SHOW SLAVE STATUS查看SLAVE两个线程是否启动成功。
启动从服务器的两个线程
MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)
如果成功启动，可以使用SHOW SLAVE STATUS查看状态
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.25.254.7 Master_User: copyuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 1120 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 1405 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes //IP_THREAD已经运行 Slave_SQL_Running: Yes //SQL_THREAD已经运行 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1120 Relay_Log_Space: 1693 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
Slave2配置
Slave2配置和Slave1几乎相同，不过要注意ServerID不能重复
下载安装mysql
[root@mysql_slave2 ~]# yum install -y mariadb* ***下载过程***
修改配置信息
vim /etc/my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 innodb_file_per_table=ON relay_log = relay-log relay_log_index = relay-log.index server_id=3 read_only=ON
开启从服务器两个线程
[root@mysql_slave2 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.25.254.7',MASTER_USER='copyuser',MASTER_PASSWORD='qq123.456',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=245; [[Query OK, 0 rows affected (2.22 sec) MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)
主从复制测试
在主服务器上创建一个test库，并在库中创建一张表
MariaDB [(none)]> CREATE DATABASE fsx; Query OK, 1 row affected (0.09 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | fsx | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
在slave1上查看是否执行主从复制，同步创建了fsx库
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | fsx | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
在slave2上查看是否执行主从复制，同步创建了fsx库
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | fsx | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
mysql读写分离
mysql主从复制，一主多从模型下。为了缓解主服务器的读写压力，经常会让从服务器为用户的读操作，又因为从服务器不能提供写操作。所以：在主服务器上写操作，在从服务器只进行读操作，这种模型就成只为读写分离。
这就需要在mysql主从集群前有一个设备(mysql-proxy)，对用户的操作进行分析，对读写操作进行分离，而且由于从服务器(提供读服务)不止一个，又要提供一个负载均衡器来进行连接分发。当然，还可以提供一个memchace服务器来提供缓存服务。这里仅仅是添加了mysql-proxy。
mysql读写分离的意义：
减轻mysql主服务器的访问压力
提高安全性
mysql_proxy配置
使用mysql自带的mysql-proxy进行读写分离操作。
这里使用的mysql-proxy服务端IP地址：172.25.254.10
下载mysql-proxy
可以在mysql官网找到，下载就行，此次使用的是mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz
解压至/usr/local/
[root@mysql_proxy ~]# tar zxf mysql-proxy-0.8.2-linux-rhel5-x86-64bit.tar.gz -C /usr/local/ [root@mysql_proxy ~]# cd /usr/local/ #切换到/usr/local/目录下，为解压的目录创建一个软连接，方便使用 [root@mysql_proxy local]# ln -s mysql-proxy-0.8.2-linux-rhel5-x86-64bit mysql-proxy [root@mysql_proxy local]# cd mysql-proxy #查看mysql-proxy中的架构 [root@mysql_proxy mysql-proxy]# ls bin include lib libexec licenses share #添加mysql-proxy用户 [root@mysql_proxy mysql-proxy]# useradd mysql-proxy
添加mysql-proxy到全局变量
[root@mysql_proxy mysql-proxy]# echo "export PATH=$PATH:/usr/local/mysql-proxy/bin/" > /etc/profile.d/mysql-proxy.sh [root@mysql_proxy mysql-proxy]# . /etc/profile.d/mysql-proxy.sh
为mysql-proxy提供SysV服务脚本
脚本内容：
vim /etc/init.d/mysql-proxy
# # mysql-proxy This script starts and stops the mysql-proxy daemon # # chkconfig: - 78 30 # processname: mysql-proxy # description: mysql-proxy is a proxy daemon for mysql # Source function library. . /etc/rc.d/init.d/functions prog="/usr/local/mysql-proxy/bin/mysql-proxy" # Source networking configuration. if [ -f /etc/sysconfig/network ]; then . /etc/sysconfig/network fi # Check that networking is up. [ ${NETWORKING} = "no" ] && exit 0 # Set default mysql-proxy configuration. ADMIN_USER="admin" ADMIN_PASSWD="admin" ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" PROXY_OPTIONS="--daemon" PROXY_PID=/var/run/mysql-proxy.pid PROXY_USER="mysql-proxy" # Source mysql-proxy configuration. if [ -f /etc/sysconfig/mysql-proxy ]; then . /etc/sysconfig/mysql-proxy fi RETVAL=0 start() { echo -n $"Starting $prog: " daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD" RETVAL=$? echo if [ $RETVAL -eq 0 ]; then touch /var/lock/subsys/mysql-proxy fi } stop() { echo -n $"Stopping $prog: " killproc -p $PROXY_PID -d 3 $prog RETVAL=$? echo if [ $RETVAL -eq 0 ]; then rm -f /var/lock/subsys/mysql-proxy rm -f $PROXY_PID fi } # See how we were called. case "$1" in start) start ;; stop) stop ;; restart) stop start ;; condrestart|try-restart) if status -p $PROXY_PIDFILE $prog >&/dev/null; then stop start fi ;; status) status -p $PROXY_PID $prog ;; *) echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}" RETVAL=1 ;; esac exit $RETVAL
将脚本内容保存到/etc/init.d/mysql-proxy，并添加执行权限，加入服务列表
[root@mysql_proxy init.d]# chmod +x /etc/init.d/mysql-proxy [root@mysql_proxy mysql-proxy]# chkconfig --add mysql-proxy
为服务脚本提供配置文件/etc/sysconfig/mysql-proxy
vim /etc/sysconfig/mysql-proxy
# Options for mysql-proxy ADMIN_USER="admin" ADMIN_PASSWORD="admin" ADMIN_ADDRESS="" #开始mysql-proxy管理，要借助lua脚本，只里指定了lua脚本的路径 ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" PROXY_ADDRESS="" PROXY_USER="mysql-proxy" PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=172.25.254.7:3306 --proxy-read-only-backend-addresses=172.25.254.8:3306 --proxy-read-only-backend-addresses=172.25.254.9:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
这里最后一行根据不同情况和需求设置额可能不同，具体选项含义：--daemon ：以守护进程模式启动mysql-proxy
--log-level=info ：日志级别
--log-use-syslog ：基于syslog记录日志
--plugins=proxy ：在mysql-proxy启动时加载proxy插件，该插件实现读写分离
--plugins=admin ：在mysql-proxy启动时加载admin插件，该插件实现proxy管理
--proxy-backend-addresses=172.25.254.7:3306 ：后端读写mysql服务器的地址和端口
--proxy-read-only-backend-addresses=172.25.254.8:3306 ： 后端只读mysql服务器的地址和端口
--proxy-read-only-backend-addresses=172.25.254.9:3306 ： 后端只读mysql服务器的地址和端口
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" ：指定读写分离时使用的lua脚本
由于mysql-proxy没有提供admin.lua文件，所以的自己编写admin.lua，并保存到指定位置
vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua
function set_error(errmsg) proxy.response = { type = proxy.MYSQLD_PACKET_ERR, errmsg = errmsg or "error" } end function read_query(packet) if packet:byte() ~= proxy.COM_QUERY then set_error("[admin] we only handle text-based queries (COM_QUERY)") return proxy.PROXY_SEND_RESULT end local query = packet:sub(2) local rows = { } local fields = { } if query:lower() == "select * from backends" then fields = { { name = "backend_ndx", type = proxy.MYSQL_TYPE_LONG }, { name = "address", type = proxy.MYSQL_TYPE_STRING }, { name = "state", type = proxy.MYSQL_TYPE_STRING }, { name = "type", type = proxy.MYSQL_TYPE_STRING }, { name = "uuid", type = proxy.MYSQL_TYPE_STRING }, { name = "connected_clients", type = proxy.MYSQL_TYPE_LONG }, } for i = 1, #proxy.global.backends do local states = { "unknown", "up", "down" } local types = { "unknown", "rw", "ro" } local b = proxy.global.backends[i] rows[#rows + 1] = { i, b.dst.name, -- configured backend address states[b.state + 1], -- the C-id is pushed down starting at 0 types[b.type + 1], -- the C-id is pushed down starting at 0 b.uuid, -- the MySQL Server's UUID if it is managed b.connected_clients -- currently connected clients } end elseif query:lower() == "select * from help" then fields = { { name = "command", type = proxy.MYSQL_TYPE_STRING }, { name = "description", type = proxy.MYSQL_TYPE_STRING }, } rows[#rows + 1] = { "SELECT * FROM help", "shows this help" } rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" } else set_error("use 'SELECT * FROM help' to see the supported commands") return proxy.PROXY_SEND_RESULT end proxy.response = { type = proxy.MYSQLD_PACKET_OK, resultset = { fields = fields, rows = rows } } return proxy.PROXY_SEND_RESULT end
在Master上，创建一个允许远程登陆访问的管理员帐号
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'172.25.254.%' IDENTIFIED BY 'coco123.456'; Query OK, 0 rows affected (0.04 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.09 sec)
启动mysql-proxy：
[root@mysql_proxy mysql-proxy]# systemctl start mysql-proxy [root@mysql_proxy mysql-proxy]# netstat -antple|grep mysql-proxy tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 0 40445 10726/mysql-proxy tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 0 40444 10726/mysql-proxy
开启成功，会开启TCP的3306和4041端口
读写分离测试
在172.25.254.0网段内，任意由mysql-client服务的用户，都可以使用mysql-proxy的管理用户和密码进行登陆：
登陆mysql-proxy管理端
[root@mysql_slave2 ~]# mysql -uadmin -padmin -h 172.25.254.10 --port=4041 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> SELECT * FROM backends; +-------------+-------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+-------------------+---------+------+------+-------------------+ | 1 | 172.25.254.7:3306 | unknown | rw | NULL | 0 | | 2 | 172.25.254.8:3306 | unknown | ro | NULL | 0 | | 3 | 172.25.254.9:3306 | unknown | ro | NULL | 0 | +-------------+-------------------+---------+------+------+-------------------+ 3 rows in set (0.00 sec)
读写分离测试：
[root@mysql_slave2 ~]# mysql -uroot -p -h 172.25.254.10 Enter password: 这里使用的密码是coco123.456 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 5.5.35-MariaDB-log MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | fsx | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
无需安装部署，在线快速体验 Byzer
更多推荐
7
0- 0
已为社区贡献1条内容
所有评论(0)