MySQL主从复制

1、主从复制介绍

MySQL支持单双向、链式级联、异步复制。在复制过程中，一个服务器充当主服务器(Master)，而一个或多个其他的服务器充当从服务器(Slave)。

如果设置了链式级联复制，那么，从（slave）服务器本身除了充当从服务器外，也会同时充当下面从服务器的主服务器。链式级联复制类似A-->B-->C-->D的复制形式。

当配置好主从复制后，所有对数据库内容的更新就必须在主服务器上进行，以避免用户对主服务器上数据库内容的更新与对从服务器上数据库内容的更新之间发生冲突。生产环境中一般会，忽略授权表同步，然后对从服务器上的用户仅授权select读权限，或在my.cnf配置文件中加read-only参数来确保从库只读，当然二者同时操作效果更佳。

①、主从服务器架构的设置，可以大大的加强数据库架构的健壮性。当主服务器出现问题时，我们可以切换到从服务器继续提供服务器。

②、主从服务器架构可实现对用户的请求读写分离，即通过从服务器上处理用户select查询请求，降低查询响应时间，及读写给主服务器带来的压力。对于更新的数据（update、insert、delete）仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。

如果网站是以非更新为主的业务，如blog,www首页展示等业务，读请求比较多，这时从服务器的负载均衡策略就很有效了，这就是传说中的读写分离数据库结构。

③、可以把几个不同的从服务器，根据公司的业务进行拆分。有为外部用户提供查询服务的从服务器，有用来备份的从服务器，还有提供公司内部后台、脚本、日志分析及开发人员服务的从服务器。这样除了减轻主服务器的压力外。使得对外用户浏览、对内处理公司内部用户业务，及DBA 备份业务互不影响。具体可以使用下面的简单架构来说明：

M-->S1 ==>对外部用户提供服务（浏览帖子、浏览博客）

-->S2==>对外部用户提供服务（浏览帖子、浏览博客）

-->S2==>对内部用户提供服务（后台访问、脚本任务、数据分析、开发人员浏览）

-->S2==>数据库备份服务（开启从服务器binlog功能，可实现增量备份及恢复）

2、主从复制原理

MySQL的主从复制是一个异步复制过程（但看起来也是实时的），数据库数据从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Slave）。在Master和Slave之间实现整个主从复制的过程有三个线程参与完成。其中有两个线程（SQL线程和IO线程）在Slave端，另一个线程（IO线程）在Master端。

要实现MySQL的主从复制，首先必须打开Master端的Binlog功能，否则无法实现主从复制。因为整个复制过程实际就是Slave从Master端获取Binlog日志，然后再再Slave自身上以相同顺序执行Binlog日志中所记录的各种操作。打开MySQL的Binlog可以通过在MySQL的配置文件my.cnf中的mysqld模块增加”log-bin”参数项。

3、主从复制过程描述

①．Slave服务器执行start slave，开启主从复制开关。；

②．此时，Slave服务器的IO线程会通过在Master上授权的复制用户请求连接Master服务器，并请求从指定Binlog日志文件的指定位置（日志文件和位置是在配置主从服务器时change master时指定的）之后的Binlog日志内容；

③．Master服务器接收到来自Slave服务器的IO线程请求后，Master服务器上负责复制的IO线程根据Slave服务器的IO线程请求的信息读取指定的Binlog日志文件指定位置之后的Binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了日志内容外，还有本次返回的日志内容后再Master服务器端的新的Binlog文件名称以及在Binlog中的指定位置；

④．当slave服务器的IO线程获取到来自Master服务器上的IO线程发送日志内容以及日志文件及位置点后，将Binlog日志内容依次写入到Slave端自身的Relay Log(即中继日志)文件的最末端，并将新的Binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新binlog日志时能够告诉Master服务器需要从新Binlog日志的哪个文件哪个位置开始请求新的Binlog日志内容；

⑤．Slave服务器的SQL线程会实时的检测本地Relay Log中新增加了日志内容，然后及时的把Log文件中的内容解析成在Master端曾经执行的SQL语句的内容，并在自身Slave服务器上按语句的顺序执行应用这些SQL语句；

⑥．经过上面的过程，就可以确保在Master端和Slave端执行了同样的SQL语句。当复制状态正常的情况下，Master端和Slave端的数据是完全一样的。

4、部署MySQL主从复制（异步）

环境

系统版本：CentOS release 6.4 (Final)，最小化安装i686

MySQL版本：mysql-5.6.11.tar.gz

主库（mysql matser）test1 ip：192.168.3.100

从库（mysql slave） test2 ip：192.168.3.101

①安装MySQL5.6.11（主从机安装步骤一样）

安装相应的软件依赖包

#yum -y install gcc gcc-c++ cmake make ncurses-devel libxml2-devel libtool bison zlib-devel

为数据库创建用户及组账户，MySQL编译安装完成后，为软件主目录设置正确的用户及组

groupadd mysql
useradd -r -s /sbin/nologin -g mysql mysql
tar zxf mysql-5.6.11.tar.gz 
cd mysql-5.6.11
cmake . -DENABLE_DOWNLOADS=1
make && make install

初始化数据库，完成后将MySQL配置文件my.cnf复制一份到/etc目录下

/usr/local/mysql/scripts/mysql_install_db  \
--user=mysql --basedir=/usr/local/mysql/  \
--datadir=/usr/local/mysql/data
cp /usr/local/mysql/my.cnf  /etc/my.cnf

设置MySQL的启动脚本来管理服务进程

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld 
chkconfig mysqld on
PATH=$PATH:/usr/local/mysql/bin/
echo "export PATH=$PATH:/usr/local/mysql/bin/" >>/etc/profile

默认没有设置密码，为了安全给root用户设置密码

# service mysqld start
# mysqladmin -uroot -p password '463951510'
Enter password:

②主服务器上的设置

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

[root@test1 ~]# mysql -uroot -p
mysql> create database hr;
mysql> use hr;
mysql> create table employees(
    -> employee_id int not null auto_increment,
    -> name char(20) not null,
    -> e_mail varchar(50),
    -> primary key(employee_id));
mysql> insert into employees values
    -> (1,'TOM','tom@example.com'),
    -> (2,'Jerry','jerry@example.com');
mysql> exit

我们需要在主服务器上开启二进制日志并设置服务器编号，服务器编号必须是1至2³²-1之间的整数，根据自己的实际情况进行设置。进行这些设置需要关闭MySQL数据库并编辑my.cnf或my.ini文件，然后在[mysqld]设置段添加相应的配置选项

[root@test1 ~]# vim /etc/my.cnf
[mysqld]
log_bin = mysql-bin
server-id = 1
[root@test1 ~]# service mysqld restart

检查配置是否生效

[root@test1 ~]# egrep 'log_bin|server_id' /etc/my.cnf 
log_bin = mysql-bin
server_id = 1

③从服务器的设置

如果从服务ID编号没有设置，或服务器ID编号与主服务器有冲突，就必须关闭MySQL服务，并重新编辑配置文件，设置唯一的服务器编号，最后重启MySQL服务。如果有多台从服务器，则所有的服务器ID编号都必须是唯一的。可以考虑将服务器ID编号与服务器IP地址关联，这样ID编号可以同时唯一标识一台服务器计算机，如采用IP地址最后一位作为MySQL服务器ID编号。

[root@test2 ~]# vim /etc/my.cnf
[mysqld]
server-id = 2
[root@test2 ~]# service mysqld restart

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

④创建复制账号

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

root@test1 ~]# mysql -uroot -p
mysql> create user 'slave_cp'@'192.168.3.%' identified by '123456';
mysql> grant replication slave on *.* to 'slave_cp'@'192.168.3.%';
mysql> flush privileges;

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

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

mysql> flush tables with read lock;
mysql> show master status;
+-------------------+----------+---------------+--------------------+-------------0------+
|       File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+---------------+--------------------+---------------------+
| mysql-bin.000001 |  518   |              |                |                  |
+-------------------+----------+--------------+---------------------+---------------------+
mysql> unlock tables;

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

flush tables with read lock命令的作用是对所有数据库的表执行只读锁定，只读锁定后所有数据库的写操作将被拒绝，但读操作可以继续。执行锁定可以防止在查看二进制日志信息的同时有人对数据进行修改操作，最后使用unlock tables语句对全局锁执行结束操作。

⑥对现有数据进行备份

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

[root@test1 ~]# mysqldump -uroot -p --all-databases --lock-all-tables >/tmp/dbdump.sql
[root@test1 ~]# scp /tmp/dbdump.sql 192.168.3.101:/tmp/
[root@test2 ~]# mysql -uroot -p </tmp/dbdump.sql

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

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

[root@test2 ~]# mysql -uroot -p
mysql> change master to                          #这些信息保存在数据目录data/master.info中
    -> master_host='192.168.3.100',
    -> master_port=3306,
    -> master_user='slave_cp',
    -> master_password='123456',
    -> master_log_file='mysql-bin.00001',
    -> master_log_pos=518;
mysql> start slave;

查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.100
Master_User: slave_cp
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1599
Relay_Log_File: test2-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes    #显示YES表示同步状态成功
Slave_SQL_Running: Yes
......
Seconds_Behind_Master: 0   #和主库同步延迟的描述，这个参数很重要
......

⑧数据同步验证

所有主从服务器设置完毕后，我们可以通过在主服务器上创建新的数据资料，然后在从服务器上查看，所有的数据将自动同步。

[root@test1 ~]# mysql -uroot -p
mysql> create database heboan;
mysql> use heboan;
mysql> create table h_table( name char(20),age int, note varchar(50));
mysql> insert into h_table values ('linda','23','Beijing'), ('jerry','33','Shanghai');

在从库查看是否同步
[root@test2 ~]# mysql -uroot -p'463951510' -e "show databases;"

[root@test2 ~]# mysql -uroot -p'463951510' -e "select * from heboan.h_table;"

posted @ 2017-02-27 22:16 sellsa 阅读(161) 评论(0) 收藏举报

刷新页面返回顶部

MySQL主从复制

公告