mysql主从备份及原理分析

为什么80%的码农都做不了架构师？>>>   

以下教程结合网上资料和自己的总结，综合而成，实际做的过程中发现网上部分网友的教程不是很详细，有些地方的坑也没有点到，从而产生了如下教程

一、mysql主从备份（复制）的基本原理

    mysql支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。mysql复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后*并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。

备份好处：

 1. 可以做灾备，其中一个坏了可以切换到另一个。

 2. 可以做负载均衡，可以将请求分摊到其中任何一台上，提高网站吞吐量。  对于异地热备，尤其适合灾备。

PS：真正误删数据库需要恢复数据库还是要从bin-log日志中恢复  

二、主从备份的实现细节

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


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。状态文件保存在硬盘 上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

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。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

 

如果要备份从服务器的数据，还应备份这两个小文件以及中继日志文件。它们用来在恢复从服务器的数据后继续进行复制。如果丢失了中继日志但仍然有relay-log.info文件，可以通过检查该文件来确定SQL线程已经执行的主服务器中二进制日志的程度。然后可以用Master_Log_File和Master_LOG_POS选项执行CHANGE MASTER TO来告诉从服务器重新从该点读取二进制日志。

三、mysql建立主从服务器配置方法（重点）

主机（master）配置：

在进行mysql主从备份时

1.确保主从服务器的版本兼容。从服务器至少与主服务器版本相同或更高。

2.确保主服务器上my.cnf文件的[mysqld]部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项，其中master_id必须为1到2³²–1之间的一个正整数值。

1.修改mysql配置文件my.cnf，通常是/etc/my.cnf

在[mysqld]标签下添加以下几行

log-bin #开启二进制日志  

server-id=id #主服务器id号  
binlog-do-db=db_nameA #指定对db_nameA记录二进制日志  
binlog-ignore-db=db_namB #指定不对db_namB记录二进制日志  

注意：

log-bin，server-id是配置文件中必须添加的内容。此时主服务器默认对所有数据库进行备份。

如果需要特殊指明只对某个数据库进行备份或不备份，则可以加入binlog-do-db和binlog-ignore-db选项。

有关（log-bin的详细说明见附录1）

在测试主机上，我们实际添加入如下内容：

log-bin =mysql-bin
server-id=1  
binlog-do-db=test  

2.为从服务器添加mysql账户并配置权限

在主服务器上为从服务器建立一个连接帐户，并设置replication slave权限。所用具体命令如下：

进入mysql下

mysql>grant replication slave  on *.*  to 'backupuser'@'192.168.1.66' identified by '密码';  
mysql>FLUSH PRIVILEGES;

这时在mysql库的user表中使用

use mysql;

select * from user where user = 'backupuser' \G;  

可以看到backupuser账号的Repl_slave_priv项对就的值为Y。

3.导出主服务器数据
导出主服务器数据，以备之后将其导入从服务器，使主从服务器的初始状态保持一致。
在测试主服务机上，以root身份登录mysql我们实际执行：

mysql> mysqldump --master-data  --opt database_name > backup-file.sql

4.重启mysql，

service mysqld restart

5.查看主服务器的状态

mysql> show master status/G;

查看主服务器状态，记录File 及Position 项的值，以便之后对从服务器进行配置。
注：由于没有锁定主服务器，这里记录的主服务器二进制日志position值可能会大于做mysqldump时的值，这将导致从服务器丢失在此期间的更新。如果可以保证在此期间主服务器不会出现创建新表的更新，那么丢失的影响不大；否则，将导致从服务器复制线程失败，这时必须在做mysqldump时锁定主服务器。

在测试机上，我们实际执行时看到的结果如下：
*************************** 1. row ***************************
File: simba-bin.000008
Position: 79
Binlog_Do_DB: test
Binlog_Ignore_DB:
1 row in set (0.00 sec)

其中File为imba-bin.000008；Position为79。

至此主服务器配置完毕

加主机锁的情况

下面对数据库表加锁只读。

 mysql锁表只读(其他账户登录mysql后无法进行写表操作，防止备份数据库后，主mysql表更新，导致和从数据库内容不一致)

mysql> flush tables with read lock;

  E：查看状态：

  [root@master mysql] mysql -u root -padmin -e "show master status"

结果如图：

  

  注：File字段的日志名称（mysql-bin.000001）就是从机备份所需要的日志文件。

从机（slave）配置：
1.修改mysql配置文件my.cnf
在[mysqld]标签下添加以下面一行：

server-id=id #从机id  

实际中，我们添加：

server-id=2  

slave_id值必须为2到2³²–1之间的一个正整数值。ID值唯一的标识了复制群集中的主从服务器，因此它们必须各不相同。

注：log-bin被注释是因为主机不需要复制备份从机的数据。
2.

1）导入主机数据库用source 命令导入数据库,

先进入mysql

mysql>create database app；

mysql>use app；

mysql>source app.sql;

等待执行完毕

2）或者导入数据库

shell> mysqladmin create target_db_name

shell> mysql target_db_name < backup-file.sql

3 .重启mysql数据库并设置相关参数

1）修改相关my.cnf参数

master_host = '192.168.1.176'

master_user = 'backupuser'

master_password = 'backupuser'

master_log_file = 'mysql-bin.000085'

master-connect-retry=60       //有些版本不支持此配置报错的话就删掉这行

master_log_pos =8429

2）或者进入mysql的状态下执行下面的语句，以系统的实际值替换选项值：

mysql>change master to  
mysql>master_host = '192.168.1.176',  
mysql>master_user = 'backupuser',  
mysql>master_password = 'backupuser',  
mysql>master_log_file = 'mysql-bin.000085',  
mysql>master_log_pos = 8429;  
mysql>slave start;  

至此从数据库配置完毕

4.检查数据库查看相关参数

从服务器：
使用show slave status \G;查看从服务器相关参数是否正确。

主服务器：

A：解锁数据库：

mysql> unlock tables;

使用出查看主服务器相关参数是否正确 

主要是（master_log_file和master_log_pos）的值。

在实际执行时，我们在从机上看到的结果如下（只摘取了部分输出）：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.146.133
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: darkstar-bin.000006
Read_Master_Log_Pos: 79
Relay_Log_File: simba-relay-bin.000003
Relay_Log_Pos: 171
Relay_Master_Log_File: darkstar-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……

对部分参数的解释：
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上参数说听slave正常运行，正等待主服务器发来的消息。此时若用netstate命令可以看到从机与主机间已经建立了一条边接。

注意：特别需要注意的两个参数是：
Master_Log_File和Read_Master_Log_Pos。

Master_Log_File代表主机上用于主备同步的日志文件名，

Read_Master_Log_Pos代表上一次成功同步到的日志文件中的位置。

如果这两项与先前在主服务器上看到的File及Position的值不相符，则无法正确进行同步。

5.执行上述程序后，从服务器应连接主服务器，并补充自从快照以来发生的任何更新。如果没有正确更新，请检查复制线程状态以及data目录下的.err文件获取信息。

 7、测试过程：

A：在主机上的test数据库中，建立test表，并插入测试数据，在从机上查看是否备份成功；

B：在从机上的test数据库中，在test表插入测试数据，在主机上查看是否备份成功；

C：主机的数据在从机的数据库上备份成功，从机的数据在主机上没有被复制备份，则说明配置成功。

D：主机的数据在从机的数据库上备份成功，从机的数据在主机上也被复制备份，则说明配置失败。

E：主机的数据在从机的数据库上备份失败，则说明配置失败。

四、主从复制如何提高可靠性

目前采用的主从单向复制，从服务器只是实时的保存了主服务器的一个副本。当主服务器发生故障时，可以切换到从服务器继续做查询，但不能更新。

如果采用双向复制，即两台mysql服务器即作为主服务器，又作为从服务器（主主备份）。那么两者都可以执行更新操作并能实现负载均衡，当一方出现故障时，另一方不受影响。但是，除非能保证任何更新操作顺序都是安全的，否则双向复制会导致失败。为了更好的提高可靠性和可用性，需要当主服务器不可用时，能够在从服务器上更新，当主服务器恢复后，能够将从服务器上的更新同步到主服务器上。目前设想的方法是当主服务器不可用时，令从服务器成为Master。原来的主服务器重新启动后，设定为Slave，并从新的Master上同步更新。待同步一致后，再重新各自切换为原来的角色。具体的操作方法有待实验验证。

 

PS:主服务器产生的二进制日志会占据大量的磁盘空间，应定期删除过期的bin-log.目前通过crontab每天运行/data/mysql/data/log_auto_del.sh来删除3天前的二进制日志文件。

附录1
关于log_bin日志 
　　my.conf 文件中的[mysqld]标签下的log_bin指定日志文件，如果不提供文件名，mysql将自己产生缺省文件名。mysql会在文件名后面自动添加数字索引，每次启动服务时，都会重新生成一个新的二进制文件。此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定 记录的数据库；使用binlog-ignore-db可以指定不记录的数据库。注意的是：binlog-do-db和binlog-ignore-db一 次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。以后对数据库每做的一次操作，都会在binlog中有所记录。

附录2 遇到的问题：

1、 Mysql 出现错误The server is not configured as slave; fix in config file or with CHANGE MASTER TO

出现该错误可能是以下原因;

1） 没有执行 change master to master_user='mysqlsync',master_password='mysqlsync',master_host='10.26.68.48',master_port=3309,master_log_file='mysql-bin.000037',master_log_pos=405;或者执行错误。

例如命令中多了master-connect-retry=60, （实际情况我遇到了具体原因可能是参数不应该这样写）导致执行错误；

2）没有设置server_id(可能是主库也可能是从库)，可以在两个服务器上执行show variables like ''ser%;查看msyql 配置文件看server_id是否是0或者1，因为一般情况下0或者1是默认值。有的情况下master的server_id为1也可能会导致此问题，大多数情况下不会有问题，但是为0 就会有问题。

3）检查master的server_id和slave  的server_id是否相同。

接下来确认slave和master的上的server_id是否正确。可以分别在slave和master上运行 SHOW VARIABLES LIKE 'server_id'; 来查看server_id是否和你配置的一样。

2、 Slave_IO_Running: Connecting问题解决

解决方法：

1）主从数据库的replication 用户 都要进行一次授权，然后做同步。

授权给从数据库服务器192.168.1.167用户为backup  密码为123456

mysql> GRANT REPLICATION SLAVE ON *.*

>TO 'backup'@'192.168.1.167' IDENTIFIED BY '123456';

2）检查下master上的配置选项bind-address是否设置的只允许本机

3）  可能是因为防火墙未关闭的原因。service iptables stop；

3、mysql修改数据后,Position值无变化

原因是由于在参数中加了 binlog-do-db=discuz,sales
此时sql必须在第一行加入 
use discuz;
然后再进行DML操作,要不然不会生效

手册说明：

Tell the server to restrict binary logging to updates for which the default database is db_name (that is, the database selected by USE). All other databases that are not explicitly mentioned are ignored. If you use this option, you should ensure that you do updates only in the default database.

4、 主从数据库没有同步

 

先上Master库：

 

mysql>show processlist;   查看下进程是否Sleep太多。发现很正常。

show master status; 也正常。

 

mysql> show master status;

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

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |

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

| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |

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

1 row in set (0.00 sec)

 

再到Slave上查看

 

mysql> show slave status\G                                                

 

Slave_IO_Running: Yes

Slave_SQL_Running: No

 

可见是Slave不同步

 

下面介绍两种解决方法：

 

方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况  

解决： 

stop slave;

#表示跳过一步错误，后面的数字可变

set global sql_slave_skip_counter =1;

start slave;

 

之后再用mysql> show slave status\G  查看：

 

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

ok，现在主从同步状态正常了。。。

 

 

方式二：重新做主从，完全同步

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况

解决步骤如下：

1.先进入主库，进行锁表，防止数据写入

使用命令：  

mysql> flush tables with read lock;  

注意：该处是锁定为只读状态，语句不区分大小写

2.进行数据备份 

 

#把数据备份到mysql.bak.sql文件

[[email protected] mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者 python脚本，都比较方便，确保数据万无一失

3.查看master 状态

mysql> show master status;

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

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |

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

| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |

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

1 row in set (0.00 sec)

4.把mysql备份文件传到从库机器，进行数据恢复  

#使用scp命令

[[email protected] mysql]# scp mysql.bak.sql [email protected]:/tmp/

 

5.停止从库的状态

mysql> stop slave;  

 

6.然后到从库执行mysql命令，导入数据备份

  mysql> source /tmp/mysql.bak.sql

 

7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项  

change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;

 

8.重新开启从同步

mysql> start slave;

 

9.查看同步状态

mysql> show slave status\G  查看：  

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

好了，同步完成。

转载于:https://my.oschina.net/zhaky/blog/639117