MySQL 主从复制

概念

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

支持的复制类型

1、基于语句（statement）的复制

在主服务器上执行sql语句，在从服务器上执行相同的语句。mysql默认基于语句的复制，效率较高。

2、基于行（row）的复制

将改变的内容复制过去，而不是把命令在从服务器上执行一遍。

3、混合型（mixed）的复制

一旦发现基于语句的复制无法精确复制时，就采用基于行的复制。

为什么做主从复制的原因

1、灾备：遇到各种原因导致的数据库数据丢失时，有备份，不会造成上海；

2、数据分布：数据库的数据存储过多时，有可能访问效率降低，从，主数据库会各自存储；

3、负载均衡：当数据库压力过大，效率降低，从服务器会承担负载压力；

4、读写分离

5、提高并发能力

主从复制的原理

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

1、在主库上把数据更换记录到二进制文件（binary log）日志
2、备库将主库的日志复制到自己的中继日志（relay log）中。
3、备库读取中继日志的事件，将其重新放到备库数据库中。

半同步复制原理

1）事务在主库写完binlog后需要从库返回一个已接受，才放回非客户端
2）5.5集成到mysql，以插件的形式存在，需要单独安装
3）确保事务提交后binlog至少传输一个从库
4）不保证从库应用完成这个事务的binlog
5）性能有一定的降低
6）网络异常或从库宕机，卡主库，直到超时或从库恢复

并行复制原理

1）从库多线程apply binlog
2）在社区5.6中新增
3）库级别并行应用binlog，同一个库数据更改还是串行的
4）5.7版本并行复制基于事务组

主机宕机后，数据丢失的解决：半同步复制

主库写压力大，因从库只有一个sql
线程持久化，复制可能延迟的解决：并行复制

主从复制的实现

1、环境准备

两台虚拟主机（一台为主库，192.168.76.164 端口为3306；另一台为从库，192.168.76.165 ），从库多实例安装 端口为3306 3307

2、主库配置

1）设置server_id 值并开启binlog 参数

cp   /usr/share/my-defaults.cnf   /etc/my.cnf

vim   /etc/my.cnf
[mysqld]
log_bin=mysql
server_id=2

2. 建立同步账号

   mysql -uroot -p123

   mysql>grant replication slave on . to ‘rep’@‘192.167.76.%’ identified by ‘123456’;

3)查看主库状态

show    master   status；

4）备份主库数据库

mysql -uroot -p123 -S /data/3306/mysql.sock - A  > all_db.sql

5)主库备份数据上传到从库

scp   all_db.sql   192.168.76.165:/tmp/all_db.sql

6）创建数据库db1

 create   database   db1；

3、从库设置

1）

mysql -uroot -p123 -P3306 -S /data/3306/mysql.sock </tmp/all_db.sql 

mysql -uroot -p123456 -P3307 -S /data/3307/mysql.sock

2）还原数据库

mysql -uroot -p123456 -P3306 -S /data/3307/mysql.sock -e "show databases;"

mysql -uroot -p123456 -P3307 -S /data/3307/mysql.sock -e "show databases;"

还原成功

设置数据同步

1）在从数据库设置从主库同步

change master to
MASTER_HOST=‘192.168.76.164’,
MASTER_PORT=3306,
MASTER_USER=‘rep’,
MASTER_PASSWORD=‘123’,
MASTER_LOG_FILE=‘mysql.000001’,
MASTER_LOG_POS=120;

2）启动从库同步

slave   start

检查状态

show slave status\G

此时发现，slave_SQL_Running并未打开

解决的办法是将数据库重启即可

3）在主库中创建表student并插入数据

create table student ( id int, name varchar(20));

insert into student values (1,'zs');

insert into student values (2,'lisi');

4）在从库中查看是否数据同步成功

show tables;

select * from student;