mysql系列之6.mysql主从同步

普通文件的数据同步

nfs: 网络文件共享

samba: 共享数据

定时任务或守护进程结合 rsync.scp

inotify(sersync)+rsync 触发式实时数据同步

ftp数据同步

ssh key + scp / rsync

svn版本管理

rsync,sersync,inotify,union(双向同步),csync2(多向同步)

 

mysql主从同步逻辑图

单向主从同步

主-从

一主多从

 

双向主主同步: 互为主从 (一般不采用)

 

线性: 级联单向双主同步

 

环状: 级联单向多主同步

 

主从同步的意义, 解决的问题

1.互为备份: 高可用性和容错性

2.读写分离: 负载平衡

3.根据服务器拆分业务: 数据分布

 

如何实现读写分离

1.通过php程序: 建立多个连接文件, 轻松实现

2.通过软件实现: mysql-proxy   amoeba 等代理软件

3.开发dbproxy

 

mysql主从复制原理

第一步就是, master服务器端记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

1.slave服务器上执行 start slave, 开启主从复制开关

2.此时, slave服务器的IO线程会通过在master上授权的复制用户权限请求连接master服务器, 并请求从指定binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行change master命令时指定的)之后发送binlog日志内容

3.master服务器接收到来自slave服务器的IO线程的请求后, master服务器上负责复制的IO线程根据slave服务器的IO线程请求的信息读取指定binlog日志文件指定位置之后的binlog日志信息,然后返回给slave端的IO线程. 返回的信息中除了binlog日志内容, 还有本次返回日志内容后在master服务器端的新的binlog文件名称以及在binlog中的下一个指定更新位置

4.当slave服务器的IO线程获取到来自master服务器上IO线程发送日志内容及日志文件及位置点后, 将binlog日志内容依次写入到slave端自身的relay log(中继日志: mysql-relay.xxxxxx)文件的最末端, 并将新的binlog文件名和位置记录到master-info文件中, 以便下一次读取master端新binlog日志时能够告诉master服务器需要从新binlog日志的哪个文件哪个位置开始请求新的binlog日志内容

5.slave服务器端的sql线程会实时的检测本地relay log中新增加的日志内容, 然后及时的把log文件中的内容解析成在master端曾经执行的sql语句的内容, 并在自身slave服务器上按语句的顺序执行应用这些sql语句, 应用完毕后清理应用过的日志

6.经过了上面的国产, 就可以确保在master端和slave端执行了同样的sql语句. 当复制状态正常的情况下, master和slave端的数据是一样的, mysql的同步机制是有一些特殊的情况的, 但是大多数情况下大家不用担心.

 

整体上来说，复制有3个步骤：  

(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

(2) slave将master的binary log events拷贝到它的中继日志(relay log)；

(3) slave重做中继日志中的事件，将改变反映它自己的数据。

 

主从复制实现步骤

1.数据库环境

单机单数据库多实例

主库:3306, 从库3307

 

2.主库: 修改my.cnf

[mysqld]

log-bin=mysql-bin

server-id = 1

expire_logs_days=7  //binlog日志保留的天数, 可不加此配置

重启服务器

#service iptables stop;  //主服务器一定要关闭防火墙

#mysqladmin -uroot -S /data/3306/mysql.sock shutdown

#mysqld_safe --defaults-file=/data/3306/my.cnf

检查log_bin

#mysql -uroot -S /data/3306/mysql.sock -e "show variables like 'log_bin';"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

 

3.从库: 修改my.cnf

server-id = 2

注意: 如果从库还想级联从库, 必须打开这两个参数------

log-bin

log-slave-updates

 

4.在master上建立授权账号

mysql> grant replication slave on *.* to "rep"@"192.168.1.%" identified by "123456";

mysql> flush privileges;

 

5.在master上导出当前数据

mysql> flush table with read lock;

然后重新开一个mysql命令窗口

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000004 |      333 |              |                  |

+------------------+----------+--------------+------------------+

mysql> show master logs;

#mysqldump -uroot -S /data/3306/mysql.sock -A -B --events --master-data=2 > /all.sql

#vim /all.sql

能看到

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=333;

mysql> show master status; //看看锁表期间数据是否有更改

mysql> unlock tables;

 

6.把备份数据拷贝到slave

#mysql -uroot -S /data/3307/mysql.sock < /all.sql

 

7.slave端开始连接master (/data/3307/data/目录下能看到两个文件,说明连接成功: master.info  relay-log.info)

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.1.116',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=443;

 

8.slave端打开复制开关: 看到两个 Yes 就说明成功!

mysql> start slave;

mysql> show slave status\G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

9.测试主从复制是否正常工作

master端:

mysql> create database ab;

Query OK, 1 row affected (0.00 sec)

slave端:

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ab                 |

| mysql              |

| performance_schema |

| test               |

+--------------------+

 

最简化的建议

1.半夜的时候, 用mysqldump带--master-data=1参数实现全备, 把.sql恢复到从库

#mysqldump -uroot -S /data/3306/mysql.sock -A -B --events --master-data=1 > /all.sql

#mysql -uroot -S /data/3307/mysql.sock < /all.sql

2.然后在从库执行change master to ...  无需binlog文件及位置点

(省略下面这两行)

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=443;

 

主库当机, 如何快速恢复数据库服务

核心思想: 把其中一个从库升级为主库

主库:   192.168.1.25

从库1:  192.168.1.30

从库2:  192.168.1.35

 

1.在每个从库上执行

停止从库的io线程

mysql> stop slave io_thread;

mysql> show processlist;

 

2.登录从库1

停止同步, 重置为主,建立复制用户,查看status

# mysql -uroot

mysql> stop slave;

mysql> reset master;

mysql> grant replication slave on *.* to "rep"@"192.168.1.%" identified by "123456";

mysql> flush privileges;

mysql> show master status;

 

3.进到从库1的数据库目录

删除从库日志

# cd /data/3307/data

# rm -f master.info  relay-log.info

 

4.把从库1提升为主库: 修改my.cnf

去掉从库的参数配置

log-bin

##log-slave-updates

##read-only

 

5.在从库2,3,4..上操作:

在每个从库上重新指定主库

mysql> stop slave;

mysql> change master to

master_host='192.168.1.30',

master_port=3306,

master_user='rep',

master_password='123456',

master_log_file='mysql-bin.000001',

master_log_pos=107;

mysql> start slave;

mysql> show slave status\G;

 

双主或多主数据库架构

核心思想是: 互为主从, 避免id重复

主1: 192.168.1.25

主2: 192.168.1.30

 

1.my.cnf (记得重启mysqld服务)

修改主1的 my.cnf

[mysqld]

auto_increment_increment = 2

auto_increment_offset    = 1  //数据表id从1开始, 递增2

log-slave-updates  //多主架构是互为主从的, 所以必须开启此项配置

log-bin

修改主2的 my.cnf

[mysqld]

auto_increment_increment = 2

auto_increment_offset    = 2

log-slave-updates

log-bin

2.手动同步主1和主2当前的数据

3.把主1作为从服务器, 连接到主2   (主2的操作同此)

mysql> stop slave;

mysql> reset master;

mysql> show master status;

mysql> grant replication slave on *.* to "rep"@"192.168.1.%" identified by "123456";

mysql> change master to

master_host='192.168.1.30',  //主2的服务器ip地址

master_port=3306,

master_user='rep',  //主2的复制账户和密码

master_password='123456', 

master_log_file='mysql-bin.000001',  //主2的file和pos值

master_log_pos=107;

mysql> start slave;

mysql> show slave status\G;

4.测试

在主1上新建一个数据库和表, 并插入几条数据, 自增id是1  3  5

然后去主2上看看数据是否已同步过来

接着在主2执行相同操作, 自增id应该是2  4  6