mysql数据库同步热备（双向以及单向）

第一种:单向主从热备

mysql主从热备有2种配置方式,备份某些库或者忽略备份某些库,建议选择后者

master机器

 

以下是代码片段：
vi my.cnf,添加下面的  
log-bin  
server-id       = 1  
#sql-bin-update-same  
binlog-do-db=mysql   //备份的数据库名,可以添加多个或者 

 

slave机器

 

以下是代码片段：
log-bin  
server-id       = 2  
#sql-bin-update-same  
master-host=192.168.8.201  
master-user=backup  
master-password=123456  
master-port=3306  
master-connect-retry=10  
replicate-do-db=mysql  
#log-slave-updates

 

 

或者
master机器

 

以下是代码片段：
server-id       = 1  
expire-logs-days = 7  
binlog-ignore-db=test 

 

slave机器

 

以下是代码片段：
server-id       = 2  
master-host=192.168.8.201  
master-user=backup  
master-password=123456  
master-port=3306  
master-connect-retry=10  
expire-logs-days = 7  
replicate-ignore-db=test

 

 

在master机器上面授权:

 

以下是代码片段：
mysql>GRANT all ON *.* TO backup@192.168.8.202 IDENTIFIED BY "123456"; 

 

到此为止配置完成了(第一次配置需要重启mysql),剩下的工作就是再主从热备开始前保持主从的数据完全一致:
对于myisam的表,直接把master机器mysql/data目录下面的所有文件同步到slave机器对应的目录即可
对应innodb的表,不能直接同步文件,需要用mysqldump导出数据,然后在slave机器上面导入即可
注意在同步数据期间,master机器锁表成只读模式:

 

以下是代码片段：
mysql>flush tables with read lock; 

 

这期间主从mysql都可以不停,数据同步完毕之后,再master机器上查看mysqlbinglog和偏移量,例如:

 

以下是代码片段：
mysql> show master status;  
+---------------------+----------+--------------+------------------+  
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+---------------------+----------+--------------+------------------+  
| mysql-bin.000012    |  4117873 |              | test             |  
+---------------------+----------+--------------+------------------+ 

 

然后在slave机器上面修改成对应的日志文件和偏移量即可:

 

以下是代码片段：
mysql>slave stop;  
mysql> CHANGE MASTER TO      
->     MASTER_LOG_FILE=’mysql-bin.000012’,      
->     MASTER_LOG_POS=4117873;  
mysql>slave start;

 
最后master解锁: 

以下是代码片段：
mysql> unlock tables; 

 

至此mysql主从热备就可以正常工作了.
还有一种简单的方法,但是必须要停止mysql:
删除master机器下面的所有日志文件,删除slave机器的所有日志文件和relay-log.info及master.info
然后同步数据保持主从数据一致,最后先启动slave的mysql后启动master的mysql即可.

附录:
关于innodb的独享表空间存储(解决ibdata1超大的问题)
配置my.cnf
加入 innodb_file_per_table

 

以下是代码片段：
[mysqld]  
innodb_file_per_table

 

InnoDB管理数据库文件的方式比较独特，它使用 tablespace 来管理数据文件。当使用 Per-Table Tablespaces，也就是每个InnoDB表都使用单独的tablespace时，数据文件的管理方式和MyISAM类型的表差不多，在这种情况下，每个数据库表都对应到一个数据文件，当分表比较多时，数据库文件也会比较多；相反,当没有启用Per-Table Tablespaces，则所有的InnoDB表的数据存在同一个tablespace中，tablespace对应到一系列的数据文件，此时，我们必须指定数据库文件的路径和大小，仅有最后一个文件可以是自动扩展的，其它的必须是固定大小(比如2G)。由于InnoDB的数据文件只会增长不会收缩(即使删除数据或者drop表),所以当前面指定的固定大小的文件写满了之后，最后一个自动扩展的文件就会一直增长而导致一个超大的文件的出现，这对于有最大文件限制的系统上就会导致问题。

我同时了遇到磁盘分区写满和最后一个数据文件超大的问题，根据MySQL手册中对InnoDB数据文件维护的说明：
1. 对于最后一个文件超大的问题，可以计算出最后一个文件的大小(按M计算的大小取整，即字节数除以1024^2)，然后修改配置，把最后一个文件大小设置为该值，然后在其后继续追加新的数据文件。
2. 对于磁盘写满的问题，可以把新的数据文件配置到其它分区，或者把以后的文件mv到其它分区，在配置文件中写数据文件的全路径

由于配置文件中指定的数据文件的大小和数据文件每次增长的大小都以M来指定，所以最后一个文件按M计算应该得到一个整数，一般不存在小数舍入取整的问题。

转移数据文件到其他分区应该用mv而不是cp，因为mv不会改变数据文件的创建时间，MySQL在启动时会比对log文件和数据文件的时间戳，如果两者不一致，则会启动失败。

相关配置选项
1. 使用Per-table tablespace

 

以下是代码片段：
[mysqld]  
innodb_file_per_table

 

2. 配置数据文件到不同分区

 

以下是代码片段：
innodb_data_home_dir = /  
innodb_data_file_path = data1/ibdata1:10M;data2/ibdata2:10M:autoextend 

 

 

 

第二种:双向主从热备 

环境：

Master server: 10.224.194.239

Slave    server: 10.224.194.237

步骤：

1.分别在Master/Slaver mysql db 创建backup user：

GRANT FILE ON *.* TO backup@10.224.194.239 IDENTIFIED BY 'pass';

GRANT REPLICATION SLAVE ON *.* TO backup@10.224.194.239 IDENTIFIED BY 'pass';

 

GRANT FILE ON *.* TO backup@10.224.194.237 IDENTIFIED BY 'pass';

GRANT REPLICATION SLAVE ON *.* TO backup@10.224.194.237 IDENTIFIED BY 'pass';

2.在Master server配置/etc/my.cf 文件：

log-bin

server-id = 1

binlog-do-db=test

binlog-ignore-db = mysql

//只保存7天的log-bin日志

expire-logs-days = 7

 

replicate-do-db=test

replicate-ignore-db = mysql

 

master-host=10.224.194.237

master-user=backup

master-password=pass

master-port=3306

master-connect-retry=60

 

slave-skip-errors=all

3.在Master server配置/etc/my.cf 文件：

log-bin

server-id = 2

//只保存7天的log-bin日志

expire-logs-days = 7

 

binlog-do-db=test

binlog-ignore-db = mysql

 

replicate-do-db=test

replicate-ignore-db = mysql

 

master-host=10.224.194.239

master-user=backup

master-password=pass

master-port=3306

master-connect-retry=60

 

slave-skip-errors=all

4.重启mysql数据库，验证命令如下：

查看Master状态

show master status;

mysql> show master status;

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

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysqllog.000003 |      301 | test         | mysql            |

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

1 row in set (0.00 sec)

查看Slave状态

show slave status \G;

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Reconnecting after a failed master event read

                  Master_Host: 10.224.194.239

                  Master_User: backup

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysqllog.000003

          Read_Master_Log_Pos: 301

               Relay_Log_File: mysqlgsb-relay-bin.000082

                Relay_Log_Pos: 348

        Relay_Master_Log_File: mysqllog.000003

             Slave_IO_Running: No

            Slave_SQL_Running: Yes

              Replicate_Do_DB: test

          Replicate_Ignore_DB: mysql

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 301

              Relay_Log_Space: 650

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)

 

ERROR:

No query specified

查看同步进程：

mysql> show processlist \G;

*************************** 1. row ***************************

     Id: 1

   User: system user

   Host: 

     db: NULL

Command: Connect

   Time: 4186

  State: Waiting for master to send event

   Info: NULL

*************************** 2. row ***************************

     Id: 2

   User: system user

   Host: 

     db: NULL

Command: Connect

   Time: 3745

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

   Info: NULL

*************************** 3. row ***************************

     Id: 5

   User: root

   Host: mysqlpri.webex.com:28293

     db: NULL

Command: Query

   Time: 0

  State: NULL

   Info: show processlist

*************************** 4. row ***************************

     Id: 6

   User: backup

   Host: 10.224.194.237:41729

     db: NULL

Command: Binlog Dump

   Time: 135

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

   Info: NULL

4 rows in set (0.00 sec)

 

ERROR: 

No query specified

PS:

一般备份只需要单向热备,请看下面这篇文章:

另外转一篇文章:http://blog.csdn.net/babydavic/article/details/8432120

http://blog.csdn.net/mackzhaozhonggang/article/details/6718831

 

 

以下是Mysql同步出错解决方法

一、主从不同步

mysql> show slave status;

报错:Error xxx dosn't exist

且show slave status\G:

Slave_SQL_Running: NO

Seconds_Behind_Master: NULL

解决办法一

1、首先停掉Slave服务:

mysql> SLAVE STOP;

2、到主服务器上查看主机状态：记录File和Position对应的值:

mysql> SHOW MASTER STATUS;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| updatelog.000001 | 215 +------------------+-----------+--------------+------------------+

3、到slave服务器上执行手动同步：

mysql> CHANGE MASTER TO

-> MASTER_HOST='master_host_name',

-> MASTER_USER='replication_user_name',

-> MASTER_PASSWORD='replication_password',

-> MASTER_PORT=3306

-> MASTER_LOG_FILE='recorded_log_file_name',

-> MASTER_LOG_POS=recorded_log_position;

mysql> SLAVE START;

// 再次查看slave状态发现：

 

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

问题解决！

解决办法二

1、首先停掉Slave服务:

mysql> SLAVE STOP;

2、从主服务器中跳过后面的n个事件。要复原由语句导致的复制中止，这是有用的。仅当从属

线程没有正在运行时，本语句时有效的。否则，会产生一个错误。

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

3、恢复Slave服务:

mysql> SLAVE START;

注意: 方法一是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删

除记录同步也会有一些错误信息,不会影响使用. 方法二不一定会有效果.

// 还需要做的一些优化与监视:

show full processlist; # 查看mysql当前同步线程号

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

max_connections=1000 # 增大Mysql的连接数目，(默认100)

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

二、SLAVE启动问题

由于一些错误操作导致 CHANGE MASTER 和SLAVE 服务无法启动，系统报错如下：

*****************************************************************

Could not initialize master info structure; more error messages can be found in the MySQL

error log.

*****************************************************************

无法初始化master info结构，MySQL错误日志记录了更详细的错误信息。

解决方法：

1、查看MySQL错误日志，如：同步的上一个Position是多少，很多情况下无法启动服务是由于

mysql识别的同步始终停留在上一个Position上。

2、查看master.info和relay-log.info，master.info 记录MASTER相关信息，relay-log.info

 

记录当前同步日志信息。

3、停止myslq服务，删除master.info和relay-log.info。

4、启动mysql服务。

5、重新CHANGE MASTER，重新启动SLAVE服务。

 或者最简单的方式:

 

mysql> slave stop;

mysql> reset slave;

mysql> CHANGE MASTER TO      
->     MASTER_LOG_FILE=’mysql-bin.000012’,      
->     MASTER_LOG_POS=4117873;  

mysql> slave start;

 最后贴个主数据库宕机后怎么处理的文章:

http://chenhao6.blog.51cto.com/6228054/1325247