Mysql主从复制

主从复制

What?

主从复制，建立与主数据库（master）的副本从数据库(slave)。开启主从复制之后master会将DML,DDL等操作写入到二进制文件，slave根据二进制文件Redo日志文件的操作。

Why?

读写分离，负载均衡

在master上执行增删改，在slave上执行查。减小响应延迟，提高系统性能。

异地灾备

若master宕机，slave可以替代master提供服务。

How?

主数据库binlog线程：记录主数据库的sql语句

从数据库I/O线程：连接主数据库，请求复制主库的bin-log到从库的relay-log文件

从数据库sql线程：执行relay-log文件的sql语句

Example

1. 主库修改linux下配置文件：/etc/mysql/my.cnf

server-id               = 129  #建议ip地址最后一位
log_bin                 = mysql-bin  #生成的二进制文件的名称
binlog-do-db            = repl  #主从复制主数据库

主库日志文件位置：/var/log/mysql/mysql-bin.index

2. 从库修改windows下配置文件my.ini

server-id                = 144  
log-bin                  = repl-log
replicate-do-db          = repl  #主从复制从数据库

配置文件的可选配置项

参数	意义
server-id	数据库唯一id，一组主从中此标识号不能重复。
log_bin	开启bin-log，并指定文件目录和文件名前缀
binlog_do_db	同步的数据库名字
binlog_ignore_db	不同步的数据库名字
max_binlog_size	单个bin-log最大容量
binlog_cache_size	日志缓存大小
expire_logs_day	设置bin-log日志文件保存的天数，（此参数mysql5.0以下版本不支持）
binlog_format	bin-log日志文件格式，设置为MIXED可以防止主键重复。

3. 主库新增复制账户replTest/Mysql#123

mysql> grant replication slave on *.* to 'replTest'@'192.168.17.144' identified by 'Mysql#123';
mysql> flush privileges;  #刷新mysql系统权限表

4. 获取主数据库的位置

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000029 |   2071   |     repl     |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5. 从库设置连接主库

CHANGE MASTER TO
MASTER_HOST='192.168.17.129',
MASTER_USER='replTest',
MASTER_PASSWORD='Mysql#123',
MASTER_LOG_FILE='mysql-bin.000029',  #上一步的文件名
MASTER_LOG_POS=2071;  #上一步的文件位置

6. 开启从库，查看从库状态

start slave;
show slave status;  #Slave_IO_Running和Slave_SQL_Running都是Yes表示正常

测试

主库：

CREATE TABLE `repl`.`test_db`  (
  `id` int NOT NULL,
  `username` char(10),
  `age` int,
  `addr` varchar(255),
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test_db (id, username ,age ,addr) VALUES(1,"kite",23,"广州");

查看从库

常见错误：

Slave_SQL_Running为No

错误分析：一般是slave机器重启后，事务回滚造成的。

解决办法：

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;  #跳过一个事务
mysql> start slave ;