MySQL读写分离实验
服务器(系统centos 6.2)：
①192.168.1.60
②192.168.1.54
③192.168.1.11
数据库(MySQL 版本5.1.73):
主：192.168.1.60(以下简称60)
备：192.168.1.54(以下简称54)
proxy: 192.168.1.11(以下简称11)
方案：master-slave 读写分离
1．创建master-slave
①在60、54、11上面分别安装mysql
60#yum –y install mysql
………………省略安装过程
②这里使用了XtraBackup来做MySQL Slave
由于XtraBackup已经出来2.1版本了，mysql版本在5.1的时候就已经不支持了，所以必须要用XtraBackup 2.0版本的有木有，害我有重新去官网下载了一个rpm安装
60#rpm -ivh percona-xtrabackup-20-2.0.8-587.rhel6.x86_64.rpm
由于XtraBackup在全备份的时候必须要指定‘datadir’(数据库数据文件的保存路径)，然而mysql在/etc/my.cnf默认没有写这个parameter，那就去加上吧：datadir = /var/mysql/data，然后开始备份.
60#innobackupex-1.5.1 --user=root--password=fengyunsen --port=3306 --defaults-file=/etc/my.cnf --slave-info--no-timestamp /opt/mysql/backup
…………………………省略安装过程
innobackupex-1.5.1: Backupcreated in directory ‘/opt/mysql/backup‘
innobackupex-1.5.1: MySQL binlogposition: filename ‘mysql-bin.000012‘, position 106
innobackupex-1.5.1: MySQLslave binlog position: master host ‘‘, filename ‘‘, position
140310 16:38:08 innobackupex-1.5.1: completed OK!
注:--slave-info表示备份完成会产生对应主库的BinLog用于同步保证数据一致性,此参数会在一段时间内全局共享锁不可写,但是比停库影响小
60#cd /opt/mysql/backup
60#ls
查看那一下是否有东西。Ok，备份的数据都有了。
为了确保数据的一致性，最好还是为一些未提交的事务做恢复
60#innobackupex-1.5.1 --apply-log /opt/mysql/backup
………………………………………………………省略N个字
xtrabackup: startingshutdown with innodb_fast_shutdown = 1
140310 16:49:16 InnoDB: Starting shutdown...
140310 16:49:16 InnoDB: Shutdown completed; log sequencenumber 1717772
140310 16:49:16 innobackupex-1.5.1: completed OK!
③拷贝备份文件到备库
因为是局域网就直接scp吧，省打字省时间
60#scp –r/opt/mysql/backup 192.168.1.54:/opt/mysql
60#scp –r/opt/mysql/backup 192.168.1.11:/opt/mysql
备库都是新安装的mysql，所以就算乱来也没所谓，在恢复主库过来的数据文件前，把备库的数据库数据文件直接rm掉吧(每次rm前我都是小心翼翼的。。。)
54#cd /var/lib/mysql(假如你的数据文件在这里，这个如果没有修改过也可以在my.cnf文件里面看到)
54#\rm –r *(这下清净了，啥都没有了，其实在这之前我备份了一份在/opt下面，嘻嘻，就算出问题我还是可以恢复)
54#rpm -ivh percona-xtrabackup-20-2.0.8-587.rhel6.x86_64.rpm
54#innobackupex-1.5.1 –copy-back /opt/mysql/backup
…………………………..省略N个字
140310 17:10:02 innobackupex-1.5.1: completed OK!
54#ls /var/lib/mysql
看看是否已经有文件，确实恢复过来了！
54#mysql_safe &(为了获得日志还是选择手动启动吧)
140310 17:24:28 mysqld_safe Logging to ‘/var/log/mysqld.log‘.
140310 17:24:28 mysqld_safe Starting mysqld daemon withdatabases from /var/lib/mysql
140310 17:24:28 mysqld_safe mysqld from pid file/var/run/mysqld/mysqld.pid ended
艾玛，果然出问题了，看看/var/log/mysqld.log
54#vi /var/log/mysqld.log
………………………….
140310 17:24:28 InnoDB: Operating system error number 13 in afile operation.
InnoDB: The error means mysqld does not have the accessrights to
InnoDB: the directory.
nnoDB: The error means mysqld does not have the access rightstoInnoDB: File name ./ibdata1
InnoDB: File operation call: ‘open‘.
InnoDB: Cannot continue operation.
………………………………………
明显是权限问题
54#chown –R mysql:mysql /var/lib/mysql
54#mysqld_safe &
54#cat /var/lib/mysql/xtrabackup_binlog_pos_innodb
./mysql-bin.000003 14863
查看一下同步状态，正常的
54#mysql –u root –p
Enter password:
ERROR 1045 (28000): Access denied foruser ‘root‘@‘localhost‘ (using password: YES)
悲剧了，发现输了密码也不对，进不去了！唯有修改root密码了，停掉mysql，修改my.cnf，在[mysqld]下面添加skip-grant-table,重启数据库，直接敲mysql进去update root密码，搞定！
60# vi /etc/my.cnf(修改主数据库的配置文件)
[mysqld]
……………….
Log-bin=mysql-bin
Server-id = 1
Binlog-do-db =hves #要备份的数据库
Binglog-ignore-db =mysql #不需要备份的数据库
Binglog-ignore-db = test #不需要备份的数据库
60#service mysqld restart
60#mysql –u root –p
60mysql>grant all privileges on hves.* to ‘deploy’@’%’
Identified by ‘123456’;
60mysql>grant replication save on *.* to ‘deploy’@’%’
Identified by ‘123456’;
(这里最后设置一个可以访问hves的用户,并赋予replication save权限，免得后面同步的时候出问题)
60mysql>show master status;
+------------------+----------+--------------+------------------+
| File |Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 | 106 | hves | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
变得很彻底，备库看来要按这个来配置了
54#vi /etc/my.cnf
[mysqld]
……………………..
Log-bin=mysql-bin
Server-id = 2
Binlog-do-db= hves
Binglog-ignore-db = mysql
Binglog-ignore-db = test
Master-port = 3306
54#mysql –u root –p
54mysql>change masterto
Master_host=’192.168.1.60’,
Master_user=’deploy’,
Master_password=’123456’,
Master_log_file=’ mysql-bin.000011’,
Master_log_pos =106;
54mysql>start slave;
54mysql>show slavestatus\G
2.mysql-proxy读写分离
先去mysql官网下载mysql-proxy，并上传到11服务器上面
11#tar xvfmysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
11#mv mysql-proxy-0.8.4-linux-el6-x86-64bit/usr/local/mysql-proxy
11#vi /etc/profile
PATH=$PATH:/usr/local/mysql-proxy/bin/
Export PATH
添加环境变量
11#mysql-proxy –help(可以看一下具体有哪些命令选项)
这里列举一些比较有用的选项：
--help-all ：获取全部帮助信息；
--proxy-address=host:port ：代理服务监听的地址和端口；
--admin-address=host:port ：管理模块监听的地址和端口；
--proxy-backend-addresses=host:port ：后端mysql服务器的地址和端口；
--proxy-read-only-backend-addresses=host:port ：后端只读mysql服务器的地址和端口；
--proxy-lua-script=file_name ：完成mysql代理功能的Lua脚本；
--daemon ：以守护进程模式启动mysql-proxy；
--keepalive ：在mysql-proxy崩溃时尝试重启之；
--log-file=/path/to/log_file_name ：日志文件名称；
--log-level=level ：日志级别；
--log-use-syslog ：基于syslog记录日志；
--plugins=plugin：在mysql-proxy启动时加载的插件；
--user=user_name ：运行mysql-proxy进程的用户；
--defaults-file=/path/to/conf_file_name：默认使用的配置文件路径；其配置段使用[mysql-proxy]标识；
--proxy-skip-profiling ：禁用profile；
--pid-file=/path/to/pid_file_name ：进程文件名；
在master和slave上面创建mysql-proxy连接用户，由于之前我们主从是忽略了mysql database的，所以要master，slave都要创建一下。
60#mysql –u root –p
60 mysql>grant all privileges onhves.* to ‘proxy’@’%’
->identified by ‘123456’;
由于是测试所以就简单点也不考虑安全性了，同样在54上面也一样的操作！
开启mysql-proxy进程：
11#mysql-proxy --daemon--log-level=debug --user=root --keepalive --log-file=/var/log/mysqld-porxy.log--plugins=‘proxy‘ --proxy-backend-addresses="192.168.1.60:3306"--proxy-read-only-backend-addresses="192.168.1.54:3306"--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin--admin-username="admin" --admin-password="admin"--admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"
11#ps –ef|grep mysql-proxy
root 5509 1 008:47 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy--daemon --log-level=debug --user=root --keepalive--log-file=/var/log/mysqld-porxy.log --plugins=proxy--proxy-backend-addresses=192.168.1.60:3306--proxy-read-only-backend-addresses=192.168.1.54:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua--plugins=admin --admin-username=admin --admin-password=admin--admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
root 5510 5509 0 08:47 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy--daemon --log-level=debug --user=root --keepalive--log-file=/var/log/mysqld-porxy.log --plugins=proxy--proxy-backend-addresses=192.168.1.60:3306--proxy-read-only-backend-addresses=192.168.1.54:3306--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua--plugins=admin --admin-username=admin --admin-password=admin--admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
11#netstat –ntlp|grep mysql-proxy
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 5510/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 5510/mysql-proxy
(4040端口是连接端口，4041是管理端口)
随便找master或者slave模拟连接proxy
60#mysql –u proxy –p –h 192.168.1.11 –P4040
60mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hves |
| test |
+--------------------+
或者进入管理端口
60# mysql –u admin–p –h 192.168.1.11 –P4041
60mysql>select * from backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid |connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 | 192.168.1.60:3306 | up | rw | NULL | 0 |
| 2 | 192.168.1.54:3306 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
这里其实我开了4个以上的连接终端，我有尝试过在脚本上面修改了，但是不管用：
11#vi /usr/local/mysql-proxy/share/doc/ mysql-proxy/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
测试：
事实上，在连接proxy的admin管理端口的是就已经看出来了：
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid |connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 | 192.168.1.60:3306 | up | rw | NULL | 0 |
| 2 | 192.168.1.54:3306 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
考虑要更明显看出来，因为同步问题，期间要把slave停掉
54mysql>stop slave;
60#use hves;
60#insert into tests(name) values (“fengyunsen”);
60#flush privileges;
连接proxy
60#mysql -u proxy –p –h 192.168.1.11 –P 4040
60mysql>use hves;
60mysql>select * from tests;
你会发现没有没有记录
+----+---------+
| id | name |
+----+---------+
| 2 | samfeng |
| 3 | ssss |
+----+---------+
为什么？因为你读取的是54服务器，54的slave停掉了，60的操作没有同步到54上面，所以就没select到记录，这就是证明了proxy部署成功了！
不信可以退回到60服务器：
60#mysql –u root –p
60mysql>use hves;
60mysql>select * from tests;
+----+------------+
| id | name |
+----+------------+
| 1 | fengyunsen |
| 2 | samfeng |
| 3 | ssss |
+----+------------+
3 rows in set (0.00 sec)
可能细心的人会发现，怎么master跟slave的数据不一致，这就是我一开始在主从没有配置好，而且又隔了一天的时间再搞，结果master有更新了就出现这样的情况，如果想同步到最新，最好的方法就是再备份master最新的database，再拷贝到从服务器！
本文出自 “fengyunsen” 博客，请务必保留此出处http://samfeng.blog.51cto.com/52272/1378171