使用xtrabackup備份和還原mysql的多執行個體

1、安裝percona-xtrabackup

# 下載下傳安裝包：

<code># cd /usr/local/src</code>

<code># wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/redhat/6/x86_64/percona-xtrabackup-2.3.2-1.el6.x86_64.rpm</code>

# 安裝依賴庫：

<code># yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL</code>

<code># wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm </code>

<code># rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm</code>

<code># rpm -ivh percona-xtrabackup-2.3.2-1.el6.x86_64.rpm</code>

# 建立備份的目錄：

<code># mkdir -p /databackup/fullbackup</code>

<code># mkdir -p /databackup/increasebackup</code>

# 建立備份使用者和授權：

<code>mysql>grant SELECT,RELOAD,SHOW DATABASES,SUPER,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,FILE on *.* to backup@</code><code>'localhost'</code> <code>identified by </code><code>'MANAGER'</code><code>;</code>

2、備份3306執行個體：

<code># innobackupex --defaults-file=/data/3306/my.cnf --socket=/data/3306/mysql.sock --user=backup --password='MANAGER' /mysqlbackup/fullbackup/3306</code>

說明：

--defaults-file，指定mysql執行個體的配置檔案

--socket，指定socket的位置

--user，指定備份的使用者

--password，指定使用者名稱

/mysqlbackup/fullbackup/3306，這裡我們将執行個體備份到/mysqlbackup/fullbackup/3306目錄下

<code># du -sh /mysqlbackup/fullbackup/3306</code>

<code>130M </code><code>/mysqlbackup/fullbackup/3306</code>

<code># ls -lrt /mysqlbackup/fullbackup/3306/*</code>

<code>-rw-r----- 1 root root 134217728 10月 12 04:32 ibdata1</code>

<code>drwx------ 2 root root 4096 10月 12 04:32 james</code>

<code>drwx------ 2 root root 4096 10月 12 04:32 mysql</code>

<code>drwx------ 2 root root 4096 10月 12 04:32 performance_schema</code>

<code>drwx------ 2 root root 4096 10月 12 04:32 opark</code>

<code>drwx------ 2 root root 4096 10月 12 04:32 dawnpro</code>

<code>drwx------ 2 root root 4096 10月 12 04:32 hangzhou_dawnpro</code>

<code>-rw-r----- 1 root root 21 10月 12 04:32 xtrabackup_binlog_info</code>

<code>-rw-r----- 1 root root 2560 10月 12 04:32 xtrabackup_logfile</code>

<code>-rw-r----- 1 root root 113 10月 12 04:32 xtrabackup_checkpoints</code>

<code>-rw-r----- 1 root root 386 10月 12 04:32 backup-my.cnf</code>

<code>-rw-r----- 1 root root 551 10月 12 04:32 xtrabackup_info</code>

3、模拟3306資料檔案丢失

說明：此時資料庫已經沒法正常運作，檢查錯誤日志，發現沒有了data目錄

161012 04:40:35 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended

161012 04:42:10 mysqld_safe Starting mysqld daemon with databases from /data/3306/data

161012 4:42:10 [Warning] Can't create test file /data/3306/data/mysql-master.lower-test

/application/mysql-5.5.32/bin/mysqld: Can't change dir to '/data/3306/data/' (Errcode: 2)

161012 4:42:10 [ERROR] Aborting

161012 4:42:10 [Note] /application/mysql-5.5.32/bin/mysqld: Shutdown complete

161012 04:42:10 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended

4、mysql全備資料恢複：

# mkdir -p /data/3306/data

# 對于一般恢複，都是直接用備份檔案還原，如果我們這裡也是直接運用該備份檔案，則可能會導緻一些意想不到的問題，比如:備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案處于不一緻的狀态，我們現在就是要通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。是以我們要用如下指令進行檢查：

<code># innobackupex --apply-log --redo-only /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/</code>

161012 04:53:07 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

prints "completed OK!".

innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)

xtrabackup: cd to /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/

xtrabackup: This target seems to be already prepared with --apply-log-only.

xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup: innodb_data_home_dir = ./

xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 50331648

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

InnoDB: Using atomics to ref count buffer pool pages

InnoDB: The InnoDB memory heap is disabled

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Memory barrier is not used

InnoDB: Compressed tables use zlib 1.2.3

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, size = 100.0M

InnoDB: Completed initialization of buffer pool

InnoDB: Highest supported file format is Barracuda.

xtrabackup: Last MySQL binlog file position 2442, file name /data/3306/mysql-bin.000008

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1606678

161012 04:53:08 completed OK!

# innobackupex --defaults-file=/data/3306/my.cnf --copy-back /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/

161012 04:56:32 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

161012 04:56:32 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0

161012 04:56:33 [01] ...done

161012 04:56:33 [01] Copying ib_logfile1 to /data/3306/data/ib_logfile1

161012 04:56:34 [01] ...done

161012 04:56:34 [01] Copying ibdata1 to /data/3306/data/ibdata1

161012 04:56:36 [01] ...done

161012 04:56:36 [01] Copying ./james/db.opt to /data/3306/data/james/db.opt

161012 04:56:36 [01] Copying ./ems/db.opt to /data/3306/data/ems/db.opt

161012 04:56:36 [01] Copying ./mysql/func.MYD to /data/3306/data/mysql/func.MYD

161012 04:56:36 [01] Copying ./mysql/time_zone.MYD to /data/3306/data/mysql/time_zone.MYD

161012 04:56:36 [01] Copying ./mysql/help_topic.MYD to /data/3306/data/mysql/help_topic.MYD

161012 04:56:36 [01] Copying ./mysql/tables_priv.MYI to /data/3306/data/mysql/tables_priv.MYI

161012 04:56:36 [01] Copying ./mysql/db.MYD to /data/3306/data/mysql/db.MYD

161012 04:56:36 [01] Copying ./mysql/proc.frm to /data/3306/data/mysql/proc.frm

161012 04:56:36 [01] Copying ./mysql/help_relation.MYI to /data/3306/data/mysql/help_relation.MYI

161012 04:56:36 [01] Copying ./mysql/proxies_priv.MYI to /data/3306/data/mysql/proxies_priv.MYI

161012 04:56:36 [01] Copying ./mysql/func.frm to /data/3306/data/mysql/func.frm

161012 04:56:36 [01] Copying ./mysql/plugin.MYD to /data/3306/data/mysql/plugin.MYD

161012 04:56:36 [01] Copying ./mysql/user.frm to /data/3306/data/mysql/user.frm

161012 04:56:36 [01] Copying ./mysql/time_zone_transition.MYD to /data/3306/data/mysql/time_zone_transition.MYD

161012 04:56:36 [01] Copying ./mysql/time_zone_transition_type.MYD to /data/3306/data/mysql/time_zone_transition_type.MYD

161012 04:56:36 [01] Copying ./mysql/general_log.CSV to /data/3306/data/mysql/general_log.CSV

161012 04:56:36 [01] Copying ./mysql/event.MYI to /data/3306/data/mysql/event.MYI

161012 04:56:36 [01] Copying ./mysql/time_zone_leap_second.frm to /data/3306/data/mysql/time_zone_leap_second.frm

161012 04:56:36 [01] Copying ./mysql/help_relation.frm to /data/3306/data/mysql/help_relation.frm

161012 04:56:36 [01] Copying ./mysql/ndb_binlog_index.MYD to /data/3306/data/mysql/ndb_binlog_index.MYD

161012 04:56:36 [01] Copying ./mysql/procs_priv.frm to /data/3306/data/mysql/procs_priv.frm

161012 04:56:36 [01] Copying ./mysql/db.frm to /data/3306/data/mysql/db.frm

161012 04:56:36 [01] Copying ./mysql/event.frm to /data/3306/data/mysql/event.frm

161012 04:56:36 [01] Copying ./mysql/plugin.MYI to /data/3306/data/mysql/plugin.MYI

161012 04:56:36 [01] Copying ./mysql/help_category.frm to /data/3306/data/mysql/help_category.frm

161012 04:56:36 [01] Copying ./mysql/time_zone_name.MYD to /data/3306/data/mysql/time_zone_name.MYD

161012 04:56:36 [01] Copying ./mysql/event.MYD to /data/3306/data/mysql/event.MYD

161012 04:56:36 [01] Copying ./mysql/proc.MYI to /data/3306/data/mysql/proc.MYI

161012 04:56:36 [01] Copying ./mysql/tables_priv.MYD to /data/3306/data/mysql/tables_priv.MYD

161012 04:56:36 [01] Copying ./mysql/servers.frm to /data/3306/data/mysql/servers.frm

161012 04:56:36 [01] Copying ./mysql/time_zone_leap_second.MYI to /data/3306/data/mysql/time_zone_leap_second.MYI

161012 04:56:37 [01] Copying ./mysql/proxies_priv.MYD to /data/3306/data/mysql/proxies_priv.MYD

161012 04:56:37 [01] ...done

161012 04:56:37 [01] Copying ./mysql/procs_priv.MYI to /data/3306/data/mysql/procs_priv.MYI

161012 04:56:37 [01] Copying ./mysql/time_zone.MYI to /data/3306/data/mysql/time_zone.MYI

161012 04:56:37 [01] Copying ./mysql/slow_log.CSV to /data/3306/data/mysql/slow_log.CSV

161012 04:56:37 [01] Copying ./mysql/time_zone_transition.frm to /data/3306/data/mysql/time_zone_transition.frm

161012 04:56:37 [01] Copying ./mysql/procs_priv.MYD to /data/3306/data/mysql/procs_priv.MYD

161012 04:56:37 [01] Copying ./mysql/help_topic.MYI to /data/3306/data/mysql/help_topic.MYI

161012 04:56:37 [01] Copying ./mysql/user.MYI to /data/3306/data/mysql/user.MYI

161012 04:56:37 [01] Copying ./mysql/help_keyword.MYD to /data/3306/data/mysql/help_keyword.MYD

161012 04:56:37 [01] Copying ./mysql/db.MYI to /data/3306/data/mysql/db.MYI

161012 04:56:37 [01] Copying ./mysql/func.MYI to /data/3306/data/mysql/func.MYI

161012 04:56:37 [01] Copying ./mysql/servers.MYI to /data/3306/data/mysql/servers.MYI

161012 04:56:37 [01] Copying ./mysql/help_category.MYD to /data/3306/data/mysql/help_category.MYD

161012 04:56:37 [01] Copying ./mysql/host.MYI to /data/3306/data/mysql/host.MYI

161012 04:56:37 [01] Copying ./mysql/time_zone.frm to /data/3306/data/mysql/time_zone.frm

161012 04:56:37 [01] Copying ./mysql/help_category.MYI to /data/3306/data/mysql/help_category.MYI

161012 04:56:37 [01] Copying ./mysql/proxies_priv.frm to /data/3306/data/mysql/proxies_priv.frm

161012 04:56:37 [01] Copying ./mysql/general_log.frm to /data/3306/data/mysql/general_log.frm

161012 04:56:37 [01] Copying ./mysql/help_keyword.frm to /data/3306/data/mysql/help_keyword.frm

161012 04:56:37 [01] Copying ./mysql/proc.MYD to /data/3306/data/mysql/proc.MYD

161012 04:56:37 [01] Copying ./mysql/columns_priv.MYI to /data/3306/data/mysql/columns_priv.MYI

161012 04:56:37 [01] Copying ./mysql/slow_log.frm to /data/3306/data/mysql/slow_log.frm

161012 04:56:37 [01] Copying ./mysql/help_topic.frm to /data/3306/data/mysql/help_topic.frm

161012 04:56:37 [01] Copying ./mysql/help_keyword.MYI to /data/3306/data/mysql/help_keyword.MYI

161012 04:56:37 [01] Copying ./mysql/time_zone_name.MYI to /data/3306/data/mysql/time_zone_name.MYI

161012 04:56:37 [01] Copying ./mysql/host.MYD to /data/3306/data/mysql/host.MYD

161012 04:56:37 [01] Copying ./mysql/columns_priv.MYD to /data/3306/data/mysql/columns_priv.MYD

161012 04:56:37 [01] Copying ./mysql/user.MYD to /data/3306/data/mysql/user.MYD

161012 04:56:37 [01] Copying ./mysql/ndb_binlog_index.frm to /data/3306/data/mysql/ndb_binlog_index.frm

161012 04:56:37 [01] Copying ./mysql/tables_priv.frm to /data/3306/data/mysql/tables_priv.frm

161012 04:56:37 [01] Copying ./mysql/general_log.CSM to /data/3306/data/mysql/general_log.CSM

161012 04:56:37 [01] Copying ./mysql/time_zone_leap_second.MYD to /data/3306/data/mysql/time_zone_leap_second.MYD

161012 04:56:37 [01] Copying ./mysql/servers.MYD to /data/3306/data/mysql/servers.MYD

161012 04:56:37 [01] Copying ./mysql/host.frm to /data/3306/data/mysql/host.frm

161012 04:56:37 [01] Copying ./mysql/time_zone_name.frm to /data/3306/data/mysql/time_zone_name.frm

161012 04:56:37 [01] Copying ./mysql/help_relation.MYD to /data/3306/data/mysql/help_relation.MYD

161012 04:56:37 [01] Copying ./mysql/time_zone_transition.MYI to /data/3306/data/mysql/time_zone_transition.MYI

161012 04:56:37 [01] Copying ./mysql/slow_log.CSM to /data/3306/data/mysql/slow_log.CSM

161012 04:56:37 [01] Copying ./mysql/ndb_binlog_index.MYI to /data/3306/data/mysql/ndb_binlog_index.MYI

161012 04:56:37 [01] Copying ./mysql/columns_priv.frm to /data/3306/data/mysql/columns_priv.frm

161012 04:56:37 [01] Copying ./mysql/time_zone_transition_type.MYI to /data/3306/data/mysql/time_zone_transition_type.MYI

161012 04:56:37 [01] Copying ./mysql/time_zone_transition_type.frm to /data/3306/data/mysql/time_zone_transition_type.frm

161012 04:56:37 [01] Copying ./mysql/plugin.frm to /data/3306/data/mysql/plugin.frm

161012 04:56:37 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /data/3306/data/performance_schema/events_waits_summary_by_instance.frm

161012 04:56:37 [01] Copying ./performance_schema/file_instances.frm to /data/3306/data/performance_schema/file_instances.frm

161012 04:56:37 [01] Copying ./performance_schema/performance_timers.frm to /data/3306/data/performance_schema/performance_timers.frm

161012 04:56:37 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /data/3306/data/performance_schema/file_summary_by_event_name.frm

161012 04:56:37 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /data/3306/data/performance_schema/events_waits_summary_by_thread_by_event_name.frm

161012 04:56:37 [01] Copying ./performance_schema/events_waits_current.frm to /data/3306/data/performance_schema/events_waits_current.frm

161012 04:56:37 [01] Copying ./performance_schema/setup_instruments.frm to /data/3306/data/performance_schema/setup_instruments.frm

161012 04:56:37 [01] Copying ./performance_schema/threads.frm to /data/3306/data/performance_schema/threads.frm

161012 04:56:37 [01] Copying ./performance_schema/file_summary_by_instance.frm to /data/3306/data/performance_schema/file_summary_by_instance.frm

161012 04:56:37 [01] Copying ./performance_schema/events_waits_history.frm to /data/3306/data/performance_schema/events_waits_history.frm

161012 04:56:37 [01] Copying ./performance_schema/rwlock_instances.frm to /data/3306/data/performance_schema/rwlock_instances.frm

161012 04:56:37 [01] Copying ./performance_schema/setup_consumers.frm to /data/3306/data/performance_schema/setup_consumers.frm

161012 04:56:37 [01] Copying ./performance_schema/setup_timers.frm to /data/3306/data/performance_schema/setup_timers.frm

161012 04:56:37 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /data/3306/data/performance_schema/events_waits_summary_global_by_event_name.frm

161012 04:56:37 [01] Copying ./performance_schema/mutex_instances.frm to /data/3306/data/performance_schema/mutex_instances.frm

161012 04:56:37 [01] Copying ./performance_schema/events_waits_history_long.frm to /data/3306/data/performance_schema/events_waits_history_long.frm

161012 04:56:37 [01] Copying ./performance_schema/cond_instances.frm to /data/3306/data/performance_schema/cond_instances.frm

161012 04:56:37 [01] Copying ./performance_schema/db.opt to /data/3306/data/performance_schema/db.opt

161012 04:56:37 [01] Copying ./wh410/db.opt to /data/3306/data/wh410/db.opt

161012 04:56:37 [01] Copying ./www/db.opt to /data/3306/data/www/db.opt

161012 04:56:37 [01] Copying ./xtrabackup_info to /data/3306/data/xtrabackup_info

161012 04:56:37 [01] Copying ./blog/db.opt to /data/3306/data/blog/db.opt

161012 04:56:37 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/3306/data/xtrabackup_binlog_pos_innodb

161012 04:56:37 [01] Copying ./opark/person.frm to /data/3306/data/opark/person.frm

161012 04:56:37 [01] Copying ./opark/db.opt to /data/3306/data/opark/db.opt

161012 04:56:37 [01] Copying ./dawnpro/db.opt to /data/3306/data/dawnpro/db.opt

161012 04:56:37 [01] Copying ./eip/db.opt to /data/3306/data/eip/db.opt

161012 04:56:37 [01] Copying ./hangzhou_dawnpro/db.opt to /data/3306/data/hangzhou_dawnpro/db.opt

161012 04:56:37 completed OK!

說明：innobackup的copy-back用于執行恢複操作時，它是通過複制所有資料相關檔案至MySQL資料目錄，是以，需要清空資料目錄。

<code>[root@mysql-master data]</code><code># ll /data/3306/data</code>

<code>drwx------ 2 root root 4096 10月 12 04:56 dawnpro</code>

<code>drwx------ 2 root root 4096 10月 12 04:56 hangzhou_dawnpro</code>

<code>-rw-r----- 1 root root 134217728 10月 12 04:56 ibdata1</code>

<code>-rw-r----- 1 root root 50331648 10月 12 04:56 ib_logfile0</code>

<code>-rw-r----- 1 root root 50331648 10月 12 04:56 ib_logfile1</code>

<code>drwx------ 2 root root 4096 10月 12 04:56 james</code>

<code>drwx------ 2 root root 4096 10月 12 04:56 mysql</code>

<code>drwx------ 2 root root 4096 10月 12 04:56 opark</code>

<code>drwx------ 2 root root 4096 10月 12 04:56 performance_schema</code>

<code>-rw-r----- 1 root root 33 10月 12 04:56 xtrabackup_bin</code>

5、更改資料庫目錄權限

<code># chown -R mysql.mysql /data/3306/data</code>

6、開啟mysql服務

啟動mysql發現沒法起來，檢查日志資訊如下（InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes）：

# tail -15 mysql_oldboy3306.err

161012 5:00:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins

161012 5:00:15 InnoDB: Compressed tables use zlib 1.2.3

161012 5:00:15 InnoDB: Using Linux native AIO

161012 5:00:15 InnoDB: Initializing buffer pool, size = 32.0M

161012 5:00:15 InnoDB: Completed initialization of buffer pool

InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes

InnoDB: than specified in the .cnf file 0 4194304 bytes!

161012 5:00:15 [ERROR] Plugin 'InnoDB' init function returned error.

161012 5:00:15 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

161012 5:00:15 [ERROR] Unknown/unsupported storage engine: InnoDB

161012 5:00:15 [ERROR] Aborting

161012 5:00:15 [Note] /application/mysql-5.5.32/bin/mysqld: Shutdown complete

161012 05:00:15 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended

<code># rm ib_logfile* -rf</code>

<code># /data/3306/mysql start</code>

<code># netstat -tunlp| grep 3306</code>

<code>tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 29697</code><code>/mysqld</code>

7、進入資料庫進行驗證

<code># mysql -uroot -predhat12345 -S /data/3306/mysql.sock</code>

<code>mysql> show databases;</code>

<code>| Database |</code>

<code>| information_schema |</code>

<code>| dawnpro |</code>

<code>| hangzhou_dawnpro |</code>

<code>| james |</code>

<code>| mysql |</code>

<code>| opark |</code>

<code>| performance_schema |</code>

本文轉自冰凍vs西瓜 51CTO部落格，原文連結：http://blog.51cto.com/molewan/1940418，如需轉載請自行聯系原作者

使用xtrabackup備份和還原mysql的多執行個體

繼續閱讀

禁止ubuntu系統彈出報錯界面

MySQL的4種隔離級别？出現問題

Ubuntu Linux下Apache的配置檔案

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

samba伺服器的功能

登入plsql 報錯 the account is locked --使用者被鎖

【Linux】UDP廣播封包接收速率問題

SequoiaDB巨杉資料庫C++驅動概述

Linux裝置模型（中）之上層容器

PowerPC平台 Linux移植三