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 installmysql
………………省略安装过程
②这里使用了XtraBackup来做MySQL Slave
由于XtraBackup已经出来2.1版本了,mysql版本在5.1的时候就已经不支持了,所以必须要用XtraBackup 2.0版本的有木有,害我有重新去官网下载了一个rpm安装
60#rpm -ivhpercona-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 indirectory '/opt/mysql/backup'
innobackupex-1.5.1: MySQL binlogposition:filename 'mysql-bin.000012', position 106
innobackupex-1.5.1: MySQLslave binlogposition: 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 withinnodb_fast_shutdown = 1
140310 16:49:16 InnoDB: Startingshutdown...
140310 16:49:16 InnoDB: Shutdowncompleted; log sequencenumber 1717772
140310 16:49:16 innobackupex-1.5.1:completed OK!
③拷贝备份文件到备库
因为是局域网就直接scp吧,省打字省时间
60#scp–r/opt/mysql/backup192.168.1.54:/opt/mysql
60#scp–r/opt/mysql/backup192.168.1.11:/opt/mysql
备库都是新安装的mysql,所以就算乱来也没所谓,在恢复主库过来的数据文件前,把备库的数据库数据文件直接rm掉吧(每次rm前我都是小心翼翼的。。。)
54#cd /var/lib/mysql(假如你的数据文件在这里,这个如果没有修改过也可以在my.cnf文件里面看到)
54#\rm–r *(这下清净了,啥都没有了,其实在这之前我备份了一份在/opt下面,嘻嘻,就算出问题我还是可以恢复)
54#rpm -ivhpercona-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 mysqlddaemon withdatabases from /var/lib/mysql
140310 17:24:28 mysqld_safe mysqld from pidfile/var/run/mysqld/mysqld.pid ended
艾玛,果然出问题了,看看/var/log/mysqld.log
54#vi /var/log/mysqld.log
………………………….
140310 17:24:28 InnoDB: Operatingsystem error number 13 in afile operation.
InnoDB: The error means mysqld does nothave the acce***ights to
InnoDB: the directory.
nnoDB: The error means mysqld does not havethe access rightstoInnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
………………………………………
明显是权限问题
54#chown–Rmysql: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#tarxvfmysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
11#mvmysql-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’@’%’
->identifiedby‘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 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
11#netstat–ntlp|grepmysql-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 –P4040
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,再拷贝到从服务器!