mysql主从复制

mysql主从复制作用（摘自网络）

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

多个数据库间主从复制与读写分离的模型(来源网络)：

在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。

Mysql主从复制的实现原理

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

实现MySQL主从复制需要进行的配置

主服务器：

1、开启二进制日志（log-bin=mysql-bin)     

2、 配置唯一的server id  (如server-id=1,server-id是4字节整数，范围是1~2^32-1)

3、获得master二进制日志文件名及位置 （show master status查看file和position对应的值，如mysql-bin.000004，254。从服务器连接主服务器时master_log_file和master_log_pos要用的，如master_log_file='mysql-bin.000004',master_log_file=254;)

4、创建一个用于slave和master通信的用户账号

从服务器：

1、配置唯一的server-id(如server-id=2）

2、使用master分配的用户账号读取master二进制日志（如:)

change master to master_host='192.168.4.35',master_user='root',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=254;

3、启动slave（start slave,因为在执行change master的之前必修先stop slave，否则会报错)；

主从复制具体实现过程

环境：

系统：centos 7.4  mysql5.7

master_server IP :192.168.4.35     slave_server IP:192.168.4.36    

在主服务器上创建测试数据库database1，database2。

create database database1;
create database database2;
use database1;
create table base1test1(id int,name varchar(10));
use database2;
create table base2test2(id int,name varchar(10),age int);

 

主数据库master配置

首先找到mysql配置所有在目录，一般在安装好mysql服务后，都会将配置文件复制一一份出来放到/ect目录下面，并且配置文件命名为：my.cnf。即配置文件准确目录为/etc/my.cnf

vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin     #[必须]开启二进制日志
server-id=35           #[必须]设置server-id,默认是1，一般去IP最后一段，好区分
上两行有的版本本来就有，我装的mysql5.7没有，需要手动插入

#binlog-do-db=database1　　　　 //[可选]记录日志的数据库，及要同步的数据库
#binlog-do-db=database2
#binlog-ignore-db=mysql    //[可选]不记录日志的数据库，不同步的数据库

#binlog-do-db可选，如果要表示多个数据库同步（如database1和database2），必须如上面一样，分开写。如#果binlog-do-db=database1,database2  也不会报错，和分开写，显示的状态是一样的，但不会同步。后来网#上查了原因说是应为系统把database1，database2看成一个整体了。以为是数据库的名字是'database1,database2'。此处一定要注意。

重启master的mysql服务，使其配置生效

service mysqld restart

 进入mysql，查看主服务器master的状态

mysql -uroot -p密码 -e'show master status;'

记住这两条信息，后面要用到

创建一个用于slave和master通信的用户账号（可不创建使用现有的），如果仅仅为了主从复制创建账号，只需要授予REPLICATION SLAVE权限。

grant replication slave on *.* to 'replicate'@'192.168.4.36' identified by '123456';

注：一般不用root账号， IP一般指定客户端IP，加强安全。

从数据库服务器slave配置

同样修改数据库配置文件my.cnf

vim /etc/my.cnf
[mysqld]
server-id=2
replicate-do-db=database1
replicate-do-db=database1
replicate-ignore-db=mysql

#replicate-do-db和replicate-ignore-db表示同步的数据库和不同步的数据库

重启mysql服务

service mysqld restart

注意：MySQL 5.1.7版本之后，已经不支持把master配置属性写入my.cnf配置文件中了，只需要把同步的数据库和要忽略的数据库写入即可。 

mysql 命令行进入slave从服务器

stop slave；  #停止slave同步进程，否则会报错

change master to 
master_host='192.168.4.35',
master_user='replicate',
master_password='super',
master_log_file='mysql-bin.000004',
master_log_pos=154;
#配置连接主服务器master的相关信息，master_log_file='mysql-bin.000002'和master_log_pos=154一定要和主服务器master读到的信息对应。

start slave;  #开启slave同步进程

 查看从服务器状态

show slave status\G;

 

注意查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上这两个参数的值为Yes，即说明配置成功！

 

主从复制如果发生事务错误，想跳过，比如出现下面错误，只需slave stop;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ；slave start 即可。

1.跳过指定数量的事务：
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳过一个事务
mysql>slave start

2.修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误
vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误