当前位置:Gxlcms > mysql > 分布式架构高可用架构篇_07_MySQL主从复制的配置(CentOS-6.7+MySQL-5.6)_MySQL

分布式架构高可用架构篇_07_MySQL主从复制的配置(CentOS-6.7+MySQL-5.6)_MySQL

时间:2021-07-01 10:21:17 帮助过:31人阅读

环境

操作系统:CentOS-6.6-x86_64-bin-DVD1.iso

MySQL版本:mysql-5.6.22.tar.gz

主节点IP:192.168.1.205 主机名:edu-mysql-01

从节点IP:192.168.1.206 主机名:edu-mysql-02

MySQL主从复制官方文档

http://dev.mysql.com/doc/refman/5.6/en/replication.html

MySQL主从复制的方式

MySQL5.6开始主从复制有两种方式:基于日志(binlog)、基于GTID(全局事务标示符)。本文主要讲基于日志(binlog)的复制。

MySQL主从复制(也称A/B复制)的原理

(1)Master将数据改变记录到二进制日志(binarylog)中,也就是配置文件log-bin指定的文件,这些记录叫做二进制日志事件(binarylogevents);

(2)Slave通过I/O线程读取Master中的binarylogevents并写入到它的中继日志(relaylog);

(3)Slave重做中继日志中的事件,把中继日志中的事件信息一条一条的在本地执行一次,完成数据在本地的存储,从而实现将改变反映到它自己的数据(数据重放)。

\

主从配置需要注意的点

(1)主从服务器操作系统版本和位数一致;

(2)Master和Slave数据库的版本要一致;

(3)Master和Slave数据库中的数据要一致;

(4)Master开启二进制日志,Master和Slave的server_id在局域网内必须唯一;

主从配置的简要步骤

1、Master上的配置

(1)安装数据库;

(2)修改数据库配置文件,指明server_id,开启二进制日志(log-bin);

(3)启动数据库,查看当前是哪个日志,position号是多少;

(4)登录数据库,授权数据复制用户(IP地址为从机IP地址,如果是双向主从,这里的还需要授权本机的IP地址,此时自己的IP地址就是从IP地址);

(5)备份数据库(记得加锁和解锁);

(6)传送备份数据到Slave上;

(7)启动数据库;

以下步骤,为单向主从搭建成功,想搭建双向主从需要的步骤:

(1)登录数据库,指定Master的地址、用户、密码等信息(此步仅双向主从时需要);

(2)开启同步,查看状态;

2、Slave上的配置

(1)安装数据库;

(2)修改数据库配置文件,指明server_id(如果是搭建双向主从的话,也要开启二进制日志log-bin);

(3)启动数据库,还原备份;

(4)查看当前是哪个日志,position号是多少(单向主从此步不需要,双向主从需要);

(5)指定Master的地址、用户、密码等信息;

(6)开启同步,查看状态。

单向主从环境(也称MySQLA/B复制)的搭建

1、Master(192.168.1.205)和Slave(192.168.1.206)上都安装了相同版本的数据库(mysql-5.6.22.tar.gz),参考《高可用架构篇--第13节--MySQL源码编译安装(CentOS6.6+MySQL5.6)》。

注意:两台数据库服务器的的selinux都要disable(永久关闭selinux,请修改/etc/selinux/config,将SELINUX改为disabled)

2、修改Master的配置文件/etc/my.cnf[root@edu-mysql-01~]#vi/etc/my.cnf

#在[mysqld]中增加以下配置项

#设置server_id,一般设置为IP

server_id=205

#复制过滤:需要备份的数据库,输出binlog

#binlog-do-db=yixiaoqun

#复制过滤:不需要备份的数据库,不输出(mysql库一般不同步)

binlog-ignore-db=mysql

#开启二进制日志功能,可以随便取,最好有含义

log-bin=edu-mysql-bin

##为每个session分配的内存,在事务过程中用来存储二进制日志的缓存

binlog_cache_size=1M

##主从复制的格式(mixed,statement,row,默认格式是statement)

binlog_format=mixed

#二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。

expire_logs_days=7

#跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。

#如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致

slave_skip_errors=1062

(如想了解以上参数的更多详细解析,大家可以直接百度参数名)

2.1复制过滤可以让你只复制服务器中的一部分数据,有两种复制过滤:

(1)在Master上过滤二进制日志中的事件;

(2)在Slave上过滤中继日志中的事件。如下:

\

2.2MySQL对于二进制日志(binlog)的复制类型

(1)基于语句的复制:在Master上执行的SQL语句,在Slave上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。一旦发现没法精确复制时,会自动选着基于行的复制。

(2)基于行的复制:把改变的内容复制到Slave,而不是把命令在Slave上执行一遍。从MySQL5.0开始支持。

(3)混合类型的复制:默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

3、启动/重启Master数据库服务,登录数据库,创建数据同步用户,并授予相应的权限

[root@edu-mysql-01~]#servicemysqlrestart

ShuttingdownMySQL..[OK]

StartingMySQL..[OK]

[root@edu-mysql-01~]#mysql-uroot-p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.22-log Source distribution


Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


##创建数据同步用户,并授予相应的权限

mysql>grantreplicationslave,replicationclienton*.*to'repl'@'192.168.1.206'identifiedby'123456';

QueryOK,0rowsaffected(0.00sec)##刷新授权表信息

mysql>flushprivileges;

QueryOK,0rowsaffected(0.00sec)

##查看position号,记下position号(从机上需要用到这个position号和现在的日志文件)

mysql>showmasterstatus;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000001 | 1312 | | mysql | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


4、创建 edu 库、表,并写入一定量的数据,用于模拟现有的业务系统数据库

createdatabaseifnotexistsedudefaultcharsetutf8collateutf8_general_ci;

useedu;

DROPTABLEIFEXISTS`edu_user`;CREATETABLE`edu_user`(

`Id`int(11)NOTNULLAUTO_INCREMENT,

`userName`varchar(255)NOTNULLDEFAULT''COMMENT'用户名',`pwd`varchar(255)NOTNULLDEFAULT''COMMENT'密码',

PRIMARYKEY(`Id`)

)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8COMMENT='用户信息表';

INSERTINTO`edu_user`VALUES(1,'yixiaoqun','123456');


5、为保证Master和Slave的数据一致,我们采用主备份,从还原来实现初始数据一致

##先临时锁表

mysql>flushtableswithreadlock;QueryOK,0rowsaffected(0.00sec)

##这里我们实行全库备份,在实际中,我们可能只同步某一个库,那也可以只备份一个库

[root@edu-mysql-01 mysql]# mysqldump -u root -p edu > /tmp/edu.sql

Enter password:

[root@edu-mysql-01 mysql]# cd /tmp

[root@edu-mysql-01 tmp]# ll | grep edu.sql
-rw-r--r-- 1 root root 2031 Apr 25 01:18 edu.sql

#注意:实际生产环境中大数据量(超2G数据)的备份,建议不要使用mysqldump进行比分,因为会非常慢。此时推荐使用XtraBackup进行备份。

#解锁表

mysql>unlocktables;

QueryOK,0rowsaffected(0.00sec)

将Master上备份的数据远程传送到Slave上,以用于Slave配置时恢复数据

[root@edu-mysql-01 tmp]# scp /tmp/edu.sql root@192.168.1.206:/tmp/edu.sql
The authenticity of host '192.168.1.206 (192.168.1.206)' can't be established.
RSA key fingerprint is da:70:7b:d5:0c:16:b3:1a:53:b7:3d:9f:20:01:26:3e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.206' (RSA) to the list of known hosts.
root@192.168.1.206's password:
edu.sql 100% 2031 2.0KB/s 00:00

6、接下来处理Slave(192.168.1.206),配置文件只需修改一项,其余配置用命令来操作

[root@edu-mysql-02 ]#vi/etc/my.cnf

#在[mysqld]中增加以下配置项

#设置server_id,一般设置为IP

server_id=206

#复制过滤:需要备份的数据库,输出binlog#binlog-do-db=edu

#复制过滤:不需要备份的数据库,不输出(mysql库一般不同步)

binlog-ignore-db=mysql

#开启二进制日志,以备Slave作为其它Slave的Master时使用

log-bin=edu-mysql-slave1-bin

##为每个session分配的内存,在事务过程中用来存储二进制日志的缓存binlog_cache_size=1M

#主从复制的格式(mixed,statement,row,默认格式是statement)binlog_format=mixed

#二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。expire_logs_days=7

#跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。

#如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致

slave_skip_errors=1062

##relay_log配置中继日志

relay_log=edu-mysql-relay-bin

##log_slave_updates表示slave将复制事件写进自己的二进制日志

log_slave_updates=1

##防止改变数据(除了特殊的线程)

read_only=1

如果Slave为其它的Slave的Master时,必须设置bin_log,在这里,我开启了二进制日志,而且显式的命名(默认名称为hostname),但是如果hostname改变则会出现问题。)

relay_log配置中继日志,log_slave_updates表示slave将复制事件 写进自己的二进制日志.当设置log_slave_updates时,你可以让slave扮演其它slave的master.此时,slave把sql线程执行的事件写进自己的二进制日志(binary log)然后,它的slave可以获取这些事件并执行它。如下图所示(发送复制事件到其它的Slave):

\

7.保存后重启MySQL服务,还原备份数据

[root@edu-mysql-02 ~]# service mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!

Slave上创建相同库

[root@edu-mysql-02 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-log Source distribution


Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> create database if not exists edu default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.02 sec)


mysql> use edu;
Database changed

6.导入数据

[root@edu-mysql-02 ~]# mysql -uroot -p edu < /tmp/edu.sql
Enter password:
[root@edu-mysql-02 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.22-log Source distribution


Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use edu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> select * from edu_user;
+----+-----------+--------+
| Id | userName | pwd |
+----+-----------+--------+
| 1 | yixiaoqun | 123456 |
+----+-----------+--------+
1 row in set (0.00 sec)

8.登录Slave数据库,添加相关参数

(Master的IP、端口、同步用户、密码、position号、读取哪个日志文件)

change master to master_host='192.168.1.205',master_user='repl',master_password='123456',master_port=3306,

master_log_file='edu-mysql-bin.000001',master_log_pos=1312,master_connect_retry=30;

\

上面执行的命令的解释:

master_host='192.168.1.205' ##Master的IP地址

master_user='repl' ##用于同步数据的用户(在Master中授权的用户)

master_password='123456' ##同步数据用户的密码

master_port=3306 ##master数据库服务的端口

master_log_file='edu-mysql-bin.000001' ##指定Slave从哪个日志文件开始读取复制文件(可在Master上使用show master status查看到日志文件名)

master_log_pos=429 ##从哪个POSITION号开始读

master_connect_retry=30 #当重新建立主从连接时,如果连接建立失败,间隔多久后重试,单位为秒,默认设置为60秒,同步延迟调优参数。

##查看主从同步状态

show slave status\G;

可看到Slave_IO_State为空,Slave_IO_Runngin和Slave_SQL_Running是No,表时Slave还是没有开始复制过程。\

#开启主从同步

mysql> start slave;

再次查看同步状态

#show slave status\G;

\

主要看以下两个参数,这两个参数如果是Yes,就表示主人同步正常

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

由截图的主从同步状态信息可以看出,我们配置的主从同步是正常的。

可查看master和slave上线程的状态,在master上,可以看到slave的I/O线程创建的连接

Master:mysql>show processlist\G;

\

1.row为处理slave的I/O线程的连接。

2.row为处理MySQL客户连接线程。

3.row为处理本地命令行的线程

Slave:mysql>show processlist\G;

\

1.row为处理slave的I/O线程的连接。

2.row为处理MySQL客户连接线程。

3.row为处理本地命令行的线程


9.主从数据复制同步测试

(1)在Master中的edu库上变更数据的同步测试:

Master:

mysql> insert into edu_user values(2,'test1','123456');

Slave:

mysql> start slave;

\

同步数据库

\

人气教程排行