mysql 读写分离

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 STATUSG
*************************** 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

#!/bin/bash
#
# 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)

相关阅读:
Asp中返回到前一页面
 vs2008 简单ajax 功能的实现。
Arcgis Server的唯一值渲染
 ArcGIS Server中缓冲区分析的实现（点）
用两个Calendar控件来控制数据库记录的读入
 把十进制转化为二进制的一种方法
 Segmentation fault (core dumped)
libc glibc glib 的关系
 使用异或加密数据
 宏定义一些内容
原文地址：https://www.cnblogs.com/xiexun/p/13734467.html