用cp能克隆数据库么

justlooks

如果用percona server的话有个方法可以在服务器之间移动ibd文件

1. 
   
2. xtrabackup  --defaults-file=/etc/my.cnf --backup --tables=test.t1 --target-dir=/data/tmp/
   
3. 
   
4. xtrabackup: tables regcomp(test.t1): Success
   
5. xtrabackup version 1.6.5 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 328)
   
6. xtrabackup: uses posix_fadvise().
   
7. xtrabackup: cd to /var/lib/mysql
   
8. xtrabackup: Target instance is assumed as followings.
   
9. xtrabackup:   innodb_data_home_dir = ./
   
10. xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
    
11. xtrabackup:   innodb_log_group_home_dir = ./
    
12. xtrabackup:   innodb_log_files_in_group = 2
    
13. xtrabackup:   innodb_log_file_size = 5242880
    
14. 120312 16:38:22  InnoDB: Warning: allocated tablespace 10, old maximum was 9
    
15. >> log scanned up to (1601674)
    
16. [01] Copying ./ibdata1
    
17.      to /data/tmp//ibdata1
    
18. [01]        ...done
    
19. [01] Copying ./test/t1.ibd
    
20.      to /data/tmp//test/t1.ibd
    
21. [01]        ...done
    
22. xtrabackup: The latest check point (for incremental): '1601674'
    
23. >> log scanned up to (1601674)
    
24. xtrabackup: Stopping log copying thread.
    
25. xtrabackup: Transaction log of lsn (1601674) to (1601674) was copied.
    
26. 
    
27. 
    
28. xtrabackup  --defaults-file=/etc/my.cnf --prepare --export --target-dir=/data/tmp
    
29. 
    
30. xtrabackup version 1.6.5 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (r                                                                                                 evision id: 328)
    
31. xtrabackup: cd to /data/tmp
    
32. xtrabackup: This target seems to be not prepared yet.
    
33. xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1601674)
    
34. xtrabackup: Temporary instance for recovery is set as followings.
    
35. xtrabackup:   innodb_data_home_dir = ./
    
36. xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
    
37. xtrabackup:   innodb_log_group_home_dir = ./
    
38. xtrabackup:   innodb_log_files_in_group = 1
    
39. xtrabackup:   innodb_log_file_size = 2097152
    
40. xtrabackup: Starting InnoDB instance for recovery.
    
41. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter                                                                                                 )
    
42. InnoDB: The InnoDB memory heap is disabled
    
43. InnoDB: Mutexes and rw_locks use GCC atomic builtins
    
44. InnoDB: Compressed tables use zlib 1.2.3
    
45. InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io                                                                                                 _threads and innodb_write_io_threads instead
    
46. 120312 16:41:17  InnoDB: Initializing buffer pool, size = 100.0M
    
47. 120312 16:41:17  InnoDB: Completed initialization of buffer pool
    
48. 120312 16:41:17  InnoDB: highest supported file format is Barracuda.
    
49. InnoDB: The log sequence number in ibdata files does not match
    
50. InnoDB: the log sequence number in the ib_logfiles!
    
51. 120312 16:41:17  InnoDB: Database was not shut down normally!
    
52. InnoDB: Starting crash recovery.
    
53. InnoDB: Reading tablespace information from the .ibd files...
    
54. 120312 16:41:17 Percona XtraDB (http://www.percona.com) 1.0.17-12.5 started; log                                                                                                  sequence number 1601674
    
55. xtrabackup: export option is specified.
    
56. xtrabackup: export metadata of table 'test/t1' to file `./test/t1.exp` (1 indexe                                                                                                 s)
    
57. xtrabackup:     name=GEN_CLUST_INDEX, id.low=16, page=3
    
58. 
    
59. [notice (again)]
    
60.   If you use binary log and don't use any hack of group commit,
    
61.   the binary log position seems to be:
    
62. 
    
63. xtrabackup: starting shutdown with innodb_fast_shutdown = 0
    
64. 120312 16:41:17  InnoDB: Starting shutdown...
    
65. 120312 16:41:17  InnoDB: Shutdown completed; log sequence number 1601674
    
66. 
    

复制代码

在需要导入数据的服务器

1. 
   
2. mysql> set global innodb_import_table_from_xtrabackup=1;
   

复制代码

创建table

1. 
   
2. mysql> create table t1 (id int)engine=innodb;
   

复制代码

删除表空间

1. 
   
2. mysql> alter table t1 discard tablespace;
   

复制代码

拷贝ibd和exp文件到指定位置
cp t1.* /var/lib/mysql/test/

1. 
   
2. mysql> alter table t1 import tablespace;
   

复制代码

另外数据可能需要重启mysql才能看到！