萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql cp復制和mysqldump備份測試分析
mysql cp復制和mysqldump備份測試分析
備份策略
針對不同的場景下, 我們應該制定不同的備份策略對數據庫進行備份, 一般情況下, 備份策略一般為以下三種：
直接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)
