一：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>
分享到:
閱讀更多 愛踢人生 的文章
