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、修改https://www.360docs.net/doc/be18339764.html,f

r oger@ubuntu:~#vim /etc/https://www.360docs.net/doc/be18339764.html,f

禁掉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、修改https://www.360docs.net/doc/be18339764.html,f

roger@ubuntu:~#vim /etc/https://www.360docs.net/doc/be18339764.html,f

禁掉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

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从服务器上，用来分担数据库压力。