JanHvizdak.COM

Backup and point-in-time recovery of MySQL databases

Backups should be performed on a regular basis in order to prevent following scenarios:

Loss of data due to hardware failure
Loss of data due to human error (deleting/updating wrong data)

Most of us don’t use Enterprise MySQL therefore setting up a valid backup policy might take some time, however the below-explained process works nicely and secures data if the backup technique applied properly on any machine.

Prerequisites

Of course there should be a MySQL database installed on your machine. If you’re missing it, get one here. I also assume you have physical access to the database server and MySQL files, or at least you have root MySQL privileges as we’re going to apply binary logs when performing a point-in-time recovery. First of all we’ll create a database “test2”, we’ll put some data there too.

#open a terminal and login to mysql as root
mysql -u root -p -A

--enter password when you’re asked for it
--now create a new database

CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE test2;

--now create our table into which we’re going to insert random data
CREATE TABLE `test_table` (
  `datecol` datetime NOT NULL,
  `name` varchar(25) NOT NULL,
  KEY `datecol` (`datecol`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--we will also need a procedure that inserts data into the table
delimiter $$  
CREATE PROCEDURE `fill_test_table`()
begin                                                                                                                                                                  
declare i int Default 0 ;   
declare random char(20) ;                                                                                                                                     
myloop: loop
set random=conv(floor(rand() * 99999999999999), 20, 36) ;
INSERT INTO `test_table` VALUES ( NOW() , random );
set i=i+1;
if i=10000 then
leave myloop;
end if;
end loop myloop;
end $$
delimiter ;

--now call the procedure in order to fill the table:
CALL fill_test_table();

--now ensure your database keeps creating binary logs:
SHOW MASTER LOGS;

--output should look like:
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       245 |
+------------------+-----------+

--if logging isn’t allowed, you will get following error:
ERROR 1381 (HY000): You are not using binary logging

--in such a case open a new terminal as root and execute:
nano /etc/my.cnf #if my.cnf is located somewhere else, find it with command below:

find / -name my.cnf -type f 2>/dev/null

#in my.cnf you have to uncomment following line:
log-bin=mysql-bin

#also ensure our filesystem won’t be clogged up with logs and set:
expire_logs_days = 1

#now, if you’re using nano editor, press CTRL+X and confirm saving

#in SuSE or openSuSE Linux restart mysql by:
service mysql restart

Backup technique - mysqldump

Once we have some data we can finally move to backup techniques. First is mysqldump. Mysqldump is a perfect tool for creating backups and it should be used in most cases. The only case when mysqldump may be ineffective is when dump takes too long to finish and a physical copy of particular tables is way faster than that tool. Since mysqldump locks tables during creating a dump, our goal is to minimise time needed for creating an exported file (locking can be disabled, however it is not recommended to do so as you may end up with inconsistent data). Most MySQL databases are small enough to use mysqldump.

--open a new terminal and execute following:

mysqldump -u root -p --routines test2 > test2.dump

#we now have a new file called test2.dump, let’s return to the MySQL console and execute:

drop database test2;

--try to connect to the database test2:

USE test2;

--you will get following error:
ERROR 1049 (42000): Unknown database 'test2'

--now create a blank database by executing:
CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET utf8 */;

--connect to database
USE test2;

--show all tables, you will see no existing tables
SHOW TABLES;

--let’s switch to the second terminal and execute:

mysql -u root -p test2 < test2.dump

#our table is now restored, if there were more tables they’d be restored too

A more complicated case - Point in time recovery

The case above is a simplification and can be used as a disaster recovery only if there are no binary logs available for a “point in time” recovery. Imagine there is a job executing mysqldump every day, and imagine binary logging is disabled. In such a case we can only perform a complete restore to the time of last (or any chosen) backup which is, as already mentioned, 24 hours. However it is usually too time and data cosuming to perform a full export every 24 hours since we’d have to keep all recent full backups which would result in lots of wasted space. Let’s assume our database contains only 1GB of data, so if we wanted to keep backups of last 30 days, then we’d have to reserve another 30GB just for a database backup.

This is why binary logging should be used along with mysqldump the way we i.e. perform a full backup once every 7 days and after that we only perform backups of binary logs which are in most cases much smaller in size than dump of entire database.

Binary logs store information which can be used to perform redo over a particular database.

The above-shown examples explain how to turn on binary logs, thus in the following example it’s assumed binary logs are enabled and also it’s assumed you know how to perform a full backup with mysqldump.

#Open a new MySQL console by executing following command:
mysql -u root -p -A

--type password when prompted
--and get current date & time from MySQL by issuing this SQL:
--it is not necessary now, but we’ll issue the command again below

SELECT now() as CurrentDateTime;

--connect to our database test2:
USE test2;

--now execute the procedure from the case above twice:
call fill_test_table();
call fill_test_table();

--get information about number of rows in the table:
SELECT COUNT(*) FROM test_table;

--something like following is expected:
+----------+
| count(*) |
+----------+
|    30000 |
+----------+
1 row in set (0.01 sec)

--Let’s open a new terminal where we’re going to perform a full dump by executing:
--Bear in mind you have to specify the password in the second command
mysqldump -R --add-drop-database --flush-privileges --databases test2 -u root -p > test2.dump &&
mysql -u root --password="" --execute="reset master; " && pwd

#What we’ve done so far: Full backup of database test2 and reset of binary logs
#which is needed for a point-in-time recovery
#Also command pwd will return current directory name where dumpfile is located

#Now let’s switch back to the MySQL console and execute some update over our table
#And before we do so we shall create a temporary table for this purpose:
CREATE TABLE test_table1 SELECT * FROM test_table;

--Now the actual update:
UPDATE test_table SET name = (SELECT name FROM test_table1 ORDER BY RAND() LIMIT 1) ORDER BY RAND() LIMIT 100;

--Output is following:
Query OK, 100 rows affected (4.08 sec)
Rows matched: 100  Changed: 100  Warnings: 0

--Assuming this update was OK, get current date and time:

SELECT now() as CurrentDateTime;

--This is our output:
+---------------------+
| CurrentDateTime     |
+---------------------+
| 2013-05-14 14:40:32 |
+---------------------+
1 row in set (0.00 sec)

--Now execute another queries which are not wanted:

UPDATE test_table SET name = (SELECT datecol FROM test_table1 ORDER BY RAND() LIMIT 1) ORDER BY RAND() LIMIT 100;
DELETE FROM test_table WHERE name LIKE '%A%' ORDER BY RAND() LIMIT 100;

--Once again we get following output:
Query OK, 100 rows affected (3.50 sec)
Rows matched: 100  Changed: 100  Warnings: 0

--The problem is that our table has been changed and we would like to restore it!
--In order to do so and restore it to state as of 2013-05-14 14:40:32 , follow this:

SHOW MASTER LOGS;

--Output will look like:
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      5556 |
+------------------+-----------+
1 row in set (0.00 sec)

--Now we have to go to the MySQL directory which is usual /var/lib/mysql , however we can get it using:

SELECT @@datadir;

--The output is in our case:
+--------------+
| @@datadir    |
+--------------+
| /mysql/data/ |
+--------------+
1 row in set (0.00 sec

--Thus switch back to the OS terminal console and restore the dumpfile:
mysql -u root -p < test2.dump

#We also have to disable all users from logging in temporarily which means
#we have to issue following in OS terminal:

mysql -u root -p

#Enter password and issue:

CREATE TABLE mysql.user_off LIKE mysql.user;
REPLACE INTO mysql.user_off SELECT * FROM mysql.user;
DELETE FROM mysql.user WHERE user NOT LIKE ('root');
FLUSH PRIVILEGES;

--No-one but root is now able to login which means we can continue the restore
--Now apply binary logs to the time before wrong SQL was issued
--In order to do so, open a new OS terminal and go to the datadir directory

cd /mysql/data/ #Bear in mind this may differ, that’s why we performed one of the selects above

#Now it’s only needed to apply all binary logs.
#In order to do so go to the OS terminal, ideally login as root and issue (specify the password):

mysqlbinlog --stop-datetime="2013-05-14 14:40:32" mysql-bin.000001 | mysql -u root --password=""

#Also return to the MySQL console where you’re logged in as root and issue following commands:

INSERT IGNORE INTO mysql.user SELECT * FROM mysql.user_off WHERE user NOT LIKE ('root');
FLUSH PRIVILEGES;

Cleanup

If everything is clear, drop the database test2

#connect to the database from terminal as root
mysql -u root -p

--enter root MySQL password and continue
--drop the database
DROP DATABASE test2;

exit;

--Now return to the OS terminal and remove dump file:

rm test2.dump -r -f

exit

Conclusion and further notes

Point-in-time recovery can be applied to a particular database and we also have to review mysql-bin.index if we have for example lost data from entire drive. This file lists all binary logs in correct order just in case there is any confusion regarding which has to be applied first, etc. It is also important to backup the database and binary logs to another physical drive so if there’s a HDD failure, entire database server can be easily restored. Replication should come into play when we deal with a database that’s really large since regular dumps may lock entire database for an unreasonably long time which is not wanted in production systems.

JanHvizdak.COM

Secure Browsing

Recent News

Cross-Linker update

January 23^rd, 2014

Worth visiting

Backup and point-in-time recovery of MySQL databases

Prerequisites

Backup technique - mysqldump

A more complicated case - Point in time recovery

Cleanup

Conclusion and further notes

Further reading & help

Secure Browsing

Recent News

Cross-Linker update

January 23rd, 2014

Worth visiting

Backup and point-in-time recovery of MySQL databases

Prerequisites

Backup technique - mysqldump

A more complicated case - Point in time recovery

Cleanup

Conclusion and further notes

Further reading & help

January 23^rd, 2014