mysql主从热备配置(含innodb)终极版

转自 http://blogread.cn/it/article/361?f=wb

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