本文来自我的github pages博客http://galengao.github.io/ 即www.gaohuirong.cn
备份策略
针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下三种：
- 直接cp,tar复制数据库文件
- mysqldump 复制BIN LOGS
- lvm2快照 复制BIN LOGS
- xtrabackup
以上的几种解决方案分别针对于不同的场景 - 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
- 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
- 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
- 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份
实战演练
cp复制数据文件备份及恢复
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.03 sec) mysql> create database test;
Query OK, 1 row affected (0.03 sec) mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec) mysql> create table a(id int,name varchar(10));
Query OK, 0 rows affected (0.04 sec) mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
+----------------+
1 row in set (0.00 sec) mysql> insert into a(id,name)values(1,'gao')
-> ;
Query OK, 1 row affected (0.01 sec) mysql> commit;
Query OK, 0 rows affected (0.00 sec) mysql> select * from a
-> ;
+------+------+
| id | name |
+------+------+
| 1 | gao |
+------+------+
1 row in set (0.00 sec) [root@my57 mysql]# mkdir /backup [root@my57 mysql]# cp -a /data/mysql/data/* /backup/ [root@my57 mysql]# ls /backup/
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 my57.err my57.pid mysql mysqld_safe.pid performance_schema sys test xtrabackup_info [root@my57 mysql]# rm -rf /data/mysql/data/*
[root@my57 mysql]# service mysql restart ERROR! MySQL server PID file could not be found!
Starting MySQL... ERROR! The server quit without updating PID file (/data/mysql/data//my57.pid).
这时启动不了，我们再把备份文件拷贝回来，在启动就可以了。 cp -a /backup/* /data/mysql/data/
mysqldump的复制与恢复
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.02 sec) mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
| b |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from b;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec) # 记住备份前position的值
mysql> show master status
-> ;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000001 | 567 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
开始备份
[root@my57 data]# mysqldump --all-databases --lock-all-tables > /backup/backup.sql
再创建一个数据库做增量测试
mysql> create database test1;
Query OK, 1 row affected (0.00 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+--------------------+
6 rows in set (0.00 sec) # 再记下现在的position位置
mysql> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000001 | 869 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
备份二进制日志 cp bin-log.000001 /backup/
停止mysql在启动，编译安装的启动不了，必须重新初始化
[root@my57 data]# service mysql stop
Shutting down MySQL. SUCCESS! # 模拟删除数据文件
[root@my57 data]# rm -rf * # 编译安装的数据库删除数据文件后启动不了
[root@my57 data]# service mysql start
Starting MySQL... ERROR! The server quit without updating PID file (/data/mysql/data//my57.pid). # 重新初始化mysql
[root@my57 data]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql
2016-06-14T02:43:04.084864Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-06-14T02:43:04.084944Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2016-06-14T02:43:04.084952Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2016-06-14T02:43:04.459619Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-06-14T02:43:04.502310Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-06-14T02:43:04.567746Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b7fa4c2e-31d9-11e6-983a-080027fff0b0.
2016-06-14T02:43:04.571117Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-06-14T02:43:04.575259Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. # 启动
[root@my57 data]# service mysql start
Starting MySQL. SUCCESS! # 利用原来的别分还原，发现还原了 但是缺少test1
mysql> source backup.sql mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec) # 利用mysqlbinlog二进制恢复test1，这时上面的开始位置和结束位置就有用了
[root@my57 backup]# mysqlbinlog --start-position=567 --stop-position=869 /backup/bin-log.000001 |mysql -uroot -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+--------------------+
6 rows in set (0.00 sec)