mysq的主从复制、备份、还原

mysq的主从复制、备份、还原

标签: mysq的主从复制、备份、还原

原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。 http://www.voidcn.com/article/p-vmewsvtu-vy.html
 
                              基于mysql数据库文件的主从同步与AB复制及数据备份
 
 
 搭建实验环境说明:
 在 RH5.4 yum安装 mysql部署master 数据库A,Centos 5.5 编译安装mysql 部署slave数据库B;
 mysql 版本信息:
  master  mysql-5.0.77    slave :mysql-5.0.87        //测试环境并没有考虑版本问题
 提示:(关闭selinux)
 ---------------------------------------------------------------------------------------------
 一般情况:mysqladmin -uroot password '123456'设置root用户访问mysql的密码;
 mysql -uroot -p 键如密码即可登录mysql>界面
 一、
 ##########################在master mysql操作:##########################
 1、授权从服务器的root用户来主服务器同步资源的权限
 mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO root#slave IP     IDENTIFIED BY '123456';             //将#换成@
 #如果需要的话添加管理用户,通过mysql的客户端来测试同步的情况
 mysql>flush privileges;                                           // 刷新权限,使设置生效
 #在node2上使用一下命令测试授权是否成功
 #mysql -h 192.168.0.42 -uroot -p
 2、在master mysql 配置
 #vim /etc/my.cnf
 # 确保有如下行
 server-id = 1
 log-bin=mysql-bin
 binlog-do-db=mytest
 binlog-ignore-db=mysql
 #binlog-do-db=                                    // 需要备份的数据库名,可写多行
 #binlog-ignore-db=                             // 不需要备份的数据库名,可写多行
 二、
 ##########################在slave mysql操作:###########################
 1、编辑/etc/my.cnf
 server-id=2
 log-bin=mysql-bin
 master-host=192.168.0.123
 master-user=root
 master-password=123456
 master-port=3306
 replicate-do-db=mytest
 可选项如下:
 # replicate-do-db=test                               //  需要备份的数据库名
 # replicate-ignore-db=mysql                        //  忽略的数据库
 # master-connect-retry=60        //如果从服务器发现主服务器断掉,重新连接的时间差(秒)
 三、
 ##########################在master mysql操作:##########################
 1、创建测试数据库
 mysql> create database mytest;                      //创建mytest库
 mysql>show databases;
 mysql> use mytest;            
 mysql> show tables;
 +------------------+
 | Tables_in_mytest |
 +------------------+
 | mytable                   | 
 +------------------+
 1 row in set (0.01 sec)
 mysql>ceate table mytable (name VARCHAR(20), sex CHAR(1), birth DATE, birthaddr VARCHAR(20));
 mysql>describe mytable; 
 mysql>insert into mytable values   ('hanfeng','b','1987-08-08','china'); 
 mysql>insert into mytable values   ('feiniao','g','1988-08-08','china');
 mysql>select * from mytable;
 +----------+------+------------+-----------+
 | name     | sex  | birth      | birthaddr |
 +----------+------+------------+-----------+
 | hanfeng  | s    | 1985-08-08 | china     | 
 | feiniao  | g    | 1988-08-08 | china     | 
 +----------+------+------------+-----------+
  
 2、在master使用mysqldump命令将数据库导出:
 #mysqldump -uroot mytest  > mysql.sql
 3、使用scp命令将mysql.sql拷贝到slave
 #scp mysq.sql root#slaveIP:/root                 //将#换成@
 四、
 ##########################在slave mysql操作:###########################
 1、创建mytest数据库,
 mysql> create database mytest;
 2、将mysql.sql导入其中
 #mysql -uroot -p mytest < mysql.sql
 3、检查mysql.sql导入是否成功
 mysql> create database mytest;                        
 mysql>show databases;
 mysql> use mytest;            
 mysql> show tables;
 +------------------+
 | Tables_in_mytest |
 +------------------+
 | mytable            | 
 +------------------+
 mysql>select * from mytable;
 +----------+------+------------+-----------+
 | name     | sex  | birth      | birthaddr |
 +----------+------+------------+-----------+
 | hanfeng  | s    | 1985-08-08 | china     | 
 | feiniao  | g    | 1988-08-08 | china     | 
 +----------+------+------------+-----------+
  
 4、验证配置的正确性
 mysql>start slave;                    // 如果启动失败,参看以下错误处理②
 mysql> show slave status\G;
 *************************** 1. row *******************
 Slave_IO_State: Waiting for master to send event 必须的
 Master_Host: 192.168.0.123
 Master_User: root
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000002
 Read_Master_Log_Pos: 287
 Relay_Log_File: mysqld-relay-bin.000009
 Relay_Log_Pos: 424
 Relay_Master_Log_File: mysql-bin.000002
 Slave_IO_Running: Yes      # 为YES状态  (说明:如果出现NO请参看下面错误解答!)
 Slave_SQL_Running: Yes   # 为YES状态
 Replicate_Do_DB: mytest
 Replicate_Ignore_DB:
 #####################################################################
                                       此时使用mysqldump的备份与还原基本完成
 五、
 ##########################在master mysql操作:##########################
 mysql> use mytest;
 mysql>insert into mytable values  ('shanghai','s','2011-09-13','china');
 mysql>select * from mytable;
 向mytest数据库的mytable表中插入一新记录;
 在slave服务器上
 mysql> use mytest;
 mysql>select * from mytable;
 得到:
 mysql> select * from mytable;
 +-----------+------+------------+---------------+
 | name      | sex  | birth      | birthaddr       | 
 +-----------+------+------------+----------------+
 | hanfeng   | b    | 1987-08-08 | china       | 
 | feiniao     | g    | 1988-08-08 | china        | 
 | shanghai | s    | 2011-09-13 | china     | 
 +-----------+------+------------+---------------+
  3 rows in set (0.01 sec)
 
  基于mysql数据库文件的主从同步与AB复制及数据备份到此完成!
  
 总结:
 ##############################  错误处理 ① ##########################
 对于       Slave_IO_Running: No
                Slave_SQL_Running: Yes
 错误解答
 Master slave 复制错误
 Description:
 Slave_IO_Running:NO
 Slave_SQL_Running:Yes
 Seconds_Behind_Master: NULL
 本人遇到的Slave_IO_Running:NO的情况有下面两种:
 1. 在配置slave同步时因为slave访问master没有权限导致;
 2. master上的mysql-bin.xxxxxx文件全被我误删除了;
 对于第一种情况,仔细检查数据库访问权限即可解决;
 对于第二种情况:
 mysql> show slave status\G
 *************************** 1. row ***************************
              Slave_IO_State: 
                 Master_Host: 192.168.0.123
                 Master_User: slave
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000016
         Read_Master_Log_Pos: 173
              Relay_Log_File: mysqld-relay-bin.000008
               Relay_Log_Pos: 98
       Relay_Master_Log_File: mysql-bin.000016
            Slave_IO_Running: No
           Slave_SQL_Running: Yes
             Replicate_Do_DB: 
         Replicate_Ignore_DB: 
          Replicate_Do_Table: 
      Replicate_Ignore_Table: br>          
                   Last_Error: 0
                Skip_Counter: 0
         Exec_Master_Log_Pos: 173
             Relay_Log_Space: 98
             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: NULL
 1 row in set (0.00 sec)
 解决步骤:
 重启master库:service mysqld restart
 mysql> show master status;
 +------------------+--------------------+----------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+-------------------+-----------------+------------------+
 | mysql-bin.000001 |       98 |              |                                         | 
 +------------------+--------------------+-----------------+------------------+
 mysql> slave stop;
 mysql> change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;
 mysql> slave start;
 mysql> show slave status\G
 *************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.0.123
                 Master_User: slave
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000001
         Read_Master_Log_Pos: 98
              Relay_Log_File: mysqld-relay-bin.000002
               Relay_Log_Pos: 235
       Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
             Replicate_Do_DB: 
     Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
 Replicate_Ignore_DB: 
          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: 98
             Relay_Log_Space: 235
             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
 1 row in set (0.00 sec)
 ##############################  错误处理 ② ##########################
 问题:如果你的mysql数据库已经有数据存储使用,将导致master与slave日志错误!
 mysql> start slave;
 ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
 mysql> show slave status;
 Empty set (0.00 sec)
 处理:删除mysql-bin.000001 日志文件,并重启mysql服务
 1、master 
 #rm -rf /var/lib/mysql/*.*         
 2、slave 
 #rm -rf /usr/local/mysql/var/*.*    
 3、service mysqld restart
相关文章
相关标签/搜索