MYSQL主从复制及读写分离小记

 在自己的破本本上实现了配置主从复制及读写分离，特记录下来备忘。

多服务器配置参考mysql5.1参考手册5.12.1. 在Windows下运行多个服务器

主从复制参考mysql5.1参考手册第6章：MySQL中的复制

读写分离参考http://www.gaojinbo.com/mysql-proxy-%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB.html

1.环境
windows xp sp3
MySQL Proxy mysql-proxy-0.8.1-win32-x86

MySQL Server mysql-noinstall-5.5.9-win32

Lua

2.配置多服务器

将MySQL Server mysql-noinstall-5.5.9-win32解压到%mysql_home%（目录自定），我此处解压到D:/MYSQL/mysql-5.5.9下，将data文件夹复制为data3309,data3301,data3302，复制启动选项文件为my3309.ini，my3301.ini，my3302.ini（ini文件的内容见3.主从复制）

创建startboot.bat文件，内容如下

cd D:\MySQL\mysql-5.5.9\bin

d:

rem 启动SQL节点
start mysqld --defaults-file=D:\MySQL\mysql-5.5.9\my3309.ini --console
start mysqld --defaults-file=D:\MySQL\mysql-5.5.9\my3301.ini --console
start mysqld --defaults-file=D:\MySQL\mysql-5.5.9\my3302.ini --console

保存后双击startboot.bat，启动服务器

对应的stopboot.bat文件，内容如下

cd %mysql_home%/bin:

rem 启动SQL节点
start mysqladmin shutdown -u root -proot -P 3301
start mysqladmin shutdown -u root -proot -P 3302
start mysqladmin shutdown -u root -proot -P 3309

 

以上脚本均采用命令行的形式

 

3.主从复制

1).配置启动选项文件

采用一主两从，主服务器端口设置为3309，从服务器端口设置为3301、3302

配置主服务器3309的my.ini文件

[mysqld]

#端口
port  = 3309
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

# 设置安装路径

basedir=D:/MySQL/mysql-5.5.9
# 设置数据文件目录

datadir=D:/MySQL/mysql-5.5.9/data3309

# 服务器默认的字符集
character-set-server=utf8

# 默认的存储引擎

default-storage-engine=INNODB

# 服务器节点，必须唯一，为方便改为和端口一样的值
server-id = 3309

# 复制目标数据库

replicate-do-db=test

# 复制忽略的数据库
replicate-ignore-db=mysql
#开启二进制日志，设置日志名
log-bin=mysql-bin-3309
# 按服务器启动日志中的提示将log换成了general-log

general-log=mylog-3309
#需要记录二进制记录复制的数据库
binlog-do-db=test
#复制忽略二进制记录的数据库
binlog-ignore-db=mysql
#在事务过程中容纳二进制日志SQL语句的缓存大小，二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(–log-bin选项)的前提下为 每个客户端分配的内存。#如果经常使用大的，多语句事务，可以增加该值以获得更有的性能
binlog_cache_size = 5M
#binlog_format=mixed
#是否记录慢查询，默认OFF。用long_query_time变量的值来确定“慢查 询”。
log_slow_queries
#超过2S的query将记slow query日志
long_query_time = 2

#既做为从服务器又做为主服务器
log-slave-updates=true

# Causes updates to non-transactional engines using statement format to be
# written directly to binary log. Before using this option make sure that
# there are no dependencies between transactional and non-transactional
# tables such as in the statement INSERT INTO t_myisam SELECT * FROM
# t_innodb; otherwise, slaves may diverge from the master.
#binlog_direct_non_transactional_updates=TRUE

# 以下是innodb引擎的配置

# innodb数据文件目录

innodb_data_home_dir = D:/MySQL/mysql-5.5.9/data3301/

# innodb数据文件，采用innodb_data_home_dir的相对路径

innodb_data_file_path = ibdata1:10M:autoextend

# innodb日志组目录

innodb_log_group_home_dir =D:/MySQL/mysql-5.5.9/data3301/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

因为是在一台机器上配置，为方便辨识，在相关目录、id都加上端口数值，如datadir=D:/MySQL/mysql-5.5.9/data3309。从服务器配置同主服务器，只需将参数中的3309替换为3301和3302，另存为my.ini即可。

启动主从服务器。

2).配置复制用户

配置主服务器复制用户

登录主服务器

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 3309

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep_master_3309'@'localhost' IDENTIFIED BY 'rep';

mysql> quit;

配置从服务器复制用户

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 3301

 

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep_slave_3301'@'localhost' IDENTIFIED BY 'rep';

mysql> quit;

 

 

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 3302

 

3).同步数据

 

使用mysqldump从主服务器导出数据，再导入到从服务器中，步骤略。或者直接将datadir=D:/MySQL/mysql-5.5.9/data3309下的数据文件ibdata1和ib_logfile0及test数据库文件拷贝到3301和3302的datadir下（因为是测试，能简则简，拷贝数据文件的原因是因为引擎采用的innodb，没有ibdata1和ib_logfile0进入客户端后查询表会出错）。

4).配置复制

登录主服务器3309

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 3309

输入

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
            File: mysql-bin-3309.000001
        Position: 107
    Binlog_Do_DB: test
Binlog_Ignore_DB: mysql

记住File和Position

 

登录从服务器3301

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 3301

输入

mysql> STOP SLAVE;

将主服务器的参数配置到从服务器中，MASTER_LOG_FILE和MASTER_LOG_POS分别对应主服务器的File和Position
mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='rep_master_3309', MASTER_PASSWORD='rep', MASTER_LOG_FILE='mysql-bin-3309.000001', MASTER_LOG_POS=107, MASTER_PORT=3309;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: rep_master_3309
                  Master_Port: 3309
                Connect_Retry: 60
              Master_Log_File: mysql-bin-3309.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: www-ff27f888e5e-relay-bin.000002
                Relay_Log_Pos: 258
        Relay_Master_Log_File: mysql-bin-3309.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           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: 107
              Relay_Log_Space: 424
              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: 3309

 

看到Slave_IO_Running: Yes

Slave_SQL_Running: Yes

即为从服务器3301连接主服务器3309成功。

 

PS:如果配置后出现

Slave_IO_Running: No

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

提示无法找到索引文件mysql-3301-bin.index，按以下方式解决。

mysql> stop slave;
mysql> change master to master_host='localhost',master_user='rep_slave_3301',master_password='rep';
mysql> start slave;

为什么改下主机、用户、密码就好了，在网上找了好多，也没有明确解释，但问题还是解决了。

 

 

从服务器3302也如法炮制。

 

5).复制数据

登录主服务器3309

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 3309

在test数据库下创建表t11，表很简单，够用了
mysql> CREATE TABLE `t11` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

利用自增长插入数据（在sql_mode=''等检查不严格时可以采用以下语句）

mysql> insert into t11 values('');

mysql> select * from t11;

+---+
| i |
+---+
| 1 |

+---+

登录主服务器3301

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 3301

查询test数据库下的表t11，就会发现这条数据。

mysql> select * from t11;

+---+
| i |
+---+
| 1 |

+---+

4.读写分离

1).配置启动选项文件

解压mysql-proxy-0.8.1-win32-x86到%mysql_proxy_home%（目录自定），我此处解压到D:/MYSQL/mysql-proxy-0.8.1-win32-x86下，配置mysql-proxy.ini如下

[mysql-proxy]
daemon=true
proxy-skip-profiling=true
keepalive = true

#代理写服务器
proxy-backend-addresses = localhost:3309

#代理只读服务器
proxy-read-only-backend-addresses = localhost:3301,localhost:3302
#lua编写的读写分离代理脚本
proxy-lua-script = D:/MySQL/mysql-proxy-0.8.1-win32-x86/share/doc/mysql-proxy/rw-splitting.lua

#新建log文件夹
log-file = D:/MySQL/mysql-proxy-0.8.1-win32-x86/logs/mysql-proxy-0.8.0.log
log-level = debug
# --proxy-fix-bug-25371 — 修正 mysql的libmysql版本大于5.1.12的一个#25371号bug

#代理主机及端口，端口默认为4040
#proxy-address=localhost:4040
#指定一个mysqo-proxy的管理端口，缺省是 4041
admin-address=localhost:4041

#管理员
admin-username=name

#管理员密码
admin-password=pwd
#lua编写的管理脚本
admin-lua-script=D:/MySQL/mysql-proxy-0.8.1-win32-x86/share/doc/mysql-proxy/admin.lua

2).修改rw-splitting.lua脚本

修改默认连接，进行快速测试，不修改的话要等达到连接数时才读写分离

        proxy.global.config.rwsplit = {
                min_idle_connections = 1,        #默认是4
                max_idle_connections = 2,        #默认是8

                is_debug = false
        }

3).添加启动的相关脚本

将D:\MySQL\mysql-proxy-0.8.1-win32-x86\lib\mysql-proxy路径下的lua文件夹整个复制到D:\MySQL\mysql-proxy-0.8.1-win32-x86\bin下

4).启动代理

windows命令行下启动
rem mysql-proxy --daemon --proxy-skip-profiling --admin-username=name --admin-password=pwd --proxy-lua-script=D:/MySQL/mysql-proxy-0.8.1-win32-x86/share/doc/mysql-proxy/rw-splitting.lua --admin-address=localhost:4041 --proxy-address=:4040 --proxy-backend-addresses=localhost:3309 --proxy-read-only-backend-addresses=localhost:3301 --proxy-read-only-backend-addresses=localhost:3302 --log-file=D:/MySQL/mysql-proxy-0.8.1-win32-x86/logs/mysql-proxy-0.8.0.log  --log-level=debug --admin-lua-script=D:/MySQL/mysql-proxy-0.8.1-win32-x86/share/doc/mysql-proxy/admin.lua

利用参数文件启动
mysql-proxy --defaults-file=D:\MySQL\mysql-proxy-0.8.1-win32-x86\mysql-proxy.ini

重启mysql服务器3309,3301,3302

登录主服务器3309，开启3个连接，确保开启读写分离

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 4040

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 4040

D:\MySQL\mysql-5.5.9\bin> mysql -u root -P 4040

登录从服务器3301

D:\MySQL\mysql-5.5.9\bin> mysql -u rep_slave_3301 -P 3301

启动主从复制

mysql> start slave;

在主服务器3309上的任一个连接下增加数据

mysql> insert into t11 values('');

mysql> select * from t11;

+---+
| i |
+---+
| 1 |

| 2 |

+---+

在从服务器3301下查询表t11

mysql> select * from t11;

+---+
| i |
+---+
| 1 |

| 2 |

+---+

从服务器3301关闭复制

mysql> stop slave;

在主服务器3309上的任一个连接下增加数据

mysql> insert into t11 values('');

在从服务器3301下查询表t11，原本自增加到3，查询只有2，说明读写分离成功。

mysql> select * from t11;

+---+
| i |
+---+
| 1 |

| 2 |

+---+

 

另记：

在登录代理管理器时的状态查询

D:\MySQL\mysql-5.5.9\bin>mysql -u name -p -P 4041
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show proxy processlist;
ERROR 1105 (07000): need a resultset + proxy.PROXY_SEND_RESULT ... got something
 else
mysql> select * from backends;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

ERROR 1105 (07000): error
mysql> select * from backends;
ERROR 1105 (07000): error
mysql> show proxy processlist;
ERROR 1105 (07000): need a resultset + proxy.PROXY_SEND_RESULT ... got something
 else

#SELECT * FROM backends时，SELECT * FROM 必须是大写，否则如上报错
mysql> SELECT * FROM backends;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 127.0.0.1:3309 | 1     | 1    |
|           2 | 127.0.0.1:3301 | 0     | 2    |
|           3 | 127.0.0.1:3302 | 0     | 2    |
+-------------+----------------+-------+------+
3 rows in set (0.23 sec)

#这句无论大小写总是查询错误，原因未知

mysql> SHOW PROXY PROCESSLIST\G;
ERROR 1105 (07000): need a resultset + proxy.PROXY_SEND_RESULT ... got something
 else
ERROR:
No query specified

 

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep_slave_3302'@'localhost' IDENTIFIED BY 'rep';

 

mysql> quit;

评论

3 楼 vieri124 2012-06-25  

是的，要安装lua，不好意思，忘记写了

2 楼 songjiesdnu 2012-05-21  

是不是需要安装lua的运行环境才行啊？

1 楼 songjiesdnu 2012-05-21  

4.读写分离-->4).启动代理
在这个时候出现下面的错误：
应用程序无法启动，因为应用程序的并行配置不正确。有关详细信息，请参阅应用程序事件
日志，或使用命令行 sxstrace.exe 工具。
请问楼主怎么解决？