MYSQL利用XTRBACKUP进行重建主从

最新推荐文章于 2024-06-01 15:29:46 发布

wshxgxiaoli

最新推荐文章于 2024-06-01 15:29:46 发布

阅读量973

点赞数

分类专栏： MYSQL 文章标签： XTRABACKUP 重建主从

本文链接：https://blog.csdn.net/wshxgxiaoli/article/details/18400311

版权

MYSQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1. 备份主库：

innobackupex --user=root --password=vipshop --socket=/tmp/mysql3306.sock --defaults-file=/apps/conf/mysql/my3306.cnf /apps/

２.　在主库中添加用户：

 grant replication slave on *.* to 'replication'@'%' identified by 'replication';

 grant all privileges on *.* to replication@'%' identified by 'replication';

３.　关闭从库，移走数据文件。

     mv mysql5_data3306 mysql5_data3306bak
     mkdir mysql5_data3306

４.　修改参数文件。
修改SERVER ID
修改数据文件目录，和原库保持一致。

５.　APPLY LOG

innobackupex --user=root --password=GAre79@#$gtyu$523e##443hg  --defaults-file=/apps/conf/mysql/my3306.cnf --apply-log /apps/2013-11-22_16-54-12


[apps@gd1ebs132 apps]$ innobackupex --user=root --password=GAre79@#$gtyu$523e##443hg  --defaults-file=/apps/conf/mysql/my3306.cnf --copy-back /apps/2013-11-22_16-54-12

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

Warning: xtrabackup: ignoring option '--innodb_adaptive_hash_index' due to invalid value 'ON'
innobackupex: Starting to copy files in '/apps/2013-11-22_16-54-12'
innobackupex: back to original data directory '/apps/dbdat/mysql5_data3306'
innobackupex: Creating directory '/apps/dbdat/mysql5_data3306/log'
innobackupex: Creating directory '/apps/dbdat/mysql5_data3306/performance_schema'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/events_waits_history.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/events_waits_history.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/cond_instances.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/cond_instances.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/events_waits_summary_by_instance.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/events_waits_summary_by_instance.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/events_waits_current.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/events_waits_current.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/events_waits_summary_global_by_event_name.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/events_waits_summary_global_by_event_name.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/setup_timers.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/setup_timers.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/rwlock_instances.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/rwlock_instances.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/setup_instruments.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/setup_instruments.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/performance_timers.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/performance_timers.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/setup_consumers.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/setup_consumers.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/file_instances.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/file_instances.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/file_summary_by_instance.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/file_summary_by_instance.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/events_waits_summary_by_thread_by_event_name.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/events_waits_summary_by_thread_by_event_name.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/events_waits_history_long.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/events_waits_history_long.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/db.opt' to '/apps/dbdat/mysql5_data3306/performance_schema/db.opt'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/threads.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/threads.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/file_summary_by_event_name.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/file_summary_by_event_name.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/performance_schema/mutex_instances.frm' to '/apps/dbdat/mysql5_data3306/performance_schema/mutex_instances.frm'
innobackupex: Creating directory '/apps/dbdat/mysql5_data3306/test'
innobackupex: Copying '/apps/2013-11-22_16-54-12/test/it.frm' to '/apps/dbdat/mysql5_data3306/test/it.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/test/it.ibd' to '/apps/dbdat/mysql5_data3306/test/it.ibd'
innobackupex: Creating directory '/apps/dbdat/mysql5_data3306/mysql'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/procs_priv.frm' to '/apps/dbdat/mysql5_data3306/mysql/procs_priv.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_name.MYD' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_name.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/user.MYI' to '/apps/dbdat/mysql5_data3306/mysql/user.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_leap_second.MYD' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_leap_second.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/tables_priv.MYD' to '/apps/dbdat/mysql5_data3306/mysql/tables_priv.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/host.frm' to '/apps/dbdat/mysql5_data3306/mysql/host.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/event.MYI' to '/apps/dbdat/mysql5_data3306/mysql/event.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/ndb_binlog_index.MYD' to '/apps/dbdat/mysql5_data3306/mysql/ndb_binlog_index.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_transition_type.MYI' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_transition_type.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_topic.MYI' to '/apps/dbdat/mysql5_data3306/mysql/help_topic.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_topic.frm' to '/apps/dbdat/mysql5_data3306/mysql/help_topic.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/general_log.CSV' to '/apps/dbdat/mysql5_data3306/mysql/general_log.CSV'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/procs_priv.MYI' to '/apps/dbdat/mysql5_data3306/mysql/procs_priv.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_category.MYI' to '/apps/dbdat/mysql5_data3306/mysql/help_category.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/proxies_priv.MYD' to '/apps/dbdat/mysql5_data3306/mysql/proxies_priv.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/user.MYD' to '/apps/dbdat/mysql5_data3306/mysql/user.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/plugin.frm' to '/apps/dbdat/mysql5_data3306/mysql/plugin.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/plugin.MYI' to '/apps/dbdat/mysql5_data3306/mysql/plugin.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/slow_log.frm' to '/apps/dbdat/mysql5_data3306/mysql/slow_log.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/db.MYD' to '/apps/dbdat/mysql5_data3306/mysql/db.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/servers.frm' to '/apps/dbdat/mysql5_data3306/mysql/servers.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/user.frm' to '/apps/dbdat/mysql5_data3306/mysql/user.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/columns_priv.MYI' to '/apps/dbdat/mysql5_data3306/mysql/columns_priv.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_transition_type.frm' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_transition_type.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/proc.MYD' to '/apps/dbdat/mysql5_data3306/mysql/proc.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/proxies_priv.frm' to '/apps/dbdat/mysql5_data3306/mysql/proxies_priv.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_keyword.MYD' to '/apps/dbdat/mysql5_data3306/mysql/help_keyword.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/slow_log.CSV' to '/apps/dbdat/mysql5_data3306/mysql/slow_log.CSV'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/func.frm' to '/apps/dbdat/mysql5_data3306/mysql/func.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/db.MYI' to '/apps/dbdat/mysql5_data3306/mysql/db.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_category.frm' to '/apps/dbdat/mysql5_data3306/mysql/help_category.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_transition.frm' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_transition.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/procs_priv.MYD' to '/apps/dbdat/mysql5_data3306/mysql/procs_priv.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/servers.MYD' to '/apps/dbdat/mysql5_data3306/mysql/servers.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/columns_priv.MYD' to '/apps/dbdat/mysql5_data3306/mysql/columns_priv.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_name.MYI' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_name.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/slow_log.CSM' to '/apps/dbdat/mysql5_data3306/mysql/slow_log.CSM'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_transition_type.MYD' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_transition_type.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/general_log.CSM' to '/apps/dbdat/mysql5_data3306/mysql/general_log.CSM'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone.MYD' to '/apps/dbdat/mysql5_data3306/mysql/time_zone.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/host.MYD' to '/apps/dbdat/mysql5_data3306/mysql/host.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/plugin.MYD' to '/apps/dbdat/mysql5_data3306/mysql/plugin.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/proxies_priv.MYI' to '/apps/dbdat/mysql5_data3306/mysql/proxies_priv.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/proc.frm' to '/apps/dbdat/mysql5_data3306/mysql/proc.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/event.frm' to '/apps/dbdat/mysql5_data3306/mysql/event.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_transition.MYI' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_transition.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_leap_second.frm' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_leap_second.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/func.MYI' to '/apps/dbdat/mysql5_data3306/mysql/func.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_relation.frm' to '/apps/dbdat/mysql5_data3306/mysql/help_relation.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_relation.MYD' to '/apps/dbdat/mysql5_data3306/mysql/help_relation.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone.frm' to '/apps/dbdat/mysql5_data3306/mysql/time_zone.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/servers.MYI' to '/apps/dbdat/mysql5_data3306/mysql/servers.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone.MYI' to '/apps/dbdat/mysql5_data3306/mysql/time_zone.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/event.MYD' to '/apps/dbdat/mysql5_data3306/mysql/event.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/tables_priv.frm' to '/apps/dbdat/mysql5_data3306/mysql/tables_priv.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_keyword.frm' to '/apps/dbdat/mysql5_data3306/mysql/help_keyword.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/columns_priv.frm' to '/apps/dbdat/mysql5_data3306/mysql/columns_priv.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_category.MYD' to '/apps/dbdat/mysql5_data3306/mysql/help_category.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/proc.MYI' to '/apps/dbdat/mysql5_data3306/mysql/proc.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_relation.MYI' to '/apps/dbdat/mysql5_data3306/mysql/help_relation.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_keyword.MYI' to '/apps/dbdat/mysql5_data3306/mysql/help_keyword.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/db.frm' to '/apps/dbdat/mysql5_data3306/mysql/db.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_transition.MYD' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_transition.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/func.MYD' to '/apps/dbdat/mysql5_data3306/mysql/func.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_leap_second.MYI' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_leap_second.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/ndb_binlog_index.MYI' to '/apps/dbdat/mysql5_data3306/mysql/ndb_binlog_index.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/help_topic.MYD' to '/apps/dbdat/mysql5_data3306/mysql/help_topic.MYD'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/host.MYI' to '/apps/dbdat/mysql5_data3306/mysql/host.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/general_log.frm' to '/apps/dbdat/mysql5_data3306/mysql/general_log.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/tables_priv.MYI' to '/apps/dbdat/mysql5_data3306/mysql/tables_priv.MYI'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/ndb_binlog_index.frm' to '/apps/dbdat/mysql5_data3306/mysql/ndb_binlog_index.frm'
innobackupex: Copying '/apps/2013-11-22_16-54-12/mysql/time_zone_name.frm' to '/apps/dbdat/mysql5_data3306/mysql/time_zone_name.frm'

innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/apps/2013-11-22_16-54-12'
innobackupex: back to original InnoDB data directory '/apps/dbdat/mysql5_data3306'
innobackupex: Copying file '/apps/2013-11-22_16-54-12/ibdata1'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/apps/2013-11-22_16-54-12'
innobackupex: back to original InnoDB log directory '/apps/dbdat/mysql5_data3306'
innobackupex: Finished copying back files.

131122 16:58:35  innobackupex: completed OK!

启动备库

[apps@gd1ebs132 sh]$ ./mysql5.sh start

修改MASTER并开启同步。在启动同步的时候，去查看下备份时的BINLOG位置。

[apps@gd1ebs132 2013-11-22_16-54-12]$ cat xtrabackup_binlog_info 
mysql-bin.000001        193

CHANGE MASTER的时候就是根据这个文件的提示进行启动。

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.93',MASTER_PORT=3306,MASTER_USER='replication',MASTER_PASSWORD='replication',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=193;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.93
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 571
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 631
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 571
              Relay_Log_Space: 781
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 621133306
1 row in set (0.00 sec)

验证同步：

主库创建表：
mysql> create table ti as select * from it;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

从库查询此表。
mysql> select * from ti;
Empty set (0.00 sec)