Setup Hot Backups of MySQL with Percona Xtrabackup on Ubuntu
When backing up a database, you generally don't want to stop the database. Because if you stop it, your application will become unavailable. But if you do not stop it, your backup might end up inconsistent and useless, due to half done transactions being backed up. Percona have come up with good solution for incremental backup of running MySQL instance. Their hot backup solution is called Percona XtraBackup and can be used to backup both MySQL, MariaDB and PerconaServer. I requires running InnoDB storage engine though. In this article we will show how to install MySQL (current version 5.7) on Ubuntu 16.o4 and use XtraBackup to backup it while running. For restoring the backup, the database server must be stopped and data dir evacuated.
Installing MySQL
MySQL is present in Ubuntu repositories and we can install it with simple apt command:
sudo apt install mysql-server mysql-client
It will prompt you for root password from ncurses environment. Enter it and complete the installation process.
After that is done, we need to run a script to secure MySQL installation.
sudo mysql_secure_installation
Here you could change the password you entered previously, you can disable the remote login and you can delete the test database.
We need as well to add your user to the mysql grup, this command will do it
sudo gpasswd -a ubuntu mysql
The user name is ubuntu, you change it for your own. To check if install is successful, you can try to log in
mysql -u root -p
Lets then create a database that we will work with latter
CREATE DATABASE dbtobackup;
Lets create user and grant privileges to this database
GRANT ALL ON dbtobackup.* TO 'miki' IDENTIFIED BY 'password';
We need to populate database so first thing we create is a table like this:
USE dbtobackup;
CREATE TABLE linoxide_staff (id INT, name VARCHAR(12), nickname VARCHAR(28));
Then we put data into table
INSERT INTO linoxide_staff (id,name,nickname) VALUES(1,"Bobin","Bobby");
INSERT INTO linoxide_staff (id,name,nickname) VALUES(2,"Mihajlo","Miki");
And check what we created
mysql> SELECT * FROM linoxide_staff;
Next for backup to work, we need to give this user some privileges also to other databases that we might create later
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'miki'@'localhost';
We also need to grant process:
GRANT SELECT, PROCESS ON *.* TO 'miki'@'localhost';
Next we can exit mysql and move to setup of Percona xtrabackup.
Using Percona Xtrabackup to backup your database
Lets get deb file for percona repository
wget https://www.percona.com/redir/downloads/percona-release/ubuntu/latest/percona-release_0.1-4.xenial_all.deb
Next we will install it
sudo dpkg -i percona-release_0.1-4.xenial_all.deb
Update sources list:
sudo apt update
And install Percona xtrabackup
sudo apt install percona-xtrabackup-24
Note that older version than 2.4 wont work.
We need to set permissions to MySQL data dir, which is where the databases are stored
sudo chown -R mysql: /var/lib/mysql
sudo find /var/lib/mysql -type d -exec chmod 775 "{}" \;
And we need to create backup dir, that is where we will store the backups
sudo mkdir -p /data/backups
sudo chown -R ubuntu: /data
Next we can backup the database:
innobackupex --user=miki --password=password --no-timestamp /data/backups/first_backup
This command will give you pretty verbose output and when it finishes execution, you should get something like this:
161109 17:04:51 [00] ...done
xtrabackup: Transaction log of lsn (2540757) to (2540766) was copied.
161109 17:04:51 completed OK!
For your backup to always be consistent and ready for restore, you would need to apply transaction log to it. That will help with consistency and with avoiding corruption.
innobackupex --apply-log /data/backups/
This command should also finish with completed OK! output if completed successfully.
Restoring the database from Xtrabackup
In order to restore backup you must stop the the database server and empty the data dir.
sudo systemctl stop mysql.service
After service have stopped, what we going to do is somewhat of a paradox. We backup the data dir for the case that backup doesn't succeed. You can't skip this process, as either way data dir needs to be empty. So if you don't want to backup, just delete. We will move/backup now:
mkdir /tmp/mysql
mv /var/lib/mysql/* /tmp/mysql/
The backup is restored with copy-back command
innobackupex --copy-back /data/backups/first_backup
When backup is restored we need to change permissions to new copy of lib directory
sudo chown -R mysql: /var/lib/mysql
Next we can start again the database server
sudo systemctl start mysql.service
Conclusion
We have successfully done a backup and restore of MySQL on Ubuntu using Percona Xtrabackup. Using this article as a string point you might want to automate this process with a bash script. Another useful guide that we recently published is one about lsyncd. You could use lysncd to sync your backup dir (/data/backups in this case) to remote server and in such way safeguard your database in case your server becomes totally inaccessible for some reason. This would be all for today's article, thank you for reading and hopefully this helps you guard your data.
You might also like
Filed Under : UBUNTU HOWTO
Free Linux Ebook to Download
When in this world you are using an user to do backups? And why stop mysql when restore? WTF!!!
You can either create a new user with a minimal set of privileges, or use an administrative account such as root.
for second question refer - https://www.percona.com/doc/percona-xtrabackup/2.0/xtrabackup_bin/restoring_a_backup.html