MySql主主互备

总结

• 安装相同版本的Mysql，本文选用的是MySql5.5
• 在配置文件中设置字符集 default-character-set=utf8，可通过以下命令查看

show variables like 'character%';

• 设置传送包大小

max_allowed_packet = 64M
wait_timeout = 6000

• 主机153

#基础配置
log-bin = mysql-bin
binlog_format = mixed
server-id = 153

#作为master的配置
read-only = 0
binlog-do-db = videoapp
auto-increment-increment = 10
auto-increment-offset = 1

#作为slave的配置
replicate-do-db = videoapp
relay_log = mysqld-relay-bin
log-slave-updates = ON

• 主机152

#基础配置
log-bin = mysql-bin
binlog_format = mixed
server-id = 152

#作为slave的配置
replicate-do-db = videoapp
relay_log = mysqld-relay-bin
log-slave-updates = ON

#作为master的配置
read-only = 0
binlog-do-db = videoapp
auto-increment-increment = 10
auto-increment-offset = 2

• 命令

  两台主机分别为从机创建复制账户，ip为从机的ip
  grant replication slave on *.* to 'repl_user'@'192.168.1.153' identified by '123456';
  grant replication slave on *.* to 'repl_user'@'192.168.1.152' identified by '123456';
  
  若主机有数据，则需要导出数据，导数据之前，先锁定数据库FLUSH TABLES WITH READ LOCK;导出数据执行show master status\G；获取文件名和位置，后边要用到；最后解除锁定UNLOCK TABLES；
  mysqldump —master-data -uroot -proot videoapp > videoapp.sql
  
  将数据导入从机
  mysql -uroot -proot videoapp < videoapp.sql
  
  在从机客户端配置主机，后两个参数，需要再主机执行show master status\G；获取
  CHANGE MASTER TO 
         MASTER_HOST='192.168.1.153', 
         MASTER_USER='repl_user', 
         MASTER_PASSWORD='123456', 
         MASTER_LOG_FILE='mysql-bin.000004', 
         MASTER_LOG_POS=7145;
  
  查看从机情况，若Slave_IO_Running、Slave_SQL_Running不是YES，则需要去看日志进行具体分析
  show slave stauts\G

  
  
• 原文地址：http://blog.csdn.net/yueritian/article/details/52170927