Mysql数据库主从配置
Mysql主从同步
Mysql数据库进行主从配置后，可以实现数据库的备份、同时应用也可以实现读写分离，提高应用的并发量。
主从同步原理
主从原理大致有三个步骤：
- 在主库上把数据更改记录到二进制日志中（Binary Log）中，这些记录称为二进制日志事件。
- 从库通过IO线程将主库上的日志复制到自己的中继日志（Relay Log）中。
- 从库通过SQL线程读取中继日志中的事件，将其重放到自己数据上。
二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。
主从配置
基本环境
- 两台服务器安装相同版本的mysql数据库
- 可以先手动将主库的数据复制一份到从库中（统一初态）
- 应保证在配置过程中主库的数据不会改变，可以通过加锁实现
配置主库
- 修改my.cnf文件，在[mysqld]加入下面的内容：
# 服务的唯一编号
server-id = 1
# 开启mysql binlog功能
log-bin = mysql-bin
# binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW
# 减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal
# 指定需要复制的数据库名为db_test
# 如果备份多个数据库，重复设置这个选项即可
binlog-do-db = db_test
# 不需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可（未测试）
# binlog-ignore-db=mysql2
# 这个参数要加上，否则不会给更新的记录些到二进制文件里（未测试）
# log-slave-updates=1
# 跳过错误，继续执行复制操作(可选)
# slave-skip-errors=1
- 保存配置文件，重启mysql服务
// Windows直接在服务中重启，Linux：
service mysqld restart
- 创建提供给从库，用于同步数据的账号（是主库的账号）
create user 'slave'@'127.0.0.2' identified by 'slave';
# 赋予replication slave权限，但在使用从库的数据时，还是要使用有read权限的账号
grant replication slave on *.* to 'slave'@'127.0.0.2';
flush privileges;
网上介绍的方法大都是使用grant 权限列表 on 数据库 to ‘用户名’@’访问主机’ identified by ‘密码’;这种方式，但实测会出现……near 'identified by 密码' at line 1这个错误！
经分析，出错的原因是新版的的mysql版本已经将创建账户和赋予权限的方式分开了，所以应该使用上面的方法。
- 查看主库的状态
- 加“\G”可以使展示更友好
show master status\G;
记下主库状态信息中的File和Position，后面会用到
配置从库
- 修改my.cnf文件，在[mysqld]加入下面的内容：
# 服务的唯一编号
server-id = 2
# 开启mysql binlog功能
log-bin = mysql-bin
# binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW
# 减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal
# 指定需要复制的数据库名为db_test
binlog-do-db = db_test
- 保存配置文件，重启mysql服务
service mysqld restart
- 执行同步命令
# 设置主服务器ip，同步账号密码，同步位置
change master to master_host='127.0.0.1',
master_user='slave',
master_password='slave',
master_log_file='mysql-bin.000100',
master_log_pos=2411;
# 开启同步功能
start slave;
注意此处设置的是主库对应的IP，以及主库配置中创建的主库账号，以及主库状态信息中的File和Position
- 查看从库的状态
show slave status\G;
主要关注Slave_IO_Running和Slave_SQL_Running的状态是否都为Yes，如果是说明从库配置成功。
测试
- 在主库中新增一行记录，看从库是否将该数据同步了；
- 在主库创建新表，看从库是否将新表同步了。
可能的问题
- 从库未能同步主库数据
可以通过
show slave status\G;检查Slave_IO_Running和Slave_SQL_Running的状态是否都为Yes
如果Slave_IO_Running的状态为Connecting，一般是从库数据被修改导致的Position失效导致的（当然原因也可能是因为网络不通、密码不对）：
- 程序可能在slave上进行了写操作
- 也可能是slave机器重启后，事务回滚造成的.
所以不要手动修改从表数据，否则数据冲突会导致主从同步失败（超过了一定的重试次数，从库不再进行同步）。
如果Slave_IO_Running的状态已经为Connecting了，可以通过手动同步数据库，然后修改Position的方法解决：
- 停掉Slave服务：
slave stop; - 到主服务器上查看主机状态，记录File和Position对应的值
- 然后到slave服务器上执行手动同步：
change master to master_log_pos=6352;
如果发生的错误比较少（只有一处），也可以在从库通过下面的方法解决（未测试）：
stop slave ;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave ;
