MySQL主从同步（复制）

mysql主从同步定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

使用主从同步的好处：

1.通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。

2.提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据。

3.在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

 

主从同步机制

Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。

每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。

主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

配置主从同步

有很多种配置主从同步的方法，可以总结为如下的步骤：

1．在主服务器上，必须开启二进制日志机制和配置一个独立的ID

2．在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号

3．在开始复制进程前，在主服务器上记录二进制文件的位置信息

4．如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）

5．配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置

配置主服务器

0.先在主服务器数据库里写点数据（可忽略，一般配置主服务器上都会有已存在数据的情况）

mysql> create database mydb;

Query OK, 1 row affected (0.00 sec)

mysql> use mydb

Database changed

mysql> create table mytb(id int(3),name char(10));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into mytb values (001,'myself');

Query OK, 1 row affected (0.00 sec)

1.更改配置文件，首先检查你的主服务器上的my.cnf文件中是否已经在[mysqld]模块下配置了log-bin和server-id

[mysqld]

log-bin=mysql-bin

server-id=1 //给数据库服务的唯一标识，一般为大家设置服务器Ip的末尾号

注意上面的log-bin和server-id的值都是可以改为其他值的，如果没有上面的配置，首先关闭mysql服务器，然后添加上去，接着重启服务器

2.创建用户，每一个从服务器都需要用到一个账户名和密码来连接主服务器，可以为每一个从服务器都创建一个账户，也可以让全部服务器使用同一个账户。下面就为同一个ip网段的所有从服务器创建一个只能进行主从同步的账户。

首先登陆mysql，然后创建一个用户名为repl，密码为123456的账户，该账户可以被192.168.9.0网段(我的虚拟机环境网段)下的所有ip地址使用，且该账户只能进行主从同步

mysql > grant replication slave on *.* to 'repl'@'192.168.9.%' identified by '123456';

3.获取二进制日志的信息并导出数据库，步骤：

首先登陆主数据库，然后刷新所有的表，同时给数据库加上读锁，阻止对数据库进行任何的写操作

mysql > flush tables with read lock;

然后执行下面的语句获取二进制日志的信息

mysql > show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 | 546 | | |

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

1 row in set (0.00 sec)

File的值是当前使用的二进制日志的文件名，Position是该日志里面的位置信息（不需要纠结这个究竟代表什么），记住这两个值，会在下面配置从服务器时用到。

以下复制原有数据方式二选一：

将主服务器的数据文件（整个datadir:/data/mysql目录）复制到从服务器datadir，建议通过tar归档压缩后再传到从服务器解压。

使用命令参考：

[root@centos6 data]# tar -czf mysql.tar.gz /data/mysql/

[root@centos6 data]# scp mysql.tar.gz root@192.168.9.135:/opt/

当然也可以使用mysqldump

使用命令参考：

mysqldump -uroot -p'123456' --all-databases > /server/backup/mysql_bak.$(date +%F).sql

如果数据量很大，可以在导出时就压缩为原来的大概三分之一

mysqldump -uroot -p'123456' --all-databases | gzip > /server/backup/mysql_bak.$(date +%F).sql.gz

从数据库服务器导入mysql -uroot -p'123456' < /server/backup/mysql_bak.2015-07-01.sql

取消主数据库锁定，主数据库就已经可以正常读写数据了，从现在开始修改的数据都会记录到二进制日志中，从数据库同步的就是这之后的操作记录

mysql> UNLOCK TABLES;

配置从服务器

首先检查从服务器上的my.cnf文件中是否已经在[mysqld]模块下配置server-id

[mysqld]

server-id=135

注意上面的server-id的值都是可以改为其他值的（建议更改为ip地址的最后一个字段），如果没有上面的配置，首先关闭mysql服务器，然后添加上去，接着重启服务器

如果有多个从服务器上，那么每个服务器上配置的server-id都必须不一致。从服务器上没必要配置log-bin，当然也可以配置log-bin选项，因为可以在从服务器上进行数据备份和灾难恢复，或者某一天让这个从服务器变成一个主服务器

先确认从数据库上已经存在之前备份的数据，配置同步参数，登陆mysql，输入如下信息：

mysql>CHANGE MASTER TO MASTER_HOST='192.168.9.134',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=546;

//填入的分别是主服务器的ip地址，用于同步的账号及密码，和之前记录的mysql-bin文件和Position值

启动主从同步进程

mysql > start slave;

检查状态

mysql > show slave status \G；

上面的两个进程都显示YES则表示配置成功

Slave_IO_Running:负责从库去主库读取二进制日志，并写入到从库的中继日志

Slave_SQL_Running:负责将中继日志转换成SQL语句后执行