Restore single table from physical backup in Percona XtraDB cluster 8.0.22
Restore single table from physical backup in Percona XtraDB cluster 8.0.22
--------------------------------------------------------------------
Step - 1 - Check the table row count before taking full backup..
--------------------------------------------------------------------
[root@percona-xtradb-1 ~]# mysql -u root -p
Enter password: root
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 30.Server version: 8.0.23-14.1 Percona XtraDB Cluster (GPL), Release rel14, Revision d3b9a1d, WSREP version 26.4.3.Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, Oracle and/or its affiliates.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> select version();
+-------------+
| version() |
+-------------+
| 8.0.23-14.1 |
+-------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| acs |
| awsdms_control |
| cluster_testing |
| sample123 |
| failover |
| failover_2 |
| information_schema |
| killed_process |
| mysql |
| performance_schema |
| server_3_down |
| sys |
+--------------------+
12 rows in set (0.01 sec)
mysql> use sample123;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_sample123 |
+-------------------+
| node |
+-------------------+
1 row in set (0.00 sec)
mysql> select count(1) from node;
+----------+
| count(1) |
+----------+
| 2228709 |
+----------+
1 row in set (3.66 sec)
mysql> select MD_ID from node LIMIT 5;
+---------+
| MD_ID |
+---------+
| 9603878 |
| 9603879 |
| 9603880 |
| 9603882 |
| 9603883 |
+---------+
5 rows in set (0.01 sec)
---------------------------------------------------------------------
Step - 2 Take a backup of FULL physical Backup by using XtraDBbackup in Percona Cluster .
---------------------------------------------------------------------
[root@percona-xtradb-1 ~]# xtrabackup --version
xtrabackup: recognized server arguments: --server-id=1 --datadir=/var/lib/mysql
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
[root@percona-xtradb-1 ~]# cd /home/
[root@percona-xtradb-1 home]# pwd
/home
[root@percona-xtradb-1 home]# ls -ltr
drwx------. 3 ec2-user ec2-user 145 Aug 26 07:53 ec2-user
[root@percona-xtradb-1 home]# mkdir backup
[root@percona-xtradb-1 home]# ls -ltr
drwx------. 3 ec2-user ec2-user 145 Aug 26 07:53 ec2-user
drwxr-xr-x 2 root root 6 Aug 26 09:51 backup
[root@percona-xtradb-1 home]#cd
[root@percona-xtradb-1 ]#
[root@percona-xtradb-1 ~]# time xtrabackup --defaults-file=/etc/my.cnf --backup --port=3306 --socket=/var/lib/mysql/mysql.sock --slave-info --target-dir=/home/backup --user=root --password=root
xtrabackup: recognized server arguments: --server-id=1 --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --socket=/var/lib/mysql/mysql.sock --port=3306 --backup=1 --port=3306 --socket=/var/lib/mysql/mysql.sock --slave-info=1 --target-dir=/home/backup --user=root --password=*
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
210826 09:53:59 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).
210826 09:53:59 version_check Connected to MySQL server
210826 09:53:59 version_check Executing a version check against the server...
# A software update is available:
210826 09:53:59 version_check Done.
210826 09:53:59 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /var/lib/mysql/mysql.sock
Using server version 8.0.23-14.1
210826 09:53:59 Executing LOCK TABLES FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/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
Number of pools: 1
xtrabackup: inititialize_service_handles suceeded
210826 09:53:59 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /var/lib/mysql/mysql.sock
xtrabackup: Redo Log Archiving is not set up.
210826 09:53:59 >> log scanned up to (4624355452)
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Directory '/var/lib/mysql/.percona-toolkit.uuid' will not be scanned because it is a hidden directory.
Completed space ID check of 2 files.
Allocated tablespace ID 34 for awsdms_control/awsdms_apply_exceptions, old maximum was 0
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
210826 09:54:00 [01] Copying ./ibdata1 to /home/backup/ibdata1
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./sys/sys_config.ibd to /home/backup/sys/sys_config.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./mysql/wsrep_cluster.ibd to /home/backup/mysql/wsrep_cluster.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./mysql/wsrep_cluster_members.ibd to /home/backup/mysql/wsrep_cluster_members.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./mysql/wsrep_streaming_log.ibd to /home/backup/mysql/wsrep_streaming_log.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./cluster_testing/verifying.ibd to /home/backup/cluster_testing/verifying.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./killed_process/killed_pro.ibd to /home/backup/killed_process/killed_pro.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./awsdms_control/awsdms_apply_exceptions.ibd to /home/backup/awsdms_control/awsdms_apply_exceptions.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./awsdms_control/awsdms_history.ibd to /home/backup/awsdms_control/awsdms_history.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./awsdms_control/awsdms_status.ibd to /home/backup/awsdms_control/awsdms_status.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./awsdms_control/awsdms_suspended_tables.ibd to /home/backup/awsdms_control/awsdms_suspended_tables.ibd
210826 09:54:00 [01] ...done
210826 09:54:00 [01] Copying ./awsdms_control/awsdms_validation_failures_v1.ibd to /home/backup/awsdms_control/awsdms_validation_failures_v1.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p0.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p0.ibd to /home/backup/sample123/node#p#p0.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p1.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p1.ibd to /home/backup/sample123/node#p#p1.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p2.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p2.ibd to /home/backup/sample123/node#p#p2.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p3.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p3.ibd to /home/backup/sample123/node#p#p3.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p4.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p4.ibd to /home/backup/sample123/node#p#p4.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p5.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p5.ibd to /home/backup/sample123/node#p#p5.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p6.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p6.ibd to /home/backup/sample123/node#p#p6.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p7.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p7.ibd to /home/backup/sample123/node#p#p7.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p8.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p8.ibd to /home/backup/sample123/node#p#p8.ibd
210826 09:54:00 [01] ...done
[01] ./sample123/node#p#p9.ibd is compressed with page size = 8192 bytes
210826 09:54:00 [01] Copying ./sample123/node#p#p9.ibd to /home/backup/sample123/node#p#p9.ibd
210826 09:54:00 >> log scanned up to (4624355452)
210826 09:54:01 >> log scanned up to (4624355452)
210826 09:54:02 >> log scanned up to (4624355452)
210826 09:54:03 >> log scanned up to (4624355452)
210826 09:54:04 >> log scanned up to (4624355452)
210826 09:54:05 >> log scanned up to (4624355452)
210826 09:54:06 >> log scanned up to (4624355452)
210826 09:54:07 [01] ...done
210826 09:54:07 [01] Copying ./mysql.ibd to /home/backup/mysql.ibd
210826 09:54:07 >> log scanned up to (4624355452)
210826 09:54:08 [01] ...done
210826 09:54:08 [01] Copying ./undo_002 to /home/backup/undo_002
210826 09:54:08 [01] ...done
210826 09:54:08 [01] Copying ./undo_001 to /home/backup/undo_001
210826 09:54:08 [01] ...done
210826 09:54:08 >> log scanned up to (4624355452)
210826 09:54:09 Starting to backup non-InnoDB tables and files
210826 09:54:09 [01] Copying mysql/general_log_222.sdi to /home/backup/mysql/general_log_222.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying mysql/general_log.CSM to /home/backup/mysql/general_log.CSM
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying mysql/general_log.CSV to /home/backup/mysql/general_log.CSV
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying mysql/slow_log_223.sdi to /home/backup/mysql/slow_log_223.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying mysql/slow_log.CSM to /home/backup/mysql/slow_log.CSM
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying mysql/slow_log.CSV to /home/backup/mysql/slow_log.CSV
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/cond_instances_91.sdi to /home/backup/performance_schema/cond_instances_91.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/error_log_92.sdi to /home/backup/performance_schema/error_log_92.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_waits_cur_93.sdi to /home/backup/performance_schema/events_waits_cur_93.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_waits_his_94.sdi to /home/backup/performance_schema/events_waits_his_94.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_waits_his_95.sdi to /home/backup/performance_schema/events_waits_his_95.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_waits_sum_96.sdi to /home/backup/performance_schema/events_waits_sum_96.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_waits_sum_97.sdi to /home/backup/performance_schema/events_waits_sum_97.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_waits_sum_98.sdi to /home/backup/performance_schema/events_waits_sum_98.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_waits_sum_99.sdi to /home/backup/performance_schema/events_waits_sum_99.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_waits_sum_100.sdi to /home/backup/performance_schema/events_waits_sum_100.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_waits_sum_101.sdi to /home/backup/performance_schema/events_waits_sum_101.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/file_instances_102.sdi to /home/backup/performance_schema/file_instances_102.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/file_summary_by__103.sdi to /home/backup/performance_schema/file_summary_by__103.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/file_summary_by__104.sdi to /home/backup/performance_schema/file_summary_by__104.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/host_cache_105.sdi to /home/backup/performance_schema/host_cache_105.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/mutex_instances_106.sdi to /home/backup/performance_schema/mutex_instances_106.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/objects_summary__107.sdi to /home/backup/performance_schema/objects_summary__107.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/performance_time_108.sdi to /home/backup/performance_schema/performance_time_108.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/processlist_109.sdi to /home/backup/performance_schema/processlist_109.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/rwlock_instances_110.sdi to /home/backup/performance_schema/rwlock_instances_110.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/setup_actors_111.sdi to /home/backup/performance_schema/setup_actors_111.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/setup_consumers_112.sdi to /home/backup/performance_schema/setup_consumers_112.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/setup_instrument_113.sdi to /home/backup/performance_schema/setup_instrument_113.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/setup_objects_114.sdi to /home/backup/performance_schema/setup_objects_114.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/setup_threads_115.sdi to /home/backup/performance_schema/setup_threads_115.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/table_io_waits_s_116.sdi to /home/backup/performance_schema/table_io_waits_s_116.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/table_io_waits_s_117.sdi to /home/backup/performance_schema/table_io_waits_s_117.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/table_lock_waits_118.sdi to /home/backup/performance_schema/table_lock_waits_118.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/threads_119.sdi to /home/backup/performance_schema/threads_119.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_stages_cu_120.sdi to /home/backup/performance_schema/events_stages_cu_120.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_stages_hi_121.sdi to /home/backup/performance_schema/events_stages_hi_121.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_stages_hi_122.sdi to /home/backup/performance_schema/events_stages_hi_122.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_stages_su_123.sdi to /home/backup/performance_schema/events_stages_su_123.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_stages_su_124.sdi to /home/backup/performance_schema/events_stages_su_124.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_stages_su_125.sdi to /home/backup/performance_schema/events_stages_su_125.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_stages_su_126.sdi to /home/backup/performance_schema/events_stages_su_126.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_stages_su_127.sdi to /home/backup/performance_schema/events_stages_su_127.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_128.sdi to /home/backup/performance_schema/events_statement_128.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_129.sdi to /home/backup/performance_schema/events_statement_129.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_130.sdi to /home/backup/performance_schema/events_statement_130.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_131.sdi to /home/backup/performance_schema/events_statement_131.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_132.sdi to /home/backup/performance_schema/events_statement_132.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_133.sdi to /home/backup/performance_schema/events_statement_133.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_134.sdi to /home/backup/performance_schema/events_statement_134.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_135.sdi to /home/backup/performance_schema/events_statement_135.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_136.sdi to /home/backup/performance_schema/events_statement_136.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_137.sdi to /home/backup/performance_schema/events_statement_137.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_138.sdi to /home/backup/performance_schema/events_statement_138.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_statement_139.sdi to /home/backup/performance_schema/events_statement_139.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_transacti_140.sdi to /home/backup/performance_schema/events_transacti_140.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_transacti_141.sdi to /home/backup/performance_schema/events_transacti_141.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_transacti_142.sdi to /home/backup/performance_schema/events_transacti_142.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_transacti_143.sdi to /home/backup/performance_schema/events_transacti_143.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_transacti_144.sdi to /home/backup/performance_schema/events_transacti_144.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_transacti_145.sdi to /home/backup/performance_schema/events_transacti_145.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_transacti_146.sdi to /home/backup/performance_schema/events_transacti_146.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_transacti_147.sdi to /home/backup/performance_schema/events_transacti_147.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_errors_su_148.sdi to /home/backup/performance_schema/events_errors_su_148.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_errors_su_149.sdi to /home/backup/performance_schema/events_errors_su_149.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_errors_su_150.sdi to /home/backup/performance_schema/events_errors_su_150.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_errors_su_151.sdi to /home/backup/performance_schema/events_errors_su_151.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/events_errors_su_152.sdi to /home/backup/performance_schema/events_errors_su_152.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/users_153.sdi to /home/backup/performance_schema/users_153.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/accounts_154.sdi to /home/backup/performance_schema/accounts_154.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/hosts_155.sdi to /home/backup/performance_schema/hosts_155.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/socket_instances_156.sdi to /home/backup/performance_schema/socket_instances_156.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/socket_summary_b_157.sdi to /home/backup/performance_schema/socket_summary_b_157.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/socket_summary_b_158.sdi to /home/backup/performance_schema/socket_summary_b_158.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/session_connect__159.sdi to /home/backup/performance_schema/session_connect__159.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/session_account__160.sdi to /home/backup/performance_schema/session_account__160.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/keyring_keys_161.sdi to /home/backup/performance_schema/keyring_keys_161.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/memory_summary_g_162.sdi to /home/backup/performance_schema/memory_summary_g_162.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/memory_summary_b_163.sdi to /home/backup/performance_schema/memory_summary_b_163.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/memory_summary_b_164.sdi to /home/backup/performance_schema/memory_summary_b_164.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/memory_summary_b_165.sdi to /home/backup/performance_schema/memory_summary_b_165.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/memory_summary_b_166.sdi to /home/backup/performance_schema/memory_summary_b_166.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/table_handles_167.sdi to /home/backup/performance_schema/table_handles_167.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/metadata_locks_168.sdi to /home/backup/performance_schema/metadata_locks_168.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/data_locks_169.sdi to /home/backup/performance_schema/data_locks_169.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/data_lock_waits_170.sdi to /home/backup/performance_schema/data_lock_waits_170.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_conn_171.sdi to /home/backup/performance_schema/replication_conn_171.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_grou_172.sdi to /home/backup/performance_schema/replication_grou_172.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_conn_173.sdi to /home/backup/performance_schema/replication_conn_173.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_appl_174.sdi to /home/backup/performance_schema/replication_appl_174.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_appl_175.sdi to /home/backup/performance_schema/replication_appl_175.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_appl_176.sdi to /home/backup/performance_schema/replication_appl_176.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_appl_177.sdi to /home/backup/performance_schema/replication_appl_177.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_grou_178.sdi to /home/backup/performance_schema/replication_grou_178.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_appl_179.sdi to /home/backup/performance_schema/replication_appl_179.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_appl_180.sdi to /home/backup/performance_schema/replication_appl_180.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_asyn_181.sdi to /home/backup/performance_schema/replication_asyn_181.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/replication_asyn_182.sdi to /home/backup/performance_schema/replication_asyn_182.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/log_status_183.sdi to /home/backup/performance_schema/log_status_183.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/prepared_stateme_184.sdi to /home/backup/performance_schema/prepared_stateme_184.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/user_variables_b_185.sdi to /home/backup/performance_schema/user_variables_b_185.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/status_by_accoun_186.sdi to /home/backup/performance_schema/status_by_accoun_186.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/status_by_host_187.sdi to /home/backup/performance_schema/status_by_host_187.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/status_by_thread_188.sdi to /home/backup/performance_schema/status_by_thread_188.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/status_by_user_189.sdi to /home/backup/performance_schema/status_by_user_189.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/global_status_190.sdi to /home/backup/performance_schema/global_status_190.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/session_status_191.sdi to /home/backup/performance_schema/session_status_191.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/variables_by_thr_192.sdi to /home/backup/performance_schema/variables_by_thr_192.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/global_variables_193.sdi to /home/backup/performance_schema/global_variables_193.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/session_variable_194.sdi to /home/backup/performance_schema/session_variable_194.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/variables_info_195.sdi to /home/backup/performance_schema/variables_info_195.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/persisted_variab_196.sdi to /home/backup/performance_schema/persisted_variab_196.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/user_defined_fun_197.sdi to /home/backup/performance_schema/user_defined_fun_197.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/binary_log_trans_198.sdi to /home/backup/performance_schema/binary_log_trans_198.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/tls_channel_stat_199.sdi to /home/backup/performance_schema/tls_channel_stat_199.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [01] Copying performance_schema/pxc_cluster_view_200.sdi to /home/backup/performance_schema/pxc_cluster_view_200.sdi
210826 09:54:09 [01] ...done
210826 09:54:09 [00] Writing /home/backup/acs/db.opt
210826 09:54:09 [00] ...done
210826 09:54:09 [00] Writing /home/backup/failover_2/db.opt
210826 09:54:09 [00] ...done
210826 09:54:09 [00] Writing /home/backup/failover/db.opt
210826 09:54:09 [00] ...done
210826 09:54:09 [00] Writing /home/backup/server_3_down/db.opt
210826 09:54:09 [00] ...done
210826 09:54:09 Finished backing up non-InnoDB tables and files
210826 09:54:09 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
210826 09:54:09 Selecting LSN and binary log position from p_s.log_status
210826 09:54:09 [00] Copying /var/lib/mysql/binlog.000031 to /home/backup/binlog.000031 up to position 156
210826 09:54:09 [00] ...done
210826 09:54:09 [00] Writing /home/backup/binlog.index
210826 09:54:09 [00] ...done
210826 09:54:09 [00] Writing /home/backup/xtrabackup_slave_info
210826 09:54:09 [00] ...done
210826 09:54:09 [00] Writing /home/backup/xtrabackup_binlog_info
210826 09:54:09 [00] ...done
210826 09:54:09 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '4624355452'
xtrabackup: Stopping log copying thread at LSN 4624355462.
210826 09:54:09 >> log scanned up to (4624355472)
Starting to parse redo log at lsn = 4624355384
210826 09:54:10 Executing UNLOCK TABLES
210826 09:54:10 All tables unlocked
210826 09:54:10 [00] Copying ib_buffer_pool to /home/backup/ib_buffer_pool
210826 09:54:10 [00] ...done
210826 09:54:10 Backup created in directory '/home/backup/'
MySQL binlog position: filename 'binlog.000031', position '156'
210826 09:54:10 [00] Writing /home/backup/backup-my.cnf
210826 09:54:10 [00] ...done
210826 09:54:10 [00] Writing /home/backup/xtrabackup_info
210826 09:54:10 [00] ...done
xtrabackup: Transaction log of lsn (4624355442) to (4624355482) was copied.
210826 09:54:11 completed OK!
real 0m12.204s
user 0m0.764s
sys 0m0.668s
[root@percona-xtradb-1 ~]#
[root@percona-xtradb-1 backup]# pwd
/home/backup
[root@percona-xtradb-1 backup]# ls -ltr
-rw-r----- 1 root root 12582912 Aug 26 09:54 ibdata1
drwxr-x--- 2 root root 28 Aug 26 09:54 sys
drwxr-x--- 2 root root 27 Aug 26 09:54 cluster_testing
drwxr-x--- 2 root root 28 Aug 26 09:54 killed_process
drwxr-x--- 2 root root 168 Aug 26 09:54 awsdms_control
drwxr-x--- 2 root root 216 Aug 26 09:54 sample123
-rw-r----- 1 root root 26214400 Aug 26 09:54 mysql.ibd
-rw-r----- 1 root root 16777216 Aug 26 09:54 undo_002
-rw-r----- 1 root root 16777216 Aug 26 09:54 undo_001
drwxr-x--- 2 root root 232 Aug 26 09:54 mysql
drwxr-x--- 2 root root 8192 Aug 26 09:54 performance_schema
drwxr-x--- 2 root root 20 Aug 26 09:54 acs
drwxr-x--- 2 root root 20 Aug 26 09:54 failover_2
drwxr-x--- 2 root root 20 Aug 26 09:54 failover
drwxr-x--- 2 root root 20 Aug 26 09:54 server_3_down
-rw-r----- 1 root root 156 Aug 26 09:54 binlog.000031
-rw-r----- 1 root root 16 Aug 26 09:54 binlog.index
-rw-r----- 1 root root 0 Aug 26 09:54 xtrabackup_slave_info
-rw-r----- 1 root root 18 Aug 26 09:54 xtrabackup_binlog_info
-rw-r----- 1 root root 2560 Aug 26 09:54 xtrabackup_logfile
-rw-r----- 1 root root 108 Aug 26 09:54 xtrabackup_checkpoints
-rw-r----- 1 root root 3932 Aug 26 09:54 ib_buffer_pool
-rw-r----- 1 root root 475 Aug 26 09:54 backup-my.cnf
-rw-r----- 1 root root 573 Aug 26 09:54 xtrabackup_info
-rw-r----- 1 root root 39 Aug 26 09:54 xtrabackup_tablespaces
[root@percona-xtradb-1 backup]# cat xtrabackup_binlog_info
binlog.000031 156
[root@percona-xtradb-1 backup]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 4624355452
last_lsn = 4624355462
flushed_lsn = 4624355442
[root@percona-xtradb-1 backup]# cat xtrabackup_info
uuid = 8feeb7e2-0653-11ec-9972-0ee2fb190aa1
name =
tool_name = xtrabackup
tool_command = --defaults-file=/etc/my.cnf --backup --port=3306 --socket=/var/lib/mysql/mysql.sock --slave-info --target-dir=/home/backup --user=root --password=...
tool_version = 8.0.25-17
ibbackup_version = 8.0.25-17
server_version = 8.0.23-14.1
start_time = 2021-08-26 09:53:59
end_time = 2021-08-26 09:54:10
lock_time = 1
binlog_pos = filename 'binlog.000031', position '156'
innodb_from_lsn = 0
innodb_to_lsn = 4624355452
partial = N
incremental = N
format = file
compressed = N
encrypted = N
[root@percona-xtradb-1 backup]# cat xtrabackup_tablespaces
{"version":3,"external_tablespaces":[]}
[root@percona-xtradb-1 backup]#
Till Now full physical Backup is done...
--------------------------------------------
Step - 3 - Delete some records from table
--------------------------------------------
[root@percona-xtradb-1 ~]# mysql -u root -p
Enter password:root
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 34.Server version: 8.0.23-14.1 Percona XtraDB Cluster (GPL), Release rel14, Revision d3b9a1d, WSREP version 26.4.3.Copyright (c) 2009-2021 Percona LLC and/or its affiliates.Copyright (c) 2000, 2021, Oracle and/or its affiliates.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> use sample123;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_sample123|
+-------------------+
| node |
+-------------------+
1 row in set (0.00 sec)
mysql> select count(1) from node;
+----------+
| count(1) |
+----------+
| 2228709 |
+----------+
1 row in set (4.09 sec)
mysql> select MD_ID from node LIMIT 5;
+---------+
| MD_ID |
+---------+
| 9603878 |
| 9603879 |
| 9603880 |
| 9603882 |
| 9603883 |
+---------+
5 rows in set (0.00 sec)
mysql> delete from node where MD_ID=9603880;
Query OK, 9 rows affected (12.19 sec)
mysql> delete from node where MD_ID=9603883;
Query OK, 9 rows affected (13.15 sec)
mysql> delete from node where MD_ID=9603879;
Query OK, 9 rows affected (11.51 sec)
mysql> delete from node where MD_ID=9603878;
Query OK, 8 rows affected (11.65 sec)
mysql> select count(1) from node;
+----------+
| count(1) |
+----------+
| 2228674 |
+----------+
1 row in set (2.53 sec)
Here Total Rows = 2228709
Deleted Rows = 35 [ 9 + 9 + 9 + 8 ]
After deleted available Rows = 2228674
So total 35 Rows got deleted.
Step - 4 - Restore the table from full Physical backup..
----------------------------------------------------------
Now, if we want to restore it, we have to prepare the backup - it’s a standard process for Xtrabackup. There is one major difference though in a way we will prepare it. We will use --export flag:
xtrabackup --prepare --export --target-dir=/backup/
[root@percona-xtradb-1 ~]# cd /home/backup/
[root@percona-xtradb-1 backup]# pwd
/home/backup
[root@percona-xtradb-1 backup]# ls -ltr
-rw-r----- 1 root root 12582912 Aug 26 09:54 ibdata1
drwxr-x--- 2 root root 28 Aug 26 09:54 sys
drwxr-x--- 2 root root 27 Aug 26 09:54 cluster_testing
drwxr-x--- 2 root root 28 Aug 26 09:54 killed_process
drwxr-x--- 2 root root 168 Aug 26 09:54 awsdms_control
drwxr-x--- 2 root root 216 Aug 26 09:54 sample123
-rw-r----- 1 root root 26214400 Aug 26 09:54 mysql.ibd
-rw-r----- 1 root root 16777216 Aug 26 09:54 undo_002
-rw-r----- 1 root root 16777216 Aug 26 09:54 undo_001
drwxr-x--- 2 root root 232 Aug 26 09:54 mysql
drwxr-x--- 2 root root 8192 Aug 26 09:54 performance_schema
drwxr-x--- 2 root root 20 Aug 26 09:54 acs
drwxr-x--- 2 root root 20 Aug 26 09:54 failover_2
drwxr-x--- 2 root root 20 Aug 26 09:54 failover
drwxr-x--- 2 root root 20 Aug 26 09:54 server_3_down
-rw-r----- 1 root root 156 Aug 26 09:54 binlog.000031
-rw-r----- 1 root root 16 Aug 26 09:54 binlog.index
-rw-r----- 1 root root 0 Aug 26 09:54 xtrabackup_slave_info
-rw-r----- 1 root root 18 Aug 26 09:54 xtrabackup_binlog_info
-rw-r----- 1 root root 2560 Aug 26 09:54 xtrabackup_logfile
-rw-r----- 1 root root 108 Aug 26 09:54 xtrabackup_checkpoints
-rw-r----- 1 root root 3932 Aug 26 09:54 ib_buffer_pool
-rw-r----- 1 root root 475 Aug 26 09:54 backup-my.cnf
-rw-r----- 1 root root 573 Aug 26 09:54 xtrabackup_info
-rw-r----- 1 root root 39 Aug 26 09:54 xtrabackup_tablespaces
[root@percona-xtradb-1 backup]# xtrabackup --prepare --export --target-dir=/home/backup/
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=1 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --export=1 --target-dir=/home/backup/
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
xtrabackup: auto-enabling --innodb-file-per-table due to the --export option
xtrabackup: cd to /home/backup/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(4624355442)
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: inititialize_service_handles suceeded
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)
PUNCH HOLE support available
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan './'
Scanning './'
Completed space ID check of 24 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 19563875 in the system tablespace does not match the log sequence number 4624355442 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 4624355384, whereas checkpoint_lsn = 4624355442 and start_lsn = 4624355328
Doing recovery: scanned up to log sequence number 4624355462
Log background threads are being started...
Applying a batch of 1 redo log records ...
100%
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 30212
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.25 started; log sequence number 4624355462
Allocated tablespace ID 6 for killed_process/killed_pro, old maximum was 0
xtrabackup: export option is specified.
xtrabackup: Last MySQL binlog file position 156, file name binlog.000022
xtrabackup: Recovered WSREP position: 29ff2ba6-01db-11ec-8a50-7a82b8ebba59:7829
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
FTS optimize thread exiting.
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 4624355462
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
PUNCH HOLE support available
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan './'
Scanning './'
Completed space ID check of 24 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
Creating log file ./ib_logfile101
Creating log file ./ib_logfile1
Renaming log file ./ib_logfile101 to ./ib_logfile0
New log files created, LSN=4624355852
Starting to parse redo log at lsn = 4624355852, whereas checkpoint_lsn = 4624355852 and start_lsn = 4624355840
Log background threads are being started...
Applying a batch of 0 redo log records ...
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 30212
Removed temporary tablespace data file: "ibtmp1"
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.25 started; log sequence number 4624355852
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
FTS optimize thread exiting.
Trying to access missing tablespace 4294967294
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 4624355852
210826 10:21:28 completed OK!
[
root@percona-xtradb-1 backup]# ls -ltr
-rw-r----- 1 root root 26214400 Aug 26 09:54 mysql.ibd
-rw-r----- 1 root root 16777216 Aug 26 09:54 undo_002
-rw-r----- 1 root root 16777216 Aug 26 09:54 undo_001
drwxr-x--- 2 root root 8192 Aug 26 09:54 performance_schema
drwxr-x--- 2 root root 20 Aug 26 09:54 acs
drwxr-x--- 2 root root 20 Aug 26 09:54 failover_2
drwxr-x--- 2 root root 20 Aug 26 09:54 failover
drwxr-x--- 2 root root 20 Aug 26 09:54 server_3_down
-rw-r----- 1 root root 156 Aug 26 09:54 binlog.000031
-rw-r----- 1 root root 16 Aug 26 09:54 binlog.index
-rw-r----- 1 root root 0 Aug 26 09:54 xtrabackup_slave_info
-rw-r----- 1 root root 18 Aug 26 09:54 xtrabackup_binlog_info
-rw-r----- 1 root root 3932 Aug 26 09:54 ib_buffer_pool
-rw-r----- 1 root root 475 Aug 26 09:54 backup-my.cnf
-rw-r----- 1 root root 573 Aug 26 09:54 xtrabackup_info
drwxr-x--- 2 root root 50 Aug 26 10:21 sys
drwxr-x--- 2 root root 321 Aug 26 10:21 mysql
drwxr-x--- 2 root root 50 Aug 26 10:21 killed_process
drwxr-x--- 2 root root 48 Aug 26 10:21 cluster_testing
drwxr-x--- 2 root root 330 Aug 26 10:21 awsdms_control
drwxr-x--- 2 root root 4096 Aug 26 10:21 sample123
-rw-r--r-- 1 root root 1 Aug 26 10:21 xtrabackup_master_key_id
-rw-r--r-- 1 root root 41 Aug 26 10:21 xtrabackup_galera_info
-rw-r--r-- 1 root root 18 Aug 26 10:21 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 8388608 Aug 26 10:21 xtrabackup_logfile
-rw-r----- 1 root root 108 Aug 26 10:21 xtrabackup_checkpoints
-rw-r----- 1 root root 50331648 Aug 26 10:21 ib_logfile1
-rw-r----- 1 root root 50331648 Aug 26 10:21 ib_logfile0
-rw-r----- 1 root root 12582912 Aug 26 10:21 ibtmp1
-rw-r----- 1 root root 12582912 Aug 26 10:21 ibdata1
drwxr-x--- 2 root root 6 Aug 26 10:21 '#innodb_temp'
-rw-r----- 1 root root 39 Aug 26 10:21 xtrabackup_tablespaces
[root@percona-xtradb-1 backup]#
Now we can restore a particular table following this process:
---------------------------------------------------------------
(i) We have to create the table using exactly the same schema as it used to have when the backup has been taken.
(ii) We have to discard its tablespace
(iii) We will copy the tablespace from the backup along with its *.cfg file
(iv) We will import new tablespace
Let’s assume one of the table few rows has been accidentally deleted :
mysql> SELECT COUNT(1) FROM sample123.node\G
*************************** 1. row ***************************
COUNT(*): 2228709
1 row in set (4.09 sec)
Here we have deleted 35 Rows ..
mysql> SELECT COUNT(1) FROM sample123.node\G
*************************** 1. row ***************************
COUNT(*): 2228674
1 row in set (2.52 sec)
In this case we already have the table with a proper schema in place and we can proceed to step (ii)
mysql> ALTER TABLE sample123.node DISCARD TABLESPACE;
Query OK, 0 rows affected (0.06 sec)
(iii) Now we have to copy the data from the backup:
[root@percona-xtradb-1 sample123]# pwd
/home/backup/sample123
[root@percona-xtradb-1 sample123]# ls -ltr
-rw-r----- 1 root root 20971520 Aug 26 09:54 node#p#p0.ibd
-rw-r----- 1 root root 12582912 Aug 26 09:54 node#p#p1.ibd
-rw-r----- 1 root root 417792 Aug 26 09:54 node#p#p2.ibd
-rw-r----- 1 root root 131072 Aug 26 09:54 node#p#p3.ibd
-rw-r----- 1 root root 131072 Aug 26 09:54 node#p#p4.ibd
-rw-r----- 1 root root 131072 Aug 26 09:54 node#p#p5.ibd
-rw-r----- 1 root root 131072 Aug 26 09:54 node#p#p6.ibd
-rw-r----- 1 root root 131072 Aug 26 09:54 node#p#p7.ibd
-rw-r----- 1 root root 131072 Aug 26 09:54 node#p#p8.ibd
-rw-r----- 1 root root 482344960 Aug 26 09:54 node#p#p9.ibd
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p1.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p0.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p8.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p7.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p6.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p5.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p4.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p3.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p2.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:21 node#p#p9.cfg
[root@percona-xtradb-1 sample123]# cd
[root@percona-xtradb-1 ~]# cp /home/backup/sample123/node#p#p* /var/lib/mysql/sample123/
[root@percona-xtradb-1 sample123]# pwd
/var/lib/mysql/sample123
[root@percona-xtradb-1 sample123]# ls -ltr
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p0.cfg
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p1.cfg
-rw-r----- 1 root root 20971520 Aug 26 10:42 node#p#p0.ibd
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p2.cfg
-rw-r----- 1 root root 12582912 Aug 26 10:42 node#p#p1.ibd
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p3.cfg
-rw-r----- 1 root root 417792 Aug 26 10:42 node#p#p2.ibd
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p4.cfg
-rw-r----- 1 root root 131072 Aug 26 10:42 node#p#p3.ibd
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p5.cfg
-rw-r----- 1 root root 131072 Aug 26 10:42 node#p#p4.ibd
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p6.cfg
-rw-r----- 1 root root 131072 Aug 26 10:42 node#p#p5.ibd
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p7.cfg
-rw-r----- 1 root root 131072 Aug 26 10:42 node#p#p6.ibd
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p9.cfg
-rw-r----- 1 root root 131072 Aug 26 10:42 node#p#p8.ibd
-rw-r--r-- 1 root root 6587 Aug 26 10:42 node#p#p8.cfg
-rw-r----- 1 root root 131072 Aug 26 10:42 node#p#p7.ibd
-rw-r----- 1 root root 482344960 Aug 26 10:42 node#p#p9.ibd
[root@percona-xtradb-1 sample123]#
So here we have to change the permissions of all files under /var/lib/mysql/sample123 folder ..
[root@percona-xtradb-1 sample123]# pwd
/var/lib/mysql/sample123
[root@percona-xtradb-1 sample123]# chown mysql:mysql /var/lib/mysql/sample123/node#p#p*
[root@percona-xtradb-1 sample123]# ls -ltr
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p0.cfg
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p1.cfg
-rw-r----- 1 mysql mysql 20971520 Aug 26 10:42 node#p#p0.ibd
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p2.cfg
-rw-r----- 1 mysql mysql 12582912 Aug 26 10:42 node#p#p1.ibd
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p3.cfg
-rw-r----- 1 mysql mysql 417792 Aug 26 10:42 node#p#p2.ibd
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p4.cfg
-rw-r----- 1 mysql mysql 131072 Aug 26 10:42 node#p#p3.ibd
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p5.cfg
-rw-r----- 1 mysql mysql 131072 Aug 26 10:42 node#p#p4.ibd
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p6.cfg
-rw-r----- 1 mysql mysql 131072 Aug 26 10:42 node#p#p5.ibd
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p7.cfg
-rw-r----- 1 mysql mysql 131072 Aug 26 10:42 node#p#p6.ibd
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p9.cfg
-rw-r----- 1 mysql mysql 131072 Aug 26 10:42 node#p#p8.ibd
-rw-r--r-- 1 mysql mysql 6587 Aug 26 10:42 node#p#p8.cfg
-rw-r----- 1 mysql mysql 131072 Aug 26 10:42 node#p#p7.ibd
-rw-r----- 1 mysql mysql 482344960 Aug 26 10:42 node#p#p9.ibd
[root@percona-xtradb-1 sample123]#
(iv) Finally, we can import the restored tablespace:
mysql> SELECT COUNT(*) FROM sample123.node\G
ERROR 1814 (HY000): Tablespace has been discarded for table 'sample123/node#p#p0'
mysql> ALTER TABLE sample123.node IMPORT TABLESPACE;
Query OK, 0 rows affected (14.16 sec)
mysql> SELECT COUNT(*) FROM sample123.node\G
*************************** 1. row ***************************
COUNT(*): 2228709
1 row in set (2.55 sec)
mysql> CHECKSUM TABLE sample123.node;
+--------------+------------+
| Table | Checksum |
+--------------+------------+
| sample123.node | 4094936473 |
+--------------+------------+
1 row in set (12.49 sec)
As you can see, the contents of the table have been restored. Now, based on how we approached the whole problem, we can either repeat this process on all of the nodes in the cluster or we can use mysqldump or SELECT … INTO OUTFILE to extract this data and then load it on the live cluster.
Please keep in mind that Xtrabackup allows as well to take a backup of a single database or single table. This is another feature, loosely tied to what we have just discussed - it is not required to create a backup of a single table to be able to restore it. What is required though is the schema - you may want to schedule backups of the schema (no data is required) using mysqldump that will go along with your xtrabackup backups. You may find them very handy if your schema changes often.
Another method , take a backup of single table ...
xtrabackup --user=root --password=root --backup --tables=node --target-dir=/home/single_table_bkp -S /var/lib/mysql/mysql.sock --datadir=/var/lib/mysql
Reference Link :-
https://www.percona.com/blog/2020/04/10/percona-xtrabackup-backup-and-restore-of-a-single-table-or-database/
Thank you Everyone .....
Comments
Post a Comment