最近做mysql 双机交互热备份实验，遇到不小细节问题，在这里分享给大家

#Master IP: 192.168.0.208
#Slave  IP: 192.168.0.108
#synchronization database: radius
# user: repl     password: repl
[root@localhost lib]# more /etc/my.cnf    ##Master mysql database configuration file
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

server-id=1
log-bin
binlog-do-db=radius
max_binlog_size=104857600
replicate-same-server-id
#========================
# under setting slave
#========================
master-host=192.168.0.108
master_user=repl
master-password=repl
master-port=3306
master-connect-retry=60
replicate-do-db=radius

binlog-ignore-db=mysql
#log-slave-updates
[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

[root@localhost log]# more /etc/my.cnf    ##Slave mysql database configuration file
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
server-id=2
log-bin
binlog-do-db=radius
max_binlog_size=104857600
replicate-same-server-id
#========================
# under setting slave
#========================
master-host=192.168.0.208
master_user=repl
master-password=repl
master-port=3306
master-connect-retry=60
# setting synchronization
replicate-do-db=radius
binlog-ignore-db=mysql
#log-slave-updates
[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

GRANT REPLICATION SLAVE ON *.* TO 'USER'@'RAH' IDENTIFIED BY 'PASSWORD';
 
CHANGE MASTER TO
MASTER_HOST='192.168.0.208',MASTER_USER='repl',MASTER_PASSWORD='repl',
master_log_file="MASER-LOG-FILE",master_log_pos=MASTER_POS;
 
 #=================================================================================
## debug command ,at mysql status
# show master status \G;
# show slave status \G;
# slave start;
# slave stop;

#Mysql 双机交互热备份注意事项

1>: slave 必须能远程访问 master
 GRANT REPLICATION SLAVE ON *.* TO 'USER'@'RAH' IDENTIFIED BY 'PASSWORD';

2>: 如果slave 没有master 的log_file和pos ，必须用下面命令静态指定,
 CHANGE MASTER TO
 MASTER_HOST='192.168.0.208',MASTER_USER='repl',MASTER_PASSWORD='repl',
 master_log_file="MASER-LOG-FILE",master_log_pos=MASTER_POS;
 
3>: "/etc/my.cnf"文件必配置正确,请见my_cnf.sh 脚本

4>: 同步数据库的结构必须一致
 

最后测试:在master 上对同步数据库中的数据表内容进行修改,看是否在slave 进行同步

                在slave上对同步数据库中的数据表内容进行修改,看是否在master 进行同步

实验结束！