mysql 主从复制读写分离

目标:实现主从复制，读写分离

环境：
mysql-proxy:192.168.1.21      version:5.0.77
mysql-master:192.168.1.24     version:5.0.95
mysql-slave:192.168.1.7       version:5.0.95

一.主从配置过程：
登陆mysql-master：
授权给从数据库服务器192.168.1.7
mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.1.7' identified by '123qwe';
查询主数据库状态
Mysql> show master status;
----------------问题解决办法---------------------------------------------
问题1：
show master status;
没有数据显示
解决办法:
1.关闭从库
2.show variables like '%log_bin%';
查看是否是OFF，如果是
在/etc/my.cnf中添加
log-bin=mysql-bin
重启主mysql 
--------------------------------------------------------------------------

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到。

配置从库
登陆mysql-slave：
修改从服务器的配置文件/etc/my.cnf
将 server-id = 1修改为 server-id = 10，并确保这个ID没有被别的MySQL服务所使用。
启动mysql

执行同步SQL语句
mysql> change master to
master_host='192.168.1.24',
master_user='rep1',
master_password='123qwe',
master_log_file='mysql-bin.000001',
master_log_pos=98;

正确执行后启动Slave同步进程
mysql> start slave;
主从同步检查
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.1.24
                Master_User: rep1
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000002
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000004
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000002
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。
-----------------------------问题解决办法-----------------------------------------------
问题：这里的Slave_IO_Running与Slave_SQL_Running必须都为YES才行，如果不行，可以使用以下命令查看问题原因：
tail /var/log/mysqld.log
比如：140220 21:59:29 [ERROR] Error reading packet from server: Misconfigured master - server id was not set ( server_errno=1236)
这句是说主数据库没有设置server-id值
解决办法：
在主数据库/etc/my.cnf配置
server-id=1
------------------------------------------------------------------------------------

如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：
(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> FLUSH TABLES WITH READ LOCK;
(2)查看主数据库状态
mysql> show master status;
(3)记录下 FILE 及 Position 的值。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |       98 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。
(4)取消主数据库锁定
mysql> UNLOCK TABLES;

从服务器上操作
change master to
master_host='192.168.1.24',
master_user='rep1',
master_password='123qwe',
master_log_file='mysql-bin.000002',
master_log_pos=411;
mysql> start slave;
主从同步检查
mysql> show slave status\G

二.主从复制效果验证
主服务器上的操作
在主服务器上创建数据库first_db1
mysql> create database first_db1;
Query Ok, 1 row affected (0.01 sec)
在主服务器上创建表first_tb
mysql> use first_db1;
mysql> create table first_tb1(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)
在主服务器上的表first_tb1中插入记录
mysql> insert into first_tb1 values (001,'myself');
Query Ok, 1 row affected (0.00 sec)

在从服务器上查看
mysql> show databases;
记录也已经存在
由此，整个MySQL主从复制的过程就完成了。
==========================================================================================
三.进行MySQL读写分离的安装与配置。
数据库Master主服务器：192.168.1.24
数据库Slave从服务器：192.168.1.7
Mysql-proxy调度服务器：192.168.1.21

登陆Mysql-proxy：
检查系统所需软件包
通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。
gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*
libevent* glib*

编译安装lua
MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。
lua安装：
lua可通过以下方式获得
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar -xzvf lua-5.1.4.tar.gz
cd lua-5.1.4
vi src/Makefile
在 CFLAGS= -O2 -Wall $(MYCFLAGS) 这一行记录里加上-fPIC，更改为 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 来避免编译过程中出现错误。
make linux
make install
cp etc/lua.pc /usr/lib/pkgconfig/
export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig
proxy安装：
tar -xzvf mysql-proxy-0.8.3-linux-rhel5-x86-32bit.tar.gz
mv mysql-proxy-0.8.3-linux-rhel5-x86-32bit /opt/mysql-proxy
创建mysql-proxy服务管理脚本
mkdir /opt/mysql-proxy/init.d/
vim mysql-proxy
    #!/bin/sh  
      
    #  
    # mysql-proxy This script starts and stops the mysql-proxy daemon  
    #  
    # chkconfig: - 78 30  
    # processname: mysql-proxy  
    # description: mysql-proxy is a proxy daemon to mysql  
      
    # Source function library.  
    . /etc/rc.d/init.d/functions  
      
    #PROXY_PATH=/usr/local/bin  
    PROXY_PATH=/opt/mysql-proxy/bin  
      
    prog="mysql-proxy"  
      
    # Source networking configuration.  
    . /etc/sysconfig/network  
      
    # Check that networking is up.  
    [ ${NETWORKING} = "no" ] && exit 0  
      
    # Set default mysql-proxy configuration.  
    #PROXY_OPTIONS="--daemon"  
      
    PROXY_OPTIONS="--proxy-read-only-backend-addresses=192.168.23.132:3306 --proxy-backend-addresses=192.168.23.131:3306 --proxy-lua-script=/opt/mysql-proxy/share/rw-splitting.lua"  
      
    PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid  
      
    # Source mysql-proxy configuration.  
    if [ -f /etc/sysconfig/mysql-proxy ]; then  
            . /etc/sysconfig/mysql-proxy  
    fi  
      
    PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH  
    # By default it's all good  
    RETVAL=0  
      
    # See how we were called.  
    case "$1" in  
      start)  
            # Start daemon.  
            echo -n $"Starting $prog: "  
            $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=root --log-level=debug --log-file=/opt/mysql-proxy/log/mysql-proxy.log  
            RETVAL=$?  
            echo  
            if [ $RETVAL = 0 ]; then  
                    touch /var/lock/subsys/mysql-proxy]  
                    echo "ok"  
            fi  
           ;;  
      stop)  
            # Stop daemons.  
            echo -n $"Stopping $prog: "  
            killproc $prog  
            RETVAL=$?  
            echo  
            if [ $RETVAL = 0 ]; then  
                    rm -f /var/lock/subsys/mysql-proxy  
                    rm -f $PROXY_PID  
            fi  
           ;;  
      restart)  
            $0 stop  
            sleep 3  
            $0 start  
           ;;  
      condrestart)  
           [ -e /var/lock/subsys/mysql-proxy ] && $0 restart  
          ;;  
      status)  
            status mysql-proxy  
            RETVAL=$?  
           ;;  
      *)  
            echo "Usage: $0 {start|stop|restart|status|condrestart}"  
            RETVAL=1  
           ;;  
    esac  
    exit $RETVAL  

--proxy-read-only-backend-addresses=192.168.1.7:3306 \ //定义后端只读从服务器地址
--proxy-backend-addresses=192.168.1.24:3306 \ //定义后端主服务器地址
--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua" \ //定义lua读写分离脚本路径
PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid //定义mysql-proxy PID文件路径

$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS \
--daemon \ //定义以守护进程模式启动
--keepalive \ //使进程在异常关闭后能够自动恢复
--pid-file=$PROXY_PID \ //定义mysql-proxy PID文件路径
--user=mysql \ //以mysql用户身份启动服务
--log-level=warning \ //定义log日志级别，由高到低分别有(error|warning|info|message|debug)
--log-file=/opt/mysql-proxy/log/mysql-proxy.log //定义log日志文件路径
==============================================

cp mysql-proxy /opt/mysql-proxy/init.d/
chmod +x /opt/mysql-proxy/init.d/mysql-proxy
mkdir /opt/mysql-proxy/run
mkdir /opt/mysql-proxy/log
mkdir /opt/mysql-proxy/scripts

配置并使用rw-splitting.lua读写分离脚本
最新的脚本我们可以从最新的mysql-proxy源码包中获取
cp /home/buniao/mysql-proxy-0.8.3-linux-rhel5-x86-32bit/share/doc/mysql-proxy/rw-splitting.lua /opt/mysql-proxy/scripts
修改读写分离脚本rw-splitting.lua
修改默认连接，进行快速测试，不修改的话要达到连接数为4时才启用读写分离
vim /opt/mysql-proxy/scripts/rw-splitting.lua
=============================
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, //默认为4
max_idle_connections = 1, //默认为8
is_debug = false
}
end
=============================

修改完成后，启动mysql-proxy
/opt/mysql-proxy/init.d/mysql-proxy start


测试读写分离效果
创建用于读写分离的数据库连接用户
登陆主数据库服务器192.168.1.24，通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p
mysql> GRANT ALL ON *.* TO 'proxy1'@'192.168.1.21' IDENTIFIED BY 'password';
由于我们配置了主从复制功能，因此从数据库服务器192.168.1.7上已经同步了此操作。

为了清晰的看到读写分离的效果，需要暂时关闭MySQL主从复制功能
登陆从数据库服务器：
mysq -uroot -p
mysql> slave stop;
mysql> select name from first_tb.first_tb;
+------+------------+
| id   | name       |
+------+------------+
|  123 | abcdefghij |
|  321 | buniao   |
|    1 | dgd        |
|    1 | term       |
|    2 | test       |
|    3 | sun        |
|    4 | dgd        |
+------+------------+
登陆主数据库服务器：
mysql> insert into first_tb values("005","mjj");
mysql> select * from first_tb;
+------+------------+
| id   | name       |
+------+------------+
|  123 | abcdefghij |
|  321 | buniao   |
|    1 | dgd        |
|    1 | term       |
|    2 | test       |
|    3 | sun        |
|    4 | dgd        |
|    5 | mjj        |
+------+------------+
此时，由于关闭了主从复制功能，所以在从服务器上不会看到数据更新

登陆proxy服务器
mysql -h192.168.1.21 -uproxy1 -p123qwe -P4040
mysql> select * from first_tb.first_tb;
+------+------------+
| id   | name       |
+------+------------+
|  123 | abcdefghij |
|  321 | buniao   |
|    1 | dgd        |
|    1 | term       |
|    2 | test       |
|    3 | sun        |
|    4 | dgd        |
|    5 | mjj        |
+------+------------+
8 rows in set (0.00 sec)
会看到在查主服务器上进行了查询
接着打开三个终端
[root@localhost ~]# mysql -h192.168.1.21 -uproxy1 -p123qwe -P4040
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.95 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from first_tb.first_tb;
+------+------------+
| id   | name       |
+------+------------+
|  123 | abcdefghij |
|  321 | buniao   |
|    1 | dgd        |
|    1 | term       |
|    2 | test       |
|    3 | sun        |
|    4 | dgd        |
+------+------------+
7 rows in set (0.00 sec)
会发现在从数据库上进行了查询

自此，数据库主从复制，读写分离完成。
在测试完后，将从服务器上同步功能打开
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> select name from first_tb.first_tb;
+------------+
| name       |
+------------+
| abcdefghij |
| buniao   |
| dgd        |
| term       |
| test       |
| sun        |
| dgd        |
| mjj        |
+------------+
8 rows in set (0.00 sec)
此时，数据重新进行同步

参考文档
http://www.cnblogs.com/luckcs/articles/2543607.html

http://www.linuxidc.com/Linux/2014-05/102265.htm

本文转自 穿越防火墙 51CTO博客，原文链接:http://blog.51cto.com/sjitwant/1695634