Ubuntu下MySQL搭建Master Slave

一、背景知识

(一) 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的位置；

 

六、参考文献

Linux MySQL主从复制(Replication)配置（大体上正确，目录有问题）

mysql 主从复制读写分离实现（正确的change master）

mysql master/slave 使用感受

mysql 动态生成测试数据

[MySQL管理] mysql主从复制，读写分离 （MySQL Proxy）

MySQL 使用 binary log 回复某段时间区间的资料（启用Binary Log）

MySQL数据库灾难恢复（启用Binary Log）

Binary Logging of Stored Programs

搭建MySQL的Master/Slave架构（Windows）

MySQL复制级别

MySQL 复制原理与实际操作过程

复制参数binlogformat（日志级别）

mysql log-bin 日志文件（查看日志+删除日志）

检查MySQL的Slave是否正常（脚本）

Slave_SQL_Running: No mysql同步故障解决方法（数据同步）

MySQL-Proxy负载平衡测试遇到的问题及其分析 && MySQL-Proxy工作机制

mysql主从服务器搭建配置（配置例子）