最近做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/f##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/f##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/f"文件必配置正确,请见my_cnf.sh 脚本
4>: 同步数据库的结构必须一致
最后测试:在master 上对同步数据库中的数据表内容进行修改,看是否在slave 进行同步
在slave上对同步数据库中的数据表内容进行修改,看是否在master 进行同步
实验结束！
