Xtrabackup vs mysqldump

[chrys...@trading-point.com]

Hello guys,

i am in the process of writing backup policies for my new databases and i am not sure which one is the best for me.

as i understand xtrabackup ( innobackupex ) it does hot backup but for the restoring i need to stop mysql server right?

as all my tables are innodb i am guessing mysqdump with single-transaction will work for me . 

the reason i don't like innobackupex is due to the mysql downtime. i can't have downtime on my mysql server

thanks in advance 

[Kenny Gryp]

On 13 Feb 2015, at 15:18, chrys...@trading-point.com wrote:

Hello guys,

i am in the process of writing backup policies for my new databases and i am not sure which one is the best for me.

as i understand xtrabackup ( innobackupex ) it does hot backup but for the restoring i need to stop mysql server right?

This is correct in the strict sense, Percona XtraBackup takes physical backups, doing a full restore means replacing the complete server anyway.

However, there are other ways to recover to an existing mysql daemon:

- After doing the —apply-log step on the backup, you can just run a MySQL server on it and take a logical dump on what you need, which you can then reimport in an existing server

- You can restore individual tables online, no need to restart the server: The process is not quite automated, it requires some steps: http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/restoring_individual_tables_ibk.html

as all my tables are innodb i am guessing mysqdump with single-transaction will work for me . 

It might, it might not. 

During the whole backup process, a transaction will be active. Long running transactions require looking at old versions of rows if they have changed (MVCC), purging of them can’t be done as long as the transaction is active. (history list length metric in SHOW ENGINE INNODB STATUS). This might in some cases cause the server to perform a lot slower.

Another thing to be aware of is the restore time. Doing a logical backup restore takes exponentially longer as the datasize increases. I’ve seen environments that needed more than a week to restore from backup. While with Percona XtraBackup, it’s only the —apply-log phase and copying the datafiles to the destination directory.

Kenny Gryp

— 
Principal Architect, Percona
Phone: +1 888 401 3401 Ext: 527
Skype: grypyrg

[chrys...@trading-point.com]

Kenny 

thanks a lot for the update

so as you suggested  i need to do the following changes

on my server.cnf i need to add 

innodb_import_table_from_xtrabackup = 1

after that and server restart i need to run first

innobackupex --defaults-file=<CNF> --user=<MYSQLUSER> --no-timestamp --password=<MYSQLPASS>  <BACKUPPATH>

innobackupex --apply-log --use-memory=<USEMEMORY> <BKPTEMPDIR>

that way i will prepare my database for restoring right?

after that need to create the table on my server ,  ALTER TABLE mydatabase.mytable DISCARD TABLESPACE; , copy the files and then  ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

is that correct?

[Kenny Gryp]

On 16 Feb 2015, at 15:40, chrys...@trading-point.com wrote:

Kenny 

thanks a lot for the update

so as you suggested  i need to do the following changes

on my server.cnf i need to add 

innodb_import_table_from_xtrabackup = 1

after that and server restart i need to run first

You need to change the above setting on a Percona Server 5.5.

If you’re using MySQL 5.6 (or Percona Server 5.6), you do not need to do that.

The setting is also dynamic: http://www.percona.com/doc/percona-server/5.5/management/innodb_expand_import.html#innodb_import_table_from_xtrabackup

Just do

SET GLOBAL innodb_import_table_from_xtrabackup = 1;

Please note that this is for an individual server, not for a Galera Cluster, in which you need to do this complete process on all nodes.

This configuration setting should only be  done on the server you want to recover to. This does not need to happen on the server you are backing up.

innobackupex --defaults-file=<CNF> --user=<MYSQLUSER> --no-timestamp --password=<MYSQLPASS>  <BACKUPPATH>

innobackupex --apply-log --use-memory=<USEMEMORY> <BKPTEMPDIR>

that way i will prepare my database for restoring right?

To do a full restore yes, not to export individual tables. You have to do ' innobackupex —apply-log —export'

after that need to create the table on my server ,  ALTER TABLE mydatabase.mytable DISCARD TABLESPACE; , copy the files and then  ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

is that correct?

Yes

[chrys...@trading-point.com]

gryp

thanks a lot for the update.

i've run SET GLOBAL innodb_import_table_from_xtrabackup = 1; on all my 3 nodes.

i then run 

innobackupex --defaults-file=<CNF> --user=<MYSQLUSER> --no-timestamp --password=<MYSQLPASS>  <BACKUPPATH>

and then

        innobackupex --apply-log --export --use-memory=<USEMEMORY> <BKPTEMPDIR>

but the .exp file doesn't exist...