mysql cp复制和mysqldump备份测试
本文来自我的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)
