MySQL复制简介和使用

 

               MySQl数据复制探索和配置

一  复制介绍：

MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。(这与同步复制可以进行对比）同步复制是MySQL簇的一个特征—主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

如果你想要设置链式复制服务器，从服务器本身也可以充当主服务器。

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

二  数据复制的好处：

1.单向复制有利于健壮性、速度和系统管理：主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。

·2.通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。SELECT查询可以发送到从服务器以降低主服务器的查询处理负荷。但修改数据的语句仍然应发送到主服务器，以便主服务器和从服务器保持同步。如果非更新查询为主，该负载均衡策略很有效，但一般是更新查询。

3. 使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。

 

  

三  复制实施概述：

MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。

每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

认识到二进制日志只是一个从启用二进制日志的固定时间点开始的记录非常重要。任何设置的从服务器需要主服务器上的在主服务器上启用二进制日志时的数据库拷贝。如果启动从服务器时，其数据库与主服务器上的启动二进制日志时的状态不相同，从服务器很可能失败。

将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATAFROM MASTER语句。请注意LOAD DATAFROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且，该语句将获得全局读锁定，因此当表正复制到从服务器上时，不可能在主服务器上进行更新。当我们执行表的无锁热备份时，则不再需要全局读锁定。

由于这些限制，建议只有主服务器上的数据集相对较小，或者主服务器上延迟读锁定已经被接受，才可以使用LOAD DATAFROM MASTER。而LOAD DATA FROM MASTER的实际速度随系统的不同而不同，对于执行时间，最好的规则是每1MB的数据用1秒钟。这是一个粗略的估计，但你会发现如果主服务器和从服务器的性能上等价于700MHzPentium CPU，通过100Mbps的网络进行连接，则该估计相当准确。

从服务器设置为复制主服务器的数据后，它连接主服务器并等待更新过程。如果主服务器失败，或者从服务器失去与主服务器之间的连接，从服务器保持定期尝试连接，直到它能够继续帧听更新。由--master-connect-retry选项控制重试间隔。 默认为60秒。

每个从服务器跟踪复制时间。主服务器不知道有多少个从服务器或在某一时刻有哪些被更新了。

四 复制实施细节：

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

在前面的描述中，每个从服务器有3个线程。有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

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

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

在主服务器上，SHOW PROCESSLIST的输出看上去应为：

可以看出，线程4为主服务器发送二进制文件的线程。该信息表示所有主要更新已经被发送到从服务器，主服务器正等待更多的更新出现。

在从服务器上，SHOW PROCESSLIST的输出看上去应为：

线程31为通信线程，从服务器利用这个线程向主服务器发送连接请求，和请求发送二进制日志文件。线程32为SQL线程，读取主服务器Binlog Dump 线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，并执行日志中包含的更新。

五  数据复制的实现过程

准备条件：

   主服务器：192.168.3.5系统（windows server 2008 ） 数据库服（mysql 5.6.12）

   从服务器：192.168.3.3系统（linux） 数据库（mysql 5.6.12）

 备注：

  1. 从服务器的版本不可以高于主服务器的版本，mysql5.17后不可以在使用master-host 类似命令

  2. linux下mysql的配置文件是my.cnf, windows 下为my.ini. 。特别强调---windows 下mysql5.6.12 默认不生产my.ini 而是生产my-default.ini,一定要将此文件改为my.ini 或者自己新建my.ini 否则配置不会生效。

  3. Windows server 2008系统下 有program File 目录 和programData目录（隐藏），默认安装的话，生成的my.ini 应该配置programData下面的配置文件，具体可以配置文件跟着查找。如果以前安装过mysql ，在配置文件前，先搜索my.ini 看服务器中存在多少个my.ini(卸载不会自动卸载my.ini文件，需自己删除)，然后删除没有用的配置文件，配置正确的mysql配置文件

  4 .mysql在windows下是以服务形式存在（控制面板-->管理工具—>服务），默认安装mysql是不会自动安装服务的，用 mysqld install命令安装服务

5.  linux 下mysql以进程方式存在，不可以重复启动mysql服务，否则会造成进程卡死

6. Mysql 密码保护时，使用的是修改密码前的密码（如果修改过密码）

7.  Mysql.sock 和hostname.pid 是连接时产生的文件，如果连接是显示不存在这两个文件，不用去找着两个文，没有连接成功是不存在这两个文件的，一般是配置文件问题，或者无法找到正确的basedir和datadir

 

 

安装mysql:

  Windows下 ：下好安装包，执行setup.exe

  Linux下：利用 #rpm + -ivh+ 安装包名   安装

 

导入数据库：

主服务器上要保证数据一致性，获取数据快照。在获取快照后，主服务器不能有写操作。

先刷新所有语句并阻止写入，这样的目的是保证主机数据快照的一致性：

FLUSHTABLES WITH READ LOCK;

执行完这句后，所有对主服务器数据库的更新都被挂起，直到执行UNLOCKTABLES;或者关闭当前的终端。

获取主服务器的快照：

showmaster status\G;

记录File 及Position项的值，以便之后对从服务器进行配置。File是当前处理的binlog，Position是处理的binlog位置，这是从服务器的同步起点。

接下来是导出主服务器的数据。导出数据有三种方式：利用Navicate for Mysql 工具导出mysql备份,mysqldump导出需要同步的数据库和拷贝数据库数据目录。（为了方便这里我们选用第一种）

利用Navicatefor Mysql 导出3.5 服务器上的netcentre数据库netcentre.sql,然后连接3.3服务器，新建netcentre数据库（UTF-8）,在新建的数据库上运行netcentre.sql.

 

 

 

配置主服务器：

  1.在主服务器上为从服务器上的用户（mysq用户）赋加权限，也可以新建用户（从服务器也要有此用户）

   Grant replication slave,reload,super,file on*.* to ‘root’@’192.168.3.3’identified by ‘bus365_0502’;

 2.my.ini 的[mysqld]下面添加如下命令：

  

server_id =1

log-bin=mysql-bin  #启动复制，设置复制二进制日志文件位置，默认mysql server /下

binlog-do-db=netcentre  #需要复制的库

port =3306

skip-name-resolve  #跳过dns名称查询，有助于加快连接及同步的速度

max_connections=1000  #增大Mysql的连接数目，(默认100)
max_connect_errors=100 #增大Mysql的错误连接数目,(默认10)

 

配置从服务器：

   1.在my.cnf的[mysqld]下面添加如下命令：

 

server_id=2 #此Id不可以和主服务器id相同，大小没有关系

replicate-do-db=netcentre   #需要复制的库

replicate-do-table=netcentre.organization  #需要复制的表

replicate-do-table=netcentre.station

replicate-do-table=netcentre.scheduleprice

replicate-do-table=netcentre.ticketsell

replicate-connect-retry=60   #断开从新连接时间设置，默认没60 秒

 

过程：重启3.5 和 3.3 服务器

1. 在3.5上 输入命令：show master status;   查看3.5master的状态，记下 File 和 Position 参数 如图：

出现了如图情况，说明配置对了，服务启动进入了我们所设置的配置文件，如果出现的是空的（并且正常启动服务），那就说明没有进入我们所配置的配置文件，那么就存在其他的配置文件启动，利用搜索功能查找本机的my.ini和my-default.ini或者my.cnf.

 

2. 进入到3.3的命令界面：

 

 Change master to

     Master_host='192.168.3.5',

    Master_port=3306,

   Master_user='root',

   Master_password='bus365_0502',

   Master_log_file='mysql-bin.000001',# 和show master status 得到的文件相同

Master_log_pos=120;  # 和show master status 得到的文件位置相同

 

删除var/lib/mysql下的主机信息文件和中继日志文件：

rmvar/lib/mysql/master.info

rmvar/lib/mysql/relay-log.info

 

启动线程开始同步：start slave;

查看slave状态：

 一旦从服务器开始同步了，就能在数据文件目录下找到2个文件master.info和relay-log.info。从服务器利用这2个文件来跟踪处理了多少master的binlog。

 

 

对主服务器表进行解锁：UNLOCK TABLES;

对主服务器3.5进行同步的库进行操作，就可以同步到3.3上面了.，MySQL数据复制配置就完成了.

 

测试同步速度

Read_Master_Log_Pos和Exec_Master_Log_Pos位置之间的时间差值吧.这个值只能在一定程度上说明复制良好，但是经常不准确，比如主从之间网络环境很差的时候，可能主和从的数据差异很大，但是复制过来的日志都能被slave的sql线程执行完成，seconds_behind_master此时为0。可以通过在主上showmaster status的Position,和从执行showslave status的 Read_Master_Log_Pos和Exec_Master_Log_Pos进行比较得出复制是否能跟上主的状态。

 

灾难恢复

主从不同步

如果主从同步出现了不一致，就需要重新实施主从复制。步骤和上面相同，只是省略了修改配置文件和创建用户的步骤。

重新配置之前，需要在从服务器停止同步线程：

stopslave;

 

从从服务器恢复

如果主机挂了，可以把从服务器提升为主机，把原主服务器作为备机。

先在从服务器停止同步线程：

stopslave;

在从服务器上添加同步用户：

grantreplication slave on *.* to  ‘user’@'新的从服务器ip'identified by '';

flushprivileges;

配置文件中my.cnf的server-id可以不修改，只要保证id不冲突就行了。

然后，按照主从复制的步骤来进行操作。