tags: backupbinary logsMySQL
Backing up MySQL data is very important if you run MySQL on any kind of production server. We'll explore some basic concepts which allows you to do that.
We're interested in two types of backup: fulland incremental.
Full backup can be saved as a collection of queries which can restore a database (logical), or raw copy of directories which contains MySQL data (physical). For large databases (>10GB), you'll probably want to use the latter option. You can do that by using tools like mysqlhotcopyfor MyISAM and InnoDB Hot Backup or Xtrabackup for InnoDB table. In this example we will make the logical full backup with mysqldumpcommand.
Incremental backup can be done by backing up MySQL binary logs.
Let's make sure that binary logs are enabled. Edit MySQL's my.cnf config file and check for this lines:
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
First option tells MySQL to enable binary logging and save the logs under /var/log/mysql/ directory. Second option means that all logs older that 10 days will be automatically deleted.
Make this changes in my.cnf as necessary and restart mysql server.
If you look at /var/log/mysql/ dir, you will find something like this:
root@test1:~# ls -al /var/log/mysql/
total 16
drwxr-s--- 2 mysql adm 4096 Sep 5 10:41 .
drwxr-xr-x 7 root root 4096 Sep 5 09:52 ..
-rw-rw---- 1 mysql adm 106 Sep 5 10:41 mysql-bin.000001
-rw-rw---- 1 mysql adm 32 Sep 5 10:41 mysql-bin.index
mysql-bin.000001 is currently active log in which will be written SQL for changes done on all databases (you can limit that behavior with binlog_do_db and binlog_ignore_db params).
/var/log/mysql/mysql-bin.index contains list of all binary log files.
Now, let's create a test database.
CREATE DATABASE `bk_test`;
USE `bk_test`;
CREATE TABLE `bk_test_t1` (
`id` INT NOT NULL AUTO_INCREMENT,
`test_field` VARCHAR(30) NOT NULL,
`time_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
And insert some rows:
USE `bk_test`;
INSERT into `bk_test_t1` (test_field) VALUES ('val1');
INSERT into `bk_test_t1` (test_field) VALUES ('val2');
INSERT into `bk_test_t1` (test_field) VALUES ('val3');
Backup
OK, this is the initial state of our db for which will now do full backup with mysqldump:
mysqldump -uroot -p --all-databases --single-transaction --flush-logs --master-data=2 > full_backup.sql
(--single-transaction will dump InnoDB tables in a consistent state, --flush-logs will close current logs and open a new one, --master-data=2 will write binary log coordinates in SQL comment in the dump file)
If you take a look again at /var/log/mysql/ dir, you'll see that new log file is created (mysql-bin.000002). All changes from now on, will be written to that file. Next time you execute flush-logs or restart mysql server, mysql-bin.000003 will be created and so on.
root@test1:~# ls -al /var/log/mysql/
total 20
drwxr-s--- 2 mysql adm 4096 Sep 5 10:48 .
drwxr-xr-x 7 root root 4096 Sep 5 09:52 ..
-rw-rw---- 1 mysql adm 149 Sep 5 10:48 mysql-bin.000001
-rw-rw---- 1 mysql adm 106 Sep 5 10:48 mysql-bin.000002
-rw-rw---- 1 mysql adm 64 Sep 5 10:48 mysql-bin.index
Let's now insert more rows into db:
USE `bk_test`;
INSERT into `bk_test_t1` (test_field) VALUES ('val4');
INSERT into `bk_test_t1` (test_field) VALUES ('val5');
INSERT into `bk_test_t1` (test_field) VALUES ('val6');
OK, now it's time to do incremental backup. It's pretty simple, we only need to flush logs again and save binary logs created from last full backup until now to some secure location.
To manually flush logs, execute this command:
mysqladmin -uroot -p flush-logs
New binary log is created in /var/log/mysql/
root@test1:~# ls -al /var/log/mysql/
total 24
drwxr-s--- 2 mysql adm 4096 Sep 5 10:58 .
drwxr-xr-x 7 root root 4096 Sep 5 09:52 ..
-rw-rw---- 1 mysql adm 149 Sep 5 10:48 mysql-bin.000001
-rw-rw---- 1 mysql adm 884 Sep 5 10:58 mysql-bin.000002
-rw-rw---- 1 mysql adm 106 Sep 5 10:58 mysql-bin.000003
-rw-rw---- 1 mysql adm 96 Sep 5 10:58 mysql-bin.index
And we only need to save mysql-bin.000002, because it contains all changes we done after our full backup.
Restore
Let's see how our table looks now...
mysql> select * from bk_test_t1;
+----+------------+---------------------+
| id | test_field | time_created |
+----+------------+---------------------+
| 1 | val1 | 2010-09-05 10:15:35 |
| 2 | val2 | 2010-09-05 10:15:35 |
| 3 | val3 | 2010-09-05 10:15:35 |
| 4 | val4 | 2010-09-05 10:57:43 |
| 5 | val5 | 2010-09-05 10:57:43 |
| 6 | val6 | 2010-09-05 10:57:44 |
+----+------------+---------------------+
6 rows in set (0.00 sec)
Now we will destroy the database and try to restore it from backup.
mysql> drop database bk_test;
Query OK, 1 row affected (0.01 sec)
First, from the full backup:
root@test1:~# mysql -u root -p
mysql> create database bk_test;
Query OK, 1 row affected (0.00 sec)
root@test1:~# mysql -u root -p bk_test < full_backup.sql
This is the contents of the table now:
mysql> select * from bk_test_t1;
+----+------------+---------------------+
| id | test_field | time_created |
+----+------------+---------------------+
| 1 | val1 | 2010-09-05 10:15:35 |
| 2 | val2 | 2010-09-05 10:15:35 |
| 3 | val3 | 2010-09-05 10:15:35 |
+----+------------+---------------------+
3 rows in set (0.00 sec)
Now we need to just apply the changes from the binary log. There is a tool called mysqlbinlogwhich outputs the contents of the logs in text format. Its result can be piped directly to the mysql command.
root@test1:~# mysqlbinlog mysql-bin.000002 | mysql -uroot -p bk_test
And voila! we have restored the original table:
mysql> select * from bk_test_t1;
+----+------------+---------------------+
| id | test_field | time_created |
+----+------------+---------------------+
| 1 | val1 | 2010-09-05 10:15:35 |
| 2 | val2 | 2010-09-05 10:15:35 |
| 3 | val3 | 2010-09-05 10:15:35 |
| 4 | val4 | 2010-09-05 10:57:43 |
| 5 | val5 | 2010-09-05 10:57:43 |
| 6 | val6 | 2010-09-05 10:57:44 |
+----+------------+---------------------+
6 rows in set (0.00 sec)
Dates are correctly restored because bin log contains SET TIMESTAMP command to adjust the time before the concrete query.
But, binary logs are much more powerful than that. You can, for example, pipe the result from mysqlbinlog to a file, remove bad queries and restore it.
You can also limit the list of returned queries with start and end time. For example, to restore the database to the state as it was on 2010-09-05 10:55:44, you can use following command:
root@test1:~# mysqlbinlog --stop-datetime="2010-09-05 10:55:44" mysql-bin.000002 | mysql -uroot -p bk_test
To include multiple logs, just include them in command, one after another:
root@test1:~# mysqlbinlog mysql-bin.000002 mysql-bin.000003 | mysql -uroot -p bk_test
You can use --start-datetime and --stop-datetime to locate positions in binary logs, and then use that positions in --start-position and --stop-position params to limit returned queries.
It's a good idea to save binary logs on some SAN storage, or rsync them periodically to another location, so if something goes wrong, you can first restore data from the last incremental backup, and then restore additional data from those new logs, so minimum amount of data is lost. You can also check sync_binlog mysql parameter, which controls when binary logs are synchronized to the disk.
Next time, I'll show you how to combine this concepts with BackupPC.
扫一扫
1119
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
