一、背景知识
(一) Replication
MySQL的replication是异步的,适用于对数据实时性要求不是特别关键的场景。slave端的IO线程负责从master读取日志,SQL线程专门负责在slave端应用从master读过来的日志。 使用replication必须启用binary log,MySQL用binary log向slave分发更新。
(二) 复制级别
1. Row Level:5.1.5开始支持。mater记录每行数据的更改日志,slave根据日志逐行应用。优点:数据一致性更有保障。缺点:可能造成日志文件比较大;
2. Statement Level:master记录每个执行的query语句以及一些上下文信息,slave节点根据这些信息重新在slave上执行。优点:binary log比较小。缺点:某些情况下数据一致性难以保障;
3. Mixed Level:MySQL根据情况选择哪种复制方式。5.1.8开始支持。
(三) 常用架构
1. Master-Slaves:通常都采用这种方式;
2. Dual Master(Master-Master):2个master节点互相同步更新。因为MySQL的异步复制方式,为了防止数据冲突造成的不一致性,一般仅将其中一台用于写操作,另一台不用或仅用于读操作。目的是其中一台master停机维护或者故障中断时可以使用另一台master;
3. 级联复制(Master-Slaves-Slaves):在Master Slaves中,如果slaves过多replication将增加master的负载,这时可以让master只向其中几台slave分发更新日志,这几台slave作为一级节点再向下级节点分发更新日志。
4. 总结
3个线程:2个IO线程,1个sql线程;
2种日志:binlog,realylog;
1种实现:全程异步;
二、测试环境与内容
环境:Ubuntu 10.04.2 LTS + MySQL5.1.41-3ubuntu12.10-log
主服务器(Master):192.168.1.126
从服务器(Slave):192.168.1.145
由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样,因此最好的搭配组合是Master的 MySQL版本和Slave的版本相同或者更低,Master的版本肯定不能高于Slave版本。
搭建主从服务器需要完成下面两个步骤:
1) MySQL启用Binary Log
2) 配置主服务器(Master)
3) 配置从服务器(Slave)
4) 测试插入数据
三、搭建过程
(一) MySQL启用Binary Log
Binary Log的运作原理很简单,它只是单纯的将所有会修改到数据库内容的操作记录在 Log 文件中,然后通过这个Binary Log 你就可以重新执行所有会修改到数据库内容的操作。
在vim /etc/mysql/my.cnf的 [mysqld] 区块中加上 log-bin=mysql-bin 选项,然后重新启动 MySQL Server:service mysql restart
查看日志:show master logs;
(图:设置Binary Log之前)
(图:设置Binary Log之后)
启用后你应该可以在 MySQL 的 Data Dir 里面发现如下的文件:mysql-bin.00000X
(图:日志文件目录:/var/lib/mysql 日志文件:mysql-bin.000001)
(二) 配置主服务器(Master)
1. 编辑数据库配置文件my.cnf
一般在/etc/mysql/my.cnf,执行#vim /etc/mysql/my.cnf
在[mysqld]的下面加入下面代码:
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_do_db=weibo
binlog_ignore_db=mysql
说明:
1) log-bin=mysql-bin启用Binary Log;
2) server-id=1中的1可以任定义,只要是唯一的就行;
3) innodb_flush_log_at_trx_commit=1
4) sync_binlog=1
5) binlog_do_db=weibo是表示只备份weibo;(binlog-do-db)
6) binlog_ignore_db=mysql表示忽略备份mysql;
不加binlog_do_db和binlog_ignore_db,那就表示备份全部数据库。
2. 重启MySQL
#service mysql restart
3. 登录MySQL服务器
#mysql –u root -p
在主服务器新建一个用户赋予“REPLICATION SLAVE”的权限。你不需要再赋予其它的权限。
mysql>CREATE USER viajar@ '192.168.1.145' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE ON *.* TO viajar@ '192.168.1.145' IDENTIFIED BY '123456';
省略了几个步骤(可以补上)
4. 再次连接数据库进入mysql命令行查看master状态
mysql>SHOW MASTER logs;
mysql>SHOW MASTER STATUS;
记下File及Position的值,后面做从服务器操作的时候需要用.
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 583 | weibo | mysql |
+------------------+----------+--------------+------------------+
(三) 配置从服务器(Slave)
1. 编辑数据库配置文件my.cnf
在[mysqld]的下面加入下面代码:
server-id=2
可以自己定义,只要保证唯一的就行。
2. 重启MySQL
#service mysql restart
3. 登录MySQL服务器
#mysql –u root -p
执行以下命令:
mysql>CHANGE MASTER TO
MASTER_HOST='192.168.1.126',
MASTER_USER='viajar',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=583,
MASTER_CONNECT_RETRY=10;
说明:
MASTER_HOST:主服务器的IP;
MASTER_USER:配置主服务器时建立的用户名;
ASTER_PASSWORD:用户密码;
ASTER_PORT:主服务器mysql端口,如果未曾修改,默认即可。
或者是执行:
mysql> change master to master_host='192.168.1.126', master_user='viajar', master_password='123456', master_log_file='mysql-bin.000004', master_log_pos=106;
4. 启动Slave进程
mysql>START SLAVE;
5. 查看MySQL的日志
mysql的日志,一般在/var/log/目录下的mysql.log,如果启动成功,你应该会看到类似下面的日志:
6. 主从同步检查
mysql>show slave status\G;
其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES,才表明状态正常。\G的好处就是以下图的格式进行显示,\g是没有这个效果的。
(四) 测试插入数据
使用SQLyog链接到192.168.1.126(以下用126表示)与192.168.1.145(以下用145表示),在126上执行插入操作:
INSERT INTO `blog` (`TaskID`, `Content`, `Quote`, `Author`, `Time`, `Url`, `ImageUrl`, `Transmits`, `Comments`, `Hash`, `AddOn`)
VALUES('5','fefef','fefef','efef','2012-02-03 11:30:40',
'http://www.cnblogs.com/zgx/archive/2011/09/13/2174823.html',NULL,'0',
'0','33333333','2012-02-04 11:30:54');
(图:126)
(图:145)
当在145上执行:TRUNCATE TABLE blog;(SQLyog按F8执行)之后,在126上执行插入的时候,只有新的数据会传递到145上;
(图:126)
(图:145)
四、补充
查看帮助:mysql>help
查看slave帮助:mysql>help slave
查看slave状态:show slave status\G;
查找当前有哪些二进制日志文件:mysql> show binary logs;
删除mysql-bin.000018之前的所有二进制日志文件:mysql> purge binary logs to 'mysql-bin.000018';
从 MySQL 5.1.12 开始,能够用以下三种形式来告终:基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混杂形式复制(mixed-based replication, MBR)。相应地,binlog的款式也有三种:STATEMENT,ROW,MIXED。MBR 形式中,SBR 形式是默认的。
#binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"
当然了,也能够在运行时动态修正binlog的款式。例如
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
MySQL同步故障:Slave_SQL_Running:No
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
五、疑问
1. 如果导入导出数据库数据?
2. Master/Slave的深入知识是什么?需要注意什么设置选项(包括Linux和MySQL的选项)?性能如何评估?延时多少?Binary Log应该设置多大为合适?
3. [mysqld] log-bin = /var/lib/mysql/mysql-bin.* 这样设置有什么不一样?
4. binlog-do-db=weibo与binlog_do_db=weibo会有不同嘛?
5. 查看MySQL的日志好像里面的内容为空?
6. 尝试Master-Slave-Slave模式,性能有相差很大?
7. 另外你可能需要设置主服务器的数据库二进制日志的过期时间,可以在配置文件/etc/mysql/my.cnf中使用参数expire_logs_days来设定。mysql-bin.00000X(mysql的日志文件、二进制日志文件)的增长规律是什么?每天一个文件?重启一次生成一个新文件?
8. 如果slave报错或者宕机一段时间,那么应该如何重新同步宕机时间master的数据到slave呢?
9. 如何设置、查看复制的级别?Row Level、Statement Level、Mixed Level?跟Binary Log的关系,本身Innodb的log是不同的,Binary Log的大小要如何设置?
10. 设置了bin-log的起始位置,那么如果设置日志文件最大值,那分了日志后会如何呢?返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置;