mysql xtrabackup 全量复制

- 日理万妓 2022-10-05 01:53 241阅读 0赞

mysql xtrabackup 全量复制

官网：https://www.percona.com/doc/percona-xtrabackup/LATEST/backup_scenarios/full_backup.html

********************

相关参数

—datadir：需要备份的数据目录

—target-dir：备份数据存放目录

  1. If the target directory does not exist, xtrabackup creates it. 
  2. If the directory does exist and is empty, xtrabackup will succeed.
  4. # xtrabackup不会覆盖文件，如果备份目录中要创建的文件存在，会报错退出
  5. xtrabackup will not overwrite existing files, it will fail with operating system error 17, file exists

—log-bin：二进制日志基础名称，默认为/var/lib/mysql/binlog

—log-index：二进制日志索引文件，默认为/var/lib/mysql/binlog.index

********************

示例：mysql数据目录为dir，备份数据到目录dir2，还原数据到dir3；mysql2使用dir3做数据目录

docker创建 mysql

  1. docker run -it  -d --net fixed --ip 172.18.0.2 -p 3306:3306 \
  2. -v /usr/mysql/single/data:/var/lib/mysql \
  3. --privileged=true -e  MYSQL_ROOT_PASSWORD=123456 --name mysql mysql
  5. docker run -it  -d --net fixed --ip 172.18.0.4 -p 3307:3306 \
  6. -v /usr/mysql/single/data2:/var/lib/mysql \
  7. --privileged=true -e  MYSQL_ROOT_PASSWORD=123456 --name mysql2 mysql

、

数据备份到目录 /usr/mysql/data/backup

  1. [root@centos single]# xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup --log-bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/backup
  2. xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index --datadir=/usr/mysql/single/data 
  3. xtrabackup: recognized client arguments: --user=root --password=* --host=192.168.57.120 --port=3306 --backup=1 --target-dir=/usr/mysql/single/backup 
  4. xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
  5. 210609 21:36:38  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.57.120;port=3306' as 'root'  (using password: YES).
  6. Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=192.168.57.120;port=3306','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: 无法打开共享对象文件: 没有那个文件或目录 at - line 1535.
  7. 210609 21:36:38 Connecting to MySQL server host: 192.168.57.120, user: root, password: set, port: 3306, socket: not set
  8. Using server version 8.0.25
  9. Warning: option 'datadir' has different values:
  10.   '/usr/mysql/single/data' in defaults file
  11.   '/var/lib/mysql/' in SHOW VARIABLES
  12. 210609 21:36:38 Executing LOCK INSTANCE FOR BACKUP...
  13. xtrabackup: uses posix_fadvise().
  14. xtrabackup: cd to /usr/mysql/single/data
  15. xtrabackup: open files limit requested 0, set to 1024
  16. xtrabackup: using the following InnoDB configuration:
  17. xtrabackup:   innodb_data_home_dir = .
  18. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  19. xtrabackup:   innodb_log_group_home_dir = ./
  20. xtrabackup:   innodb_log_files_in_group = 2
  21. xtrabackup:   innodb_log_file_size = 50331648
  22. Number of pools: 1
  23. xtrabackup: inititialize_service_handles suceeded
  24. 210609 21:36:38 Connecting to MySQL server host: 192.168.57.120, user: root, password: set, port: 3306, socket: not set
  25. xtrabackup: Redo Log Archiving is not set up.
  26. 210609 21:36:39 >> log scanned up to (28649493)
  27. xtrabackup: Generating a list of tablespaces
  28. xtrabackup: Generating a list of tablespaces
  29. Scanning './'
  30. Completed space ID check of 2 files.
  31. Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
  32. Using undo tablespace './undo_001'.
  33. Using undo tablespace './undo_002'.
  34. Opened 2 existing undo tablespaces.
  35. 210609 21:36:39 [01] Copying ./ibdata1 to /usr/mysql/single/backup/ibdata1
  36. 210609 21:36:39 [01]        ...done
  37. 210609 21:36:39 [01] Copying ./sys/sys_config.ibd to /usr/mysql/single/backup/sys/sys_config.ibd
  38. 210609 21:36:39 [01]        ...done
  39. 210609 21:36:39 [01] Copying ./mysql.ibd to /usr/mysql/single/backup/mysql.ibd
  40. 210609 21:36:39 [01]        ...done
  41. 210609 21:36:39 [01] Copying ./undo_002 to /usr/mysql/single/backup/undo_002
  42. 210609 21:36:39 [01]        ...done
  43. 210609 21:36:39 [01] Copying ./undo_001 to /usr/mysql/single/backup/undo_001
  44. 210609 21:36:39 [01]        ...done
  45. 210609 21:36:40 >> log scanned up to (28649493)
  46. 210609 21:36:40 Starting to backup non-InnoDB tables and files
  47. 210609 21:36:40 [01] Copying mysql/general_log_213.sdi to /usr/mysql/single/backup/mysql/general_log_213.sdi
  48. 210609 21:36:40 [01]        ...done
  49. 210609 21:36:40 [01] Copying mysql/general_log.CSM to /usr/mysql/single/backup/mysql/general_log.CSM
  50. 210609 21:36:40 [01]        ...done
  51. 210609 21:36:40 [01] Copying mysql/general_log.CSV to /usr/mysql/single/backup/mysql/general_log.CSV
  52. 210609 21:36:40 [01]        ...done
  53. 210609 21:36:40 [01] Copying mysql/slow_log_214.sdi to /usr/mysql/single/backup/mysql/slow_log_214.sdi
  54. 210609 21:36:40 [01]        ...done
  55. 210609 21:36:40 [01] Copying mysql/slow_log.CSM to /usr/mysql/single/backup/mysql/slow_log.CSM
  56. 210609 21:36:40 [01]        ...done
  57. 210609 21:36:40 [01] Copying mysql/slow_log.CSV to /usr/mysql/single/backup/mysql/slow_log.CSV
  58. 210609 21:36:40 [01]        ...done
  59. 210609 21:36:40 [01] Copying performance_schema/cond_instances_82.sdi to /usr/mysql/single/backup/performance_schema/cond_instances_82.sdi
  60. 210609 21:36:40 [01]        ...done
  61. 210609 21:36:40 [01] Copying performance_schema/error_log_83.sdi to /usr/mysql/single/backup/performance_schema/error_log_83.sdi
  62. 210609 21:36:40 [01]        ...done
  63. 210609 21:36:40 [01] Copying performance_schema/events_waits_cur_84.sdi to /usr/mysql/single/backup/performance_schema/events_waits_cur_84.sdi
  64. 210609 21:36:40 [01]        ...done
  65. 210609 21:36:40 [01] Copying performance_schema/events_waits_his_85.sdi to /usr/mysql/single/backup/performance_schema/events_waits_his_85.sdi
  66. 210609 21:36:40 [01]        ...done
  67. 210609 21:36:40 [01] Copying performance_schema/events_waits_his_86.sdi to /usr/mysql/single/backup/performance_schema/events_waits_his_86.sdi
  68. 210609 21:36:40 [01]        ...done
  69. 210609 21:36:40 [01] Copying performance_schema/events_waits_sum_87.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_87.sdi
  70. 210609 21:36:40 [01]        ...done
  71. 210609 21:36:40 [01] Copying performance_schema/events_waits_sum_88.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_88.sdi
  72. 210609 21:36:40 [01]        ...done
  73. 210609 21:36:40 [01] Copying performance_schema/events_waits_sum_89.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_89.sdi
  74. 210609 21:36:40 [01]        ...done
  75. 210609 21:36:40 [01] Copying performance_schema/events_waits_sum_90.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_90.sdi
  76. 210609 21:36:40 [01]        ...done
  77. 210609 21:36:40 [01] Copying performance_schema/events_waits_sum_91.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_91.sdi
  78. 210609 21:36:40 [01]        ...done
  79. 210609 21:36:40 [01] Copying performance_schema/events_waits_sum_92.sdi to /usr/mysql/single/backup/performance_schema/events_waits_sum_92.sdi
  80. 210609 21:36:40 [01]        ...done
  81. 210609 21:36:40 [01] Copying performance_schema/file_instances_93.sdi to /usr/mysql/single/backup/performance_schema/file_instances_93.sdi
  82. 210609 21:36:40 [01]        ...done
  83. 210609 21:36:40 [01] Copying performance_schema/file_summary_by__94.sdi to /usr/mysql/single/backup/performance_schema/file_summary_by__94.sdi
  84. 210609 21:36:40 [01]        ...done
  85. 210609 21:36:40 [01] Copying performance_schema/file_summary_by__95.sdi to /usr/mysql/single/backup/performance_schema/file_summary_by__95.sdi
  86. 210609 21:36:40 [01]        ...done
  87. 210609 21:36:40 [01] Copying performance_schema/host_cache_96.sdi to /usr/mysql/single/backup/performance_schema/host_cache_96.sdi
  88. 210609 21:36:40 [01]        ...done
  89. 210609 21:36:40 [01] Copying performance_schema/mutex_instances_97.sdi to /usr/mysql/single/backup/performance_schema/mutex_instances_97.sdi
  90. 210609 21:36:40 [01]        ...done
  91. 210609 21:36:40 [01] Copying performance_schema/objects_summary__98.sdi to /usr/mysql/single/backup/performance_schema/objects_summary__98.sdi
  92. 210609 21:36:40 [01]        ...done
  93. 210609 21:36:40 [01] Copying performance_schema/performance_time_99.sdi to /usr/mysql/single/backup/performance_schema/performance_time_99.sdi
  94. 210609 21:36:40 [01]        ...done
  95. 210609 21:36:40 [01] Copying performance_schema/processlist_100.sdi to /usr/mysql/single/backup/performance_schema/processlist_100.sdi
  96. 210609 21:36:40 [01]        ...done
  97. 210609 21:36:40 [01] Copying performance_schema/rwlock_instances_101.sdi to /usr/mysql/single/backup/performance_schema/rwlock_instances_101.sdi
  98. 210609 21:36:40 [01]        ...done
  99. 210609 21:36:40 [01] Copying performance_schema/setup_actors_102.sdi to /usr/mysql/single/backup/performance_schema/setup_actors_102.sdi
  100. 210609 21:36:40 [01]        ...done
  101. 210609 21:36:40 [01] Copying performance_schema/setup_consumers_103.sdi to /usr/mysql/single/backup/performance_schema/setup_consumers_103.sdi
  102. 210609 21:36:40 [01]        ...done
  103. 210609 21:36:40 [01] Copying performance_schema/setup_instrument_104.sdi to /usr/mysql/single/backup/performance_schema/setup_instrument_104.sdi
  104. 210609 21:36:40 [01]        ...done
  105. 210609 21:36:40 [01] Copying performance_schema/setup_objects_105.sdi to /usr/mysql/single/backup/performance_schema/setup_objects_105.sdi
  106. 210609 21:36:40 [01]        ...done
  107. 210609 21:36:40 [01] Copying performance_schema/setup_threads_106.sdi to /usr/mysql/single/backup/performance_schema/setup_threads_106.sdi
  108. 210609 21:36:40 [01]        ...done
  109. 210609 21:36:40 [01] Copying performance_schema/table_io_waits_s_107.sdi to /usr/mysql/single/backup/performance_schema/table_io_waits_s_107.sdi
  110. 210609 21:36:40 [01]        ...done
  111. 210609 21:36:40 [01] Copying performance_schema/table_io_waits_s_108.sdi to /usr/mysql/single/backup/performance_schema/table_io_waits_s_108.sdi
  112. 210609 21:36:40 [01]        ...done
  113. 210609 21:36:40 [01] Copying performance_schema/table_lock_waits_109.sdi to /usr/mysql/single/backup/performance_schema/table_lock_waits_109.sdi
  114. 210609 21:36:40 [01]        ...done
  115. 210609 21:36:40 [01] Copying performance_schema/threads_110.sdi to /usr/mysql/single/backup/performance_schema/threads_110.sdi
  116. 210609 21:36:40 [01]        ...done
  117. 210609 21:36:40 [01] Copying performance_schema/events_stages_cu_111.sdi to /usr/mysql/single/backup/performance_schema/events_stages_cu_111.sdi
  118. 210609 21:36:40 [01]        ...done
  119. 210609 21:36:40 [01] Copying performance_schema/events_stages_hi_112.sdi to /usr/mysql/single/backup/performance_schema/events_stages_hi_112.sdi
  120. 210609 21:36:40 [01]        ...done
  121. 210609 21:36:40 [01] Copying performance_schema/events_stages_hi_113.sdi to /usr/mysql/single/backup/performance_schema/events_stages_hi_113.sdi
  122. 210609 21:36:40 [01]        ...done
  123. 210609 21:36:40 [01] Copying performance_schema/events_stages_su_114.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_114.sdi
  124. 210609 21:36:40 [01]        ...done
  125. 210609 21:36:40 [01] Copying performance_schema/events_stages_su_115.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_115.sdi
  126. 210609 21:36:40 [01]        ...done
  127. 210609 21:36:40 [01] Copying performance_schema/events_stages_su_116.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_116.sdi
  128. 210609 21:36:40 [01]        ...done
  129. 210609 21:36:40 [01] Copying performance_schema/events_stages_su_117.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_117.sdi
  130. 210609 21:36:40 [01]        ...done
  131. 210609 21:36:40 [01] Copying performance_schema/events_stages_su_118.sdi to /usr/mysql/single/backup/performance_schema/events_stages_su_118.sdi
  132. 210609 21:36:40 [01]        ...done
  133. 210609 21:36:40 [01] Copying performance_schema/events_statement_119.sdi to /usr/mysql/single/backup/performance_schema/events_statement_119.sdi
  134. 210609 21:36:40 [01]        ...done
  135. 210609 21:36:40 [01] Copying performance_schema/events_statement_120.sdi to /usr/mysql/single/backup/performance_schema/events_statement_120.sdi
  136. 210609 21:36:40 [01]        ...done
  137. 210609 21:36:40 [01] Copying performance_schema/events_statement_121.sdi to /usr/mysql/single/backup/performance_schema/events_statement_121.sdi
  138. 210609 21:36:40 [01]        ...done
  139. 210609 21:36:40 [01] Copying performance_schema/events_statement_122.sdi to /usr/mysql/single/backup/performance_schema/events_statement_122.sdi
  140. 210609 21:36:40 [01]        ...done
  141. 210609 21:36:40 [01] Copying performance_schema/events_statement_123.sdi to /usr/mysql/single/backup/performance_schema/events_statement_123.sdi
  142. 210609 21:36:40 [01]        ...done
  143. 210609 21:36:40 [01] Copying performance_schema/events_statement_124.sdi to /usr/mysql/single/backup/performance_schema/events_statement_124.sdi
  144. 210609 21:36:40 [01]        ...done
  145. 210609 21:36:40 [01] Copying performance_schema/events_statement_125.sdi to /usr/mysql/single/backup/performance_schema/events_statement_125.sdi
  146. 210609 21:36:40 [01]        ...done
  147. 210609 21:36:40 [01] Copying performance_schema/events_statement_126.sdi to /usr/mysql/single/backup/performance_schema/events_statement_126.sdi
  148. 210609 21:36:40 [01]        ...done
  149. 210609 21:36:40 [01] Copying performance_schema/events_statement_127.sdi to /usr/mysql/single/backup/performance_schema/events_statement_127.sdi
  150. 210609 21:36:40 [01]        ...done
  151. 210609 21:36:40 [01] Copying performance_schema/events_statement_128.sdi to /usr/mysql/single/backup/performance_schema/events_statement_128.sdi
  152. 210609 21:36:40 [01]        ...done
  153. 210609 21:36:40 [01] Copying performance_schema/events_statement_129.sdi to /usr/mysql/single/backup/performance_schema/events_statement_129.sdi
  154. 210609 21:36:40 [01]        ...done
  155. 210609 21:36:40 [01] Copying performance_schema/events_statement_130.sdi to /usr/mysql/single/backup/performance_schema/events_statement_130.sdi
  156. 210609 21:36:40 [01]        ...done
  157. 210609 21:36:40 [01] Copying performance_schema/events_transacti_131.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_131.sdi
  158. 210609 21:36:40 [01]        ...done
  159. 210609 21:36:40 [01] Copying performance_schema/events_transacti_132.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_132.sdi
  160. 210609 21:36:40 [01]        ...done
  161. 210609 21:36:40 [01] Copying performance_schema/events_transacti_133.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_133.sdi
  162. 210609 21:36:40 [01]        ...done
  163. 210609 21:36:40 [01] Copying performance_schema/events_transacti_134.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_134.sdi
  164. 210609 21:36:40 [01]        ...done
  165. 210609 21:36:40 [01] Copying performance_schema/events_transacti_135.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_135.sdi
  166. 210609 21:36:40 [01]        ...done
  167. 210609 21:36:40 [01] Copying performance_schema/events_transacti_136.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_136.sdi
  168. 210609 21:36:40 [01]        ...done
  169. 210609 21:36:40 [01] Copying performance_schema/events_transacti_137.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_137.sdi
  170. 210609 21:36:40 [01]        ...done
  171. 210609 21:36:40 [01] Copying performance_schema/events_transacti_138.sdi to /usr/mysql/single/backup/performance_schema/events_transacti_138.sdi
  172. 210609 21:36:40 [01]        ...done
  173. 210609 21:36:40 [01] Copying performance_schema/events_errors_su_139.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_139.sdi
  174. 210609 21:36:40 [01]        ...done
  175. 210609 21:36:40 [01] Copying performance_schema/events_errors_su_140.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_140.sdi
  176. 210609 21:36:40 [01]        ...done
  177. 210609 21:36:40 [01] Copying performance_schema/events_errors_su_141.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_141.sdi
  178. 210609 21:36:40 [01]        ...done
  179. 210609 21:36:40 [01] Copying performance_schema/events_errors_su_142.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_142.sdi
  180. 210609 21:36:40 [01]        ...done
  181. 210609 21:36:40 [01] Copying performance_schema/events_errors_su_143.sdi to /usr/mysql/single/backup/performance_schema/events_errors_su_143.sdi
  182. 210609 21:36:40 [01]        ...done
  183. 210609 21:36:40 [01] Copying performance_schema/users_144.sdi to /usr/mysql/single/backup/performance_schema/users_144.sdi
  184. 210609 21:36:40 [01]        ...done
  185. 210609 21:36:40 [01] Copying performance_schema/accounts_145.sdi to /usr/mysql/single/backup/performance_schema/accounts_145.sdi
  186. 210609 21:36:40 [01]        ...done
  187. 210609 21:36:40 [01] Copying performance_schema/hosts_146.sdi to /usr/mysql/single/backup/performance_schema/hosts_146.sdi
  188. 210609 21:36:40 [01]        ...done
  189. 210609 21:36:40 [01] Copying performance_schema/socket_instances_147.sdi to /usr/mysql/single/backup/performance_schema/socket_instances_147.sdi
  190. 210609 21:36:40 [01]        ...done
  191. 210609 21:36:40 [01] Copying performance_schema/socket_summary_b_148.sdi to /usr/mysql/single/backup/performance_schema/socket_summary_b_148.sdi
  192. 210609 21:36:40 [01]        ...done
  193. 210609 21:36:40 [01] Copying performance_schema/socket_summary_b_149.sdi to /usr/mysql/single/backup/performance_schema/socket_summary_b_149.sdi
  194. 210609 21:36:40 [01]        ...done
  195. 210609 21:36:40 [01] Copying performance_schema/session_connect__150.sdi to /usr/mysql/single/backup/performance_schema/session_connect__150.sdi
  196. 210609 21:36:40 [01]        ...done
  197. 210609 21:36:40 [01] Copying performance_schema/session_account__151.sdi to /usr/mysql/single/backup/performance_schema/session_account__151.sdi
  198. 210609 21:36:40 [01]        ...done
  199. 210609 21:36:40 [01] Copying performance_schema/keyring_keys_152.sdi to /usr/mysql/single/backup/performance_schema/keyring_keys_152.sdi
  200. 210609 21:36:40 [01]        ...done
  201. 210609 21:36:40 [01] Copying performance_schema/memory_summary_g_153.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_g_153.sdi
  202. 210609 21:36:40 [01]        ...done
  203. 210609 21:36:40 [01] Copying performance_schema/memory_summary_b_154.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_b_154.sdi
  204. 210609 21:36:40 [01]        ...done
  205. 210609 21:36:40 [01] Copying performance_schema/memory_summary_b_155.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_b_155.sdi
  206. 210609 21:36:40 [01]        ...done
  207. 210609 21:36:40 [01] Copying performance_schema/memory_summary_b_156.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_b_156.sdi
  208. 210609 21:36:40 [01]        ...done
  209. 210609 21:36:40 [01] Copying performance_schema/memory_summary_b_157.sdi to /usr/mysql/single/backup/performance_schema/memory_summary_b_157.sdi
  210. 210609 21:36:40 [01]        ...done
  211. 210609 21:36:40 [01] Copying performance_schema/table_handles_158.sdi to /usr/mysql/single/backup/performance_schema/table_handles_158.sdi
  212. 210609 21:36:40 [01]        ...done
  213. 210609 21:36:40 [01] Copying performance_schema/metadata_locks_159.sdi to /usr/mysql/single/backup/performance_schema/metadata_locks_159.sdi
  214. 210609 21:36:40 [01]        ...done
  215. 210609 21:36:40 [01] Copying performance_schema/data_locks_160.sdi to /usr/mysql/single/backup/performance_schema/data_locks_160.sdi
  216. 210609 21:36:40 [01]        ...done
  217. 210609 21:36:40 [01] Copying performance_schema/data_lock_waits_161.sdi to /usr/mysql/single/backup/performance_schema/data_lock_waits_161.sdi
  218. 210609 21:36:40 [01]        ...done
  219. 210609 21:36:40 [01] Copying performance_schema/replication_conn_162.sdi to /usr/mysql/single/backup/performance_schema/replication_conn_162.sdi
  220. 210609 21:36:40 [01]        ...done
  221. 210609 21:36:40 [01] Copying performance_schema/replication_grou_163.sdi to /usr/mysql/single/backup/performance_schema/replication_grou_163.sdi
  222. 210609 21:36:40 [01]        ...done
  223. 210609 21:36:40 [01] Copying performance_schema/replication_conn_164.sdi to /usr/mysql/single/backup/performance_schema/replication_conn_164.sdi
  224. 210609 21:36:40 [01]        ...done
  225. 210609 21:36:40 [01] Copying performance_schema/replication_appl_165.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_165.sdi
  226. 210609 21:36:40 [01]        ...done
  227. 210609 21:36:40 [01] Copying performance_schema/replication_appl_166.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_166.sdi
  228. 210609 21:36:40 [01]        ...done
  229. 210609 21:36:40 [01] Copying performance_schema/replication_appl_167.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_167.sdi
  230. 210609 21:36:40 [01]        ...done
  231. 210609 21:36:40 [01] Copying performance_schema/replication_appl_168.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_168.sdi
  232. 210609 21:36:40 [01]        ...done
  233. 210609 21:36:40 [01] Copying performance_schema/replication_grou_169.sdi to /usr/mysql/single/backup/performance_schema/replication_grou_169.sdi
  234. 210609 21:36:40 [01]        ...done
  235. 210609 21:36:40 [01] Copying performance_schema/replication_appl_170.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_170.sdi
  236. 210609 21:36:40 [01]        ...done
  237. 210609 21:36:40 [01] Copying performance_schema/replication_appl_171.sdi to /usr/mysql/single/backup/performance_schema/replication_appl_171.sdi
  238. 210609 21:36:40 [01]        ...done
  239. 210609 21:36:40 [01] Copying performance_schema/replication_asyn_172.sdi to /usr/mysql/single/backup/performance_schema/replication_asyn_172.sdi
  240. 210609 21:36:40 [01]        ...done
  241. 210609 21:36:40 [01] Copying performance_schema/replication_asyn_173.sdi to /usr/mysql/single/backup/performance_schema/replication_asyn_173.sdi
  242. 210609 21:36:40 [01]        ...done
  243. 210609 21:36:40 [01] Copying performance_schema/log_status_174.sdi to /usr/mysql/single/backup/performance_schema/log_status_174.sdi
  244. 210609 21:36:40 [01]        ...done
  245. 210609 21:36:40 [01] Copying performance_schema/prepared_stateme_175.sdi to /usr/mysql/single/backup/performance_schema/prepared_stateme_175.sdi
  246. 210609 21:36:40 [01]        ...done
  247. 210609 21:36:40 [01] Copying performance_schema/user_variables_b_176.sdi to /usr/mysql/single/backup/performance_schema/user_variables_b_176.sdi
  248. 210609 21:36:40 [01]        ...done
  249. 210609 21:36:40 [01] Copying performance_schema/status_by_accoun_177.sdi to /usr/mysql/single/backup/performance_schema/status_by_accoun_177.sdi
  250. 210609 21:36:40 [01]        ...done
  251. 210609 21:36:40 [01] Copying performance_schema/status_by_host_178.sdi to /usr/mysql/single/backup/performance_schema/status_by_host_178.sdi
  252. 210609 21:36:40 [01]        ...done
  253. 210609 21:36:40 [01] Copying performance_schema/status_by_thread_179.sdi to /usr/mysql/single/backup/performance_schema/status_by_thread_179.sdi
  254. 210609 21:36:40 [01]        ...done
  255. 210609 21:36:40 [01] Copying performance_schema/status_by_user_180.sdi to /usr/mysql/single/backup/performance_schema/status_by_user_180.sdi
  256. 210609 21:36:40 [01]        ...done
  257. 210609 21:36:40 [01] Copying performance_schema/global_status_181.sdi to /usr/mysql/single/backup/performance_schema/global_status_181.sdi
  258. 210609 21:36:40 [01]        ...done
  259. 210609 21:36:40 [01] Copying performance_schema/session_status_182.sdi to /usr/mysql/single/backup/performance_schema/session_status_182.sdi
  260. 210609 21:36:40 [01]        ...done
  261. 210609 21:36:40 [01] Copying performance_schema/variables_by_thr_183.sdi to /usr/mysql/single/backup/performance_schema/variables_by_thr_183.sdi
  262. 210609 21:36:40 [01]        ...done
  263. 210609 21:36:40 [01] Copying performance_schema/global_variables_184.sdi to /usr/mysql/single/backup/performance_schema/global_variables_184.sdi
  264. 210609 21:36:40 [01]        ...done
  265. 210609 21:36:40 [01] Copying performance_schema/session_variable_185.sdi to /usr/mysql/single/backup/performance_schema/session_variable_185.sdi
  266. 210609 21:36:40 [01]        ...done
  267. 210609 21:36:40 [01] Copying performance_schema/variables_info_186.sdi to /usr/mysql/single/backup/performance_schema/variables_info_186.sdi
  268. 210609 21:36:40 [01]        ...done
  269. 210609 21:36:40 [01] Copying performance_schema/persisted_variab_187.sdi to /usr/mysql/single/backup/performance_schema/persisted_variab_187.sdi
  270. 210609 21:36:40 [01]        ...done
  271. 210609 21:36:40 [01] Copying performance_schema/user_defined_fun_188.sdi to /usr/mysql/single/backup/performance_schema/user_defined_fun_188.sdi
  272. 210609 21:36:40 [01]        ...done
  273. 210609 21:36:40 [01] Copying performance_schema/binary_log_trans_189.sdi to /usr/mysql/single/backup/performance_schema/binary_log_trans_189.sdi
  274. 210609 21:36:40 [01]        ...done
  275. 210609 21:36:40 [01] Copying performance_schema/tls_channel_stat_190.sdi to /usr/mysql/single/backup/performance_schema/tls_channel_stat_190.sdi
  276. 210609 21:36:40 [01]        ...done
  277. 210609 21:36:40 [01] Copying performance_schema/keyring_componen_191.sdi to /usr/mysql/single/backup/performance_schema/keyring_componen_191.sdi
  278. 210609 21:36:40 [01]        ...done
  279. 210609 21:36:40 Finished backing up non-InnoDB tables and files
  280. 210609 21:36:40 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
  281. 210609 21:36:40 Selecting LSN and binary log position from p_s.log_status
  282. 210609 21:36:40 [00] Copying /usr/mysql/single/data/binlog.000006 to /usr/mysql/single/backup/binlog.000006 up to position 156
  283. 210609 21:36:40 [00]        ...done
  284. 210609 21:36:40 [00] Writing /usr/mysql/single/backup/binlog.index
  285. 210609 21:36:40 [00]        ...done
  286. 210609 21:36:40 [00] Writing /usr/mysql/single/backup/xtrabackup_binlog_info
  287. 210609 21:36:40 [00]        ...done
  288. 210609 21:36:40 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
  289. xtrabackup: The latest check point (for incremental): '28649493'
  290. xtrabackup: Stopping log copying thread at LSN 28649493.
  291. Starting to parse redo log at lsn = 28649493
  293. 210609 21:36:40 Executing UNLOCK INSTANCE
  294. 210609 21:36:40 All tables unlocked
  295. 210609 21:36:40 [00] Copying ib_buffer_pool to /usr/mysql/single/backup/ib_buffer_pool
  296. 210609 21:36:40 [00]        ...done
  297. 210609 21:36:40 Backup created in directory '/usr/mysql/single/backup/'
  298. MySQL binlog position: filename 'binlog.000006', position '156'
  299. 210609 21:36:40 [00] Writing /usr/mysql/single/backup/backup-my.cnf
  300. 210609 21:36:40 [00]        ...done
  301. 210609 21:36:40 [00] Writing /usr/mysql/single/backup/xtrabackup_info
  302. 210609 21:36:40 [00]        ...done
  303. xtrabackup: Transaction log of lsn (28649493) to (28649503) was copied.
  304. 210609 21:36:41 completed OK!

说明：transaction log即为 redo log

数据备份时，如果备份目录存在文件/usr/mysql/single/backup/xtrabackup_logfile，则会报错退出

  1. [root@centos single]# xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup --log-bin=/usr/mysql/single/data/logbin --log-bin-index=/usr/mysql/single/data/logbin.index --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/backup
  2. xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=/usr/mysql/single/data/logbin --log-bin-index=/usr/mysql/single/data/logbin.index --datadir=/usr/mysql/single/data 
  3. xtrabackup: recognized client arguments: --user=root --password=* --host=192.168.57.120 --port=3306 --backup=1 --target-dir=/usr/mysql/single/backup 
  4. xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
  5. 210609 21:29:26  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.57.120;port=3306' as 'root'  (using password: YES).
  6. Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=192.168.57.120;port=3306','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: 无法打开共享对象文件: 没有那个文件或目录 at - line 1535.
  7. 210609 21:29:26 Connecting to MySQL server host: 192.168.57.120, user: root, password: set, port: 3306, socket: not set
  8. Using server version 8.0.25
  9. Warning: option 'datadir' has different values:
  10.   '/usr/mysql/single/data' in defaults file
  11.   '/var/lib/mysql/' in SHOW VARIABLES
  12. 210609 21:29:26 Executing LOCK INSTANCE FOR BACKUP...
  13. xtrabackup: uses posix_fadvise().
  14. xtrabackup: cd to /usr/mysql/single/data
  15. xtrabackup: open files limit requested 0, set to 1024
  16. xtrabackup: using the following InnoDB configuration:
  17. xtrabackup:   innodb_data_home_dir = .
  18. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  19. xtrabackup:   innodb_log_group_home_dir = ./
  20. xtrabackup:   innodb_log_files_in_group = 2
  21. xtrabackup:   innodb_log_file_size = 50331648
  22. Number of pools: 1
  23. xtrabackup: inititialize_service_handles suceeded
  24. 210609 21:29:26 Connecting to MySQL server host: 192.168.57.120, user: root, password: set, port: 3306, socket: not set
  25. xtrabackup: Redo Log Archiving is not set up.
  26. xtrabackup: Can't create/write to file '/usr/mysql/single/backup/xtrabackup_logfile' (OS errno 17 - File exists)
  27. xtrabackup: error: failed to open the target stream for 'xtrabackup_logfile'.

prepare 备份目录中的数据，回滚数据文件中事务没有提交的数据

  1. [root@centos single]# xtrabackup --prepare --target-dir=/usr/mysql/single/backup
  2. xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
  3. xtrabackup: recognized client arguments: --prepare=1 --target-dir=/usr/mysql/single/backup 
  4. xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
  5. xtrabackup: cd to /usr/mysql/single/backup/
  6. xtrabackup: This target seems to be not prepared yet.
  7. Number of pools: 1
  8. xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(28649493)
  9. xtrabackup: using the following InnoDB configuration for recovery:
  10. xtrabackup:   innodb_data_home_dir = .
  11. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  12. xtrabackup:   innodb_log_group_home_dir = .
  13. xtrabackup:   innodb_log_files_in_group = 1
  14. xtrabackup:   innodb_log_file_size = 8388608
  15. xtrabackup: inititialize_service_handles suceeded
  16. xtrabackup: using the following InnoDB configuration for recovery:
  17. xtrabackup:   innodb_data_home_dir = .
  18. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  19. xtrabackup:   innodb_log_group_home_dir = .
  20. xtrabackup:   innodb_log_files_in_group = 1
  21. xtrabackup:   innodb_log_file_size = 8388608
  22. xtrabackup: Starting InnoDB instance for recovery.
  23. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
  24. PUNCH HOLE support available
  25. Uses event mutexes
  26. GCC builtin __atomic_thread_fence() is used for memory barrier
  27. Compressed tables use zlib 1.2.11
  28. Number of pools: 1
  29. Using CPU crc32 instructions
  30. Directories to scan './'
  31. Scanning './'
  32. Completed space ID check of 4 files.
  33. Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 
  34. Completed initialization of buffer pool
  35. page_cleaner coordinator priority: -20
  36. page_cleaner worker priority: -20
  37. page_cleaner worker priority: -20
  38. page_cleaner worker priority: -20
  39. The log sequence number 28594717 in the system tablespace does not match the log sequence number 28649493 in the ib_logfiles!
  40. Database was not shutdown normally!
  41. Starting crash recovery.
  42. Starting to parse redo log at lsn = 28649493, whereas checkpoint_lsn = 28649493 and start_lsn = 28649472
  43. Doing recovery: scanned up to log sequence number 28649493
  44. Log background threads are being started...
  45. Applying a batch of 0 redo log records ...
  46. Apply batch completed!
  47. Using undo tablespace './undo_001'.
  48. Using undo tablespace './undo_002'.
  49. Opened 2 existing undo tablespaces.
  50. GTID recovery trx_no: 2571
  51. Creating shared tablespace for temporary tables
  52. Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
  53. File './ibtmp1' size is now 12 MB.
  54. Scanning temp tablespace dir:'./#innodb_temp/'
  55. Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
  56. 8.0.25 started; log sequence number 28649493
  57. Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
  58. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  59. FTS optimize thread exiting.
  60. Starting shutdown...
  61. Log background threads are being closed...
  62. Shutdown completed; log sequence number 28649493
  63. Number of pools: 1
  64. xtrabackup: using the following InnoDB configuration for recovery:
  65. xtrabackup:   innodb_data_home_dir = .
  66. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  67. xtrabackup:   innodb_log_group_home_dir = .
  68. xtrabackup:   innodb_log_files_in_group = 2
  69. xtrabackup:   innodb_log_file_size = 50331648
  70. PUNCH HOLE support available
  71. Uses event mutexes
  72. GCC builtin __atomic_thread_fence() is used for memory barrier
  73. Compressed tables use zlib 1.2.11
  74. Number of pools: 1
  75. Using CPU crc32 instructions
  76. Directories to scan './'
  77. Scanning './'
  78. Completed space ID check of 4 files.
  79. Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 
  80. Completed initialization of buffer pool
  81. page_cleaner coordinator priority: -20
  82. page_cleaner worker priority: -20
  83. page_cleaner worker priority: -20
  84. page_cleaner worker priority: -20
  85. Creating log file ./ib_logfile101
  86. Creating log file ./ib_logfile1
  87. Renaming log file ./ib_logfile101 to ./ib_logfile0
  88. New log files created, LSN=28649996
  89. Starting to parse redo log at lsn = 28649996, whereas checkpoint_lsn = 28649996 and start_lsn = 28649984
  90. Log background threads are being started...
  91. Applying a batch of 0 redo log records ...
  92. Apply batch completed!
  93. Using undo tablespace './undo_001'.
  94. Using undo tablespace './undo_002'.
  95. Opened 2 existing undo tablespaces.
  96. GTID recovery trx_no: 2571
  97. Removed temporary tablespace data file: "ibtmp1"
  98. Creating shared tablespace for temporary tables
  99. Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
  100. File './ibtmp1' size is now 12 MB.
  101. Scanning temp tablespace dir:'./#innodb_temp/'
  102. Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
  103. 8.0.25 started; log sequence number 28649996
  104. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  105. FTS optimize thread exiting.
  106. Trying to access missing tablespace 4294967294
  107. Starting shutdown...
  108. Log background threads are being closed...
  109. Shutdown completed; log sequence number 28649996
  110. 210609 21:43:35 completed OK!

数据还原到目录 /usr/mysql/single/data2，data2目录须为空

  1. [root@centos single]# xtrabackup --copy-back --datadir=/usr/mysql/single/data2 --target-dir=/usr/mysql/single/backup
  2. xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --datadir=/usr/mysql/single/data2 
  3. xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/usr/mysql/single/backup 
  4. xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
  5. 210609 21:46:14 [01] Copying undo_001 to /usr/mysql/single/data2/undo_001
  6. 210609 21:46:14 [01]        ...done
  7. 210609 21:46:14 [01] Copying undo_002 to /usr/mysql/single/data2/undo_002
  8. 210609 21:46:14 [01]        ...done
  9. 210609 21:46:15 [01] Copying ib_logfile0 to /usr/mysql/single/data2/ib_logfile0
  10. 210609 21:46:15 [01]        ...done
  11. 210609 21:46:15 [01] Copying ib_logfile1 to /usr/mysql/single/data2/ib_logfile1
  12. 210609 21:46:15 [01]        ...done
  13. 210609 21:46:15 [01] Copying ibdata1 to /usr/mysql/single/data2/ibdata1
  14. 210609 21:46:15 [01]        ...done
  15. 210609 21:46:15 [01] Copying binlog.000006 to /usr/mysql/single/data2/binlog.000006
  16. 210609 21:46:15 [01]        ...done
  17. 210609 21:46:15 [01] Copying binlog.index to /usr/mysql/single/data2/binlog.index
  18. 210609 21:46:15 [01]        ...done
  19. 210609 21:46:15 [01] Copying ./sys/sys_config.ibd to /usr/mysql/single/data2/sys/sys_config.ibd
  20. 210609 21:46:15 [01]        ...done
  21. 210609 21:46:15 [01] Copying ./mysql.ibd to /usr/mysql/single/data2/mysql.ibd
  22. 210609 21:46:15 [01]        ...done
  23. 210609 21:46:15 [01] Copying ./mysql/general_log_213.sdi to /usr/mysql/single/data2/mysql/general_log_213.sdi
  24. 210609 21:46:15 [01]        ...done
  25. 210609 21:46:15 [01] Copying ./mysql/general_log.CSM to /usr/mysql/single/data2/mysql/general_log.CSM
  26. 210609 21:46:15 [01]        ...done
  27. 210609 21:46:15 [01] Copying ./mysql/general_log.CSV to /usr/mysql/single/data2/mysql/general_log.CSV
  28. 210609 21:46:15 [01]        ...done
  29. 210609 21:46:15 [01] Copying ./mysql/slow_log_214.sdi to /usr/mysql/single/data2/mysql/slow_log_214.sdi
  30. 210609 21:46:15 [01]        ...done
  31. 210609 21:46:15 [01] Copying ./mysql/slow_log.CSM to /usr/mysql/single/data2/mysql/slow_log.CSM
  32. 210609 21:46:15 [01]        ...done
  33. 210609 21:46:15 [01] Copying ./mysql/slow_log.CSV to /usr/mysql/single/data2/mysql/slow_log.CSV
  34. 210609 21:46:15 [01]        ...done
  35. 210609 21:46:15 [01] Copying ./performance_schema/cond_instances_82.sdi to /usr/mysql/single/data2/performance_schema/cond_instances_82.sdi
  36. 210609 21:46:15 [01]        ...done
  37. 210609 21:46:15 [01] Copying ./performance_schema/error_log_83.sdi to /usr/mysql/single/data2/performance_schema/error_log_83.sdi
  38. 210609 21:46:15 [01]        ...done
  39. 210609 21:46:15 [01] Copying ./performance_schema/events_waits_cur_84.sdi to /usr/mysql/single/data2/performance_schema/events_waits_cur_84.sdi
  40. 210609 21:46:15 [01]        ...done
  41. 210609 21:46:15 [01] Copying ./performance_schema/events_waits_his_85.sdi to /usr/mysql/single/data2/performance_schema/events_waits_his_85.sdi
  42. 210609 21:46:15 [01]        ...done
  43. 210609 21:46:15 [01] Copying ./performance_schema/events_waits_his_86.sdi to /usr/mysql/single/data2/performance_schema/events_waits_his_86.sdi
  44. 210609 21:46:15 [01]        ...done
  45. 210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_87.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_87.sdi
  46. 210609 21:46:15 [01]        ...done
  47. 210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_88.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_88.sdi
  48. 210609 21:46:15 [01]        ...done
  49. 210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_89.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_89.sdi
  50. 210609 21:46:15 [01]        ...done
  51. 210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_90.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_90.sdi
  52. 210609 21:46:15 [01]        ...done
  53. 210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_91.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_91.sdi
  54. 210609 21:46:15 [01]        ...done
  55. 210609 21:46:15 [01] Copying ./performance_schema/events_waits_sum_92.sdi to /usr/mysql/single/data2/performance_schema/events_waits_sum_92.sdi
  56. 210609 21:46:15 [01]        ...done
  57. 210609 21:46:15 [01] Copying ./performance_schema/file_instances_93.sdi to /usr/mysql/single/data2/performance_schema/file_instances_93.sdi
  58. 210609 21:46:15 [01]        ...done
  59. 210609 21:46:15 [01] Copying ./performance_schema/file_summary_by__94.sdi to /usr/mysql/single/data2/performance_schema/file_summary_by__94.sdi
  60. 210609 21:46:15 [01]        ...done
  61. 210609 21:46:15 [01] Copying ./performance_schema/file_summary_by__95.sdi to /usr/mysql/single/data2/performance_schema/file_summary_by__95.sdi
  62. 210609 21:46:15 [01]        ...done
  63. 210609 21:46:15 [01] Copying ./performance_schema/host_cache_96.sdi to /usr/mysql/single/data2/performance_schema/host_cache_96.sdi
  64. 210609 21:46:15 [01]        ...done
  65. 210609 21:46:15 [01] Copying ./performance_schema/mutex_instances_97.sdi to /usr/mysql/single/data2/performance_schema/mutex_instances_97.sdi
  66. 210609 21:46:15 [01]        ...done
  67. 210609 21:46:15 [01] Copying ./performance_schema/objects_summary__98.sdi to /usr/mysql/single/data2/performance_schema/objects_summary__98.sdi
  68. 210609 21:46:15 [01]        ...done
  69. 210609 21:46:15 [01] Copying ./performance_schema/performance_time_99.sdi to /usr/mysql/single/data2/performance_schema/performance_time_99.sdi
  70. 210609 21:46:15 [01]        ...done
  71. 210609 21:46:15 [01] Copying ./performance_schema/processlist_100.sdi to /usr/mysql/single/data2/performance_schema/processlist_100.sdi
  72. 210609 21:46:15 [01]        ...done
  73. 210609 21:46:15 [01] Copying ./performance_schema/rwlock_instances_101.sdi to /usr/mysql/single/data2/performance_schema/rwlock_instances_101.sdi
  74. 210609 21:46:15 [01]        ...done
  75. 210609 21:46:15 [01] Copying ./performance_schema/setup_actors_102.sdi to /usr/mysql/single/data2/performance_schema/setup_actors_102.sdi
  76. 210609 21:46:15 [01]        ...done
  77. 210609 21:46:15 [01] Copying ./performance_schema/setup_consumers_103.sdi to /usr/mysql/single/data2/performance_schema/setup_consumers_103.sdi
  78. 210609 21:46:15 [01]        ...done
  79. 210609 21:46:15 [01] Copying ./performance_schema/setup_instrument_104.sdi to /usr/mysql/single/data2/performance_schema/setup_instrument_104.sdi
  80. 210609 21:46:15 [01]        ...done
  81. 210609 21:46:15 [01] Copying ./performance_schema/setup_objects_105.sdi to /usr/mysql/single/data2/performance_schema/setup_objects_105.sdi
  82. 210609 21:46:15 [01]        ...done
  83. 210609 21:46:15 [01] Copying ./performance_schema/setup_threads_106.sdi to /usr/mysql/single/data2/performance_schema/setup_threads_106.sdi
  84. 210609 21:46:15 [01]        ...done
  85. 210609 21:46:15 [01] Copying ./performance_schema/table_io_waits_s_107.sdi to /usr/mysql/single/data2/performance_schema/table_io_waits_s_107.sdi
  86. 210609 21:46:15 [01]        ...done
  87. 210609 21:46:15 [01] Copying ./performance_schema/table_io_waits_s_108.sdi to /usr/mysql/single/data2/performance_schema/table_io_waits_s_108.sdi
  88. 210609 21:46:15 [01]        ...done
  89. 210609 21:46:15 [01] Copying ./performance_schema/table_lock_waits_109.sdi to /usr/mysql/single/data2/performance_schema/table_lock_waits_109.sdi
  90. 210609 21:46:15 [01]        ...done
  91. 210609 21:46:15 [01] Copying ./performance_schema/threads_110.sdi to /usr/mysql/single/data2/performance_schema/threads_110.sdi
  92. 210609 21:46:15 [01]        ...done
  93. 210609 21:46:15 [01] Copying ./performance_schema/events_stages_cu_111.sdi to /usr/mysql/single/data2/performance_schema/events_stages_cu_111.sdi
  94. 210609 21:46:15 [01]        ...done
  95. 210609 21:46:15 [01] Copying ./performance_schema/events_stages_hi_112.sdi to /usr/mysql/single/data2/performance_schema/events_stages_hi_112.sdi
  96. 210609 21:46:15 [01]        ...done
  97. 210609 21:46:15 [01] Copying ./performance_schema/events_stages_hi_113.sdi to /usr/mysql/single/data2/performance_schema/events_stages_hi_113.sdi
  98. 210609 21:46:15 [01]        ...done
  99. 210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_114.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_114.sdi
  100. 210609 21:46:15 [01]        ...done
  101. 210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_115.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_115.sdi
  102. 210609 21:46:15 [01]        ...done
  103. 210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_116.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_116.sdi
  104. 210609 21:46:15 [01]        ...done
  105. 210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_117.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_117.sdi
  106. 210609 21:46:15 [01]        ...done
  107. 210609 21:46:15 [01] Copying ./performance_schema/events_stages_su_118.sdi to /usr/mysql/single/data2/performance_schema/events_stages_su_118.sdi
  108. 210609 21:46:15 [01]        ...done
  109. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_119.sdi to /usr/mysql/single/data2/performance_schema/events_statement_119.sdi
  110. 210609 21:46:15 [01]        ...done
  111. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_120.sdi to /usr/mysql/single/data2/performance_schema/events_statement_120.sdi
  112. 210609 21:46:15 [01]        ...done
  113. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_121.sdi to /usr/mysql/single/data2/performance_schema/events_statement_121.sdi
  114. 210609 21:46:15 [01]        ...done
  115. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_122.sdi to /usr/mysql/single/data2/performance_schema/events_statement_122.sdi
  116. 210609 21:46:15 [01]        ...done
  117. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_123.sdi to /usr/mysql/single/data2/performance_schema/events_statement_123.sdi
  118. 210609 21:46:15 [01]        ...done
  119. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_124.sdi to /usr/mysql/single/data2/performance_schema/events_statement_124.sdi
  120. 210609 21:46:15 [01]        ...done
  121. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_125.sdi to /usr/mysql/single/data2/performance_schema/events_statement_125.sdi
  122. 210609 21:46:15 [01]        ...done
  123. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_126.sdi to /usr/mysql/single/data2/performance_schema/events_statement_126.sdi
  124. 210609 21:46:15 [01]        ...done
  125. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_127.sdi to /usr/mysql/single/data2/performance_schema/events_statement_127.sdi
  126. 210609 21:46:15 [01]        ...done
  127. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_128.sdi to /usr/mysql/single/data2/performance_schema/events_statement_128.sdi
  128. 210609 21:46:15 [01]        ...done
  129. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_129.sdi to /usr/mysql/single/data2/performance_schema/events_statement_129.sdi
  130. 210609 21:46:15 [01]        ...done
  131. 210609 21:46:15 [01] Copying ./performance_schema/events_statement_130.sdi to /usr/mysql/single/data2/performance_schema/events_statement_130.sdi
  132. 210609 21:46:15 [01]        ...done
  133. 210609 21:46:15 [01] Copying ./performance_schema/events_transacti_131.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_131.sdi
  134. 210609 21:46:15 [01]        ...done
  135. 210609 21:46:15 [01] Copying ./performance_schema/events_transacti_132.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_132.sdi
  136. 210609 21:46:15 [01]        ...done
  137. 210609 21:46:15 [01] Copying ./performance_schema/events_transacti_133.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_133.sdi
  138. 210609 21:46:15 [01]        ...done
  139. 210609 21:46:15 [01] Copying ./performance_schema/events_transacti_134.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_134.sdi
  140. 210609 21:46:15 [01]        ...done
  141. 210609 21:46:15 [01] Copying ./performance_schema/events_transacti_135.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_135.sdi
  142. 210609 21:46:15 [01]        ...done
  143. 210609 21:46:15 [01] Copying ./performance_schema/events_transacti_136.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_136.sdi
  144. 210609 21:46:15 [01]        ...done
  145. 210609 21:46:15 [01] Copying ./performance_schema/events_transacti_137.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_137.sdi
  146. 210609 21:46:15 [01]        ...done
  147. 210609 21:46:15 [01] Copying ./performance_schema/events_transacti_138.sdi to /usr/mysql/single/data2/performance_schema/events_transacti_138.sdi
  148. 210609 21:46:15 [01]        ...done
  149. 210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_139.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_139.sdi
  150. 210609 21:46:15 [01]        ...done
  151. 210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_140.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_140.sdi
  152. 210609 21:46:15 [01]        ...done
  153. 210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_141.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_141.sdi
  154. 210609 21:46:15 [01]        ...done
  155. 210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_142.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_142.sdi
  156. 210609 21:46:15 [01]        ...done
  157. 210609 21:46:15 [01] Copying ./performance_schema/events_errors_su_143.sdi to /usr/mysql/single/data2/performance_schema/events_errors_su_143.sdi
  158. 210609 21:46:15 [01]        ...done
  159. 210609 21:46:15 [01] Copying ./performance_schema/users_144.sdi to /usr/mysql/single/data2/performance_schema/users_144.sdi
  160. 210609 21:46:15 [01]        ...done
  161. 210609 21:46:15 [01] Copying ./performance_schema/accounts_145.sdi to /usr/mysql/single/data2/performance_schema/accounts_145.sdi
  162. 210609 21:46:15 [01]        ...done
  163. 210609 21:46:15 [01] Copying ./performance_schema/hosts_146.sdi to /usr/mysql/single/data2/performance_schema/hosts_146.sdi
  164. 210609 21:46:15 [01]        ...done
  165. 210609 21:46:15 [01] Copying ./performance_schema/socket_instances_147.sdi to /usr/mysql/single/data2/performance_schema/socket_instances_147.sdi
  166. 210609 21:46:15 [01]        ...done
  167. 210609 21:46:15 [01] Copying ./performance_schema/socket_summary_b_148.sdi to /usr/mysql/single/data2/performance_schema/socket_summary_b_148.sdi
  168. 210609 21:46:15 [01]        ...done
  169. 210609 21:46:15 [01] Copying ./performance_schema/socket_summary_b_149.sdi to /usr/mysql/single/data2/performance_schema/socket_summary_b_149.sdi
  170. 210609 21:46:15 [01]        ...done
  171. 210609 21:46:15 [01] Copying ./performance_schema/session_connect__150.sdi to /usr/mysql/single/data2/performance_schema/session_connect__150.sdi
  172. 210609 21:46:15 [01]        ...done
  173. 210609 21:46:15 [01] Copying ./performance_schema/session_account__151.sdi to /usr/mysql/single/data2/performance_schema/session_account__151.sdi
  174. 210609 21:46:15 [01]        ...done
  175. 210609 21:46:15 [01] Copying ./performance_schema/keyring_keys_152.sdi to /usr/mysql/single/data2/performance_schema/keyring_keys_152.sdi
  176. 210609 21:46:15 [01]        ...done
  177. 210609 21:46:15 [01] Copying ./performance_schema/memory_summary_g_153.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_g_153.sdi
  178. 210609 21:46:15 [01]        ...done
  179. 210609 21:46:15 [01] Copying ./performance_schema/memory_summary_b_154.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_b_154.sdi
  180. 210609 21:46:15 [01]        ...done
  181. 210609 21:46:15 [01] Copying ./performance_schema/memory_summary_b_155.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_b_155.sdi
  182. 210609 21:46:15 [01]        ...done
  183. 210609 21:46:15 [01] Copying ./performance_schema/memory_summary_b_156.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_b_156.sdi
  184. 210609 21:46:15 [01]        ...done
  185. 210609 21:46:15 [01] Copying ./performance_schema/memory_summary_b_157.sdi to /usr/mysql/single/data2/performance_schema/memory_summary_b_157.sdi
  186. 210609 21:46:15 [01]        ...done
  187. 210609 21:46:15 [01] Copying ./performance_schema/table_handles_158.sdi to /usr/mysql/single/data2/performance_schema/table_handles_158.sdi
  188. 210609 21:46:15 [01]        ...done
  189. 210609 21:46:15 [01] Copying ./performance_schema/metadata_locks_159.sdi to /usr/mysql/single/data2/performance_schema/metadata_locks_159.sdi
  190. 210609 21:46:15 [01]        ...done
  191. 210609 21:46:15 [01] Copying ./performance_schema/data_locks_160.sdi to /usr/mysql/single/data2/performance_schema/data_locks_160.sdi
  192. 210609 21:46:15 [01]        ...done
  193. 210609 21:46:15 [01] Copying ./performance_schema/data_lock_waits_161.sdi to /usr/mysql/single/data2/performance_schema/data_lock_waits_161.sdi
  194. 210609 21:46:15 [01]        ...done
  195. 210609 21:46:15 [01] Copying ./performance_schema/replication_conn_162.sdi to /usr/mysql/single/data2/performance_schema/replication_conn_162.sdi
  196. 210609 21:46:15 [01]        ...done
  197. 210609 21:46:15 [01] Copying ./performance_schema/replication_grou_163.sdi to /usr/mysql/single/data2/performance_schema/replication_grou_163.sdi
  198. 210609 21:46:15 [01]        ...done
  199. 210609 21:46:15 [01] Copying ./performance_schema/replication_conn_164.sdi to /usr/mysql/single/data2/performance_schema/replication_conn_164.sdi
  200. 210609 21:46:15 [01]        ...done
  201. 210609 21:46:15 [01] Copying ./performance_schema/replication_appl_165.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_165.sdi
  202. 210609 21:46:15 [01]        ...done
  203. 210609 21:46:15 [01] Copying ./performance_schema/replication_appl_166.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_166.sdi
  204. 210609 21:46:15 [01]        ...done
  205. 210609 21:46:15 [01] Copying ./performance_schema/replication_appl_167.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_167.sdi
  206. 210609 21:46:15 [01]        ...done
  207. 210609 21:46:15 [01] Copying ./performance_schema/replication_appl_168.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_168.sdi
  208. 210609 21:46:15 [01]        ...done
  209. 210609 21:46:15 [01] Copying ./performance_schema/replication_grou_169.sdi to /usr/mysql/single/data2/performance_schema/replication_grou_169.sdi
  210. 210609 21:46:15 [01]        ...done
  211. 210609 21:46:15 [01] Copying ./performance_schema/replication_appl_170.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_170.sdi
  212. 210609 21:46:15 [01]        ...done
  213. 210609 21:46:15 [01] Copying ./performance_schema/replication_appl_171.sdi to /usr/mysql/single/data2/performance_schema/replication_appl_171.sdi
  214. 210609 21:46:15 [01]        ...done
  215. 210609 21:46:15 [01] Copying ./performance_schema/replication_asyn_172.sdi to /usr/mysql/single/data2/performance_schema/replication_asyn_172.sdi
  216. 210609 21:46:15 [01]        ...done
  217. 210609 21:46:15 [01] Copying ./performance_schema/replication_asyn_173.sdi to /usr/mysql/single/data2/performance_schema/replication_asyn_173.sdi
  218. 210609 21:46:15 [01]        ...done
  219. 210609 21:46:15 [01] Copying ./performance_schema/log_status_174.sdi to /usr/mysql/single/data2/performance_schema/log_status_174.sdi
  220. 210609 21:46:15 [01]        ...done
  221. 210609 21:46:15 [01] Copying ./performance_schema/prepared_stateme_175.sdi to /usr/mysql/single/data2/performance_schema/prepared_stateme_175.sdi
  222. 210609 21:46:15 [01]        ...done
  223. 210609 21:46:15 [01] Copying ./performance_schema/user_variables_b_176.sdi to /usr/mysql/single/data2/performance_schema/user_variables_b_176.sdi
  224. 210609 21:46:15 [01]        ...done
  225. 210609 21:46:15 [01] Copying ./performance_schema/status_by_accoun_177.sdi to /usr/mysql/single/data2/performance_schema/status_by_accoun_177.sdi
  226. 210609 21:46:15 [01]        ...done
  227. 210609 21:46:15 [01] Copying ./performance_schema/status_by_host_178.sdi to /usr/mysql/single/data2/performance_schema/status_by_host_178.sdi
  228. 210609 21:46:15 [01]        ...done
  229. 210609 21:46:15 [01] Copying ./performance_schema/status_by_thread_179.sdi to /usr/mysql/single/data2/performance_schema/status_by_thread_179.sdi
  230. 210609 21:46:15 [01]        ...done
  231. 210609 21:46:15 [01] Copying ./performance_schema/status_by_user_180.sdi to /usr/mysql/single/data2/performance_schema/status_by_user_180.sdi
  232. 210609 21:46:15 [01]        ...done
  233. 210609 21:46:15 [01] Copying ./performance_schema/global_status_181.sdi to /usr/mysql/single/data2/performance_schema/global_status_181.sdi
  234. 210609 21:46:15 [01]        ...done
  235. 210609 21:46:15 [01] Copying ./performance_schema/session_status_182.sdi to /usr/mysql/single/data2/performance_schema/session_status_182.sdi
  236. 210609 21:46:15 [01]        ...done
  237. 210609 21:46:15 [01] Copying ./performance_schema/variables_by_thr_183.sdi to /usr/mysql/single/data2/performance_schema/variables_by_thr_183.sdi
  238. 210609 21:46:15 [01]        ...done
  239. 210609 21:46:15 [01] Copying ./performance_schema/global_variables_184.sdi to /usr/mysql/single/data2/performance_schema/global_variables_184.sdi
  240. 210609 21:46:15 [01]        ...done
  241. 210609 21:46:15 [01] Copying ./performance_schema/session_variable_185.sdi to /usr/mysql/single/data2/performance_schema/session_variable_185.sdi
  242. 210609 21:46:15 [01]        ...done
  243. 210609 21:46:15 [01] Copying ./performance_schema/variables_info_186.sdi to /usr/mysql/single/data2/performance_schema/variables_info_186.sdi
  244. 210609 21:46:15 [01]        ...done
  245. 210609 21:46:15 [01] Copying ./performance_schema/persisted_variab_187.sdi to /usr/mysql/single/data2/performance_schema/persisted_variab_187.sdi
  246. 210609 21:46:15 [01]        ...done
  247. 210609 21:46:15 [01] Copying ./performance_schema/user_defined_fun_188.sdi to /usr/mysql/single/data2/performance_schema/user_defined_fun_188.sdi
  248. 210609 21:46:15 [01]        ...done
  249. 210609 21:46:15 [01] Copying ./performance_schema/binary_log_trans_189.sdi to /usr/mysql/single/data2/performance_schema/binary_log_trans_189.sdi
  250. 210609 21:46:15 [01]        ...done
  251. 210609 21:46:15 [01] Copying ./performance_schema/tls_channel_stat_190.sdi to /usr/mysql/single/data2/performance_schema/tls_channel_stat_190.sdi
  252. 210609 21:46:15 [01]        ...done
  253. 210609 21:46:15 [01] Copying ./performance_schema/keyring_componen_191.sdi to /usr/mysql/single/data2/performance_schema/keyring_componen_191.sdi
  254. 210609 21:46:15 [01]        ...done
  255. 210609 21:46:15 [01] Copying ./ib_buffer_pool to /usr/mysql/single/data2/ib_buffer_pool
  256. 210609 21:46:15 [01]        ...done
  257. 210609 21:46:15 [01] Copying ./xtrabackup_info to /usr/mysql/single/data2/xtrabackup_info
  258. 210609 21:46:15 [01]        ...done
  259. 210609 21:46:15 [01] Copying ./xtrabackup_master_key_id to /usr/mysql/single/data2/xtrabackup_master_key_id
  260. 210609 21:46:15 [01]        ...done
  261. 210609 21:46:15 [01] Copying ./ibtmp1 to /usr/mysql/single/data2/ibtmp1
  262. 210609 21:46:16 [01]        ...done
  263. 210609 21:46:16 [01] Creating directory ./#innodb_temp
  264. 210609 21:46:16 [01] ...done.
  265. 210609 21:46:16 completed OK!

如果还原目录不为空，则不能还原数据

  1. [root@centos ~]# xtrabackup --copy-back --datadir=/usr/mysql/single/data2 --target-dir=/usr/mysql/single/backup
  2. xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --datadir=/usr/mysql/single/data2 
  3. xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/usr/mysql/single/backup 
  4. xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
  5. Original data directory /usr/mysql/single/data2 is not empty!

mysql2 查询数据

  1. root@6f71527b845f:/# mysql -u root -p
  2. Enter password: 
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 8
  5. Server version: 8.0.25 MySQL Community Server - GPL
  7. Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> show databases;
  16. +--------------------+
  17. | Database           |
  18. +--------------------+
  19. | information_schema |
  20. | mysql              |
  21. | performance_schema |
  22. | sys                |
  23. | test               |
  24. +--------------------+
  25. 5 rows in set (0.00 sec)
  27. mysql> use test;
  28. Reading table information for completion of table and column names
  29. You can turn off this feature to get a quicker startup with -A
  31. Database changed
  32. mysql> select * from test;
  33. +----+-------+
  34. | id | value |
  35. +----+-------+
  36. |  1 |     2 |
  37. |  2 |     3 |
  38. |  3 |     4 |
  39. |  4 |     5 |
  40. |  5 |     6 |
  41. |  6 |     8 |
  42. |  7 |     9 |
  43. |  8 |    10 |
  44. |  9 |    10 |
  45. +----+-------+
  46. 9 rows in set (0.00 sec)

mysql2可查询到mysql中插入的数据

发表评论

表情：
评论列表 （有 0 条评论，241人围观）

还没有评论，来说两句吧...

相关阅读

    相关 redis全量复制和部分复制简介

    基本概念： 全量复制和部分复制都是围绕着redis主从复制展开讨论，这里不讨论概念性的理论，只讨论大家比较关心的几个点。 有兴趣深入研究的同学，可以参考 a)

    清疚清疚/ 0 赞/ 201 阅读