Mysql双机备份方案

运行环境：
虚拟机安装的是CentOS5
Mysql版本：5.1.31- log MySQL Community Server (GPL)

操作步骤：
1.  在 主从服务器上分别安装mysql

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &

2. 配 置主服务器
2.1 先登录主机(59.57.251.56)

mysql>grant all on *.* to root@'%' identified by '1234';  -- 添加权限（赋予从机权限，有多台丛机，就执行多次）
mysql>GRANT All ON *.* TO 'backup'@'%' IDENTIFIED BY 'testbak';
mysql>flush privileges;   -- 刷新权限
mysql>select host,user from user;  --查看权限

2.2 打开 主机A的my.cnf，输入

server-id         = 1    # 主机标示，整数
log_bin            = /var/log/mysql/mysql-bin.log   #确保此 文件可写
read-only        =0  #主机，读写都可以
binlog-do-db    =test   #需要备份数 据，多个写多行
binlog-ignore-db=mysql #不需要备份的数据库，多个写多行

2.3  打开从 机B的my.cnf，输入

server-id       =2
log_bin         =/var/log/mysql/mysql-bin.log
master-host     =59.57.251.56
master-user     =backup
master-pass     =testbak
master-port     =3306
master- connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差(秒)
replicate-do-db =test #只 复制某个库
replicate-ignore-db=mysql #不复制某个库

3.  主从数据 同步
有多种方法:
1) 先mysqldump导出主机A的数据test为 test.sql
然后在从机B上建 立数据库test，mysql导入 test.sql到test库中

2) 如果你想使用复制数据文件的方式来备份数据库
只要在从 服务器上的mysql命令行先键入slave stop;然后复制数据库文件，复制好了，再在mysql命令行键入slave start;启动从服务 器，这样就即备份了数据有保证了数据完整性，而且整个过程中主服务器的mysql无需停止。

4.  先重启主机A的 mysql，再重启从机B的mysql
If you get this error File '/var/log/mysql/mysql-bin.index' not found (Errcode: 13) you might want to try and chown -R mysql:mysql /var/log/mysql

5. 验 证
在主机A中，mysql>show master status\G;
在从机B 中，mysql>show slave status\G;
能看到大致这些内容

File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql

 

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.139.77
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 410
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

可 以在主机A中，做一些INSERT, UPDATE, DELETE 操作，看看主机B中，是否已经被修改

6.  相关 注意事项
提示：如果修改了主服务器的配置，记得删除从服务器上数据库目录下的master.info文件。否则从服务器使用的 还是老配置，可能会导致错误。
----------------------------------------------------------------------

注意： 关于要复制多个数据库时，binlog-do-db和replicate-do-db选项的设置，网上很 多人说是用半角逗号分隔，经过测试，这样的说法是错

误的，MySQL官方文档也明确指出，如果要备份多个数据库，只要重复设置相应选项就 可以了。

比如：

binlog-do-db=a
binlog-do-db=b

replicate-do-db=a
replicate-do-db=b

-----------------------------------------------------------------------

补充：
在从服务器上使用show slave status\G
Slave_IO_Running, 为No,
则说明IO_THREAD没有启动，请执行start slave io_thread
Slave_SQL_Running为 No
则复制出错,查看Last_error字段排除错误后执行start slave sql_thread 

查看 Slave_IO_State字段空 //复制没有启动
Connecting to master//没有连接上master
Waiting for master to send event// 已经连上

补充： 可以使用LOAD DATA FROM MASTER语句来建立 slave。但有约束条件：
数据表要全部是MyISAM表，必须有SUPER权限，master的复制用户必须具备RELOAD和SUPER权 限。
在master端执行RESET MASTER清除已有的日志变更，
此时slave端会因为找不到master日志无法启动 IO_THREAD，请清空data目录下
relay-log.info,hosname-relay-bin*等文件重新启动mysql
中 继日志文件默认的文件为hostname-relay-bin.nnn和hostname-relay-bin.index。可用从服务器的--
relay- log和--relay-log-index选项修改。在从服务器中还有一个relay-log.info中继信息文件，可用
--relay- log-info-file启动选项修改文件名。
双机互备则是两个mysql同时配置为master及slave


++主 服务器上的相关命令

show master status
show slave hosts
show logs
show binlog events
purge logs to ‘log_name’
purge logs before ‘date’
reset master(老 版本flush master)
set sql_log_bin=

++从服务器上的相关命令

slave start
slave stop
SLAVE STOP IO_THREAD //此线 程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程 把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= // 动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志

++ 产生了mysql-bin.00000x文件可以删除

reset master; #http://bbs.chinaunix.net/thread-745343-1-1.html `

相 关链接
http://www.ningoo.net/html/2007/mysql_replication_configuration.html
http://leftleg.hzpub.com/post/645/
http://blog.zhangjianfeng.com/article/705
http://blog.chinaunix.net/u/10047/showart_336011.html
http://www.d5s.cn/archives/95