Partial Backup Dengan Percona XtraBackup

Catatan Oprekan Simulasi Partial Backup Dengan Percona XtraBackup , Silahkan ikuti Comman - Command dibawah ini dengan menggunakan terminal anda :)

1. Membuat Database , masuk comman mysql dengan perintah : mysql -u root -p dan masukkan password anda

mysql> create database data1;
Query OK, 1 row affected (0.03 sec)

mysql> create database data2;
Query OK, 1 row affected (0.00 sec)

mysql> create database data3;
Query OK, 1 row affected (0.00 sec)

2. Membuat Tabel Database

mysql> create table data1.t1(i int);
Query OK, 0 rows affected (0.31 sec)

mysql> create table data1.t2(i int);
Query OK, 0 rows affected (0.24 sec)

mysql> create table data1.t3(i int);
Query OK, 0 rows affected (0.04 sec)

mysql> create table data2.t1(i int);
Query OK, 0 rows affected (0.22 sec)

mysql> create table data2.t2(i int);
Query OK, 0 rows affected (0.22 sec)

mysql> create table data2.t3(i int);
Query OK, 0 rows affected (0.30 sec)

mysql> create table data3.t1(i int);
Query OK, 0 rows affected (0.41 sec)

mysql> create table data3.t2(i int);
Query OK, 0 rows affected (0.32 sec)

mysql> create table data3.t3(i int);
Query OK, 0 rows affected (0.18 sec)

3. Cek diskusage mysql dengan perintah :

[root@jordan]# mysqldiskusage --server=root:123456@localhost:3306:/var/lib/mysql/mysql.sock --all
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Database totals:
+---------------------+------------+
| db_name             |     total  |
+---------------------+------------+
| data1               | 373,887    |
| data2               | 373,887    |
| data3               | 373,887    |
| mysql               | 1,577,981  |
| performance_schema  | 489,543    |
+---------------------+------------+

Total database disk usage = 3,189,185 bytes or 3.04 MB

# Log information.
# The general_log is turned off on the server.
# The slow_query_log is turned off on the server.
+-------------+---------+
| log_name    |   size  |
+-------------+---------+
| mysqld.log  | 36,043  |
+-------------+---------+

Total size of logs = 36,043 bytes or 35.20 KB

# Binary log information:
Current binary log file = mysql01-bin.000041
+---------------------+-------+
| log_file            | size  |
+---------------------+-------+
| mysql01-bin.000001  | 1825  |
| mysql01-bin.000002  | 570   |
| mysql01-bin.000003  | 240   |
| mysql01-bin.000004  | 240   |
[...]
| mysql01-bin.index   | 1280  |
+---------------------+-------+

Total size of binary logs = 15,234 bytes or 14.88 KB

# Relay log information:
Current relay log file = mysqld-relay-bin.000003
+--------------------------+-------+
| log_file                 | size  |
+--------------------------+-------+
| mysqld-relay-bin.000003  | 143   |
| mysqld-relay-bin.000004  | 143   |
| mysqld-relay-bin.000005  | 120   |
| mysqld-relay-bin.index   | 78    |
+--------------------------+-------+

Total size of relay logs = 484 bytes

# InnoDB tablespace information:
+--------------+-------------+
| innodb_file  |       size  |
+--------------+-------------+
| ib_logfile0  | 50,331,648  |
| ib_logfile1  | 50,331,648  |
| ibdata1      | 12,582,912  |
+--------------+-------------+

Total size of InnoDB files = 113,246,208 bytes or 108.00 MB

#...done.

4.ikuti perintah dibawah ini , masuk kembali ke mysql command dengan perintah :

sudo mysql -u root -p

mysql> SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) INTO OUTFILE '/tmp/tablenames-data1' LINES TERMINATED BY '\n' 
    -> FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='data1' AND TABLE_NAME NOT IN ('t2','t3');
Query OK, 1 row affected (0.00 sec)

mysql> \! cat /tmp/tablenames-data1
data1.t1

mysql> SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) INTO OUTFILE '/tmp/tablename' LINES TERMINATED BY '\n' 
    -> FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN ('data2','data3','mysql', 'performance_schema');
Query OK, 86 rows affected (0.00 sec)

mysql> \! cat /tmp/tablenames-data1 >> /tmp/tablename

mysql> \! cat /tmp/tablename
data2.t1
data2.t2
data2.t3
data3.t1
data3.t2
data3.t3
mysql.columns_priv
mysql.db
mysql.event
mysql.func
mysql.general_log
mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.innodb_index_stats
mysql.innodb_table_stats
mysql.ndb_binlog_index
mysql.plugin
mysql.proc
mysql.procs_priv
mysql.proxies_priv
mysql.servers
mysql.slave_master_info
mysql.slave_relay_log_info
mysql.slave_worker_info
mysql.slow_log
mysql.tables_priv
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type
mysql.user
performance_schema.accounts
performance_schema.cond_instances
performance_schema.events_stages_current
performance_schema.events_stages_history
performance_schema.events_stages_history_long
performance_schema.events_stages_summary_by_account_by_event_name
performance_schema.events_stages_summary_by_host_by_event_name
performance_schema.events_stages_summary_by_thread_by_event_name
performance_schema.events_stages_summary_by_user_by_event_name
performance_schema.events_stages_summary_global_by_event_name
performance_schema.events_statements_current
performance_schema.events_statements_history
performance_schema.events_statements_history_long
performance_schema.events_statements_summary_by_account_by_event_name
performance_schema.events_statements_summary_by_digest
performance_schema.events_statements_summary_by_host_by_event_name
performance_schema.events_statements_summary_by_thread_by_event_name
performance_schema.events_statements_summary_by_user_by_event_name
performance_schema.events_statements_summary_global_by_event_name
performance_schema.events_waits_current
performance_schema.events_waits_history
performance_schema.events_waits_history_long
performance_schema.events_waits_summary_by_account_by_event_name
performance_schema.events_waits_summary_by_host_by_event_name
performance_schema.events_waits_summary_by_instance
performance_schema.events_waits_summary_by_thread_by_event_name
performance_schema.events_waits_summary_by_user_by_event_name
performance_schema.events_waits_summary_global_by_event_name
performance_schema.file_instances
performance_schema.file_summary_by_event_name
performance_schema.file_summary_by_instance
performance_schema.host_cache
performance_schema.hosts
performance_schema.mutex_instances
performance_schema.objects_summary_global_by_type
performance_schema.performance_timers
performance_schema.rwlock_instances
performance_schema.session_account_connect_attrs
performance_schema.session_connect_attrs
performance_schema.setup_actors
performance_schema.setup_consumers
performance_schema.setup_instruments
performance_schema.setup_objects
performance_schema.setup_timers
performance_schema.socket_instances
performance_schema.socket_summary_by_event_name
performance_schema.socket_summary_by_instance
performance_schema.table_io_waits_summary_by_index_usage
performance_schema.table_io_waits_summary_by_table
performance_schema.table_lock_waits_summary_by_table
performance_schema.threads
performance_schema.users
data1.t1

5. Sekarang kita coba melakukan backup dengan perintah dibawah ini :
[root@jordan]# sudo innobackupex --user=root --password=123456 --tables-file=/tmp/tablename --history=partial01 /opt

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:

http://www.percona.com/xb/p

150331 17:32:29  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
150331 17:32:29  innobackupex: Connected to MySQL server
150331 17:32:29  innobackupex: Executing a version check against the server...
150331 17:32:29  innobackupex: Done.
150331 17:32:29  innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex:  Using server version 5.6.23-log

innobackupex: Created backup directory /opt/2015-03-31_17-32-29

150331 17:32:29  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/opt/2015-03-31_17-32-29 --innodb_log_file_size="50331648" --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --tables_file='/tmp/tablename'
innobackupex: Waiting for ibbackup (pid=4771) to suspend
innobackupex: Suspend file '/opt/2015-03-31_17-32-29/xtrabackup_suspended_2'

xtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
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
>> log scanned up to (1694982)
xtrabackup: Generating a list of tablespaces
>> log scanned up to (1694982)
[01] Copying ./ibdata1 to /opt/2015-03-31_17-32-29/ibdata1
[01]        ...done
>> log scanned up to (1694982)
[01] Copying ./mysql/innodb_index_stats.ibd to /opt/2015-03-31_17-32-29/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /opt/2015-03-31_17-32-29/mysql/slave_worker_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /opt/2015-03-31_17-32-29/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /opt/2015-03-31_17-32-29/mysql/slave_master_info.ibd
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /opt/2015-03-31_17-32-29/mysql/slave_relay_log_info.ibd
[01]        ...done
[01] Copying ./db3/t1.ibd to /opt/2015-03-31_17-32-29/db3/t1.ibd
[01]        ...done
[01] Copying ./db3/t2.ibd to /opt/2015-03-31_17-32-29/db3/t2.ibd
[01]        ...done
>> log scanned up to (1694982)
[01] Copying ./db3/t3.ibd to /opt/2015-03-31_17-32-29/db3/t3.ibd
[01]        ...done
[01] Copying ./db2/t1.ibd to /opt/2015-03-31_17-32-29/db2/t1.ibd
[01]        ...done
[01] Copying ./db2/t2.ibd to /opt/2015-03-31_17-32-29/db2/t2.ibd
[01]        ...done
[01] Copying ./db2/t3.ibd to /opt/2015-03-31_17-32-29/db2/t3.ibd
[01]        ...done
[01] Copying ./db1/t1.ibd to /opt/2015-03-31_17-32-29/db1/t1.ibd
[01]        ...done
>> log scanned up to (1694982)
xtrabackup: Creating suspend file '/opt/2015-03-31_17-32-29/xtrabackup_suspended_2' with pid '4771'

150331 17:32:34  innobackupex: Continuing after ibbackup has suspended
150331 17:32:34  innobackupex: Executing FLUSH TABLES WITH READ LOCK...
150331 17:32:34  innobackupex: All tables locked and flushed to disk

150331 17:32:34  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up files '/var/lib/mysql//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
>> log scanned up to (1694982)
>> log scanned up to (1694982)
>> log scanned up to (1694982)
innobackupex: Backing up files '/var/lib/mysql//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
>> log scanned up to (1694982)
innobackupex: Backing up file '/var/lib/mysql//db3/t3.frm'
innobackupex: Backing up file '/var/lib/mysql//db3/t1.frm'
innobackupex: Backing up file '/var/lib/mysql//db3/t2.frm'
>> log scanned up to (1694982)
innobackupex: Backing up file '/var/lib/mysql//db2/t3.frm'
innobackupex: Backing up file '/var/lib/mysql//db2/t1.frm'
innobackupex: Backing up file '/var/lib/mysql//db2/t2.frm'
innobackupex: Backing up file '/var/lib/mysql//db1/t1.frm'
150331 17:32:38  innobackupex: Finished backing up non-InnoDB tables and files

150331 17:32:38  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
150331 17:32:38  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1694982'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1694982)

xtrabackup: Creating suspend file '/opt/2015-03-31_17-32-29/xtrabackup_log_copied' with pid '4771'
xtrabackup: Transaction log of lsn (1694982) to (1694982) was copied.
150331 17:32:39  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/opt/2015-03-31_17-32-29'
innobackupex: MySQL binlog position: GTID of the last change 'f2b66a45-ce62-11e4-8a01-0800274fb806:1-18'
innobackupex: Backup history record uuid edfd8656-d7cb-11e4-9cd1-0800274fb806 successfully written
150331 17:32:40  innobackupex: Connection to database server closed
150331 17:32:40  innobackupex: completed OK!



6. Kedua , Bersihkan semua metadata dari ibdata1 dengan perintah :
[root@jordan]# sudo innobackupex --user=root --password=123456 --apply-log /opt/2015-03-31_17-32-29

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:

http://www.percona.com/xb/p

150331 17:41:06  innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".


150331 17:41:07  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/opt/2015-03-31_17-32-29/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/opt/2015-03-31_17-32-29

xtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to /opt/2015-03-31_17-32-29
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1694982)
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 = 2097152
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 = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 1638299 and 1638299 in ibdata files do not match the log sequence number 1694982 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 1802, file name mysql01-bin.000001
InnoDB: Table db1/t2 in the InnoDB data dictionary has tablespace id 8, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table db1/t3 in the InnoDB data dictionary has tablespace id 9, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.22 started; log sequence number 1694982

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 1802, file name mysql01-bin.000001

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1696565

150331 17:41:10  innobackupex: Restarting xtrabackup with command: xtrabackup  --defaults-file="/opt/2015-03-31_17-32-29/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/opt/2015-03-31_17-32-29
for creating ib_logfile*

xtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to /opt/2015-03-31_17-32-29
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
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
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
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1696565
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.22 started; log sequence number 1696780

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 1802, file name mysql01-bin.000001

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1696790
150331 17:41:12  innobackupex: completed OK!

Sekian Hasil Catatan Oprekan dari saya, semoga bermanfaat :)