mysql分离解析原理_mysql主从复制与读写分离原理解析和配置

一.工作原理

简单的说就是把 一个服务器上执行过的sql语句在别的服务器上也重复执行一遍， 这样只要两个数据库的初态是一样的，那么它们就能一直同步。

当然这种复制和重复都是mysql自动实现的，我们只需要配置即可。

我们进一步详细介绍原理的细节， 这有一张图：

上图中有两个服务器， 演示了从一个主服务器(master) 把数据同步到从服务器(slave)的过程。

这是一个主-从复制的例子。 主-主互相复制只是把上面的例子反过来再做一遍。 所以我们以这个例子介绍原理。

对于一个mysql服务器， 一般有两个线程来负责复制和被复制。当开启复制之后。

1. 作为主服务器Master，  会把自己的每一次改动都记录到 二进制日志 Binlog 中。 (从服务器会负责来读取这个log， 然后在自己那里再执行一遍。)

2. 作为从服务器Slave， 会用master上的账号登陆到 master上， 读取master的Binlog,  写入到自己的中继日志 Relaylog， 然后自己的sql线程会负责读取这个中继日志，并执行一遍。  到这里主服务器上的更改就同步到从服务器上了。

在mysql上可以查看当前服务器的主，从状态。 其实就是当前服务器的 Binary(作为主服务器角色)状态和位置。 以及其RelayLog(作为从服务器)的复制进度。

二.主从备份的实现细节

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

1.复制线程状态

通过show slave status\G和show master status可以查看复制线程状态。常见的线程状态有：

(1)主服务器Binlog Dump线程

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

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

(2)从服务器I/O线程状态

Waiting for master to send event

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

(3)从服务器SQL线程状态

Reading event from the relay log

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

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

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

2.复制过程中使用的传递和状态文件

默认情况，中继日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序列号。中继日志与二进制日志的格式相同，并且可以用mysqlbinlog读取。

从服务器在data目录中另外创建两个小文件。这些状态文件默认名为主master.info和relay-log.info。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

三：实现过程

MySQL主从复制

场景描述：

主数据库服务器：192.168.8.214，MySQL已经安装，并且无应用数据。

从数据库服务器：192.168.8.213，MySQL已经安装，并且无应用数据。

3.1主服务器上进行的操作

启动mysql服务

service mysqld restart

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

mysql-uroot -p'new-password'授权给从数据库服务器192.168.8.213mysql> GRANT REPLICATION SLAVE ON *.* to 'hh'@'192.168.8.213'identified by ‘mysql’;

查询主数据库状态

mysql>show master status;+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000047 | 903 | | |

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

1 row in set (0.00sec)

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

3.2配置从服务器

修改从服务器的配置文件/etc/my.cnf

将 server-id = 1修改为 server-id = 7，并确保这个ID没有被别的MySQL服务所使用。

启动mysql服务

service mysqld restart

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

mysql-uroot -p'new-password'执行同步SQL语句

mysql> change master to master_host='192.168.8.214', master_user='hh', master_password='shasd123',MASTER_LOG_FILE='mysql-bin.000047',MASTER_LOG_POS=903;

Query OK,0 rows affected (0.04sec)

正确执行后启动Slave同步进程

mysql>start slave;

主从同步检查

mysql>show salve status\G

ERROR1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'salve status' at line 1mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master to send eventMaster_Host:192.168.8.214Master_User: hh

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000047Read_Master_Log_Pos:903Relay_Log_File: mysqld-relay-bin.000002Relay_Log_Pos:252Relay_Master_Log_File: mysql-bin.000047Slave_IO_Running: 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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:903Relay_Log_Space:408Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:1 row in set (0.00sec)==============================================其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。

如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：

(1)主数据库进行锁表操作，不让数据再进行写入动作

mysql> flush tables with read lock;

Query OK,0 rows affected (0.01sec)

(2)查看主数据库状态

mysql>show master status;

(3)记录下 FILE 及 Position 的值。

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

(4)取消主数据库锁定

mysql>unlock tables;2.3验证主从复制效果

主服务器上的操作

在主服务器上创建数据库second_db

mysql>create database second_db;

Query Ok,1 row affected (0.01sec)

在主服务器上创建表second_tb

mysql>use second_db

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

Query Ok,1 row affected (0.00sec)

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

mysql> insert into second_tb values (001,’haha’);

Query Ok,1 row affected (0.00sec)

在从服务器上查看

mysql>show databases;+--------------------+

| Database |

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

| information_schema |

| first_db |

| mysql |

| second_db |

| test |

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

5 rows in set (0.00sec)

mysql>use second_db;

Reading table informationforcompletion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql>show tables;+---------------------+

| Tables_in_second_db |

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

| second_tb |

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

1 row in set (0.00sec)

mysql>desc second_tb;+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| id | int(3) | YES | | NULL | |

| name | char(7) | YES | | NULL | |

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

2 rows in set (0.00sec)

mysql> selectsecond_tb;

ERROR1054 (42S22): Unknown column 'second_tb' in 'field list'mysql> select * fromsecond_tb;+------+------+

| id | name |

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

| 1 | haha |

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

1 row in set (0.00sec)=============================记录也已经存在

由此，整个MySQL主从复制的过程就完成了，接下来，我们进行MySQL读写分离的安装与配置。