3.1 執(zhí)行數(shù)據(jù)庫的全備份;
點(diǎn)擊(此處)折疊或打開
創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供站前網(wǎng)站建設(shè)、站前做網(wǎng)站、站前網(wǎng)站設(shè)計(jì)、站前網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、站前企業(yè)網(wǎng)站模板建站服務(wù),十載站前做網(wǎng)站經(jīng)驗(yàn),不只是建網(wǎng)站,更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
-
[root@mysql01 backup]# mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log //運(yùn)行數(shù)據(jù)庫的完整備份
3.2 創(chuàng)建數(shù)據(jù)庫、表并插入數(shù)據(jù)
-
mysql> SELECT CURRENT_TIMESTAMP;
-
+---------------------+
-
| CURRENT_TIMESTAMP |
-
+---------------------+
-
| 2014-11-26 17:51:27 |
-
+---------------------+
-
1 row in set (0.01 sec)
-
-
mysql> show databases; //尚未創(chuàng)建數(shù)據(jù)庫BKT
-
+--------------------+
-
| Database |
-
+--------------------+
-
| information_schema |
-
| john |
-
| mysql |
-
| performance_schema |
-
+--------------------+
-
4 rows in set (0.03 sec)
-
-
mysql> Ctrl-C --
-
Aborted
-
[root@mysql02 data]# mysql -uroot -p
-
Enter password:
-
Welcome to the MySQL monitor. Commands end with ; or \\g.
-
Your MySQL connection id is 2
-
Server version: 5.5.36-log Source distribution
-
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
-
Oracle is a registered trademark of Oracle Corporation and/or its
-
affiliates. Other names may be trademarks of their respective
-
owners.
-
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
-
mysql> show master status;
-
+------------------+----------+--------------+------------------+
-
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
-
+------------------+----------+--------------+------------------+
-
| mysql-bin.000001 | 107 | | | //當(dāng)前數(shù)據(jù)庫log的pos狀態(tài)
-
+------------------+----------+--------------+------------------+
-
1 row in set (0.00 sec)
-
mysql> SELECT CURRENT_TIMESTAMP; //當(dāng)前的時(shí)間戳 當(dāng)前時(shí)間點(diǎn)A
-
+---------------------+
-
| CURRENT_TIMESTAMP |
-
+---------------------+
-
| 2014-11-26 17:54:12 |
-
+---------------------+
-
1 row in set (0.00 sec)
-
mysql> create database BKT; //創(chuàng)建數(shù)據(jù)庫BKT
-
Query OK, 1 row affected (0.01 sec)
-
mysql> create table john (id varchar(32));
-
ERROR 1046 (3D000): No database selected
-
mysql> use bkt;
-
ERROR 1049 (42000): Unknown database \'bkt\'
-
mysql> use BKT;
-
Database changed
-
mysql> create table john (id varchar(32));
-
Query OK, 0 rows affected (0.02 sec)
-
mysql> insert into john values(\'1\');
-
Query OK, 1 row affected (0.01 sec)
-
mysql> insert into john values(\'2\');
-
Query OK, 1 row affected (0.01 sec)
-
mysql> insert into john values(\'3\');
-
Query OK, 1 row affected (0.00 sec)
-
mysql> insert into john values(\'4\');
-
Query OK, 1 row affected (0.01 sec)
-
mysql> insert into john values(\'5\');
-
Query OK, 1 row affected (0.01 sec)
-
mysql> SELECT CURRENT_TIMESTAMP; //插入5條數(shù)據(jù)后數(shù)據(jù)庫的時(shí)間點(diǎn)B,記錄該點(diǎn)便于數(shù)據(jù)庫的恢復(fù)
-
+---------------------+
-
| CURRENT_TIMESTAMP |
-
+---------------------+
-
| 2014-11-26 17:55:53 |
-
+---------------------+
-
1 row in set (0.00 sec)
-
-
mysql> show master status;
-
+------------------+----------+--------------+------------------+
-
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
-
+------------------+----------+--------------+------------------+
-
| mysql-bin.000001 | 1204 | | | //當(dāng)前binlog的pos位置
-
+------------------+----------+--------------+------------------+
-
1 row in set (0.00 sec)
3.3 設(shè)置時(shí)間點(diǎn)C的測(cè)試
-
mysql> insert into john values(\'6\');
-
Query OK, 1 row affected (0.02 sec)
-
mysql> insert into john values(\'7\');
-
Query OK, 1 row affected (0.01 sec)
-
mysql> insert into john values(\'8\');
-
Query OK, 1 row affected (0.01 sec)
-
mysql> insert into john values(\'9\');
-
Query OK, 1 row affected (0.01 sec)
-
mysql> insert into john values(\'10\');
-
Query OK, 1 row affected (0.03 sec)
-
mysql> show master status;
-
+------------------+----------+--------------+------------------+
-
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
-
+------------------+----------+--------------+------------------+
-
| mysql-bin.000001 | 2125 | | |
-
+------------------+----------+--------------+------------------+
-
1 row in set (0.00 sec)
-
mysql> SELECT CURRENT_TIMESTAMP;
-
+---------------------+
-
| CURRENT_TIMESTAMP |
-
+---------------------+
-
| 2014-11-26 17:58:08 |
-
+---------------------+
-
1 row in set (0.00 sec)
3.4 以上的操作完成之后,便可以執(zhí)行數(shù)據(jù)庫的恢復(fù)測(cè)試
-
[root@mysql02 data]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back
-
MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26]
-
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
-
mysqlbackup: INFO: Starting with following command line ...
-
mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql
-
--backup-dir=/backup/ copy-back
-
mysqlbackup: INFO:
-
IMPORTANT: Please check that mysqlbackup run completes successfully.
-
At the end of a successful \'copy-back\' run mysqlbackup
-
prints \"mysqlbackup completed OK!\".
-
141126 17:59:58 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-26.17-59-58_copy_back.log
-
--------------------------------------------------------------------
-
Server Repository Options:
-
--------------------------------------------------------------------
-
datadir = /data/mysql
-
innodb_data_home_dir = /data/mysql
-
innodb_data_file_path = ibdata1:10M:autoextend
-
innodb_log_group_home_dir = /data/mysql/
-
innodb_log_files_in_group = 2
-
innodb_log_file_size = 5242880
-
innodb_page_size = Null
-
innodb_checksum_algorithm = none
-
--------------------------------------------------------------------
-
Backup Config Options:
-
--------------------------------------------------------------------
-
datadir = /backup/datadir
-
innodb_data_home_dir = /backup/datadir
-
innodb_data_file_path = ibdata1:10M:autoextend
-
innodb_log_group_home_dir = /backup/datadir
-
innodb_log_files_in_group = 2
-
innodb_log_file_size = 5242880
-
innodb_page_size = 16384
-
innodb_checksum_algorithm = none
-
mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
-
141126 17:59:58 mysqlbackup: INFO: Copy-back operation starts with following threads
-
1 read-threads 1 write-threads
-
mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin.
-
Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible.
-
141126 17:59:58 mysqlbackup: INFO: Copying /backup/datadir/ibdata1.
-
mysqlbackup: Progress in MB: 200 400 600
-
141126 18:00:22 mysqlbackup: INFO: Copying the database directory \'john\'
-
141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'mysql\'
-
141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'performance_schema\'
-
141126 18:00:23 mysqlbackup: INFO: Completing the copy of all non-innodb files.
-
141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile0\'
-
141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile1\'
-
141126 18:00:24 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql
-
141126 18:00:24 mysqlbackup: INFO: Copy-back operation completed successfully.
-
141126 18:00:24 mysqlbackup: INFO: Finished copying backup files to \'/data/mysql\'
-
mysqlbackup completed //數(shù)據(jù)庫恢復(fù)完成
授權(quán)并打開數(shù)據(jù)庫
-
[root@mysql02 data]# chmod -R 777 mysql //需要授權(quán)后才能打開
-
[root@mysql02 data]# cd mysql
-
[root@mysql02 mysql]# ll
-
總用量 733220
-
-rwxrwxrwx. 1 root root 305 11月 26 18:00 backup_variables.txt
-
-rwxrwxrwx. 1 root root 740294656 11月 26 18:00 ibdata1
-
-rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile0
-
-rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile1
-
drwxrwxrwx. 2 root root 4096 11月 26 18:00 john
-
drwxrwxrwx. 2 root root 4096 11月 26 18:00 mysql
-
drwxrwxrwx. 2 root root 4096 11月 26 18:00 performance_schema
-
-rwxrwxrwx. 1 root root 8488 11月 26 18:00 server-all.cnf
-
-rwxrwxrwx. 1 root root 1815 11月 26 18:00 server-my.cnf //沒有BKT數(shù)據(jù)庫
-
[root@mysql02 mysql]# service mysqld start //啟動(dòng)數(shù)據(jù)庫
3.5 進(jìn)行數(shù)據(jù)庫的恢復(fù)到時(shí)間點(diǎn)B
-
[root@mysql02 mysql2]# pwd //備份的時(shí)候,需要備份binlog日志,之前的binlog目錄為/data/mysql2
-
/data/mysql2
-
[root@mysql02 mysql2]# mysqlbinlog --start-position=107 --stop-position=1203 mysql-bin.000001| mysql -uroot -p //根據(jù)post的位置進(jìn)行恢復(fù),當(dāng)前的pos位置為107,恢復(fù)到pos位置到1203
-
Enter password:
-
[root@mysql02 mysql2]# mysql -uroot -p
-
Enter password:
-
Welcome to the MySQL monitor. Commands end with ; or \\g.
-
Your MySQL connection id is 3
-
Server version: 5.5.36-log Source distribution
-
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
-
Oracle is a registered trademark of Oracle Corporation and/or its
-
affiliates. Other names may be trademarks of their respective
-
owners.
-
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
-
mysql> show databases;
-
+--------------------+
-
| Database |
-
+--------------------+
-
| information_schema |
-
| BKT |
-
| john |
-
| mysql |
-
| performance_schema |
-
+--------------------+
-
5 rows in set (0.02 sec)
-
mysql> use BKT
-
Database changed
-
mysql> show tables;
-
+---------------+
-
| Tables_in_BKT |
-
+---------------+
-
| john |
-
+---------------+
-
1 row in set (0.00 sec)
-
mysql> select * from john;
-
+------+
-
| id |
-
+------+
-
| 1 |
-
| 2 |
-
| 3 |
-
| 4 |
-
| 5 |
-
+------+
-
5 rows in set (0.01 sec) //查看數(shù)據(jù)庫恢復(fù)成功
3.6 恢復(fù)數(shù)據(jù)庫到時(shí)間點(diǎn)C
-
[root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\" mysql-bin.000001| mysql -uroot -p123456 //本次通過基于時(shí)間點(diǎn)的恢復(fù),恢復(fù)到時(shí)間點(diǎn)C
-
Warning: Using unique option prefix start-date instead of start-datetime is deprecated and will be removed in a future release. Please use the full name instead.
-
Warning: Using unique option prefix stop-date instead of stop-datetime is deprecated and will be removed in a future release. Please use the full name instead.
-
[root@mysql02 mysql2]# mysql -uroot -p
-
Enter password:
-
Welcome to the MySQL monitor. Commands end with ; or \\g.
-
Your MySQL connection id is 6
-
Server version: 5.5.36-log Source distribution
-
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
-
Oracle is a registered trademark of Oracle Corporation and/or its
-
affiliates. Other names may be trademarks of their respective
-
owners.
-
Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
-
mysql> show databases;
-
+--------------------+
-
| Database |
-
+--------------------+
-
| information_schema |
-
| BKT |
-
| john |
-
| mysql |
-
| performance_schema |
-
+--------------------+
-
5 rows in set (0.00 sec)
-
mysql> use BKT
-
Database changed
-
mysql> select * from john;
-
+------+
-
| id |
-
+------+
-
| 1 |
-
| 2 |
-
| 3 |
-
| 4 |
-
| 5 |
-
| 6 |
-
| 7 |
-
| 8 |
-
| 9 |
-
| 10 |
-
+------+
-
10 rows in set (0.00 sec) //經(jīng)過檢查成功恢復(fù)到時(shí)間點(diǎn)C
四、mysqlbinlog的其他總結(jié):以上是利用binlog文件進(jìn)行基于時(shí)間點(diǎn)和binlog的POS位置恢復(fù)的測(cè)試,mysqlbinlog的使用還有很多功能,運(yùn)行mysqlbinlog --help可以查看相應(yīng)參數(shù);
4.1 查看binlog的內(nèi)容:[root@mysql02 mysql2]# mysqlbinlog mysql-bin.000001
4.2 mysqlbinlog的其他常用參數(shù):
-h 根據(jù)數(shù)據(jù)庫的IP
-P 根據(jù)數(shù)據(jù)庫所占用的端口來分
-server-id 根據(jù)數(shù)據(jù)庫serverid來還原(在集群中很有用)
-d 根據(jù)數(shù)據(jù)庫名稱
例如: [root@mysql02 mysql2]# mysqlbinlog-d BKT mysql-bin.000001//還原BKT數(shù)據(jù)庫的信息
參數(shù)的組合使用:
-
[root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\" -d BKT -h 127.0.0.1 /var/lib/mysql/mysql-bin.000001 |mysql -u root -p
-
#如果有多個(gè)binlog文件,用逗號(hào)隔開;
4.4 恢復(fù)是一件很重要的事情,如果不知道具體要恢復(fù)的時(shí)間點(diǎn),請(qǐng)把binlog文件先轉(zhuǎn)換成文本文件,詳細(xì)查看完相應(yīng)的內(nèi)容再進(jìn)行恢復(fù);
[root@mysql02 mysql2]# mysqlbinlogmysql-bin.000001 > /tmp/00001.sql
總結(jié):備份有時(shí)候永遠(yuǎn)都用不上,但是你永遠(yuǎn)也不知道什么時(shí)候會(huì)用上,正所謂養(yǎng)兵千日用兵一時(shí),作為一個(gè)合格的DBA有個(gè)可用的備份,就可以做到胸有成竹;