mysql主从、主主复制详解
Mysql 主从、主主复制详解
一、复制的介绍
MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器
充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。
这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从
服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后
封锁并等待主服务器通知新的更新。
请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，
以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
单向复制有利于健壮性、速度和系统管理：
l 主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为
份。
l 通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。
SELECT查询可以发送到从服务器以降低主服务器的查询处理负荷。但修改数据的语句仍然
应发送到主服务器，以便主服务器和从服务器保持同步。如果非更新查询为主，该负载均
衡策略很有效，但一般是更新查询。
l 使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份
过程中主服务器可以继续处理更新。
MySQL 提供了数据库的同步功能，这对我们实现数据库的冗灾、备份、恢复、负载均衡等都是
有极大帮助的
MySQL 使用3 个线程来执行复制功能(其中1 个在主服务器上，另两个在从服务器上。当发出
START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让主服务器发送二进制日志。
主服务器创建一个线程将二进制日志中的内容发送到从服务器。从服务器I/O 线程读取主服务
器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继
日志。第3个线程是SQL 线程，从服务器使用此线程读取中继日志并执行日志中包含的更新。
SHOW PROCESSLIST语句可以查询在主服务器上和从服务器上发生的关于复制的信息。
默认中继日志使用host_name-relay-bin.nnnnnn 形式的文件名，其中host_name 是从服务
器主机名，nnnnnn是序列号。用连续序列号来创建连续中继日志文件，从000001开始。从服
务器跟踪中继日志索引文件来识别目前正使用的中继日志。默认中继日志索引文件名为
host_name-relay-bin.index。在默认情况，这些文件在从服务器的数据目录中被创建。中继日
志与二进制日志的格式相同，并且可以用mysqlbinlog读取。当SQL 线程执行完中继日志中的
所有事件后，中继日志将会被自动删除。
从服务器在数据目录中另外创建两个状态文件–master.info 和relay-log.info。状态文件保存
在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服
务器读取了多少二进制日志，以及处理自己的中继日志的程度。
二、实验环境
操作系统：Centos 5.5 64bit
版本： 5.1.49 （参考“Centos 5使用yum安装Mysql”文档）
A: master 计算机名：beijing IP地址：192.168.20.101
B: slave 计算机名：shanghai IP地址：192.168.20.102
三、mysql的单向复制
注意 mysql 数据库的版本，两个数据库版本要相同，或者slave比master版本低！
1、在主服务器上为复制设置一个连接账户。该账户必须授予REPLICATION SLAVE权限。如果
账户仅用于复制(推荐这样做)，则不需要再授予任何其它权限。
# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO
BY ’123456′;
2、在主服务器上建立测试数据库test1
mysql> create database test1;
mysql> use test1;
mysql> create table user(id int(4),name varchar(20));
mysql> insert into user values(1,”mary”);
mysql> insert into user values(2,”joe”);
// 刷新权限，使设置生效
mysql>Flush privileges;
3、配置主服务器的my.cof
// mysql客户端程序不要退出，在/etc/my.cnf配置文件中添加以下内容
log-bin=mysql-bin # 启动二进制日志系统
server-id=1 # 本机数据库ID 标示为主服务器
log-bin=/var/log/mysql/updatelog # 设定生成log文件名，这里的路径没有mysql
目录要手动创建并给于它mysql用户的权限。
binlog-do-db=test1 # 二进制需要同步的数据库名
binlog-ignore-db=mysql,test # 避免同步mysql用户配置，以免不必要的麻烦
// 创建更新日志的目录并给mysql用户的权限
# mkdir /var/log/mysql
# chown -R mysql.mysql /var/log/mysql
4、执行FLUSH TABLES WITH READ LOCK语句清空所有表和块写入语句, 并将本地需要同步
数据库打包拷贝到从数据库上
mysql> FLUSH TABLES WITH READ LOCK;
// 在另一个终端对主服务器数据目录做备份
# cd /var/lib/mysql/
# tar -cvf /tmp/mysqldb.tar test1/
// 通过远程拷贝到从服务器上，通过这个拷贝的时候需要输入从服务器root密码
# scp /tmp/mysqldb.tar
// 对主服务器进行解锁
mysql> UNLOCK TABLES
// 重启mysql服务
# /etc/init.d/mysqld restart
5、配置从服务器
// 配置slave服务器/etc/my.cnf 文件，添加以下内容：
server-id=2 # 从服务器ID号，不要和主ID相同
master-host=192.168.20.155 # 指定主服务器IP地址
master-user=replication # 指定在主服务器上可以进行同步的用户名
master-password=123456 # 密码
master-port=3306 # 同步所用端口
master-connect-retry=60 # 断点从新连接时间
replicate-ignore-db=mysql # 屏蔽对mysql库的同步
replicate-do-db=test1 # 同步的数据库的名称
6、从服务器上装在主服务器数据库
# cd /var/lib/mysql/
# tar xvf mysqldb.tar
# rm mysqldb.tar
# /etc/init.d/mysqld restart
// 启动从服务器线程：
# mysql -uroot -p123456
mysql> START SLAVE;
7、验证配置
// 主服务器：
mysql > SHOW MASTER STATUS;
+——————+———-+—————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————+——————+
| mysql-bin.000004 | 106 | test1,netseek | mysql,test |
+——————+———-+—————+——————+
//（同步之前如果怀疑主从数据不同步可以采取：上面冷备份远程拷贝法或者在从服务器上命行
同步方法）在从服务器执行MySQL 命令下：
mysql> SLAVE STOP; #先停止slave服务
mysql> CHANGE MASTER TO MASTER_LOG_FILE=’updatelog.000004′,MASTER_LOG_
POS=106;
// 根据上面主服务器的show master status的结果，进行从服务器的二进制数据库记录回归，
达到同步的效果
mysql> SLAVE START; #启动从服务器同步服务
// 用show slave statusG;看一下从服务器的同步情况
mysql> SHOW SLAVE STATUSG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes，那代表已经在同步.
8、测试
// 在主服务器上建立一个表
mysql> use test1;
mysql> create table name(id int(4),name varchar(20));
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| user |
+—————–+
2 rows in set (0.01 sec)
// 在从服务器上查询
mysql> use test1;
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| user |
+—————–+
2 rows in set (0.00 sec)
单项复制试验成功！！！！
四、双向同步配置
1、修改原slave 服务器配置
192.168.20.102
// 配置原slave服务器/etc/my.cnf文件,添加红字的内容：
server-id=2 # 从服务器ID号，不要和主ID相同
master-host=192.168.20.155 # 指定主服务器IP地址
master-user=replication # 指定在主服务器上可以进行同步的用户名
master-password=123456 # 密码
master-port=3306 # 同步所用端口
master-connect-retry=60 # 断点从新连接时间
replicate-ignore-db=mysql # 屏蔽对mysql库的同步
replicate-do-db=test1 # 同步的数据库的名称
log-bin=/var/log/mysql/updatelog # 设定生成log文件名
binlog-do-db=test1 # 设置同步数据库名
binlog-ignore-db=mysql # 避免同步mysql用户配置，以免不必要的麻烦
2、创建更新日志的目录并给mysql用户的权限
# mkdir /var/log/mysql
# chown -R mysql.mysql /var/log/mysql
3、重新启动mysql服务，创建一个同步专用账号
# service mysqld restart
//给与从服务器用户replication的同步权限
# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO
BY ’123456′;
//刷新权限，使设置生效
mysql>Flush privileges;
4、修改原master配置文件
192.168.20.101
// 配置原master务器/etc/my.cnf文件,添加红字的内容：
log-bin=mysql-bin # 启动二进制日志系统
server-id=1 # 本机数据库ID 标示为主
log-bin=/var/log/mysql/updatelog # 设定生成log文件名，这里的路径没有mysql
目录要手动创建并给于它mysql用户的权限。
binlog-do-db=test1 # 二进制需要同步的数据库名
binlog-ignore-db=mysql,test # 避免同步mysql用户配置，以免不必要的麻烦
master-host=192.168.20.128 # 设置从原slave数据库同步更新
master-user=replication # 更新用户
master-password=123456 # 密码
master-port=3306 # 端口
replicate-do-db=test1 # 需要更新的库
// 重启mysql服务
# service mysqld restart
// 在B服务器查询
192.168.20.102
# mysql -uroot -p123456
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| updatelog.000001 | 106 | test1 | mysql |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
// 在A服务器查询
192.168.20.101
# mysql -uroot -p123456
mysql> SHOW MASTER STATUS;
// 先停止slave服务
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.20.128′,MASTER_USER=’repli
cation’,MASTER_PASSWORD=’123456′,MASTER_PORT=3306,MASTER_LOG_FILE=’upda
telog.000001′,MASTER_LOG_POS=106;
// 根据上面主服务器的show master status的结果，进行从服务器的二进制数据库记录回归，
达到同步的效果
// 启动B服务器同步服务
192.168.20.102
mysql> SLAVE START;
5、验证配置
// 在A服务器上进入mysql命令行
192.168.20.101
mysql> SHOW SLAVE STATUSG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此处Slave_IO_Running ,Slave_SQL_Running 都应该是yes,表示从库的I/O,Slave_SQL线程都
正确开启.表明数据库正在同步。
// 在B服务器上进入mysql命令行
192.168.20.102
mysql> SHOW SLAVE STATUSG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此处Slave_IO_Running ,Slave_SQL_Running 都应该是yes,表示从库的I/O,Slave_SQL线程都
正确开启.表明数据库正在同步。
6、测试
// 在A服务器上建立一个表
192.168.20.101
mysql> use test1;
mysql> create table test1(id int(4),name varchar(20));
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| test1 |
| user |
+—————–+
3 rows in set (0.00 sec)
// 在B服务器上查询
192.168.20.102
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| test1 |
| user |
+—————–+
3 rows in set (0.00 sec)
// 在B服务器上建立一个表
192.168.20.102
mysql> create table test2(id int(4),name varchar(20));
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| name |
| test1 |
| test2 |
| user |
+—————–+
4 rows in set (0.00 sec)| user |
+—————–+
3 rows in set (0.00 sec)
// 在A服务器上查询
192.168.20.101
mysql> show tables;
+—————–+
| Tables_in_test1 |
| name |
| test1 |
| test2 |
| user |
+—————–+
4 rows in set (0.00 sec)
双向复制试验成功！！！
mysql5.5.9半同步复制功能部署
1.mysql5.5.9 半同步复制功能：
mysql5.5 版本支持半同步复制功能（Semisynchronous Replication），但
还不是原生的支持，是通过plugin来支持的，并且默认是没有安装这个插件的。
不论是二进制发布的，还是自己源代码编译的，都会默认生成这个插件，
一个是针对master 的一个是针对slave的，在使用之前需要先安装这俩plugins
首先先检查 mysql是否支持动态添加插件，
> select @@have_dynamic_loading ;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)
支持动态增减插件，
添加插件：
)> install plugin rpl_semi_sync_master soname
semisync_master.so ;
Query OK, 0 rows affected (0.00 sec)
)> install plugin rpl_semi_sync_slave soname
semisync_slave.so;
Query OK, 0 rows affected (0.00 sec)
添加完插件后，系统会默认的增加了几个系统参数
)> show global variables like rpl_semi_sync%;
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+------------------------------------+-------+
6 rows in set (0.00 sec)
这些参数是可以动态修改的
rpl_semi_sync_master_enabled ：
启动master 支持半同步复制。
rpl_semi_sync_master_timeout ：
主库等待半同步复制信息返回的超时间隔，默认10秒
rpl_semi_sync_master_trace_level ：
监控等级：
1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit)
rpl_semi_sync_master_wait_no_slave ：
是否允许master 每个事物提交后都要等待slave 的receipt信号。
默认为on ，每一个事务都会等待，如果slave当掉后，当slave追赶上master的日志时
，可以自动的切换为半同步方式，如果为off,则slave追赶上后，也不会彩玉半同步的
方式复制了，需要手工发动。
rpl_semi_sync_slave_enabled ：
启动slave 支持半同步复制。
rpl_semi_sync_slave_trace_level ：
监控等级，同 上面的rpl_semi_sync_master_trace_leve。
相应的系统的状态变量：
> show global status like rpl_semi_sync%;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 902 |
| Rpl_semi_sync_master_net_wait_time | 902 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 501 |
| Rpl_semi_sync_master_tx_wait_time | 501 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)
Rpl_semi_sync_master_clients ：
记录支持半同步的slave的个数。
Rpl_semi_sync_master_net_avg_wait_time ：
master 等待slave 回复的平均等待时间。 单位毫秒.
| Rpl_semi_sync_master_net_wait_time ：
master 总的等待时间。
Rpl_semi_sync_master_net_waits ：
master 等待slave 回复的的总的等待次数。
Rpl_semi_sync_master_no_times ：
master 关闭半同步复制的次数。
Rpl_semi_sync_master_no_tx ：
master 没有收到slave的回复而提交的次数，（应该可以理解为master 等待超时的次
数)
Rpl_semi_sync_master_status :
标记master现在是否是半同步复制状态。
Rpl_semi_sync_master_timefunc_failures ：
The number of times the master failed when calling time functions such as
gettimeofday().
Rpl_semi_sync_master_tx_avg_wait_time ：
master 花在每个事务上的平均等待时间。
Rpl_semi_sync_master_tx_wait_time ：
master 总的等待次数。
Rpl_semi_sync_master_wait_pos_backtraverse：
我理解的意思就是后来的先到了，而先来的还没有到的次数。
The total number of times the master waited for an event with binary
coordinates lower than events waited for previously. This can occur when
the order in which transactions start waiting for a reply is different from
the order in which their binary log events are written.
Rpl_semi_sync_master_wait_sessions：
当前有多少个session 因为slave 的回复而造成等待。
Rpl_semi_sync_master_yes_tx ：
master 成功接收到slave的回复的次数。
Rpl_semi_sync_slave_status :
标记slave 是否在半同步状态。
========================================================
配置半同步的步骤就很简单了：
1.先按照我们一般的配置异步复制的方式 建立好复制。
2. 启动异步方式复制。
3.当slave追赶上master的状态时，停止slave ：
4. 修改主从库的半同步的参数：
主库执行：
set global rpl_semi_master_enabled=1；
set global rpl_semi_sync_master_timeout=1000；
从库执行 ；
set global rpl_semi_slave_enabled=1；
5. 从库启动slave：
start slave；
6.查看参数，根据复制的状态调整global rpl_semi_sync_master_timeout的值。