{"id":1082,"date":"2015-03-31T15:04:50","date_gmt":"2015-03-31T18:04:50","guid":{"rendered":"http:\/\/wagnerbianchi.com\/blog\/?p=1082"},"modified":"2015-12-07T12:05:45","modified_gmt":"2015-12-07T15:05:45","slug":"exploring-partial-backups-with-xtrabackup","status":"publish","type":"post","link":"http:\/\/wagnerbianchi.com\/blog\/?p=1082","title":{"rendered":"Exploring InnoDB Schema Partial Backups with Percona Xtrabackup"},"content":{"rendered":"

\"logo_percona_xtrabackup_new\"<\/a>I remember the time when all the database and sys admins used to speak about MySQL backup strategy and it was really something to not worry about too much for many reasons. One of them was that the dataset on MySQL schemas was not too big, not that critical and the information was not that sensible such as today. Yes, as time went by, I’ve seen many organisations using MySQL to store really sensible information such as banks, vehicles manufactures and critical information in a sense of “we must be ready all time, my customer needs our services 24×7<\/em>“.<\/p>\n

This is not just Facebook or Twitter, even LinkedIn or\u00a0Google, but, many companies around the world such as Booking.com needs systems ready all the time. Regardless of their scale-out or HA strategy, a good tool for export\/import tables and even backing up databases is too important and this is what I’ve planned to write here to register all my adventures with xtrabackup and InnoDB. If you run MyISAM, maybe you can face a simple script to cold backup tables and period end, considering FLUSH TABLES WITH READ LOCK or even a moment in which you can just put down everything, copy files and put the database backup up again (can be a little bit different and not that simpler, but, it’s something like that).<\/p>\n

The\u00a0scenario of partial backups<\/strong><\/p>\n

Starting up with a sample of the online documentation:<\/p>\n

\n

There is only one caveat about partial backups: do not copy back the prepared backup. Restoring partial backups should be done by importing the tables, not by using the traditional\u00a0–copy-back<\/i>\u00a0option. Although there are some scenarios where restoring can be done by copying back the files, this may be lead to database inconsistencies in many cases and it is not the recommended way to do it.<\/span><\/p>\n<\/blockquote>\n

My problem was very clear at the first sight, we’ve got a huge amount of information on our MySQL’s schemas and part of the biggest one is not needed to be backed up. Explaining more about the scenario, there are 29 schemas and the biggest one is not completely necessary to be backed up due to our business rules. A special SLAVE server that is dedicated to sales processes does not need the whole dataset of the biggest schema and then, we don’t need to spent all the server’s disk space with useless data (in the context of this slave server). Besides that, a huge list of replicate-ignore-table<\/em> can be found in the MySQL configuration file and from that I start thinking about how to solve this problem using partial backups with a file listing all the tables part of a backup!<\/p>\n

The first step was to select all the tables of the biggest schema, different of those pointed out in replicate-ignore-table options and the have the results into a file. Second step was to select all other schemas different then the biggest schema. Bottom line, I merges files and got the file to backup just the tables of my interest in this task. Unfortunately I cannot post the real data I’ve worked with for obvious reasons, but, I will try to use some examples…<\/p>\n

#: let's create some databases\r\n\r\nmysql> create database db1;\r\nQuery OK, 1 row affected (0.03 sec)\r\n\r\nmysql> create database db2;\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql> create database db3;\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\n#: let's create some tables\r\n\r\nmysql> create table db1.t1(i int);\r\nQuery OK, 0 rows affected (0.31 sec)\r\n\r\nmysql> create table db1.t2(i int);\r\nQuery OK, 0 rows affected (0.24 sec)\r\n\r\nmysql> create table db1.t3(i int);\r\nQuery OK, 0 rows affected (0.04 sec)\r\n\r\nmysql> create table db2.t1(i int);\r\nQuery OK, 0 rows affected (0.22 sec)\r\n\r\nmysql> create table db2.t2(i int);\r\nQuery OK, 0 rows affected (0.22 sec)\r\n\r\nmysql> create table db2.t3(i int);\r\nQuery OK, 0 rows affected (0.30 sec)\r\n\r\nmysql> create table db3.t1(i int);\r\nQuery OK, 0 rows affected (0.41 sec)\r\n\r\nmysql> create table db3.t2(i int);\r\nQuery OK, 0 rows affected (0.32 sec)\r\n\r\nmysql> create table db3.t3(i int);\r\nQuery OK, 0 rows affected (0.18 sec)<\/pre>\n
This way, I’ve got the following MySQL’s structures upon disk:<\/p>\n
[root@mysql01 opt]# mysqldiskusage --server=root:123456@localhost:3306:\/var\/lib\/mysql\/mysql.sock --all\r\nWARNING: Using a password on the command line interface can be insecure.\r\n# Source on localhost: ... connected.\r\n# Database totals:\r\n+---------------------+------------+\r\n| db_name             |     total  |\r\n+---------------------+------------+\r\n| db1                 | 373,887    |\r\n| db2                 | 373,887    |\r\n| db3                 | 373,887    |\r\n| mysql               | 1,577,981  |\r\n| performance_schema  | 489,543    |\r\n+---------------------+------------+\r\n\r\nTotal database disk usage = 3,189,185 bytes or 3.04 MB\r\n\r\n# Log information.\r\n# The general_log is turned off on the server.\r\n# The slow_query_log is turned off on the server.\r\n+-------------+---------+\r\n| log_name    |   size  |\r\n+-------------+---------+\r\n| mysqld.log  | 36,043  |\r\n+-------------+---------+\r\n\r\nTotal size of logs = 36,043 bytes or 35.20 KB\r\n\r\n# Binary log information:\r\nCurrent binary log file = mysql01-bin.000041\r\n+---------------------+-------+\r\n| log_file            | size  |\r\n+---------------------+-------+\r\n| mysql01-bin.000001  | 1825  |\r\n| mysql01-bin.000002  | 570   |\r\n| mysql01-bin.000003  | 240   |\r\n| mysql01-bin.000004  | 240   |\r\n[...]\r\n| mysql01-bin.index   | 1280  |\r\n+---------------------+-------+\r\n\r\nTotal size of binary logs = 15,234 bytes or 14.88 KB\r\n\r\n# Relay log information:\r\nCurrent relay log file = mysqld-relay-bin.000003\r\n+--------------------------+-------+\r\n| log_file                 | size  |\r\n+--------------------------+-------+\r\n| mysqld-relay-bin.000003  | 143   |\r\n| mysqld-relay-bin.000004  | 143   |\r\n| mysqld-relay-bin.000005  | 120   |\r\n| mysqld-relay-bin.index   | 78    |\r\n+--------------------------+-------+\r\n\r\nTotal size of relay logs = 484 bytes\r\n\r\n# InnoDB tablespace information:\r\n+--------------+-------------+\r\n| innodb_file  |       size  |\r\n+--------------+-------------+\r\n| ib_logfile0  | 50,331,648  |\r\n| ib_logfile1  | 50,331,648  |\r\n| ibdata1      | 12,582,912  |\r\n+--------------+-------------+\r\n\r\nTotal size of InnoDB files = 113,246,208 bytes or 108.00 MB\r\n\r\n#...done.<\/pre>\n
Ok, after this creation processes to simulate what I’m going to blog here, I’ll assume that the the biggest schema here is the db1 and we don’t need to backup all the tables. The only table on db1 that is required for this backup is t1 and then, all other databases including mysql and performance_schema are required (even having the mysql_upgrade execution creating\/upgrading performance_schema by chance). This way I can get now the list of tables of all databases excluding those I don’t want from the db1 – t2, t3.<\/p>\n
mysql> SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) INTO OUTFILE '\/tmp\/tablenames-db1' LINES TERMINATED BY '\\n' \r\n    -> FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db1' AND TABLE_NAME NOT IN ('t2','t3');\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql> \\! cat \/tmp\/tablenames-db1\r\ndb1.t1\r\n\r\nmysql> SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) INTO OUTFILE '\/tmp\/tablename' LINES TERMINATED BY '\\n' \r\n    -> FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN ('db2','db3','mysql', 'performance_schema');\r\nQuery OK, 86 rows affected (0.00 sec)\r\n\r\nmysql> \\! cat \/tmp\/tablenames-db1 >> \/tmp\/tablename\r\n\r\nmysql> \\! cat \/tmp\/tablename\r\ndb2.t1\r\ndb2.t2\r\ndb2.t3\r\ndb3.t1\r\ndb3.t2\r\ndb3.t3\r\nmysql.columns_priv\r\nmysql.db\r\nmysql.event\r\nmysql.func\r\nmysql.general_log\r\nmysql.help_category\r\nmysql.help_keyword\r\nmysql.help_relation\r\nmysql.help_topic\r\nmysql.innodb_index_stats\r\nmysql.innodb_table_stats\r\nmysql.ndb_binlog_index\r\nmysql.plugin\r\nmysql.proc\r\nmysql.procs_priv\r\nmysql.proxies_priv\r\nmysql.servers\r\nmysql.slave_master_info\r\nmysql.slave_relay_log_info\r\nmysql.slave_worker_info\r\nmysql.slow_log\r\nmysql.tables_priv\r\nmysql.time_zone\r\nmysql.time_zone_leap_second\r\nmysql.time_zone_name\r\nmysql.time_zone_transition\r\nmysql.time_zone_transition_type\r\nmysql.user\r\nperformance_schema.accounts\r\nperformance_schema.cond_instances\r\nperformance_schema.events_stages_current\r\nperformance_schema.events_stages_history\r\nperformance_schema.events_stages_history_long\r\nperformance_schema.events_stages_summary_by_account_by_event_name\r\nperformance_schema.events_stages_summary_by_host_by_event_name\r\nperformance_schema.events_stages_summary_by_thread_by_event_name\r\nperformance_schema.events_stages_summary_by_user_by_event_name\r\nperformance_schema.events_stages_summary_global_by_event_name\r\nperformance_schema.events_statements_current\r\nperformance_schema.events_statements_history\r\nperformance_schema.events_statements_history_long\r\nperformance_schema.events_statements_summary_by_account_by_event_name\r\nperformance_schema.events_statements_summary_by_digest\r\nperformance_schema.events_statements_summary_by_host_by_event_name\r\nperformance_schema.events_statements_summary_by_thread_by_event_name\r\nperformance_schema.events_statements_summary_by_user_by_event_name\r\nperformance_schema.events_statements_summary_global_by_event_name\r\nperformance_schema.events_waits_current\r\nperformance_schema.events_waits_history\r\nperformance_schema.events_waits_history_long\r\nperformance_schema.events_waits_summary_by_account_by_event_name\r\nperformance_schema.events_waits_summary_by_host_by_event_name\r\nperformance_schema.events_waits_summary_by_instance\r\nperformance_schema.events_waits_summary_by_thread_by_event_name\r\nperformance_schema.events_waits_summary_by_user_by_event_name\r\nperformance_schema.events_waits_summary_global_by_event_name\r\nperformance_schema.file_instances\r\nperformance_schema.file_summary_by_event_name\r\nperformance_schema.file_summary_by_instance\r\nperformance_schema.host_cache\r\nperformance_schema.hosts\r\nperformance_schema.mutex_instances\r\nperformance_schema.objects_summary_global_by_type\r\nperformance_schema.performance_timers\r\nperformance_schema.rwlock_instances\r\nperformance_schema.session_account_connect_attrs\r\nperformance_schema.session_connect_attrs\r\nperformance_schema.setup_actors\r\nperformance_schema.setup_consumers\r\nperformance_schema.setup_instruments\r\nperformance_schema.setup_objects\r\nperformance_schema.setup_timers\r\nperformance_schema.socket_instances\r\nperformance_schema.socket_summary_by_event_name\r\nperformance_schema.socket_summary_by_instance\r\nperformance_schema.table_io_waits_summary_by_index_usage\r\nperformance_schema.table_io_waits_summary_by_table\r\nperformance_schema.table_lock_waits_summary_by_table\r\nperformance_schema.threads\r\nperformance_schema.users\r\ndb1.t1<\/pre>\n
This way I produced the file to be used with the option –tables-file. Ok, now it’s time for the backup:<\/p>\n
[root@mysql01 opt]# sudo innobackupex --user=root --password=123456 --tables-file=\/tmp\/tablename --history=partial01 \/opt\r\n\r\nInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy\r\nand Percona LLC and\/or its affiliates 2009-2013.  All Rights Reserved.\r\n\r\nThis software is published under\r\nthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.\r\n\r\nGet the latest version of Percona XtraBackup, documentation, and help resources:\r\nhttp:\/\/www.percona.com\/xb\/p\r\n\r\n150331 17:32:29  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).\r\n150331 17:32:29  innobackupex: Connected to MySQL server\r\n150331 17:32:29  innobackupex: Executing a version check against the server...\r\n150331 17:32:29  innobackupex: Done.\r\n150331 17:32:29  innobackupex: Starting the backup operation\r\n\r\nIMPORTANT: Please check that the backup run completes successfully.\r\n           At the end of a successful backup run innobackupex\r\n           prints \"completed OK!\".\r\n\r\ninnobackupex:  Using server version 5.6.23-log\r\n\r\ninnobackupex: Created backup directory \/opt\/2015-03-31_17-32-29\r\n\r\n150331 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'\r\ninnobackupex: Waiting for ibbackup (pid=4771) to suspend\r\ninnobackupex: Suspend file '\/opt\/2015-03-31_17-32-29\/xtrabackup_suspended_2'\r\n\r\nxtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )\r\nxtrabackup: uses posix_fadvise().\r\nxtrabackup: cd to \/var\/lib\/mysql\r\nxtrabackup: open files limit requested 0, set to 1024\r\nxtrabackup: using the following InnoDB configuration:\r\nxtrabackup:   innodb_data_home_dir = .\/\r\nxtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend\r\nxtrabackup:   innodb_log_group_home_dir = .\/\r\nxtrabackup:   innodb_log_files_in_group = 2\r\nxtrabackup:   innodb_log_file_size = 50331648\r\n>> log scanned up to (1694982)\r\nxtrabackup: Generating a list of tablespaces\r\n>> log scanned up to (1694982)\r\n[01] Copying .\/ibdata1 to \/opt\/2015-03-31_17-32-29\/ibdata1\r\n[01]        ...done\r\n>> log scanned up to (1694982)\r\n[01] Copying .\/mysql\/innodb_index_stats.ibd to \/opt\/2015-03-31_17-32-29\/mysql\/innodb_index_stats.ibd\r\n[01]        ...done\r\n[01] Copying .\/mysql\/slave_worker_info.ibd to \/opt\/2015-03-31_17-32-29\/mysql\/slave_worker_info.ibd\r\n[01]        ...done\r\n[01] Copying .\/mysql\/innodb_table_stats.ibd to \/opt\/2015-03-31_17-32-29\/mysql\/innodb_table_stats.ibd\r\n[01]        ...done\r\n[01] Copying .\/mysql\/slave_master_info.ibd to \/opt\/2015-03-31_17-32-29\/mysql\/slave_master_info.ibd\r\n[01]        ...done\r\n[01] Copying .\/mysql\/slave_relay_log_info.ibd to \/opt\/2015-03-31_17-32-29\/mysql\/slave_relay_log_info.ibd\r\n[01]        ...done\r\n[01] Copying .\/db3\/t1.ibd to \/opt\/2015-03-31_17-32-29\/db3\/t1.ibd\r\n[01]        ...done\r\n[01] Copying .\/db3\/t2.ibd to \/opt\/2015-03-31_17-32-29\/db3\/t2.ibd\r\n[01]        ...done\r\n>> log scanned up to (1694982)\r\n[01] Copying .\/db3\/t3.ibd to \/opt\/2015-03-31_17-32-29\/db3\/t3.ibd\r\n[01]        ...done\r\n[01] Copying .\/db2\/t1.ibd to \/opt\/2015-03-31_17-32-29\/db2\/t1.ibd\r\n[01]        ...done\r\n[01] Copying .\/db2\/t2.ibd to \/opt\/2015-03-31_17-32-29\/db2\/t2.ibd\r\n[01]        ...done\r\n[01] Copying .\/db2\/t3.ibd to \/opt\/2015-03-31_17-32-29\/db2\/t3.ibd\r\n[01]        ...done\r\n[01] Copying .\/db1\/t1.ibd to \/opt\/2015-03-31_17-32-29\/db1\/t1.ibd\r\n[01]        ...done\r\n>> log scanned up to (1694982)\r\nxtrabackup: Creating suspend file '\/opt\/2015-03-31_17-32-29\/xtrabackup_suspended_2' with pid '4771'\r\n\r\n150331 17:32:34  innobackupex: Continuing after ibbackup has suspended\r\n150331 17:32:34  innobackupex: Executing FLUSH TABLES WITH READ LOCK...\r\n150331 17:32:34  innobackupex: All tables locked and flushed to disk\r\n\r\n150331 17:32:34  innobackupex: Starting to backup non-InnoDB tables and files\r\ninnobackupex: in subdirectories of '\/var\/lib\/mysql\/'\r\ninnobackupex: Backing up files '\/var\/lib\/mysql\/\/mysql\/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)\r\n>> log scanned up to (1694982)\r\n>> log scanned up to (1694982)\r\n>> log scanned up to (1694982)\r\ninnobackupex: 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)\r\n>> log scanned up to (1694982)\r\ninnobackupex: Backing up file '\/var\/lib\/mysql\/\/db3\/t3.frm'\r\ninnobackupex: Backing up file '\/var\/lib\/mysql\/\/db3\/t1.frm'\r\ninnobackupex: Backing up file '\/var\/lib\/mysql\/\/db3\/t2.frm'\r\n>> log scanned up to (1694982)\r\ninnobackupex: Backing up file '\/var\/lib\/mysql\/\/db2\/t3.frm'\r\ninnobackupex: Backing up file '\/var\/lib\/mysql\/\/db2\/t1.frm'\r\ninnobackupex: Backing up file '\/var\/lib\/mysql\/\/db2\/t2.frm'\r\ninnobackupex: Backing up file '\/var\/lib\/mysql\/\/db1\/t1.frm'\r\n150331 17:32:38  innobackupex: Finished backing up non-InnoDB tables and files\r\n\r\n150331 17:32:38  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...\r\n150331 17:32:38  innobackupex: Waiting for log copying to finish\r\n\r\nxtrabackup: The latest check point (for incremental): '1694982'\r\nxtrabackup: Stopping log copying thread.\r\n.>> log scanned up to (1694982)\r\n\r\nxtrabackup: Creating suspend file '\/opt\/2015-03-31_17-32-29\/xtrabackup_log_copied' with pid '4771'\r\nxtrabackup: Transaction log of lsn (1694982) to (1694982) was copied.\r\n150331 17:32:39  innobackupex: All tables unlocked\r\n\r\ninnobackupex: Backup created in directory '\/opt\/2015-03-31_17-32-29'\r\ninnobackupex: MySQL binlog position: GTID of the last change 'f2b66a45-ce62-11e4-8a01-0800274fb806:1-18'\r\ninnobackupex: Backup history record uuid edfd8656-d7cb-11e4-9cd1-0800274fb806 successfully written\r\n150331 17:32:40  innobackupex: Connection to database server closed\r\n150331 17:32:40  innobackupex: completed OK!<\/pre>\n
Observing carefully the xtrabackup output, one can quickly see that the tables we left out really stayed out and this is the result I was looking for, not news here. Until now we’ve been working to get things working s clearly as possible and work like a charm. But, this is not enough to avoid problems when using the backup directory produced by xtrabackup (in this case, \/opt\/2015-03-31_17-32-29). If one use the \/opt\/2015-03-31_17-32-29 as MySQL DATADIR at this point, when start up mysqld, this error message below will be seen for each table which is not part of the backup:<\/p>\n
2015-03-30 21:27:56 44823 [ERROR] InnoDB: Tablespace open failed for '\"db1\".\"t2\"', ignored.\r\n2015-03-30 21:27:56 7ff5d9f92720  InnoDB: Operating system error number 2 in a file operation.\r\nInnoDB: The error means the system cannot find the path specified.\r\nInnoDB: If you are installing InnoDB, remember that you must create\r\nInnoDB: directories yourself, InnoDB does not create them.\r\n\r\n2015-03-30 21:27:56 44823 [ERROR] InnoDB: Tablespace open failed for '\"db1\".\"t3\"', ignored.\r\n2015-03-30 21:27:56 7ff5d9f92720  InnoDB: Operating system error number 2 in a file operation.\r\nInnoDB: The error means the system cannot find the path specified.\r\nInnoDB: If you are installing InnoDB, remember that you must create\r\nInnoDB: directories yourself, InnoDB does not create them.<\/pre>\n
To avoid this, a second step is needed to clean out all the metadata from ibdata1 (prepare phase!!<\/strong>):<\/p>\n
[root@mysql01 opt]# sudo innobackupex --user=root --password=123456 --apply-log \/opt\/2015-03-31_17-32-29\r\n\r\nInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy\r\nand Percona LLC and\/or its affiliates 2009-2013.  All Rights Reserved.\r\n\r\nThis software is published under\r\nthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.\r\n\r\nGet the latest version of Percona XtraBackup, documentation, and help resources:\r\nhttp:\/\/www.percona.com\/xb\/p\r\n\r\n150331 17:41:06  innobackupex: Starting the apply-log operation\r\n\r\nIMPORTANT: Please check that the apply-log run completes successfully.\r\n           At the end of a successful apply-log run innobackupex\r\n           prints \"completed OK!\".\r\n\r\n\r\n150331 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\r\n\r\nxtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )\r\nxtrabackup: cd to \/opt\/2015-03-31_17-32-29\r\nxtrabackup: This target seems to be not prepared yet.\r\nxtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1694982)\r\nxtrabackup: using the following InnoDB configuration for recovery:\r\nxtrabackup:   innodb_data_home_dir = .\/\r\nxtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend\r\nxtrabackup:   innodb_log_group_home_dir = .\/\r\nxtrabackup:   innodb_log_files_in_group = 1\r\nxtrabackup:   innodb_log_file_size = 2097152\r\nxtrabackup: using the following InnoDB configuration for recovery:\r\nxtrabackup:   innodb_data_home_dir = .\/\r\nxtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend\r\nxtrabackup:   innodb_log_group_home_dir = .\/\r\nxtrabackup:   innodb_log_files_in_group = 1\r\nxtrabackup:   innodb_log_file_size = 2097152\r\nxtrabackup: Starting InnoDB instance for recovery.\r\nxtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)\r\nInnoDB: Using atomics to ref count buffer pool pages\r\nInnoDB: The InnoDB memory heap is disabled\r\nInnoDB: Mutexes and rw_locks use GCC atomic builtins\r\nInnoDB: Memory barrier is not used\r\nInnoDB: Compressed tables use zlib 1.2.3\r\nInnoDB: Not using CPU crc32 instructions\r\nInnoDB: Initializing buffer pool, size = 100.0M\r\nInnoDB: Completed initialization of buffer pool\r\nInnoDB: Highest supported file format is Barracuda.\r\nInnoDB: The log sequence numbers 1638299 and 1638299 in ibdata files do not match the log sequence number 1694982 in the ib_logfiles!\r\nInnoDB: Database was not shutdown normally!\r\nInnoDB: Starting crash recovery.\r\nInnoDB: Reading tablespace information from the .ibd files...\r\nInnoDB: Restoring possible half-written data pages\r\nInnoDB: from the doublewrite buffer...\r\nInnoDB: Last MySQL binlog file position 0 1802, file name mysql01-bin.000001\r\nInnoDB: 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.\r\nInnoDB: It will be removed from the data dictionary.\r\nInnoDB: Please refer to\r\nInnoDB: http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-troubleshooting-datadict.html\r\nInnoDB: for how to resolve the issue.\r\nInnoDB: 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.\r\nInnoDB: It will be removed from the data dictionary.\r\nInnoDB: Please refer to\r\nInnoDB: http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/innodb-troubleshooting-datadict.html\r\nInnoDB: for how to resolve the issue.\r\nInnoDB: 128 rollback segment(s) are active.\r\nInnoDB: Waiting for purge to start\r\nInnoDB: 5.6.22 started; log sequence number 1694982\r\n\r\n[notice (again)]\r\n  If you use binary log and don't use any hack of group commit,\r\n  the binary log position seems to be:\r\nInnoDB: Last MySQL binlog file position 0 1802, file name mysql01-bin.000001\r\n\r\nxtrabackup: starting shutdown with innodb_fast_shutdown = 1\r\nInnoDB: FTS optimize thread exiting.\r\nInnoDB: Starting shutdown...\r\nInnoDB: Shutdown completed; log sequence number 1696565\r\n\r\n150331 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\r\nfor creating ib_logfile*\r\n\r\nxtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )\r\nxtrabackup: cd to \/opt\/2015-03-31_17-32-29\r\nxtrabackup: This target seems to be already prepared.\r\nxtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.\r\nxtrabackup: using the following InnoDB configuration for recovery:\r\nxtrabackup:   innodb_data_home_dir = .\/\r\nxtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend\r\nxtrabackup:   innodb_log_group_home_dir = .\/\r\nxtrabackup:   innodb_log_files_in_group = 2\r\nxtrabackup:   innodb_log_file_size = 50331648\r\nxtrabackup: using the following InnoDB configuration for recovery:\r\nxtrabackup:   innodb_data_home_dir = .\/\r\nxtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend\r\nxtrabackup:   innodb_log_group_home_dir = .\/\r\nxtrabackup:   innodb_log_files_in_group = 2\r\nxtrabackup:   innodb_log_file_size = 50331648\r\nxtrabackup: Starting InnoDB instance for recovery.\r\nxtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)\r\nInnoDB: Using atomics to ref count buffer pool pages\r\nInnoDB: The InnoDB memory heap is disabled\r\nInnoDB: Mutexes and rw_locks use GCC atomic builtins\r\nInnoDB: Memory barrier is not used\r\nInnoDB: Compressed tables use zlib 1.2.3\r\nInnoDB: Not using CPU crc32 instructions\r\nInnoDB: Initializing buffer pool, size = 100.0M\r\nInnoDB: Completed initialization of buffer pool\r\nInnoDB: Setting log file .\/ib_logfile101 size to 48 MB\r\nInnoDB: Setting log file .\/ib_logfile1 size to 48 MB\r\nInnoDB: Renaming log file .\/ib_logfile101 to .\/ib_logfile0\r\nInnoDB: New log files created, LSN=1696565\r\nInnoDB: Highest supported file format is Barracuda.\r\nInnoDB: 128 rollback segment(s) are active.\r\nInnoDB: Waiting for purge to start\r\nInnoDB: 5.6.22 started; log sequence number 1696780\r\n\r\n[notice (again)]\r\n  If you use binary log and don't use any hack of group commit,\r\n  the binary log position seems to be:\r\nInnoDB: Last MySQL binlog file position 0 1802, file name mysql01-bin.000001\r\n\r\nxtrabackup: starting shutdown with innodb_fast_shutdown = 1\r\nInnoDB: FTS optimize thread exiting.\r\nInnoDB: Starting shutdown...\r\nInnoDB: Shutdown completed; log sequence number 1696790\r\n150331 17:41:12  innobackupex: completed OK!<\/pre>\n
Doing this way, one can just transfer the backupset, if it’s huge size, try ftp files between servers, change the owner of the new directory and point MySQL’s\u00a0DATADIR variable to it and finally, restart mysqld monitoring the error log:<\/p>\n
150331 17:46:32 mysqld_safe Starting mysqld daemon with databases from \/opt\/2015-03-31_17-32-29\r\n2015-03-31 17:46:33 4759 [Note] Plugin 'FEDERATED' is disabled.\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Using atomics to ref count buffer pool pages\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: The InnoDB memory heap is disabled\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Memory barrier is not used\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Compressed tables use zlib 1.2.3\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Using Linux native AIO\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Not using CPU crc32 instructions\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Initializing buffer pool, size = 128.0M\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Completed initialization of buffer pool\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Highest supported file format is Barracuda.\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: 128 rollback segment(s) are active.\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: Waiting for purge to start\r\n2015-03-31 17:46:34 4759 [Note] InnoDB: 5.6.23 started; log sequence number 1696790\r\n2015-03-31 17:46:35 4759 [Note] Server hostname (bind-address): '*'; port: 3306\r\n2015-03-31 17:46:35 4759 [Note] IPv6 is available.\r\n2015-03-31 17:46:35 4759 [Note]   - '::' resolves to '::';\r\n2015-03-31 17:46:35 4759 [Note] Server socket created on IP: '::'.\r\n2015-03-31 17:46:36 4759 [Note] Event Scheduler: Loaded 0 events\r\n2015-03-31 17:46:36 4759 [Note] \/usr\/sbin\/mysqld: ready for connections.\r\nVersion: '5.6.23-log'  socket: '\/var\/lib\/mysql\/mysql.sock'  port: 3306  MySQL Community Server (GPL)<\/pre>\n
It must be as clean as possible to convince that everything went well with the backupset produce and with all the processes to get it done. I’ve just tried it with log_warnings=3 and ended up getting a clean log once again.<\/p>\n
Some questions have brought on your mind? Fire up a comment!!<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
I remember the time when all the database and sys admins used to speak about MySQL backup strategy and it was really something to not worry about too much for many reasons. One of them was that the dataset on MySQL schemas was not too big, not that critical and the information was not that […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[37,10,36,38,35],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1082"}],"collection":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1082"}],"version-history":[{"count":25,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1082\/revisions"}],"predecessor-version":[{"id":1119,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1082\/revisions\/1119"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1082"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1082"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1082"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}