两台服务器实现mysql数据库同步备份配置教程

网站数据库对一个网站的最重性是不可言喻的,如果数据异常而你又没有做好备份那是很难恢复的,今天领航教你将两台服务器的mysql数据库同步备份配置,这样子如果我们网站出了问题,我们就可以用最快的速度进行切换,这样子才能不影响用户的访问。

配置环境要求:两台机器,已经安装好了mysql(尽量版本相同,版本也可以不相同。两台机器在同一个网络,可以互相ping通)

主机A(master):192.168.1.80 (ubuntu server 11.10 & mysql 5.5.17)
主机B(slave):192.168.1.81(centos 6.3 & mysql 5.1.61)

1: 在master上授权slave的复制账户和权限,使slave可以从主机A上去复制数据。




  1. mysql>grantallprivilegesonoa.*toadmin@localhostidentifiedby'在这里定义密码';

附:关于mysql授权的一些SQL语句的掌握。




  1. mysql>grantselect,insert,update,deleteon*.*admin@localhostidentifiedby'密码';
  2. mysql>grantselect,insert,update,deleteon*.*admin@'192.168.1.%'identifiedby'密码';
  3. mysql>revokeallprivilegeson*.*fromadmin@localhost; ←取消admin用户对数据库的操作权限
  4. mysql>deletefrommysql.userwhereuser='admin'andhost='localhost'; ←删除admin用户
  5. mysql>flushprivileges; ←刷新,使以上操作生效

2:添加iptabels规则,mysql的3306端口可以通过
# iptabels -A INPUT -m state --state NEW -m tcp -ptcp --dport 3306 -j ACCEPT
3:修改主机A的mysql配置文件my.cnf,并设置编码为UTF-8.




  1. [mysqld]
  2. #注意:该部分server-id=Master_id必须为12321之间的正整数值。从服务器的ID必须与主服务器的ID不相同。
  3. server-id =1
  4. #用户slaver读取的二进制日志文件。
  5. log-bin=msyql-bin
  6. #要复制的数据库replication-do-db=oa
  7. #数据库存放的目录
  8. datadir=/var/lib/mysql
  9. socket=/var/lib/mysql/mysql.sock
  10. #Defaulttousingoldpasswordformatforcompatibilitywithmysql3.x
  11. #clients(thoseusingthemysqlclient10compatibilitypackage).
  12. old_passwords=1 ←找到这一行,在这一行的下面添加新的规则,让MySQL的默认编码为UTF-8
  13. default-character-set=utf8 ←添加这一行
  14. 然后在配置文件的文尾填加如下语句:
  15. [mysql]
  16. default-character-set=utf8

4:配置slave ,打开从机B的my.cnf。




  1. server-id=2

5:同步数据库

主机A上master上面需要的同步的数据库比较大时,先锁定数据表,然后导出数据库。再到主机B slave上导入数据库。




  1. mysql> flushtableswithreadlock; mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000583 | 6682 | oa19 | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)
  2. 在不停主库的情况下备份数据库,root@80:~# mysqldump-uroot -pxxxx -h127.0.0.1 --databases oa --master-data=1 > bak.sql

6:远程复制到slave上mater关于oa的备份文件。




  1. [root@sample~]#scp/home/back/mysql_bak.$(date+%F).sql.gzroot@192.168.1.81:/tmp/

7:到slave上面解压并且导入数据库。




  1. [root@sample~]#gzip-dmysql_bak.$(date+%F).sql.gz
  2. 在从服务器上导入数据
  3. [root@sample~]#mysql>source/home/back/mysql_bak.$(date+%F).sql

8:手动同步slave并且开启slave同步。




mysql>change master to master_host='192.168.1.80',

  1. >master_user='admin',
  2. >master_password='123456',
  3. >master_log_file='mysql-bin.000583',
  4. >master_log_pos=107;
  5. mysql>startslave;
  6. mysql>showslavestatus\G;
  7. 注意:此处的master_log_pos=107或者master_port=3306处,千万不要添加引号,负责会出现语法的错误。