MySQL复制实现互为主从双机热备.docx

MySQL复制实现互为主从双机热备A B 为两台MySQL服务器，均开启二进制日志，数据库版本 5.5.2一、服务器参数[A 服务器]log-bin=mysql-binserver-id = 1binlog-do-db = testbinlog-ignore-db = mysqlreplicate-do-db = testreplicate-ignore-db = mysqlmaster-host = 192.168.1.100master-user = backupmaster-password = 123master-port = 3306master-connect-retry = 10#sync-binlog = 1[B 服务器]log-bin=mysql-binserver-id = 2binlog-do-db = testbinlog-ignore-db = mysqlreplicate-do-db = testreplicate-ignore-db = mysqlmaster-host = 192.168.1.101master-user = backupmaster-password = 123master-port = 3306master-connect-retry = 10#sync-binlog = 1二、操作步骤# A B 服务器停止同步STOP SLAVE;# A B 服务器清空Master日志RESET MASTER;# A 服务器授权同步账户GRANT REPLICATION SLAVE ON *.* TO ‘backup’@%’ IDENTIFIED BY ’123′;FLUSH PRIVILEGES;# A B 服务器锁表（锁表状态下不能终止mysql进程，否则会失败）FLUSH TABLES WITH READ LOCK;# 如果使用SSH，需要重新开启，复制数据库文件。tar -cvf /tmp/mysql-data.tar /usr/local/mysql/datatar -xvf /tmp/mysql-data.tar /usr/local/mysql/data# 查看 A 服务器主机状态（记录二进制开始文件，位置）SHOW MASTER STATUS;# B 服务器锁表（锁表状态下不能终止mysql进程，否则会失败）FLUSH TABLES WITH READ LOCK;# 修改 B 服务器配置CHANGE MASTER TO MASTER_HOST=’192.168.1.100′,MASTER_USER=’backup’, MASTER_PASSWORD=’123′,MASTER_LOG_FILE=’binlog.000001′,MASTER_LOG_POS=107;# 开启 B 服务器同步进程START SLAVE;# 查看 B 服务器同步状态是否正常SHOW SLAVE STATUS;# 查看 B 服务器主机（记录二进制开始文件，位置）SHOW MASTER STATUS;# 修改 A 服务器配置CHANGE MASTER TO MASTER_HOST=’192.168.1.101′,MASTER_USER=’backup’,MASTER_PASSWORD=’123′,MASTER_LOG_FILE=’binlog.000001′,MASTER_LOG_POS=107;# 开启 A 服务器同步进程START SLAVE;# 分别查看 A B 服务器同步状态，确定是否成功SHOW SLAVE STATUS;SHOW MASTER STATUS;# 解锁 A B 服务器UNLOCK TABLES;# 数据测试分别在 A B 服务器上创建表插入数据测试USE test;CREATE TABLE `test_aa` (`id` int(10) unsigned NOT NULL auto_increment,`string` varchar(30) NOT NULL DEFAULT ’0′,`time` datetime NOT NULL DEFAULT ’00-00-00 00:00:00′,PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `test_log_1` VALUES (”, ‘test_1′, NOW());SELECT * FROM `test_log_1`;CREATE DATABASE `test_db_1` DEFAULT CHARACTER SET utf8 COL