mysql主从复制与读写分离
Linux(Ubuntu)环境MYSQL->Master/Slave主从同步设置以及注意事项
主库(192.168.120) Ubuntu 10.10 Server Mysql version: 5.1.49-1ubuntu8.1-log 备库(192.168.121) Ubuntu 10.10 Server Mysql version: 5.1.49-1ubuntu8.1-log
由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样，因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能高于Slave版本。 =======================主库(192.168.120)====================================== 1、 修改my.cnf
roger@ubuntu:~#vim /etc/my.cnf 禁掉 bind-address此项 [mysqld] server-id = 1
log-bin=mysql-bin
binlog_format=mixed (保持主从库的日志格式一致，如果不一致可能会不成功) 注：
a、下面两个选项可以制定生成binlog和忽略的库，如果要同步所有数据库则下面两行不要不用写
b、binlog-ignore-db = mysql 不需要同步的数据库 c、binlog-do-db = rogerdb 需要同步的库
d、binlog-ignore-db与binlog-do-db正好相反，如果你有100个库，只想同步其中几个，那么你应该使用binlog-do-db，如果不想同步其中的几个，就使用binlog-ignore-db
e、在实际测试中发现，如果通过上面两个命令指定了库，则master的Position 一直不会变化，google了一下有人说执行sql前要先use rogerdb才行-_-! 2、重启mysql服务 roger@ubuntu:~# /usr/local/mysql/share/mysql/mysql.server restart 3、建立同步用的数据库账户 mysql>GRANT REPLICATION SLAVE ON *.* TO slave@192.168.1.121 IDENTIFIED BY '1q2w3e'; 如果想要在Slave上有权限执行 “LOAD TABLE FROM MASTER” 或 “LOAD DATA FROM MASTER” 语句的话，必须授予全局的 FILE 和 SELECT 权限： mysql>GRANT RELOAD,SUPER,FILE,SELECT,REPLICATION SLAVE ON *.* TO backup@192.168.1.121 IDENTIFIED BY '1q2w3e'; 4、锁住主库表，停止数据更新 mysql> flush tables with read lock; 不要退出这个终端，否则这个锁就不生效了； 5、显示主库信息并记录 mysql> show master status; 记录前两框的数据
+—————————–+————-+———————–+—————————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +—————————–+————-+———————–+—————————+
| mysql-bin.000016 | 1676483 | | | +—————————–+————-+———————–+—————————+
6、备份需要同步库文件并复制到从库服务器上,如果要同步全部库，则打包/usr/local/msyql/var目录，如果只同步一个数据rogerdb,则只复制rogerdb roger@ubuntu:~#cd /use/local/mysql/var
roger@ubuntu:~#tar -zcvf mysql-master-rogerdb.tar.gz rogerdb //将rogerdb数据库打包
将文件
mysql-master-rogerdb.tar.gz
传到
slave
机
192.168.1.3:/usr/local/mysql/var/
=======================
从库
(192.168.121)======================================
1
、登陆从库，解压数据
roger@ubuntu:~# cd /usr/local/mysql/var
roger@ubuntu:~# tar -zxvf mysql-master-rogerdb.tar.gz
roger@ubuntu:~# chown -R mysql:mysql rogerdb
2
、修改
my.cnf
roger@ubuntu:~#vim /etc/my.cnf
禁掉
bind-address
此项
#
日志的名称
log-bin=mysql-bin
#
日志格式
binlog_format=mixed
#
从服务器
ID
server-id=2
#
主服务器的
IP
地址或者域名
master-host=192.168.1.120
#
主数据库的端口号
master-port=3306
#
同步数据库的用户
master-user=slave
#
同步数据库的密码
master-password=1q2w3e
#
如果从服务器发现主服务器断掉，重新连接的时间差
master-connect-retry=60
#
需要备份的库
# replicate-do-db=rogerdb
#
忽略的数据库
replicate-ignore-db=mysql
#replicate-ignore-db=test
3
、重启
mysql
服务
roger@ubuntu:~# /usr/local/mysql/share/mysql/mysql.server restart
4
、手动同步从库
mysql>slave
stop;
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.121',
mysql>CHANGE MASTER TO MASTER_USER='slave',
mysql>CHANGE MASTER TO MASTER_PASSWORD='1q2w3e',
mysql>CHANGE MASTER TO MASTER_PORT=3306,
mysql>CHANGE
MASTER
TO
MASTER_LOG_FILE='mysql-bin.000016',(
刚才在主库记录的数
据
)
mysql>CHANGE MASTER TO MASTER_LOG_POS=1676483,(
刚才在主库记录的数据
)
mysql>CHANGE MASTER TO MASTER_CONNECT_RETRY=60;
mysql>slave start;
如果此处CHANGE出错，可以执行 reset slave，来重新设置。 5、检查从库是否正常同步 mysql>show slave status \G;
当Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 “Yes”，表明 Slave 的 I/O 和 SQL 线程都在正常运行，如果出现错误，可以从Last_Error这个参数中看出哪里出错，然后进行排查
6、查看主库复制进度 mysql>show processlist \G; 7、 解锁主库表 mysql>unlock tables;
8、 分别用如下命令查看master和slave状态 master: mysql> show master status; slave: mysql> show slave status \G
注意事项：2边都关机重启后，发现SLAVE上报错ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log 解决的办法：在slave上执行 mysql>slave stop; mysql>reset slave; mysql> slave stop; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to master_host='192.168.9.120'; Query OK, 0 rows affected (0.04 sec) mysql> change master to master_user='slave'; Query OK, 0 rows affected (0.03 sec) mysql> change master to master_password='1q2w3e'; Query OK, 0 rows affected (0.01 sec) mysql> change master to master_log_file='mysql-bin.000004'; Query OK, 0 rows affected (0.02 sec) mysql> change master to master_log_pos=106; Query OK, 0 rows affected (0.02 sec) mysql> change master to master_connect_retry=60; Query OK, 0 rows affected (0.03 sec) mysql> slave start; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G; 发现正常了。
============================读写分离的实现================================
MYSQL读写分离
主服务器:192.168.1.120 从服务器:192.168.1.121
MySQL-Proxy调度服务器：192.168.1.120(在同一台机器上，不建议这么做，
最好分离)
以下操作，均是在192.168.1.120即MySQL-Proxy调度服务器上进行的
MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua
apt-get install lua5.1
6.安装配置MySQL-Proxy
apt-get install mysql-proxy mysql-client
7.配置并使用rw-splitting.lua读写分离脚本 脚本目录是 /usr/share/mysql-proxy 修改读写分离脚本rw-splitting.lua 修改默认连接，进行快速测试，不修改的话要达到连接数为4时才启用读写分离 vim /usr/share/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
8.修改完成后，启动mysql-proxy(服务管理脚本需要自己修改,哪个服务器读,那个服务器写,是在脚本里定义的) /etc/init.d/mysql-proxy start 附：可以使用的mysql-proxy脚本 #!/bin/bash
export LUA_PATH=/usr/local/share/mysql-proxy/?.lua mode=$1
if [ -z "$mode" ] then mode="start" fi
case $mode in
'start')
mysql-proxy --daemon \
--proxy-backend-addresses=192.168.9.120:3306 \
--proxy-read-only-backend-addresses=192.168.9.121:3306 \ --proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua ;; 'stop')
killall mysql-proxy ;; 'restart')
if $0 stop then $0 start else
echo "retart failed!!!" exit 1 fi ;; esac exit 0
9. 测试读写分离效果
为了清晰的看到读写分离的效果，需要暂时关闭MySQL主从复制功能
登陆从数据库服务器192.168.10.121，通过命令行登录管理MySQL服务器 mysql -u root -pylmf stop slave;
连接MySQL-Proxy
mysql -uroot -pylmf -P4040 -h192.168.1.120
登陆成功后，在rogerdb数据库mytable表中插入两条记录 use rogerdb;
insert into mytable values (“aaa”,'man); insert into mytable values (“bbb”,”woman'); quit
分别登陆到主从数据库服务器，对比记录信息,首先,检查主数据库服务器 登录到192.168.1.120主数据库服务器 use rogerdb;
select * from mytable;
然后，检查从数据库服务器
登录到192.168.1.121从数据库服务器 use rogerdb;
select * from mytable; 没有新记录存在 ！！记着 slave start
由此，我们已经实现了MySQL读写分离，目前所有的写操作都全部在Master主服务器上,另外，所有的读操作都分摊给了其它各个Slave从服务器上，用来分担数据库压力。
