目标:实现主从复制，读写分离环境：mysql-proxy:192.168.1.21 version:5.0.77mysql-master:192.168.1.24 version:5.0.95mysql-slave:192.168.1.7 version:5.0.95一.主从配置过程：登陆mysql-master：授权给从数据库服务器192.168.1.7mysql> 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 tomaster_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 tomaster_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_db1mysql> create database first_db1;Query Ok, 1 row affected (0.01 sec)在主服务器上创建表first_tbmysql> 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.7Mysql-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*编译安装luaMySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。lua安装：lua可通过以下方式获得wget http://www.lua.org/ftp/lua-5.1.4.tar.gztar -xzvf lua-5.1.4.tar.gzcd lua-5.1.4vi src/Makefile在 CFLAGS= -O2 -Wall $(MYCFLAGS) 这一行记录里加上-fPIC，更改为 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 来避免编译过程中出现错误。make linuxmake installcp etc/lua.pc /usr/lib/pkgconfig/export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfigproxy安装：tar -xzvf mysql-proxy-0.8.3-linux-rhel5-x86-32bit.tar.gzmv 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-proxymkdir /opt/mysql-proxy/runmkdir /opt/mysql-proxy/logmkdir /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 poolif not proxy.global.config.rwsplit thenproxy.global.config.rwsplit = {min_idle_connections = 1, //默认为4max_idle_connections = 1, //默认为8is_debug = false}end=============================修改完成后，启动mysql-proxy/opt/mysql-proxy/init.d/mysql-proxy start测试读写分离效果创建用于读写分离的数据库连接用户登陆主数据库服务器192.168.1.24，通过命令行登录管理MySQL服务器/opt/mysql/bin/mysql -uroot -pmysql> GRANT ALL ON *.* TO 'proxy1'@'192.168.1.21' IDENTIFIED BY 'password';由于我们配置了主从复制功能，因此从数据库服务器192.168.1.7上已经同步了此操作。为了清晰的看到读写分离的效果，需要暂时关闭MySQL主从复制功能登陆从数据库服务器：mysq -uroot -pmysql> 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 -P4040mysql> 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 -P4040Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.0.95 Source distributionType '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.htmlhttp://www.linuxidc.com/Linux/2014-05/102265.htm
本文转自 穿越防火墙 51CTO博客，原文链接:http://blog.51cto.com/sjitwant/1695634
网友评论