mysql主从配置原理_mysql主从复制原理及操作

1.简介

mysql主从复制功能是非常重要的，读操作从数据库，写操作在主数据库，主从数据库的数据库同步是使用mysql的主从复制功能实现的。

2.原理

mysql主从复制原理是基于binary log实现的，主数据库将本数据库的所有操作信息写入binary log文件中，从数据库有个io线程将主数据库的binary log文件中的信息写入从数据库中的relay log(中继日志)文件中，然后从数据库主要读取relay log文件中的信息，进行数据同步。msyql主从复制是基于偏移点(接入点)实现的，所以在搭建完成主从复制后，搭建前主数据库的信息不会同步到从数据库中。

binary 记录信息有三种格式：

statement格式： 记录主数据库的所有sql到binary log文件中，从数据库从中继日志中获取到，然后更新进行数据同步，这个格式有个问题是：如sql中有now()函数，因为同步数据之间有延迟，就会产生数据不一致问题。

row格式：从数据库直接执行主数据库的sql会导致数据不一致，那么久直接将主数据库变更的行信息直接写入binary log中，从数据然后同步，这个格式也有一个问题：如果主数据库对一张表的所有行进行update操作，一条sql语句就执行了，如果使用statement格式将非常方便，row格式会将表中的所有行全部写入binary log文件中，然后同步，效率非常低。

mixed格式：采用statement和row的混合模式，如果sql语句中带有执行时候数据差别的信息时候就用row格式复制，如果使用是普通的sql语句就使用statement格式。该格式也有缺点当使用主机名变量的时候不会同步(@@host name)。

3.配置

192.168.124.101 主机为主数据库，192.168.124.102 主机为从数据库，均安装了msyql.

主数据库配置/etc/my.cnf：

server-id=1 #唯一id

log-bin=mysql-bin #启用二进制日志

binlog-ignore-db=mysql #设置不要同步的数据库,

binlog-ignore-db=information-schema #mysql系统数据库设置为不同步

binlog-do-db=datasource1 #需要同步的数据库

binlog-do-db=datasource2

binlog-format=MIXED #设置binlog同步的格式 statement row mixed

从数据库配置/etc/my.cnf:

server-id=2

relay-log=mysql-relay

修改完了后，需要重启systemctl restart mysqld;

systemctl status mysqld;

4.执行命令：

在主数据库中创建用于主从复制的用户：然后可以show master status;查看状态

GRANT REPLICATION SLAVE ON *.* TO 'slaveDB'@'%' IDENTIFIED BY '123456'

在从数据库上指定要复制的主数据库信息：

CHANGE MASTER to MASTER_HOST='192.168.124.101',

MASTER_USER='slaveDB',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=154;

#MASTER_LOG_FILE(日志文件名)和MASER_LOG_POS(偏移点)信息在主数据库show master status;命令可以获取到。

如果从数据库对别的主数据库进行过主从复制，从数据库需要

stop slave;

然后reset master;

然后设置主数据库信息；

设置完毕后启动主从复制功能：

start slave;

查看从数据库信息： show slave status\G;

主要有俩列：SLAVE_IO_RUNNING:,SLAVE_SQL_RUNNING,如果都为yes那么主从复制完成了，如果有no，那么下面会有相关日志错误信息。