一：XtraBackup是一個物理備份工具，cp和tar也屬於物理備份，上一章使用的mysqldump是邏輯備份工具，mysqldump可以對innodb和myisam引擎做溫備，而XtraBackup則可以對線上MySQL做物理熱備操作，XtraBackup的開發者是Percona，Percona是一個在MySQL領域是僅次於官方的一個組織，其在開源版的MySQL重新開發併發布了Percona Server for MySQL，其使用的引擎是xtraDB而不是原生的InoDB，MariaDB的引擎也是使用的XtraDB，不過XtraBackup工具對InnodB和xtraDB的備份都支持，但是XtraBackup需要MySQL是在線才可以備份即不能離線備份，XtraBackup是一個免費的、開源的、可以在線上實現無阻塞備份的備份解決方案，其針對事務性引擎主要優勢：
<code>快速可靠高度安全的備份
備份期間的不間斷事務處理
以更好的壓縮節省磁盤空間和網絡帶寬
自動備份驗證
更快的數據恢復時間/<code>
1.1：安裝並對數據庫進行備份：
1.1.1：下載並安裝：
下載地址：https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.5/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm
1.1.2：查看安裝了哪些命令：
<code>[root@sql-master ~]# rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex #功能比較少的備份工具，是一個簡單的perl腳本
/usr/bin/xbcloud #輔助插件
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup #主要就是使用這個命令，使用C編寫
/usr/share/doc/percona-xtrabackup-24-2.4.5
/usr/share/doc/percona-xtrabackup-24-2.4.5/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz/<code>
1.1.3：確認數據庫的引擎類型：
只有InnoDB才可以實現熱備和增量，MyISAM僅能實現溫備和完整備份，即使是使用的增量但也是完整備份。
1.1.4：開啟為每個庫的每個表保存一個單獨的表空間，需要提前在/etc/my.cnf文件中添加以下選項：
<code>innodb_file_per_table = 1/<code>
1.2：實現完整備份並將數據在另外一臺MySQL服務器進行還原：
#xtrabackup在備份時是根據日誌序列號來識別是完全備份還是增量備份的，也是使用日誌序列號來保證增量備份可以在線進行的。
1.2.1：完整備份：
<code>[root@sql-master ~]# innobackupex --user=root --password=123456 /mysqlbackup/
170123 05:02:02 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
170123 05:02:02 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/lib/mysql/mysql.sock' as 'root' (using password: YES).
170123 05:02:02 version_check Connected to MySQL server
170123 05:02:02 version_check Executing a version check against the server...
170123 05:02:02 version_check Done.
170123 05:02:02 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /var/lib/mysql/mysql.sock
Using server version 5.6.34-log
innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
170123 05:02:02 >> log scanned up to (1802325)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
170123 05:02:02 [01] Copying ./ibdata1 to /mysqlbackup//2017-01-23_05-02-02/ibdata1
170123 05:02:03 [01] ...done
170123 05:02:03 >> log scanned up to (1802325)
170123 05:02:03 [01] Copying ./mysql/innodb_table_stats.ibd to /mysqlbackup//2017-01-23_05-02-02/mysql/innodb_table_stats.ibd
170123 05:02:03 [01] ...done
170123 05:02:03 [01] Copying ./mysql/innodb_index_stats.ibd to /mysqlbackup//2017-01-23_05-02-02/mysql/innodb_index_stats.ibd
170123 05:02:03 [01] ...done
170123 05:02:03 [01] Copying ./mysql/slave_relay_log_info.ibd to /mysqlbackup//2017-01-23_05-02-02/mysql/slave_relay_log_info.ibd
170123 05:02:03 [01] ...done
170123 05:02:03 [01] Copying ./mysql/slave_master_info.ibd to /mysqlbackup//2017-01-23_05-02-02/mysql/slave_master_info.ibd
170123 05:02:03 [01] ...done
\t。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
170123 05:02:04 [01] Copying ./testdatabase/testtable.frm to /mysqlbackup//2017-01-23_05-02-02/testdatabase/testtable.frm
170123 05:02:04 [01] ...done
170123 05:02:04 [01] Copying ./testdatabase/testtable2.frm to /mysqlbackup//2017-01-23_05-02-02/testdatabase/testtable2.frm
170123 05:02:04 [01] ...done
170123 05:02:04 Finished backing up non-InnoDB tables and files
170123 05:02:04 [00] Writing xtrabackup_binlog_info
170123 05:02:04 [00] ...done
170123 05:02:04 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1802325'
xtrabackup: Stopping log copying thread.
.170123 05:02:04 >> log scanned up to (1802325)
170123 05:02:04 Executing UNLOCK TABLES
170123 05:02:04 All tables unlocked
170123 05:02:04 Backup created in directory '/mysqlbackup//2017-01-23_05-02-02'
MySQL binlog position: filename 'master-log.000012', position '264'
170123 05:02:04 [00] Writing backup-my.cnf
170123 05:02:04 [00] ...done
170123 05:02:04 [00] Writing xtrabackup_info
170123 05:02:04 [00] ...done
xtrabackup: Transaction log of lsn (1802325) to (1802325) was copied.
170123 05:02:04 completed OK!
\t
/<code>
1.2.2：在另外一臺服務器安裝percona工具並進行整理：
#一般情況下備份完成後，備份數據尚且不能用於恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態。“準備”的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處於一致性狀態，innobakupex命令的–apply-log選項可用於實現上述功能，如下面的命令：
<code>[root@sql-s3 ~]# mkdir /mysqlbackup
[root@sql-s3 ~]# yum install https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.5/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm
[root@sql-master ~]# scp -r /mysqlbackup/ 192.168.10.103:/mysqlbackup #將Master備份的數據文件複製到要導入數據的MtSQL服務器
[root@sql-s3 ~]# ll /mysqlbackup/mysqlbackup/2017-01-23_05-02-02/ #確認目標服務器的數據文件已經存在
[root@sql-s3 ~]# innobackupex --apply-log /mysqlbackup/mysqlbackup/2017-01-23_05-02-02/ #直接寫備份目錄
170117 11:21:45 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.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
xtrabackup: cd to /mysqlbackup/mysqlbackup/2017-01-23_05-02-02/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1802325)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1799353 in the system tablespace does not match the log sequence number 1802325 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Doing recovery: scanned up to log sequence number 1802325 (0%)
InnoDB: xtrabackup: Last MySQL binlog file position 5726, file name master-log.000008
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 1802325
InnoDB: xtrabackup: Last MySQL binlog file position 5726, file name master-log.000008
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1802428
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1802428
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1802764
InnoDB: Doing recovery: scanned up to log sequence number 1802773 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 1802773 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 5726, file name master-log.000008
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 1802773
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1802792
170117 11:21:49 completed OK!/<code>
1.2.3：開始還原：
1.2.3.1：還原的時候要在被還原的MySQL服務器將原來的數據目錄的文件全部清空，如果新新安裝的MySQL就先不要啟動，等恢復數據之後再啟動MySQL，否則報錯如下：
<code>[root@sql-s3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@sql-s3 ~]# innobackupex --copy-back /mysqlbackup/mysqlbackup/2017-01-23_05-02-02/
170117 11:30:29 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
prints "completed OK!".
innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
Original data directory /data/mysql is not empty!/<code>
1.2.3.2：清空目錄再次還原：
<code>[root@sql-s3 ~]# rm -rf /data/mysql/ #刪除原目錄的數據，如果有數據可以換一個目錄或者部署一個MySQL服務器
[root@sql-s3 ~]# innobackupex --copy-back /mysqlbackup/mysqlbackup/2017-01-23_05-02-02/ #上一步驟整理後的數據目錄
170117 11:33:35 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
prints "completed OK!".
innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
170117 11:33:35 [01] Copying ib_logfile0 to /data/mysql/ib_logfile0
170117 11:33:35 [01] ...done
170117 11:33:35 [01] Copying ib_logfile1 to /data/mysql/ib_logfile1
170117 11:33:35 [01] ...done
170117 11:33:35 [01] Copying ibdata1 to /data/mysql/ibdata1
170117 11:33:36 [01] ...done
170117 11:33:36 [01] Copying ./mysql/innodb_table_stats.ibd to /data/mysql/mysql/innodb_table_stats.ibd
170117 11:33:36 [01] ...done
170117 11:33:36 [01] Copying ./mysql/innodb_index_stats.ibd to /data/mysql/mysql/innodb_index_stats.ibd
170117 11:33:36 [01] ...done
170117 11:33:36 [01] Copying ./mysql/slave_relay_log_info.ibd to /data/mysql/mysql/slave_relay_log_info.ibd
170117 11:33:36 [01] ...done
170117 11:33:36 [01] Copying ./mysql/slave_master_info.ibd to /data/mysql/mysql/slave_master_info.ibd
170117 11:33:36 [01] ...done
。。。。。。。。。。。。。。。。。。。。。。
170117 11:33:36 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info
170117 11:33:36 [01] ...done
170117 11:33:36 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/xtrabackup_binlog_pos_innodb
170117 11:33:36 [01] ...done
170117 11:33:36 [01] Copying ./ibtmp1 to /data/mysql/ibtmp1
170117 11:33:36 [01] ...done
170117 11:33:36 completed OK!/<code>
1.2.4：啟動MySQL：
<code>[root@sql-s3 ~]# chown -R mysql.mysql /data/mysql #使用root做還原需要將屬主和屬組改為MySQL，可以直接使用MySQL用戶做還原
[root@sql-s3 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) #需要使用源MySQL服務器的用戶名密碼
[root@sql-s3 ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 2
Server version: 5.6.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdatabase | #數據已經恢復
+--------------------+
5 rows in set (0.00 sec)/<code>
1.3：實現增量備份和恢復，增量備份僅支持InnoDB和XtraDB引擎，不支持MyISAM引擎增量備份，每次MyISAM都是完整備份：
完整備份：人畜不分，全部備份。
差異備份：備份自上一次完整備份之後有變化的所有數據，其他的備份類型不考慮只看上一次是否完整備份。
增量備份：備份自上一次備份(包含完整備份、差異備份和增量備份)之後有變化的數據。
1.3.1：先執行完整備份：
<code>[root@sql-master ~]# innobackupex --user=root --password=123456 /mysqlbackup/
[root@sql-master ~]# tail /mysqlbackup/2017-01-23_06-47-08/xtrabackup_checkpoints #查看本次的備份類型
backup_type = full-backuped
from_lsn = 0
to_lsn = 1802325
last_lsn = 1802325
compact = 0
recover_binlog_info = 0/<code>
1.3.2：向表中插入數據：
<code>mysql> select * from testtable2;
+----+------+------+------+----------+-----------+
| id | Name | Age | Job | Province | Education |
+----+------+------+------+----------+-----------+
| 1 | Jack | 18 | NULL | NULL | NULL |
+----+------+------+------+----------+-----------+
1 row in set (0.00 sec)
mysql> insert into testtable2 values (2,"Tom",19,"IT","BeiJing","Undergraduate");
Query OK, 1 row affected (0.00 sec)/<code>
1.3.3：基於完整備份做增量備份：
<code>#第一次增量備份命令格式：
[root@sql-master ~]# innobackupex --user=root --password=123456 --incremental /增量備份目標目錄 --incremental-basedir=/原完整備份目錄
#第一次增量完整完整命令如下：
[root@sql-master ~]# innobackupex --user=root --password=123456 --incremental /bak --incremental-basedir=/mysqlbackup/2017-01-23_06-47-08/<code>
1.3.4：再次插入一條數據並再做增量備份：
<code>mysql> insert into testtable2 values (3,"lili",20,"IT","BeiJing","Undergraduate");
Query OK, 1 row affected (0.27 sec)
#第二次增量備份的basedir要指向上一次進行增量備份的目錄，以此類推
[root@sql-master ~]#innobackupex --user=root --password=123456 --incremental /bak --incremental-basedir=/bak/2017-01-23_07-04-56
#當前的表內容
mysql> select * from testtable2;
+----+------+------+------+----------+---------------+
| id | Name | Age | Job | Province | Education |
+----+------+------+------+----------+---------------+
| 1 | Jack | 18 | NULL | NULL | NULL |
| 2 | Tom | 19 | IT | BeiJing | Undergraduate |
| 3 | lili | 20 | IT | BeiJing | Undergraduate |
+----+------+------+------+----------+---------------+
3 rows in set (0.00 sec) /<code>
1.3.5：驗證增量備份的數據目錄文件是否存在：
<code>[root@sql-master ~]# ll /bak/2017-01-23_07-04-56/
total 228
-rw-r----- 1 root root 419 Jan 23 07:04 backup-my.cnf
-rw-r----- 1 root root 196608 Jan 23 07:04 ibdata1.delta
-rw-r----- 1 root root 44 Jan 23 07:04 ibdata1.meta
drwxr-x--- 2 root root 4096 Jan 23 07:04 mysql
drwxr-x--- 2 root root 4096 Jan 23 07:04 performance_schema
drwxr-x--- 2 root root 19 Jan 23 07:04 test
drwxr-x--- 2 root root 4096 Jan 23 07:04 testdatabase
-rw-r----- 1 root root 22 Jan 23 07:04 xtrabackup_binlog_info
-rw-r----- 1 root root 117 Jan 23 07:04 xtrabackup_checkpoints
-rw-r----- 1 root root 536 Jan 23 07:04 xtrabackup_info
-rw-r----- 1 root root 2560 Jan 23 07:04 xtrabackup_logfile/<code>
1.3.6：驗證增量備份的xtrabackup_checkpoints文件：
<code>[root@sql-master ~]# tail /bak/2017-01-23_07-04-56/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1802325 #開始的lsn，是上一次完整備份的結束lsn
to_lsn = 1803419
last_lsn = 1803419
compact = 0
recover_binlog_info = 0/<code>
1.4：數據恢復
先整理完整備份，然後將第一次增量備份合併到完整備份、第二次增量備份合併到完整備份。。。。：
1.4.1：整理完整備份併合並各次的增量備份：
<code>#整理完整備份
[root@sql-master ~]# innobackupex --apply-log --redo-only /mysqlbackup/2017-01-23_06-47-08
#合併第一次增量備份
[root@sql-master ~]# innobackupex --apply-log --redo-only /mysqlbackup/2017-01-23_06-47-08 --incremental-dir=/bak/2017-01-23_07-04-56
#合併第二次的增量備份，以此類推
[root@sql-master ~]# innobackupex --apply-log --redo-only /mysqlbackup/2017-01-23_06-47-08 --incremental-dir=/bak/2017-01-23_07-18-12 /<code>
1.4.2：複製到另外一臺MySQL服務器進行恢復：
<code>[root@sql-s3 ~]# innobackupex --copy-back /root/2017-01-23_06-47-08/
[root@sql-s3 ~]# chown mysql.mysql -R /data/mysql/
[root@sql-s3 ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! /<code>
1.4.3：驗證數據恢復完成：
<code>mysql> select * from testtable2;
+----+------+------+------+----------+---------------+
| id | Name | Age | Job | Province | Education |
+----+------+------+------+----------+---------------+
| 1 | Jack | 18 | NULL | NULL | NULL |
| 2 | Tom | 19 | IT | BeiJing | Undergraduate |
| 3 | lili | 20 | IT | BeiJing | Undergraduate |
+----+------+------+------+----------+---------------+
3 rows in set (0.00 sec)/<code>
1.5：單張表的備份：
需要再啟動MySQL服務之前在在配置文件中配置innodb_file_per_table=1選項，導出但張表也需要先做一次完整的備份，然後使用–export參數在完整備份的目錄生成每個表文件，每個表文件以.exp結尾，這個文件裡面保存的只是數據沒有表結構，需要先在目標服務器創建表和表結構，然後再把.exp結尾的文件複製到其他服務器再導入即可：
1.5.1：先進行一次完整備份：
<code>[root@sql-master ~]# innobackupex --user=root --password=123456 /mysqlbackup/ #執行完整備份
[root@sql-master ~]# ll /mysqlbackup/2017-01-23_08-18-20/ #完整備份目錄
[root@sql-master ~]# innobackupex --apply-log --export /mysqlbackup/2017-01-23_08-18-20 #在完整備份目錄執行導出
[root@sql-master ~]# ll /mysqlbackup/2017-01-23_08-18-20/testdatabase/ #驗證是否生成talbe_name.exp的文件
total 260
-rw-r----- 1 root root 65 Jan 23 08:18 db.opt
-rw-r--r-- 1 root root 637 Jan 23 08:23 testtable2.cfg
-rw-r----- 1 root root 16384 Jan 23 08:23 testtable2.exp
-rw-r----- 1 root root 8720 Jan 23 08:18 testtable2.frm
-rw-r----- 1 root root 98304 Jan 23 08:18 testtable2.ibd
-rw-r--r-- 1 root root 554 Jan 23 08:23 testtable.cfg
-rw-r----- 1 root root 16384 Jan 23 08:23 testtable.exp
-rw-r----- 1 root root 8656 Jan 23 08:18 testtable.frm
-rw-r----- 1 root root 98304 Jan 23 08:18 testtable.ibd/<code>
1.5.2：在另外一臺服務器創建數據庫和表：
<code>mysql> create database testdb; #在新MySQL新建一個庫
Query OK, 1 row affected (0.00 sec)
mysql> use testdb;
Database changed
mysql> CREATE TABLE `testtable2` ( #該語句需要在原MySQL使用show create table xxx; 查看
-> `id` int(11) NOT NULL,
-> `Name` varchar(20) DEFAULT NULL,
-> `Age` int(11) DEFAULT NULL,
-> `Job` varchar(20) DEFAULT NULL,
-> `Province` varchar(20) DEFAULT NULL,
-> `Education` varchar(20) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)
mysql> desc testtable2; /<code>
1.5.3：#刪除該表的表空間：
<code>mysql> alter table testtable2 discard tablespace;
Query OK, 0 rows affected (0.01 sec)/<code>
1.5.4：在原服務器上exp文件和ibd文件複製到目標MySQL服務器的xxx庫目錄裡面：
<code>[root@sql-master testdatabase]# scp testtable2.exp testtable2.ibd 192.168.10.103:/data/mysql/testdb/
[email protected]'s password:
Permission denied, please try again.
[email protected]'s password:
testtable2.exp 100% 16KB 16.0KB/s 00:00
testtable2.ibd 100% 96KB 96.0KB/s 00:00/<code>
1.5.5：在目標MySQL服務器將複製的文件更改屬主屬組為mysql用戶並重新載入表空間文件：
<code>mysql> alter table testtable2 import tablespace;
Query OK, 0 rows affected, 1 warning (0.03 sec/<code>
1.5.6：目標服務器驗證數據導入成功：
<code>mysql> select * from testtable2;
+----+------+------+------+----------+---------------+
| id | Name | Age | Job | Province | Education |
+----+------+------+------+----------+---------------+
| 1 | Jack | 18 | NULL | NULL | NULL |
| 2 | Tom | 19 | IT | BeiJing | Undergraduate |
| 3 | lili | 20 | IT | BeiJing | Undergraduate |
+----+------+------+------+----------+---------------+
3 rows in set (0.00 sec)/<code>
分享到:
閱讀更多 愛踢人生 的文章
