mysql主从复制

一、 目标

将多台虚拟机配置成一台主机、多台从机，通过主从复制的方式来同步数据。如下图所示：

二、 基本步骤

a) mysql的安装与配置

每台虚拟机上都已经安装好了mysql，具体方法与步骤请参照：

http://blog.csdn.net/wssg3620625/article/details/17893381

b) 先在两台机器上实践

主数据库服务器：172.30.204.71

从数据库服务器：172.30.204.72

c) 主数据库上的操作

修改配置文件my.cnf，在[mysqld]中加入

server_id = 1

log-bin=/data/qiuzl/software/public/usr/local/mysql/logs/binlog/bin-log

max_binlog_size = 500M

binlog_cache_size = 128K

binlog-do-db = adb

binlog-ignore-db = mysql

log-slave-updates

expire_logs_day=2

binlog_format="MIXED"

启动mysql服务

通过命令行登录管理MySQL服务器

# /data/qiuzl/software/public/usr/local/mysql /bin/mysql -uroot -p'password'

创建同步数据库adb

mysql> create database adb;

授权给从数据库服务器172.30.204.72

mysql> grant replication slave on *.* to 'steven'@'172.30.204.72' identified by ‘password’;

查询主数据库状态
mysql> show master status;

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

| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| bin-log.000003 | 3065 | adb   | mysql     |            |

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

记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到。

d) 从数据库上的操作

修改配置文件my.cnf，在[mysqld]中将server_id = 1修改为server_id = 2，确保这个id没有被别的mysql服务器所使用。

启动mysql服务

通过命令行登录管理MySQL服务器

# /data/qiuzl/software/public/usr/local/mysql /bin/mysql -uroot -p'password'

执行同步SQL语句

mysql> change master to

master_host=’1172.30.204.71’,

master_user=’steven’,

master_password=’password’,

master_port=3307,

master_log_file=’mysql-bin.000003’,

master_log_pos=3065;

正确执行后启动Slave同步进程
mysql> start slave;

主从同步检查
mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.30.204.71

Master_User: steven

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: bin-log.000003

Read_Master_Log_Pos: 3065

Relay_Log_File: localhost-relay-bin.000009

Relay_Log_Pos: 724

Relay_Master_Log_File: bin-log.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

…..

Master_Server_Id: 1

……

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

……

1 row in set (0.00 sec)

*************************** ***************************

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。

而Slave_SQL_Running_State表明当前Slave的状态。

e) 复制线程状态

mysql使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上)。当发出start slave时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器show processlist的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。

在前面的描述中，每一对主从连接有3个线程。有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。这样读取和执行语句被分成两个独立的任务。如果语句执行较慢则语句读取任务没有慢下来。例如，如果从服务器有一段时间没有运行了，当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容，即使SQL线程远远滞后。如果从服务器在SQL线程执行完所有索取的语句前停止，I/O 线程至少已经索取了所有内容，以便语句的安全拷贝保存到本地从服务器的中继日志中，供从服务器下次启动时执行。这样允许清空主服务器上的二进制日志，因为不再需要等候从服务器来索取其内容。

show processlist语句可以提供在主服务器上和从服务器上发生的关于复制的信息。

在主服务器上，show processlist的输出为：

mysql> show processlist\G;

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

Id: 19

User: stevenHost:

172.30.204.72:52288

db: NULL

Command: Binlog Dump

Time: 77130

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

*************************** *********************************

这儿，线程19是一个连接从服务器的复制线程。该信息表示所有主要更新已经被发送到从服务器，主服务器正等待更多的更新出现。

在从服务器上，show processlist的输出看上去应为：

mysql> show processlist\G;

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

Id: 24

User: system user

Host:

db: NULL

Command: Connect

Time: 77064

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 25

User: system user

Host:

db: NULL

Command: Connect

Time: -295964741

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

************************************************************

该信息表示线程24是同主服务器通信的I/O线程，线程25是处理保存在中继日志中的更新的SQL线程。show processlist运行时，两个线程均空闲，等待其它更新。

请注意Time列的值可以显示从服务器比主服务器滞后多长时间。

f) 线程状态

i. 复制主线程状态

下面列出了主服务器的Binlog Dump线程的State列的最常见的状态。如果你没有在主服务器上看见任何Binlog Dump线程，这说明复制没有在运行——即，目前没有连接任何从服务器。

Sending binlog event to slave

二进制日志由各种事件组成，一个事件通常为一个更新加一些其它信息。线程已经从二进制日志读取了一个事件并且正将它发送到从服务器。

 Finished reading one binlog; switching to next binlog

线程已经读完二进制日志文件并且正打开下一个要发送到从服务器的日志文件。

 Has sent all binlog to slave; waiting for binlog to be updated

线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。线程现在正空闲，等待由主服务器上新的更新导致的出现在二进制日志中的新事件。

 Waiting to finalize termination

线程停止时发生的一个很简单的状态。

ii. 复制从I/O线程状态

下面列出了从服务器的I/O线程的State列的最常见的状态。该状态也出现在Slave_IO_State列，由show slave status显示。这说明你可以只通过该语句仔细浏览所发生的事情。

 Connecting to master

线程正试图连接主服务器。

 Checking master version

建立同主服务器之间的连接后立即临时出现的状态。

Registering slave on master

建立同主服务器之间的连接后立即临时出现的状态。

Requesting binlog dump

建立同主服务器之间的连接后立即临时出现的状态。线程向主服务器发送一条请求，索取从请求的二进制日志文件名和位置开始的二进制日志的内容。

Waiting to reconnect after a failed binlog dump request

如果二进制日志转储请求失败(由于没有连接)，线程进入睡眠状态，然后定期尝试重新连接。可以使用--master-connect-retry选项指定重试之间的间隔。

Reconnecting after a failed binlog dump request

线程正尝试重新连接主服务器。

Waiting for master to send event

线程已经连接上主服务器，正等待二进制日志事件到达。如果主服务器正空闲，会持续较长的时间。如果等待持续slave_read_timeout秒，则发生超时。此时，线程认为连接被中断并企图重新连接。

Queueing master event to the relay log

线程已经读取一个事件，正将它复制到中继日志供SQL线程来处理。

Waiting to reconnect after a failed master event read

读取时(由于没有连接)出现错误。线程企图重新连接前将睡眠master-connect-retry秒。

Reconnecting after a failed master event read

线程正尝试重新连接主服务器。当连接重新建立后，状态变为Waiting for master to send event。

Waiting for the slave SQL thread to free enough relay log space

正使用一个非零relay_log_space_limit值，中继日志已经增长到其组合大小超过该值。I/O线程正等待直到SQL线程处理中继日志内容并删除部分中继日志文件来释放足够的空间。

Waiting for slave mutex on exit

线程停止时发生的一个很简单的状态。

iii. 复制从SQL线程状态

下面列出了从服务器的SQL线程的State列的最常见的状态。

Reading event from the relay log

线程已经从中继日志读取一个事件，可以对事件进行处理了。

Has read all relay log; waiting for the slave I/O thread to update it

线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。

Waiting for slave mutex on exit

线程停止时发生的一个很简单的状态。

I/O线程的State列也可以显示语句的文本。这说明线程已经从中继日志读取了一个事件，从中提取了语句，并且正在执行语句。

g) 验证复制效果

主服务器上的操作

进入adb数据库

mysql> use adb;

在主服务器上创建表first_tb

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

Query Ok, 1 row affected (0.00 sec)

在主服务器上的表first_tb中插入记录

mysql> insert into first_tb values (001,’myself’);

Query Ok, 1 row affected (0.00 sec)

在从服务器上查看

mysql> show databases;

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

| Database  |

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

| information_schema |

| adb         |

| mysql       |

| performance_schema |

| test         |

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

5 rows in set (0.00 sec)

数据库adb已经自动生成

mysql> use adb;

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> show tables;

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

| Tables_in_adb |

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

| first_tb      |

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

1 row in set (0.00 sec)

表first_tb也已经自动创建

mysql> select * from first_tb;

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

| id   | name   |

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

|    1 | myself |

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

1 rows in set (0.00 sec)

记录也已经存在

由此，整个mysql主从复制的过程就完成了。 

三、 总结

整个主从配置比较简单，复制机制如上所述，是通过三个线程读写日志文件来实现的。中间也可能会出现其他的问题，可以通过查看日志文件来发现问题。

 

 

 

参考资料：

http://dev.mysql.com/doc/refman/5.1/zh/replication.html

http://heylinux.com/archives/1004.html

http://blog.51yip.com/mysql/357.html