mysql 双机交互热备份

最近做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 上对同步数据库中的数据表内容进行修改,看是否在sla

凌众科技专业提供服务器租用、服务器托管、企业邮局、虚拟主机等服务，公司网站：http://www.lingzhong.cn 为了给广大客户了解更多的技术信息，本技术文章收集来源于网络,凌众科技尊重文章作者的版权，如果有涉及你的版权有必要删除你的文章，请和我们联系。以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!