1）安裝percona-xtrabackup
<code>Xtrabackup是一個對InnoDB做資料備份的工具，支援線上熱備份(備份時不影響資料讀寫)，是商業備份</code>
<code>工具InnoDB Hotbackup的一個很好的替代品</code>
<code># 下載下傳安裝包：</code>
<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># 安裝依賴庫：</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># 建立備份的目錄：</code>
<code># mkdir -p /databackup/fullbackup</code>
<code># mkdir -p /databackup/increasebackup</code>
<code># 建立備份使用者和授權：</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>
<code>說明：</code>
<code>--defaults-</code><code>file</code><code>，指定mysql執行個體的配置檔案</code>
<code>--socket，指定socket的位置</code>
<code>--user，指定備份的使用者</code>
<code>--password，指定使用者名稱</code>
<code>/mysqlbackup/fullbackup/3306</code><code>，這裡我們将執行個體備份到</code><code>/mysqlbackup/fullbackup/3306</code><code>目錄下</code>
<code># du -sh /mysqlbackup/fullbackup/3306</code>
<code>130M</code><code>/mysqlbackup/fullbackup/3306</code>
<code># ls -lrt /mysqlbackup/fullbackup/3306/*</code>
<code>總用量 131136</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 ems</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 wh410</code>
<code>drwx------ 2 root root 4096 10月 12 04:32 www</code>
<code>drwx------ 2 root root 4096 10月 12 04:32 blog</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 eip</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資料檔案丢失
<code># rm -rf /data/3306/data</code>
<code># lsof -i :3306</code>
<code>說明：此時資料庫已經沒法正常運作，檢查錯誤日志，發現沒有了data目錄</code>
<code>161012 04:40:35 mysqld_safe mysqld from pid </code><code>file</code> <code>/data/3306/mysqld</code><code>.pid ended</code>
<code>161012 04:42:10 mysqld_safe Starting mysqld daemon with databases from </code><code>/data/3306/data</code>
<code>161012 4:42:10 [Warning] Can't create </code><code>test</code> <code>file</code> <code>/data/3306/data/mysql-master</code><code>.lower-</code><code>test</code>
<code>/application/mysql-5</code><code>.5.32</code><code>/bin/mysqld</code><code>: Can</code><code>'t change dir to '</code><code>/data/3306/data/</code><code>' (Errcode: 2)</code>
<code>161012 4:42:10 [ERROR] Aborting</code>
<code>161012 4:42:10 [Note] </code><code>/application/mysql-5</code><code>.5.32</code><code>/bin/mysqld</code><code>: Shutdown complete</code>
<code>161012 04:42:10 mysqld_safe mysqld from pid </code><code>file</code> <code>/data/3306/mysqld</code><code>.pid ended</code>
4）mysql全備資料恢複：
<code># mkdir -p /data/3306/data</code>
<code># 對于一般恢複，都是直接用備份檔案還原，如果我們這裡也是直接運用該備份檔案，則可能會導緻</code>
<code>一些意想不到的問題，比如:備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料</code>
<code>檔案中的事務。是以，此時資料檔案處于不一緻的狀态，我們現在就是要通過復原未送出的事務及同步</code>
<code>已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。是以我們要用如下指令進行檢查：</code>
<code># innobackupex --apply-log --redo-only /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/</code>
<code>161012 04:53:07 innobackupex: Starting the apply-log operation</code>
<code>IMPORTANT: Please check that the apply-log run completes successfully.</code>
<code> </code><code>At the end of a successful apply-log run innobackupex</code>
<code> </code><code>prints </code><code>"completed OK!"</code><code>.</code>
<code>innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision </code><code>id</code><code>: 306a2e0)</code>
<code>xtrabackup: </code><code>cd</code> <code>to </code><code>/mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/</code>
<code>xtrabackup: This target seems to be already prepared with --apply-log-only.</code>
<code>xtrabackup: notice: xtrabackup_logfile was already used to </code><code>'--prepare'</code><code>.</code>
<code>xtrabackup: using the following InnoDB configuration </code><code>for</code> <code>recovery:</code>
<code>xtrabackup: innodb_data_home_dir = ./</code>
<code>xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend</code>
<code>xtrabackup: innodb_log_group_home_dir = ./</code>
<code>xtrabackup: innodb_log_files_in_group = 2</code>
<code>xtrabackup: innodb_log_file_size = 50331648</code>
<code>xtrabackup: Starting InnoDB instance </code><code>for</code> <code>recovery.</code>
<code>xtrabackup: Using 104857600 bytes </code><code>for</code> <code>buffer pool (</code><code>set</code> <code>by --use-memory parameter)</code>
<code>InnoDB: Using atomics to ref count buffer pool pages</code>
<code>InnoDB: The InnoDB memory heap is disabled</code>
<code>InnoDB: Mutexes and rw_locks use GCC atomic builtins</code>
<code>InnoDB: Memory barrier is not used</code>
<code>InnoDB: Compressed tables use zlib 1.2.3</code>
<code>InnoDB: Using CPU crc32 instructions</code>
<code>InnoDB: Initializing buffer pool, size = 100.0M</code>
<code>InnoDB: Completed initialization of buffer pool</code>
<code>InnoDB: Highest supported </code><code>file</code> <code>format</code> <code>is Barracuda.</code>
<code>xtrabackup: Last MySQL binlog </code><code>file</code> <code>position 2442, </code><code>file</code> <code>name </code><code>/data/3306/mysql-bin</code><code>.000008</code>
<code>xtrabackup: starting </code><code>shutdown</code> <code>with innodb_fast_shutdown = 1</code>
<code>InnoDB: Starting </code><code>shutdown</code><code>...</code>
<code>InnoDB: Shutdown completed; log sequence number 1606678</code>
<code>161012 04:53:08 completed OK!</code>
<code># innobackupex --defaults-file=/data/3306/my.cnf --copy-back /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/</code>
<code>161012 04:56:32 innobackupex: Starting the copy-back operation</code>
<code>IMPORTANT: Please check that the copy-back run completes successfully.</code>
<code> </code><code>At the end of a successful copy-back run innobackupex</code>
<code>161012 04:56:32 [01] Copying ib_logfile0 to </code><code>/data/3306/data/ib_logfile0</code>
<code>161012 04:56:33 [01] ...</code><code>done</code>
<code>161012 04:56:33 [01] Copying ib_logfile1 to </code><code>/data/3306/data/ib_logfile1</code>
<code>161012 04:56:34 [01] ...</code><code>done</code>
<code>161012 04:56:34 [01] Copying ibdata1 to </code><code>/data/3306/data/ibdata1</code>
<code>161012 04:56:36 [01] ...</code><code>done</code>
<code>161012 04:56:36 [01] Copying .</code><code>/james/db</code><code>.opt to </code><code>/data/3306/data/james/db</code><code>.opt</code>
<code>161012 04:56:36 [01] Copying .</code><code>/ems/db</code><code>.opt to </code><code>/data/3306/data/ems/db</code><code>.opt</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/func</code><code>.MYD to </code><code>/data/3306/data/mysql/func</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/time_zone</code><code>.MYD to </code><code>/data/3306/data/mysql/time_zone</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/help_topic</code><code>.MYD to </code><code>/data/3306/data/mysql/help_topic</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/tables_priv</code><code>.MYI to </code><code>/data/3306/data/mysql/tables_priv</code><code>.MYI</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/db</code><code>.MYD to </code><code>/data/3306/data/mysql/db</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/proc</code><code>.frm to </code><code>/data/3306/data/mysql/proc</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/help_relation</code><code>.MYI to </code><code>/data/3306/data/mysql/help_relation</code><code>.MYI</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/proxies_priv</code><code>.MYI to </code><code>/data/3306/data/mysql/proxies_priv</code><code>.MYI</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/func</code><code>.frm to </code><code>/data/3306/data/mysql/func</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/plugin</code><code>.MYD to </code><code>/data/3306/data/mysql/plugin</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/user</code><code>.frm to </code><code>/data/3306/data/mysql/user</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/time_zone_transition</code><code>.MYD to </code><code>/data/3306/data/mysql/time_zone_transition</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/time_zone_transition_type</code><code>.MYD to </code><code>/data/3306/data/mysql/time_zone_transition_type</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/general_log</code><code>.CSV to </code><code>/data/3306/data/mysql/general_log</code><code>.CSV</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/event</code><code>.MYI to </code><code>/data/3306/data/mysql/event</code><code>.MYI</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/time_zone_leap_second</code><code>.frm to </code><code>/data/3306/data/mysql/time_zone_leap_second</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/help_relation</code><code>.frm to </code><code>/data/3306/data/mysql/help_relation</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/ndb_binlog_index</code><code>.MYD to </code><code>/data/3306/data/mysql/ndb_binlog_index</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/procs_priv</code><code>.frm to </code><code>/data/3306/data/mysql/procs_priv</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/db</code><code>.frm to </code><code>/data/3306/data/mysql/db</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/event</code><code>.frm to </code><code>/data/3306/data/mysql/event</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/plugin</code><code>.MYI to </code><code>/data/3306/data/mysql/plugin</code><code>.MYI</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/help_category</code><code>.frm to </code><code>/data/3306/data/mysql/help_category</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/time_zone_name</code><code>.MYD to </code><code>/data/3306/data/mysql/time_zone_name</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/event</code><code>.MYD to </code><code>/data/3306/data/mysql/event</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/proc</code><code>.MYI to </code><code>/data/3306/data/mysql/proc</code><code>.MYI</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/tables_priv</code><code>.MYD to </code><code>/data/3306/data/mysql/tables_priv</code><code>.MYD</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/servers</code><code>.frm to </code><code>/data/3306/data/mysql/servers</code><code>.frm</code>
<code>161012 04:56:36 [01] Copying .</code><code>/mysql/time_zone_leap_second</code><code>.MYI to </code><code>/data/3306/data/mysql/time_zone_leap_second</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/proxies_priv</code><code>.MYD to </code><code>/data/3306/data/mysql/proxies_priv</code><code>.MYD</code>
<code>161012 04:56:37 [01] ...</code><code>done</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/procs_priv</code><code>.MYI to </code><code>/data/3306/data/mysql/procs_priv</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/time_zone</code><code>.MYI to </code><code>/data/3306/data/mysql/time_zone</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/slow_log</code><code>.CSV to </code><code>/data/3306/data/mysql/slow_log</code><code>.CSV</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/time_zone_transition</code><code>.frm to </code><code>/data/3306/data/mysql/time_zone_transition</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/procs_priv</code><code>.MYD to </code><code>/data/3306/data/mysql/procs_priv</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/help_topic</code><code>.MYI to </code><code>/data/3306/data/mysql/help_topic</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/user</code><code>.MYI to </code><code>/data/3306/data/mysql/user</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/help_keyword</code><code>.MYD to </code><code>/data/3306/data/mysql/help_keyword</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/db</code><code>.MYI to </code><code>/data/3306/data/mysql/db</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/func</code><code>.MYI to </code><code>/data/3306/data/mysql/func</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/servers</code><code>.MYI to </code><code>/data/3306/data/mysql/servers</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/help_category</code><code>.MYD to </code><code>/data/3306/data/mysql/help_category</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/host</code><code>.MYI to </code><code>/data/3306/data/mysql/host</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/time_zone</code><code>.frm to </code><code>/data/3306/data/mysql/time_zone</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/help_category</code><code>.MYI to </code><code>/data/3306/data/mysql/help_category</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/proxies_priv</code><code>.frm to </code><code>/data/3306/data/mysql/proxies_priv</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/general_log</code><code>.frm to </code><code>/data/3306/data/mysql/general_log</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/help_keyword</code><code>.frm to </code><code>/data/3306/data/mysql/help_keyword</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/proc</code><code>.MYD to </code><code>/data/3306/data/mysql/proc</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/columns_priv</code><code>.MYI to </code><code>/data/3306/data/mysql/columns_priv</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/slow_log</code><code>.frm to </code><code>/data/3306/data/mysql/slow_log</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/help_topic</code><code>.frm to </code><code>/data/3306/data/mysql/help_topic</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/help_keyword</code><code>.MYI to </code><code>/data/3306/data/mysql/help_keyword</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/time_zone_name</code><code>.MYI to </code><code>/data/3306/data/mysql/time_zone_name</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/host</code><code>.MYD to </code><code>/data/3306/data/mysql/host</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/columns_priv</code><code>.MYD to </code><code>/data/3306/data/mysql/columns_priv</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/user</code><code>.MYD to </code><code>/data/3306/data/mysql/user</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/ndb_binlog_index</code><code>.frm to </code><code>/data/3306/data/mysql/ndb_binlog_index</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/tables_priv</code><code>.frm to </code><code>/data/3306/data/mysql/tables_priv</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/general_log</code><code>.CSM to </code><code>/data/3306/data/mysql/general_log</code><code>.CSM</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/time_zone_leap_second</code><code>.MYD to </code><code>/data/3306/data/mysql/time_zone_leap_second</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/servers</code><code>.MYD to </code><code>/data/3306/data/mysql/servers</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/host</code><code>.frm to </code><code>/data/3306/data/mysql/host</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/time_zone_name</code><code>.frm to </code><code>/data/3306/data/mysql/time_zone_name</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/help_relation</code><code>.MYD to </code><code>/data/3306/data/mysql/help_relation</code><code>.MYD</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/time_zone_transition</code><code>.MYI to </code><code>/data/3306/data/mysql/time_zone_transition</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/slow_log</code><code>.CSM to </code><code>/data/3306/data/mysql/slow_log</code><code>.CSM</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/ndb_binlog_index</code><code>.MYI to </code><code>/data/3306/data/mysql/ndb_binlog_index</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/columns_priv</code><code>.frm to </code><code>/data/3306/data/mysql/columns_priv</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/time_zone_transition_type</code><code>.MYI to </code><code>/data/3306/data/mysql/time_zone_transition_type</code><code>.MYI</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/time_zone_transition_type</code><code>.frm to </code><code>/data/3306/data/mysql/time_zone_transition_type</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/mysql/plugin</code><code>.frm to </code><code>/data/3306/data/mysql/plugin</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/events_waits_summary_by_instance</code><code>.frm to </code><code>/data/3306/data/performance_schema/events_waits_summary_by_instance</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/file_instances</code><code>.frm to </code><code>/data/3306/data/performance_schema/file_instances</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/performance_timers</code><code>.frm to </code><code>/data/3306/data/performance_schema/performance_timers</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/file_summary_by_event_name</code><code>.frm to </code><code>/data/3306/data/performance_schema/file_summary_by_event_name</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/events_waits_summary_by_thread_by_event_name</code><code>.frm to </code><code>/data/3306/data/performance_schema/events_waits_summary_by_thread_by_event_name</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/events_waits_current</code><code>.frm to </code><code>/data/3306/data/performance_schema/events_waits_current</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/setup_instruments</code><code>.frm to </code><code>/data/3306/data/performance_schema/setup_instruments</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/threads</code><code>.frm to </code><code>/data/3306/data/performance_schema/threads</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/file_summary_by_instance</code><code>.frm to </code><code>/data/3306/data/performance_schema/file_summary_by_instance</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/events_waits_history</code><code>.frm to </code><code>/data/3306/data/performance_schema/events_waits_history</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/rwlock_instances</code><code>.frm to </code><code>/data/3306/data/performance_schema/rwlock_instances</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/setup_consumers</code><code>.frm to </code><code>/data/3306/data/performance_schema/setup_consumers</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/setup_timers</code><code>.frm to </code><code>/data/3306/data/performance_schema/setup_timers</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/events_waits_summary_global_by_event_name</code><code>.frm to </code><code>/data/3306/data/performance_schema/events_waits_summary_global_by_event_name</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/mutex_instances</code><code>.frm to </code><code>/data/3306/data/performance_schema/mutex_instances</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/events_waits_history_long</code><code>.frm to </code><code>/data/3306/data/performance_schema/events_waits_history_long</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/cond_instances</code><code>.frm to </code><code>/data/3306/data/performance_schema/cond_instances</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/performance_schema/db</code><code>.opt to </code><code>/data/3306/data/performance_schema/db</code><code>.opt</code>
<code>161012 04:56:37 [01] Copying .</code><code>/wh410/db</code><code>.opt to </code><code>/data/3306/data/wh410/db</code><code>.opt</code>
<code>161012 04:56:37 [01] Copying .</code><code>/www/db</code><code>.opt to </code><code>/data/3306/data/www/db</code><code>.opt</code>
<code>161012 04:56:37 [01] Copying .</code><code>/xtrabackup_info</code> <code>to </code><code>/data/3306/data/xtrabackup_info</code>
<code>161012 04:56:37 [01] Copying .</code><code>/blog/db</code><code>.opt to </code><code>/data/3306/data/blog/db</code><code>.opt</code>
<code>161012 04:56:37 [01] Copying .</code><code>/xtrabackup_binlog_pos_innodb</code> <code>to </code><code>/data/3306/data/xtrabackup_binlog_pos_innodb</code>
<code>161012 04:56:37 [01] Copying .</code><code>/opark/person</code><code>.frm to </code><code>/data/3306/data/opark/person</code><code>.frm</code>
<code>161012 04:56:37 [01] Copying .</code><code>/opark/db</code><code>.opt to </code><code>/data/3306/data/opark/db</code><code>.opt</code>
<code>161012 04:56:37 [01] Copying .</code><code>/dawnpro/db</code><code>.opt to </code><code>/data/3306/data/dawnpro/db</code><code>.opt</code>
<code>161012 04:56:37 [01] Copying .</code><code>/eip/db</code><code>.opt to </code><code>/data/3306/data/eip/db</code><code>.opt</code>
<code>161012 04:56:37 [01] Copying .</code><code>/hangzhou_dawnpro/db</code><code>.opt to </code><code>/data/3306/data/hangzhou_dawnpro/db</code><code>.opt</code>
<code>161012 04:56:37 completed OK!</code>
<code>說明：innobackup的copy-back用于執行恢複操作時，它是通過複制所有資料相關檔案至MySQL資料目錄，是以，需要清空資料目錄。</code>
<code>[root@mysql-master data]</code><code># ll /data/3306/data</code>
<code>總用量 229432</code>
<code>drwx------ 2 root root 4096 10月 12 04:56 blog</code>
<code>drwx------ 2 root root 4096 10月 12 04:56 dawnpro</code>
<code>drwx------ 2 root root 4096 10月 12 04:56 eip</code>
<code>drwx------ 2 root root 4096 10月 12 04:56 ems</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>drwx------ 2 root root 4096 10月 12 04:56 wh410</code>
<code>drwx------ 2 root root 4096 10月 12 04:56 www</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>
<code>這一步很重要，一定要記得</code>
6) 開啟mysql服務
<code># /data/3306/mysql start</code>
<code>啟動mysql發現沒法起來，檢查日志資訊如下（報錯資訊：InnoDB: Error: log </code><code>file</code> <code>.</code><code>/ib_logfile0</code> <code>is of different size 0 50331648 bytes）：</code>
<code>161012 5:00:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins</code>
<code>161012 5:00:15 InnoDB: Compressed tables use zlib 1.2.3</code>
<code>161012 5:00:15 InnoDB: Using Linux native AIO</code>
<code>161012 5:00:15 InnoDB: Initializing buffer pool, size = 32.0M</code>
<code>161012 5:00:15 InnoDB: Completed initialization of buffer pool</code>
<code>InnoDB: Error: log </code><code>file</code> <code>.</code><code>/ib_logfile0</code> <code>is of different size 0 50331648 bytes</code>
<code>InnoDB: than specified </code><code>in</code> <code>the .cnf </code><code>file</code> <code>0 4194304 bytes!</code>
<code>161012 5:00:15 [ERROR] Plugin </code><code>'InnoDB'</code> <code>init </code><code>function</code> <code>returned error.</code>
<code>161012 5:00:15 [ERROR] Plugin </code><code>'InnoDB'</code> <code>registration as a STORAGE ENGINE failed.</code>
<code>161012 5:00:15 [ERROR] Unknown</code><code>/unsupported</code> <code>storage engine: InnoDB</code>
<code>161012 5:00:15 [ERROR] Aborting</code>
<code>161012 5:00:15 [Note] </code><code>/application/mysql-5</code><code>.5.32</code><code>/bin/mysqld</code><code>: Shutdown complete</code>
<code>161012 05:00:15 mysqld_safe mysqld from pid </code><code>file</code> <code>/data/3306/mysqld</code><code>.pid ended</code>
解決方法：
<code># rm ib_logfile* -rf</code>
<code>推薦的方法：不删除而直接重命名</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>+--------------------+</code>
<code>| Database |</code>
<code>| information_schema |</code>
<code>| blog |</code>
<code>| dawnpro |</code>
<code>| eip |</code>
<code>| ems |</code>
<code>| hangzhou_dawnpro |</code>
<code>| james |</code>
<code>| mysql |</code>
<code>| opark |</code>
<code>| performance_schema |</code>
<code>| wh410 |</code>
<code>| www |</code>
<code>12 rows </code><code>in</code> <code>set</code> <code>(0.01 sec)</code>
本文轉自 冰凍vs西瓜 51CTO部落格，原文連結：http://blog.51cto.com/molewan/1861487，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)