mysql主从复制轮训_MySQL主从复制

MySQL复制介绍

MySQL复制是从服务器复制到一个或多个从服务器的异步过程，整个复制过程主要由三个线程来完成，其中一个I/O线程在主服务器端，另外两个线程(SQL线程和IO线程)在从服务端。要实现MySQL复制，首先要在主服务器上打开MySQL的Binary Log功能

复制详细过程如下

１、首先从服务器上的I/O线程连接上主服务器，然后请求从指定的日志文件的日志内容

２、主服务器在接收到来自从服务器的I/O线程请求后，通过自身的I/O线程，根据请求信息读取指定的日志信息，并返回给从服务器的I/O线程。

３、从服务器的I/O线程接收到信息后，将获取到的日志内容依次写入从服务器端的中继日志文件(类似于mysql-relay-bin.xxxxx)的最后，并且将读取到主服务器端的二进制日志的文件名和位置记录到一个名为master-info文件中，以便下次读取的时候可以迅速定位从哪个位置开始往后读取日志信息

４、从服务器的SQL线程在检测到中继日志文件中新增加了内容后，会马上解析该中继日志文件中的内容，将日志内容解析为SQL语句，然后再执行这些SQL,由于服务器端和主服务器端执行了同样的SQL操作，所以两端的数据是完全一样的，至此整个复制过程结束

MySQL复制技术在实际应用中有多种实现架构

一主一从：　一个主服务器和一个从服务器，这是最常见的架构

一主多从：　一个主服务器个两个或两个以上的从服务器，经常用在写操作不频繁、查询量比较大的业务环境中

双主互备：　两个MySQL服务器互相将对方作为自己的主服务器，自己又同时作为对方的从服务器来进行复制

双主多从：　就是双主互备，然后再加上多个从服务器

MySQL复制的各种部署之前，有一些必须遵守的规则

１、同一时刻只能有一个主服务器进行写操作

２、一个主服务器可以有多个从服务器

３、无论是主服务器还是从服务器，都要确保各自服务器ID唯一

４、一个从服务器可以将从服务器获得的更新信息传递给其他的从服务器，依次类推

MySQL复制类型

基于语句的复制

MySQL默认采用基于语句的复制，效率很高。基本方式是：在主服务器上执行的SQL语句，在从服务器上再次执行同样的语句。而一旦发现没法精确复制时，会自动选择基于行的复制

基于行的复制

基本方式：把主服务器上改变的内容复制过去，而不是把SQL语句在从服务器上执行一遍，从MySQL5.0开始支持基于行的复制

混合类型的复制

其实就是上面两种类型的组合，默认采用基于语句的复制，如果发现基于语句的复制无法精确完成，就会采用基于行的复制

一主一从配置

ip地址

hostname

系统版本

数据库版本

角色

192.168.88.1

c1.heboan.com

CentOS7.2.1511

5.5.56-MariaDB

master

192.168.88.2

c2.heboan.com

CentOS7.2.1511

5.5.56-MariaDB

salve

安装

yum -y install mariadb-server mariadb

systemctl start mariadb

mysqladmin -uroot -p password '123456'　　　＃设置root密码

＃安装完成后的目录结构

/var/lib/mysql/数据文件存放路径，可自定义

/etc/my.cnf配置文件

/usr/lib64/mysql 库文件路径

/usr/bin/mysql*二进制可执行文件路径

/var/log/mariadb/

/var/log/mariadb/mariadb.log 日志文件地址

主服务器环境配置(192.168.88.1)

在生产环境中，可能在我们还没有部署数据复制前，数据库中就已经存在大量的数据。所以这里事先创建一个测试用数据库及数据表，用来演示如何对已经存在的数据进行数据同步。

create database hr;

use hr;

create table employees(

employee_id int not null auto_increment,

name char(20) not null,

e_mail varchar(50),

primary key(employee_id));

insert into employees values

(1,'TOM','tom@example.com'),

(2,'Jerry','jerry@example.com');

在主服务器上开启二进制日志并设置服务器ID,编辑/etc/my.cnf,在[mysqld]段添加相应的配置选项

vim /etc/my.cnf

[mysqld]

...

log_bin = mysql-bin

server-id = 1

#则所有的服务器ID编号都必须是唯一的。可以考虑将服务器ID编号与服务器IP地址关联

...

systemctl restart mariadb

检查配置是否生效

MariaDB [(none)]> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

从服务器环境配置(192.168.88.2)

设置服务器ID

vim /etc/my.cnf

[mysqld]

...

server-id=2

...

systemctl restart mariadb

对复制而言，MySQL从服务器上二进制日志功能是不需要开启的。但是，你也可以通过启用从服务器的二进制日志功能，实现数据备份与恢复。此外，在一些更复杂的拓扑环境中，MySQL从服务器也可以扮演其他从服务器的主服务器

在主服务器上创建复制账号

执行数据复制时，所有从服务器都需要使用账户与密码连接MySQL主服务器，所以在主服务器上必须存在至少一个用户账户及相应的密码供从服务器连接。这个账户必须拥有REPLICATION SLAVE权限，你可以为不同的从服务器创建不同的账户与密码，也可以使用统一的账户和密码。MySQL可以使用CREATE USER语句创建用户，使用GRANT语句为账户赋权。如果该用户仅为数据库复制所用，则该账户仅需要REPLICATION SLAVE权限即可

MariaDB [(none)]> create user 'slave_cp'@'192.168.88.%' identified by '123123';

MariaDB [(none)]> grant replication slave on *.* to 'slave_cp'@'192.168.88.%';

MariaDB [(none)]> flush privileges;

获取主服务器二进制日志信息

在进行主从数据复制之前，我们来了解一些主服务器的二进制日志文件的基本信息，这些信息在对从服务器的设置中需要用到，它包括主服务器二进制文件名称及当前日志记录位置，这样从服务器就可以知道从哪里开始进行复制操作。我们可以使用如下操作查看主服务器二进制日志数据信息。

MariaDB [(none)]> flush tables with read lock;

#对所有数据库的表执行只读锁定，防止在查看二进制日志信息的同时有人对数据进行修改操作

MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 573 | | |

+------------------+----------+--------------+------------------+

#其中，File列显示的是二进制日志文件名，Position为当前日志记录位置

MariaDB [(none)]> unlock tables;

#对全局锁执行结束操作

对现有数据进行备份

如果在使用二进制日志进行数据复制以前，MySQL数据库系统中已经存在大量的数据资源，对这些资料进行数据备份的一种方法是使用mysqldump工具，在主服务器上使用该工具对数据备份后，即可对从服务器上进行数据还原。当希望的数据达到一致后，就可以使用数据复制功能进行自动同步操作。

[root@c1 ~]# mysqldump -uroot -p --all-databases --lock-all-tables >/tmp/dbdumps.sql

[root@c1 ~]# scp /tmp/dbdumps.sql 192.168.88.2:/tmp

[root@c2 ~]# mysql -uroot -p

配置从服务器连接主服务器进行数据复制

数据复制的关键操作是配置从服务器去连接主服务器进行数据复制，我们需要告知从服务器建立网络连接所有必要的信息。使用CHANGE MASTER TO 语句完成该项工作

#这些信息保存在数据目录/var/lib/mysql/master.info中

MariaDB [(none)]> change master to

-> master_host='192.168.88.1',

-> master_port=3306,

-> master_user='slave_cp',

-> master_password='123123',

-> master_log_file='mysql-bin.000001',

-> master_log_pos=573;

MariaDB [(none)]> start slave;

查看主从状态

MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.88.1

Master_User: slave_cp

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 851

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 807

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes --------------->显示Yes表示同步状态成功

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 851

Relay_Log_Space: 1103

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0　　-------------->和主库同步延迟描述，这个参数很重要

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

最后，我们可以再主服务器上进行写操作来验证，是否主从同步