mysql主从复制,读写分离

From：http://hi.baidu.com/yzy888/blog/item/98784fd074381d84a0ec9cc3.html

 

mysql主从复制
主服务器IP：192.168.99.10
从服务器IP：192.168.99.20
(一)安装mysql(主从服务器操作相同)
yum -y install gcc gcc-c++ ncurses-devel
增加组和用户
#/usr/sbin/groupadd mysql        
#/usr/sbin/useradd -g mysql mysql -s /sbin/nologin
解压、编译、安装
#tar xvf mysql-5.1.35.tar.gz
#cd mysql-5.1.35
#./configure --prefix=/usr/local/mysql --localstatedir=/data/mysql/data --with-extra-charsets=utf8,gb2312,gbk --with-pthread --enable-thread-safe-client
#make && make install
拷贝配置文件
#cp support-files/my-large.cnf   /etc/my.cnf
修改权限
#chown -R mysql.mysql /usr/local/mysql
初始化数据库
#/usr/local/mysql/bin/mysql_install_db --user=mysql
改变权限
#chown -R mysql:mysql /data/mysql/data
(二)修改配置文件
主服务器
server-id = 1
从服务器
server-id = 2..3..4依次增加
(三)启动服务(主从相同)
#/usr/local/mysql/bin/mysqld_safe --user=mysql&
(四)授权(仅主服务器)
mysql>GRANT REPLICATION SLAVE ON *.* to 'rep'@'192.168.99.%' identified by 'sasasa';
(五)查询主数据库状态(主服务器上)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      771 |              |                  | 
+------------------+----------+--------------+------------------+
记住file和position的值
(六)配置从服务器
mysql> change master to master_host='192.168.99.10', master_user='rep', master_password='sasasa1', master_log_file='mysql-bin.000003', master_log_pos=771;
master_host主服务器ip，master_user连接帐户,master_password连接密码,maser_log_file填入上面的file值，master_log_pos填入上面的position值
mysql> start slave;        启用了复制功能
mysql> show slave status\G;        检查slave状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.99.10
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 771
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 771
              Relay_Log_Space: 410
              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:

注意：Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 如果这两个状态不是yes，那么请检查上面的配置是否正确，主要是file和position设置是否正确，从库是否能用主库的账号登陆
如果主服务器还有数据传输，那么先进行锁表操作 mysql> FLUSH TABLES WITH READ LOCK; 然后按照第五步的进行操作

安装mysql-proxy分发请求
（一）安装依赖包
lua-5.1
#tar zxvf lua-5.1.tar.gz
#cd lua-5.1
#sed -i 's#INSTALL_TOP= /usr/local#INSTALL_TOP= /usr/local/lua#' Makefile
#make posix && make install
libevent-1.1a
#tar zxvf libevent-1.1a.tar.gz
#cd libevent-1.1a
#./configure --prefix=/usr/local/libevent
#make && make install
check-0.8.4
tar zxvf check-0.8.4.tar.gz
cd check-0.8.4
./configure
make
make install
(二)安装mysql客户端
#tar zxvf mysql-5.1.35.tar.gz
#cd mysql-5.1.35
#./configure --prefix=/usr/local/mysql --without-server
#make && make install
（三）设置环境变量
export LUA_CFLAGS="-I/usr/local/lua/include" LUA_LIBS="-L/usr/local/lua/lib -llua -ldl" LDFLAGS="-L/usr/local/libevent/lib -lm"
export CPPFLAGS="-I/usr/local/libevent/include"
export CFLAGS="-I/usr/local/libevent/include"
（四）安装mysql-proxy
#tar xvf mysql-proxy-0.6.1.tar.gz
#cd mysql-proxy-0.6.1
#./configure --prefix=/usr/local/mysql-proxy --with-mysql=/usr/local/mysql --with-lua
#Make && Make install
(五)创建启动脚本
#!/bin/bash
LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" /usr/local/mysql-proxy/sbin/mysql-proxy --proxy-backend-addresses=192.168.99.10:3306 --proxy-read-only-backend-addresses=192.168.99.20:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua >> /var/log/mysql-proxy.log &

#chmod a+x /usr/local/bin/mysql-proxy.sh
执行命令/usr/local/bin/mysql-proxy.sh启动服务
主要的命令行选项
--help-all显示所有的帮助选项
--admin-address=host:port         管理主机及端口，默认是4041
--proxy-address=host:port         代理服务器的监听地址及端口，默认4040
--proxy-read-only-address=host:port         只读连接时，代理服务器的监听地址及端口
--proxy-backend-addresses=host:port        连接真实服务器的地址及监听端口，默认是3306
--proxy-lua-script=file         指定lua脚本的名称
使用方法
mysql-proxy --proxy-backend-addresses=<master_ip> :3306\
        --proxy-read-only-backend-addresses=<slave_ip1>:3306 \
        --proxy-read-only-backend-addresses=<slave_ip2>:3306 
测试 略。。。