mysql主从备份_MySQL

bitsCN.com

一、准备服务器 

由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样，因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能高于Slave版本。

我们假设主服务器(以下简称Master)和从服务器(以下简称Slave)的版本都是5.0.63。 

假设同步Master的主机名为：A(IP:192.168.0.1)，Slave主机名为：B(IP:192.168.0.2)，2个MySQL的basedir目录都是/usr/local/mysql，datadir都是：/var/mysql。 

二、设置同步服务器 

1、设置同步Master 

修改 my.cnf 文件，在 
# Replication Master Server (default) 
# binary logging is required for replication 
添加如下内容： 

Java代码  

1. log-bin=mysql-bin  
2. server-id = 1  
3. binlog-do-db=test  
4. binlog-ignore-db=mysql  

重启MySQL,创建一个MySQL帐号为同步专用 

Sql代码  

1. GRANT REPLICATION SLAVE,RELOAD,SUPER, ON *.* TO backup@192.168.0.2 IDENTIFIED BY 'slavepass';  
2. FLUSH PRIVILEGES ;  

复制数据库： 

Sql代码  

1. FLUSH TABLES WITH READ LOCK;  

锁定表清除写入操作。 

Sql代码  

1. SHOW MASTER STATUS;  

File列显示日志名，而Position显示偏移量，记录备用。 
备份数据库，建议对于MYI存储的MYSQL采用直接文件复制，效率会好很多。备份完解锁。 

Sql代码  

1. UNLOCK TABLES;  

备份后对于从服务器导入数据库 

以上复制过程也可以用mysqldump，如果数据库在提供服务，必须加入--lock-all-tables以保证数据为同一时间的。加入--master-data=1在dump时会记录binlog名称和偏移值，以CHANGE MASTER TO形式存在文件中，这样导入slave后不用自己设置master的binlog文件和偏移值了。 

2、设置同步Slave 

修改my.cnf文件，添加 

Java代码  

1. server-id = 2  
2. master-host = 192.168.0.1  
3. master-user = backup  
4. master-password = slavepass  
5. master-port = 3306  
6.   
7. replicate-ignore-db=mysql  
8. replicate-do-db=test  

server-id不能与master相同 

重启MySQL 

3、启动同步 

在主服务器A MySQL命令符下： 

Sql代码  

1. show master status;  

显示（当然这个是我机器的情况，你的不可能跟我一样哈，只是个例子）： 
+------------------+----------+-------------------+------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+-------------------+------------------+ 
| mysql-bin.000028 | 313361 | test | mysql | 
+------------------+----------+-------------------+------------------+ 

在从服务器B MySQL命令符下： 

Sql代码  

1. slave stop;  
2. MySQL> CHANGE MASTER TO  
3. -> MASTER_HOST='master_host_name',  
4. -> MASTER_USER='replication_user_name',  
5. -> MASTER_PASSWORD='replication_password',  
6. -> MASTER_LOG_FILE='recorded_log_file_name',  
7. -> MASTER_LOG_POS=recorded_log_position;  
8. slave start;  

用show slave status;看一下从服务器的同步情况 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
如果都是yes，那代表已经在同步 

往表里面写点数据测试一下看是否同步成功，如果不成功，绝对不是你的RP问题，再检查一下操作步骤！ 


4、设置双向同步 


其实也就是A->B单向同步的反向操作！双向同步，就这么简单啦！ 

三、同步错误处理 

发现mysql slave服务器经常因为一些特殊字符或者符号产生的更新语句报错，整个同步也会因此而卡在那，最初的办法只是手动去出错的机器，执行下面三条sql语句，跳过错误即可。 

Sql代码  

1. slave stop;  
2. set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;  
3. slave start;  

四、备份底层知识 

1、binlog 
mysql的binlog记录了数据库的所有操作，比如我有个新的数据库bbs，建立数据库时候也开启了binlog，那么mysql会在var目录生成个mysql-bin.000001，这个文件记录了对数据库bbs的所有操作sql命令。每个binlog文件默认1G，超过了会自动换到mysql-bin.000002。mysql-bin.index记录了所有mysql-bin的名字。 

mysql-bin.index示例： 

Java代码  

1. ./mysql-bin.000001  
2. ./mysql-bin.000002  
3. ./mysql-bin.000003  
4. ./mysql-bin.000004  
5. ./mysql-bin.000005  
6. ./mysql-bin.000006  
7. ./mysql-bin.000007  
8. ./mysql-bin.000008  
9. ./mysql-bin.000009  
10. ./mysql-bin.000010  

数据主从备份其实就是master把binlog发给slave，然后slave在本地执行这些sql语句。 

2、master.info 
master.info文件存在于slave的var目录，记录了master的信息。 
master.info示例： 

Java代码  

1. 15  
2. mysql-bin.000105  #master的binlog文件  
3. 498027148         #偏移值  
4. 192.168.0.1  
5. backup  
6. slavepass  
7. 3306  
8. 60  
9. 0  
10.   
11.   
12.   
13.   
14.   
15. 0  

3、relay-log.info 
relay-log.info文件存在于slave的var目录，记录了slave执行binlog文件情况的信息。 
relay-log.info示例： 

Java代码  

1. ./lab-relay-bin.000050     #slave存放master的binlog的文件  
2. 210263408                               #slave偏移值  
3. mysql-bin.000105  
4. 498027148  
5. 2  

从master的mysql-bin.000105发送信息写到lab-relay-bin.000050里，slave再从lab-relay-bin.000050取sql语句执行。

bitsCN.com