MySQL主从库

一、MySQL主从库
MySQL的主从复制方案，是数据的传输。只不过MySQL无需借助第三方工具，而是其自身同步复制的功能，另外一点，MySQL的主从复制并不是磁盘上直接同步，而是逻辑binlog日志同步到本地再应用执行的过程。
二、主从库原理
1）从库上的IO 进程不停的向主库发送请求，请求信息包括用户名、密码、IP地址、端口、以及要读取的主库哪个位置点的二进制日志文件
2）主库上的IO进程检查来自从库的IO进程的请求，核对权限和其他的相关信息，然后给从库的IO进程回传从库指定的二进制文件和更新位置点
3）此时从库接收到主库回传过来的二进制文件存放到中继日志（即relay-log）中，并且从主库传过来的二进制日志中取得下次要更新的二进制文件名和位置点存储到master.info文件里，然后继续向主库发送IO请求；
4）由于从库上的SQL进程对本地的中继日志（即relay-log）是处于时时监听状态，所以只要中继日志有变化，就会读取其中变化的部分，然后将变化的部分经过转换写到从库的存储引擎里面，至此完成一次数据同步。
三、主从库配置
1.master上my.cnf配置文件

[root@master data]# cat /etc/my.cnf         
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = /application/mysql-5.6.20 
datadir = /application/mysql-5.6.20/data
port = 3306
server_id = 1
socket = /application/mysql/tmp/mysql.sock
log-bin = /application/mysql/data/mysql-bin
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# #safe-updates
#
# [myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
#
# [mysqlhotcopy]
interactive-timeout
[root@master data]#

2.slave上my.cnf配置文件

[root@slave etc]# cat my.cnf              
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = /application/mysql-5.6.20 
datadir = /application/mysql-5.6.20/data
port = 3306
server_id = 3
socket = /application/mysql/tmp/mysql.sock
log-bin = /application/mysql/data/mysql-bin
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# #safe-updates
#
# [myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
#
# [mysqlhotcopy]
interactive-timeout
[root@slave etc]#

3.主从配置文件区别在于server_id的值不同，这里主从配置在两台不同服务器，所以只需要server-id不同即可,另外在主服务器（master）上开启二进制日志记录功能,从服务器可以开启也可以不开启

[root@master data]# egrep "log-bin|server_id" /etc/my.cnf
server_id = 1
log-bin = /application/mysql/data/mysql-bin
[root@master data]#

4.注意事项
a.server_id和log-bin参数位置一定要放在【msyqld】模块内
b.server-id的值使用服务器ip地址的最后8位如19，目的是避免不同机器或者实例ID重复（不适合多实例）
c.要先在my.cnf配置文件中查找相关参数，并按照要求修改，不存在时添加参数，切记，参数不能重复
d.修改my.cnf配置后需要重启数据库，命令为:/etc/init.d/mysqld restart,注意要确认真正重启了
5.主服务器添加授权和同步账户

grant replication slave on *.* to 'rep'@'10.0.0.%' identified by 'pwd123';

6.验证从库开关是否真的开启:

show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
两个参数显示为Yes，则表明从库运行正常
7.验证数据同步
在主库创建表、修改表结构、字段、数据。在slave上查看是否同步。
四、主从同步管理
1.停止mysql同步

STOP SLAVE IO_THREAD; #停止IO进程
STOP SLAVE SQL_THREAD; #停止SQL进程
STOP SLAVE; #停止IO和SQL进程

2.启动mysql同步

START SLAVE IO_THREAD; #启动IO进程
START SLAVE SQL_THREAD; #启动SQL进程
START SLAVE; #启动IO和SQL进程

3.重置mysql同步

RESET SLAVE;

4.临时跳过MYSQL同步错误
经常会出现mysql主从同步遇到错误的时候，比如一个主键冲突等，
那么就需要在确保那一行数据一致的情况下临时的跳过这个错误，使用命令

SQL_SLAVE_SKIP_COUNTER = n

n是表示跳过后面的n个事件,比如我跳过一个事件的操作如下

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

5.从指定位置重新同步

CHANGE MASTER TO MASTER_HOST='192.168.106.1主库地址',
MASTER_USER='replication',
 
MASTER_PASSWORD='mysqlpassword',
MASTER_LOG_FILE='mysql-bin.000006',
 
MASTER_LOG_POS=106;
START SLAVE;