How to use Xtrabackup to back up and restore MySQL

Backup

Backup is easy as we are use innobackupex.

innobackupex --user=bkpuser --password=$passwd --stream=xbstream --parallel=5  --compress --compress-threads=5  $backup_dir > $backup_dir/mysql_"$backup_date".xtream

Note: innobackupex is a wrapper of xtrabackup, which let us use xtrabackup easily

innobackupex does a Locks-Free backup on your database.

Restore

Now, assume that you have already had a backup named backup.xtream

Unpack your backup

$ xbstream -x < backup.xbstream

It will unpack your data in current directory.

Extract the qp files

$ for i in $(find -name "*.qp"); do qpress -vd $i $(dirname ${i}) && rm -f $i; done

Use apply-log on your backup

$ innobackupex --apply-log ./

Stop MySQL

$ mysqladmin -u root -p shutdown

Move back your data

$ rm -Rf /path/of/mysql/data/*
$ innobackupex --copy-back ./

Innobackupex will automatically find the data path of mysql by reading the config in /etc/mysql/my.cnf

Correct the ownership of mysql data

$ chown -Rf mysql.mysql /path/of/mysql/data

Start MySQL

$ sudo service mysql start

Start slave

First, copy the GTIDs in xtrabackup_binlog_info.

Then set it to GTID_PURGED in mysql.

mysql> SET GLOBAL GTID_PURGED="YOUR_GTIDS"

Set master info

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_ip',
-> MASTER_PORT=master_port,
-> MASTER_USER='slave_user_name',
-> MASTER_PASSWORD='slave_password',
-> MASTER_AUTO_POSITION = 1;

Start slave

mysql> start slave;
mysql> show slave status\G

If everything goes well, you will get this:


Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Seconds_Behind_Master: 12345

Troubleshooting

  • relay log error after starting slave
mysql> stop slave;
mysql> show slave status\G
// record GTIDs at this time
mysql> reset slave;
mysql> reset master;
mysql> SET GLOBAL GTID_PURGED="YOUR_GTIDS"
mysql> start slave;
mysql> show slave status\G
  • Lost master info

Use change master query above.

  • Replication user auth failed

In master:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'121.196.193.45' IDENTIFIED BY 'password_of_repl’;

‘121.196.193.45’ is your slave ip.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.