目标:实现主从复制，读写分离 环境： 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