Mysql数据备份四(镜像备份)

我会带着你远行 2022-08-26 13:29 280阅读 0赞

MySQL同步机制基于master把所有对数据库的更新、删除等都记录在二进制日志里。因此，想要启用同步机制，在master就必须启用二进制日志。每个slave接受来自master上在二

进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到，二进制日志只是从启用二进制日志开始的时刻才记录更新操作的。所有的 slave

必须在启用二进制日志时把master上已经存在的数据拷贝过来。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话，那么slave同步就会失败。把

master上的数据拷贝过来的方法之一实在slave上执行 LOAD DATA FROM MASTER 语句。不过要注意，LOAD DATA FROM MASTER 是从MySQL 4.0.0之后才开始可以用的，而且只支持

master上的 MyISAM 类型表。同样地，这个操作需要一个全局的读锁，这样的话传送日志到slave的时候在master上就不会有更新操作了。当实现了自由锁表热备份时(在 MySQL

5.0中)，全局读锁就没必要了。由于有这些限制，因此我们建议只在master上相关数据比较小的时候才执行 LOAD DATA FROM MASTER 语句，或者在master上允许一个长时间的读锁

。由于每个系统之间 LOAD DATA FROM MASTER 的速度各不一样，一个比较好的衡量规则是每秒能拷贝1MB数据。slave上已经完整拷贝master数据后，就可以连接到master上然后等

待处理更新了。如果 master当机或者slave连接断开，slave会定期尝试连接到master上直到能重连并且等待更新。重试的时间间隔由 --master-connect-retry 选项来控制，它的

默认值是60秒。每个slave都记录了它关闭时的日志位置。msater是不知道有多少个slave连接上来或者哪个slave从什么时候开始更新。

MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行 START SLAVE 语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的

语句。master创建一个线程来把日志的内容发送到slave上。这个线程在master上执行 SHOW PROCESSLIST 语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取

master的 Binlog Dump 线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新数据。

如上所述，每个master/slave上都有3个线程。每个 master上有多个线程，它为每个slave连接都创建一个线程，每个slave只有I/O和SQL线程。在MySQL 4.0.2以前，同步只需2个

线程(master和slave各一个)。slave上的I/O和SQL线程合并成一个了，它不使用中继日志。slave上使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢

的话，读日志任务不会跟着慢下来。例如，如果slave停止了一段时间，那么 I/O线程可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小

时。如果slave在SQL线程没全部执行完就停止了，但I/O线程却已经把所有的更新日志都读取并且保存在本地的中继日志中了，因此在slave再次启动后就会继续执行它们了。这就

允许在 master上清除二进制日志，因为slave已经无需去master读取更新日志了。执行 SHOW PROCESSLIST 语句就会告诉我们所关心的master和slave上发生的情况。

下面我们来具体配置

1. 在主服务器上为从服务器建立一个用户：

grant replication slave on \*.\* to '用户名['@'][Link 1]主机' identified by '密码'; （在MySQL 4.0.2以前，用 FILE 权限来代替 REPLICATION SLAVE）

如果打算在slave上执行 LOAD TABLE FROM MASTER 或 LOAD DATA FROM MASTER 语句，那么必须给该帐户授予附加权限：  
授予全局 SUPER 和 RELOAD 权限。  
mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON \*.\* TO [rep@rep2][rep_rep2] IDENTIFIED BY 'rep';  
授予对想要加载的所有表上的 SELECT 权限。在master上任何没有 SELECT 权限的表都会被 LOAD DATA FROM MASTER 略过。

2. 编辑主服务器的配置文件：/etc/my.cnf

server-id = 1  
log-bin  
binlog-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可  
binlog-ignore-db=不需要备份的数据库苦命，如果备份多个数据库，重复设置这个选项即可

3. 编辑从服务器的配置文件：/etc/my.cnf

server-id=2 （配置多个从服务器时依次设置id号）  
master-host=主机  
master-user=用户名  
master-password=密码  
master-port=端口  
replicate-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可

记得先手动同步一下主从服务器中要备份的数据库，然后重启主，从服务器。

要验证主从设置是否已经成功，可以登录从服务器输入如下命令：

mysql> show slave status/G

得到的列表会有类似下面的数据：

Slave\_IO\_State: Waiting for master to send event  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes

如果后面两个选项不全是Yes，那就说明你前面某个步骤配置错了。

如果你的设置是正确的，尝试在主服务器上插入若干条记录，然后你再转到从服务器，会发现相应的新记录已经自动同步过来了。

如果你的主从服务器已经配置好了，那么你在应用程序中，只要保证所有的insert/delete/update操作是在主服务器上进行的，那么相应的数据变化会自动同步到从服务器上，这

样，我们就可以把select操作分担到多台从数据库上，从而降低服务器的载荷。

如果你想使用复制数据文件的方式来备份数据库，只要在从服务器上的mysql命令行先键入slave stop;然后复制数据库文件，复制好了，再在mysql命令行键入slave start;启动从

服务器，这样就即备份了数据有保证了数据完整性，而且整个过程中主服务器的mysql无需停止。

\-----------------------------------------------------------------------------------

提示：如果修改了主服务器的配置，记得删除从服务器上的master.info文件。否则从服务器使用的还是老配置，可能会导致错误。

\-----------------------------------------------------------------------------------

注意：关于要复制多个数据库时，binlog-do-db和replicate-do-db选项的设置，如果要备份多个数据库，只要重复设置相应选项就可以了。

比如：

binlog-do-db=a  
binlog-do-db=b

replicate-do-db=a  
replicate-do-db=b

\-----------------------------------------------------------------------------------

补充：  
在从服务器上使用show slave status  
Slave\_IO\_Running,为No,则说明IO\_THREAD没有启动，请执行slave start \[IO\_THREAD\]  
Slave\_SQL\_Running为No则复制出错,查看Last\_error字段排除错误后执行slave start \[SQL\_THREAD\]

查看Slave\_IO\_State字段  
空 //复制没有启动  
Connecting to master//没有连接上master  
Waiting for master to send event//已经连上

补充：可以使用LOAD DATA FROM MASTER语句来建立slave。但有约束条件：  
数据表要全部是MyISAM表，必须有SUPER权限，master的复制用户必须具备RELOAD和SUPER权限。  
在master端执行RESET MASTER清除已有的日志变更，  
此时slave端会因为找不到master日志无法启动IO\_THREAD，请清空data目录下  
relay-log.info,hosname-relay-bin\*等文件重新启动mysql  
中继日志文件默认的文件为hostname-relay-bin.nnn和hostname-relay-bin.index。可用从服务器的--  
relay-log和--relay-log-index选项修改。在从服务器中还有一个relay-log.info中继信息文件，可用  
\--relay-log-info-file启动选项修改文件名。  
双机互备则是两个mysql同时配置为master及slave

主服务器上的相关命令：  
show master status  
show slave hosts  
show logs  
show binlog events  
purge logs to 'log\_name'  
purge logs before 'date'  
reset master(老版本flush master)  
set sql\_log\_bin=

从服务器上的相关命令:  
slave start  
slave stop  
SLAVE STOP IO\_THREAD //此线程把master段的日志写到本地  
SLAVE start IO\_THREAD  
SLAVE STOP SQL\_THREAD //此线程把写到本地的日志应用于数据库  
SLAVE start SQL\_THREAD  
reset slave  
SET GLOBAL SQL\_SLAVE\_SKIP\_COUNTER  
load data from master  
show slave status(SUPER,REPLICATION CLIENT)  
CHANGE MASTER TO MASTER\_HOST=, MASTER\_PORT=,MASTER\_USER=, MASTER\_PASSWORD= //动态改变master信息  
PURGE MASTER \[before 'date'\] 删除master端已同步过的日志

以下是mysql-5.0.33例子

主服务器my.cnf

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

\[mysqld\]  
port            = 3306  
\#socket          = /tmp/mysql.sock  
log-bin=mysql-bin  
server-id       = 1  
port            = 3306

table-cache=500  
max-connections=1000  
key\_buffer = 700M  
long\_query\_time=1  
log\_slow\_queries= slow\_query.log  
sort\_buffer = 128M

\[mysqldump\]  
quick  
max\_allowed\_packet = 16M

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

从服务器my.cnf

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

\# The following options will be passed to all MySQL clients  
\[client\]  
\#password       = your\_password  
port            = 3306  
socket          = /tmp/mysql.sock

\# The MySQL server  
\[mysqld\]  
port            = 3306  
socket          = /tmp/mysql.sock  
skip-locking  
key\_buffer = 300M  
max\_allowed\_packet = 1M  
table\_cache = 500  
query\_cache\_size=16777216  
sort\_buffer\_size=10485760

max-connections=1000

log-bin  
\#server-id=slave\_id  
server-id       = 7

master-host     =   192.168.0.92

master-user     =   rep

master-password =   123

master-port     =  3306

replicate-ignore-db=mysql

skip-slave-start

slave-skip-errors=1064 1146

\[mysqldump\]  
quick  
max\_allowed\_packet = 16M

\[mysql\]  
no-auto-rehash

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#