| |
Mysqldump Backup Introduction
mysqldump mysql is used to turn the utility database is stored. It mainly produces a SQL script that contains the command to re-create the database from scratch necessary CREATE TABLE INSERT, applied to the amount of data to back up the database.
Advantages: simple backup and restore easy.
Backup drawback: schema and data are stored together, a huge SQL statements, a single huge backup files (backup database and tables are in a single file).
mysqldump: a mysql client command to connect to mysqld by mysql protocol to implement database backup
Command syntax:
mysqldump [OPTIONS] database [tables]: back up a single database, or one or more tables specified library
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3 ...]: back up one or more libraries
mysqldump [OPTIONS] --all-databases [OPTIONS]: Back up all libraries
Common options:
-A, - All-databases # Backup all databases
InnoDB:
--single-transaction: start a large single transaction backup
-B, - Databases db_name1 db_name2 ...: backup of the specified database
-C, - Compress: compression and transmission;
-x, --lock-all-tables: Lock all tables
-l, --lock-tables: Table backup lock
--flush-logs, -F: execute flush logs command after locking the table;
Other options:
-E, - Events: backup of the specified database event scheduler;
-R, - Routines: backup storage procedures and stored functions;
--triggers: Backup Trigger
--master-data [= #]:
1: Record CHANGE MASTER TO statement; this statement is uncommented;
2: recorded as comment statements;
Special Note: Mariadb data recovery is heavily dependent on the bin-log log, so in order to prevent a disk failure data files and bin-log files are missing together, so it is best to bin-log log stored in the shared storage.
Setting method: Mariadb modify configuration files, log file location to a local mount network storage path, and then restart Mariadb the service.
[Root @ MariaDB ~] # vim /etc/my.cnf
log-bin = "/ backup / bin-log / mysql-bin.x"
Validation: After restart the service / backup / bin-log / directory will have a Mysql binary log file and log index file.
[Root @ MariaDB ~] # ll /backup/bin-log/mysql-bin.*
-rw-rw ---- 1 mysql mysql 245 Jun 16 00: 00 / backup / bin-log / mysql-bin.000001
-rw-rw ---- 1 mysql mysql 33 Jun 16 00: 00 / backup / bin-log / mysql-bin.index
Single database backup recovery
There hellodb a database table, the table needs to be done to hellodb backup, in order to achieve a database failure occurs or can promptly recover accidentally deleted the database operation.
Backup program: a database full backup every Sunday, incremental backups Monday through Saturday databases
The backup process is as follows
The first day of the backup: full backup hellodb database, and when a full backup of locked tables and the binary log rolling
[Root @ MariaDB ~] # mysqldump -B hellodb -u root -p --lock-all-tables --flush-logs --master-data = 2> /backup/hellodb-`date+%F`.sql
Enterpassword:
[Root @ MariaDB ~] # ll / backup /
total8
-rw-r - r - 1 root root 7950 Jun 16 11:59 hellodb-2015-06-16.sql
Since doing a full backup when the binary log rolling, so do certain operations on the database will record the changes to the new binary log, that subsequent log records to mysql-bin.000002 from view in.
MariaDB [(none)]> show master logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000001 | 288 |
| Mysql-bin.000002 | 245 |
+ ------------------ + ----------- +
2 rows in set (0.00 sec)
A day after the backup completes, the created a table, and the table, insert some data
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> create table tb1 (id int);
MariaDB [hellodb]> insert into tb1 values (1), (2), (3);
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
+ ------ +
The first day of incremental backup: day all changes to the database are recorded in the statement to the binary log file, you only need to scroll binary logs, the binary log can be backed up, and then scroll binary, the next day all of the database statements to change, and will be recorded into a new binary log file.
MariaDB [hellodb]> flush logs;
MariaDB [hellodb]> show master logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000001 | 288 |
| Mysql-bin.000002 | 577 |
| Mysql-bin.000003 | 245 |
+ ------------------ + ----------- +
3 rows in set (0.00 sec)
The binary log files into sql file
[Root @ MariaDB ~] # mysqlbinlog / backup / bin-log / mysql-bin.000002> /backup/1.sql
The next day Action: continue to insert data in tb1
MariaDB [hellodb]> insert into tb1 values (21), (22), (23);
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 21 |
| 22 |
| 23 |
+ ------ +
Fault simulation
hellodb database was accidentally deleted:
MariaDB [hellodb]> DROP database hellodb;
Preparing for recovery
See binary log is recorded to the third log
MariaDB [(none)]> show master logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000001 | 288 |
| Mysql-bin.000002 | 577 |
| Mysql-bin.000003 | 533 | current log recording position #
+ ------------------ + ----------- +
3rows in set (0.00 sec)
MariaDB [(none)]> show binlog events in 'mysql-bin.000003' \ G;
*************************** 5. Row ******************** *******
Log_name: mysql-bin.000005
Pos: 446
Event_type: Query
Server_id: 1
End_log_pos: 533
Info: DROP database hellodb # delete statement recorded
5rows in set (0.00 sec)
ERROR: No query specified
Since the entire hellodb database was accidentally deleted, so the full backup file need a beginning, and the first day of incremental backup files, restore the database and the first day hellodb database content modifications.
The next day the contents of the database modifications recovery: If the day directly to the binary log into the database, since the database contains a delete statement, so the database will still be removed; so the next day when importing binary log, you need to delete the log the DROP statement.
The next day will be converted into a binary log file sql file into the backup directory
[Root @ MariaDB ~] # mysqlbinlog /backup/bin-log/mysql-bin.000003> /backup/2.sql
Open the file 2.sql you can see DROP statement to delete this statement
# At446
# 15061612: 15: 22 server id 1 end_log_pos 533 Query thread_id = 20 exec_time = 0 error_code = 0
SETTIMESTAMP = 1434428122 / * * /!;
DROP database hellodb # remove or comment this line
The recovery process:
Before restoring order to avoid useless binary log, you can log off the record binary
MariaDB [(none)]> SET SESSION sql_log_bin = 0;
The first full backup data into the database
MariaDB [hellodb]> SOURCE /backup/hellodb-2015-06-16.sql;
View the database has been imported, but the table does not exist tb1
MariaDB [(none)]> use hellodb;
Databasechanged
MariaDB [hellodb]> show tables;
+ ------------------- +
| Tables_in_hellodb |
+ ------------------- +
| Classes |
| Coc |
| Courses |
| Scores |
| Students |
| Teachers |
| Toc |
+ ------------------- +
1.sql file into the first day of incremental backup.
MariaDB [hellodb]> SOURCE /backup/1.sql;
See tb1 table, find the first day of the inserted data exists
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
+ ------ +
Import day 2.sql file
MariaDB [hellodb]> SOURCE /backup/2.sql;
View the data has been fully restored
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 21 |
| 22 |
| 23 |
+ ------ +
Recovery is completed, enable the binary log record
MariaDB [hellodb]> SET SESSION sql_log_bin = 1;
Special Note: If you close the binary log recovery, import data must Mariadb command line import, if the import shell command line will still record the binary logs.
Backup the entire reservoir recovery
Backup
The first full backup, backup locked tables and the binary log rolling
[Root @ MariaDB ~] # mysqldump -A -u root -p - lock-all-tables --flush-logs --master-data = 2> / backup / ALL-`date +% F`.sql
Enter password:
[Root @ MariaDB ~] # ll / backup /
total 532
-rw-r - r-- 1 root root 1980 Jun 16 00:46 1.sql
-rw-r - r-- 1 root root 1957 Jun 16 00:52 2.sql
-rw-r - r-- 1 root root 521774 Jun 16 01: 04ALL-2015-06-16.sql
drwxr-xr-x 2 mysql mysql 4096 Jun 16 01:04 bin-log
-rw-r - r-- 1 root root 7950 Jun 16 00: 43hellodb-2015-06-16.sql
Since the time of the backup roll binary logs, so all of the database after the backup operation will produce changes recorded in the mysql-bin.000004.
MariaDB [hellodb]> show master logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000001 | 288 |
| Mysql-bin.000002 | 577 |
| Mysql-bin.000003 | 8833 |
| Mysql-bin.000004 | 245 |
+ ------------------ + ----------- +
4 rows in set (0.00 sec)
The backup is complete on the day to do some operations.
MariaDB [hellodb]> delete from tb1 where id = 21;
MariaDB [hellodb]> delete from tb1 where id = 22;
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 23 |
+ ------ +
The first day the amount of backup
Day all changes to the database are recorded in the statement to the binary log file, you only need to scroll binary logs, the binary log can be backed up, and then scroll binary, all changes to the database of the statement the next day, will be recorded new binary log file.
MariaDB [hellodb]> flush logs;
MariaDB [hellodb]> show master logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000001 | 288 |
| Mysql-bin.000002 | 577 |
| Mysql-bin.000003 | 8833 |
| Mysql-bin.000004 | 670 |
| Mysql-bin.000005 | 245 |
+ ------------------ + ----------- +
5 rows in set (0.00 sec)
The first day of the binary log file into sql file
[Root @ MariaDB ~] # mysqlbinlog /backup/bin-log/mysql-bin.000004> /backup/all.1.sql
The next day some of the operations performed on the data
MariaDB [hellodb]> insert into tb1 values (1000), (9000);
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 23 |
| 1000 |
| 9000 |
+ ------ +
Fault simulation
Delete all the files under the directory data, fault simulation database:
[Root @ MariaDB ~] # rm -rf / mydata / data / *
This time can also log database, but the database is no longer the
MariaDB [hellodb]> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
+ -------------------- +
Preparing for recovery
Discovery database failure to close the database, but the database can not be closed properly, only to shut down the process
[Root @ MariaDB ~] # service mysqld stop
MySQL server PID file could not be found! [FAILED]
[Root @ MariaDB ~] # killall mysqld
Because all the contents of the database data directory is deleted, even if it is introduced into the full backup file is missing some documents, the solution is to re-initialize the database about.
[Root @ MariaDB ~] # cd / usr / local / mysql /
[Root @ MariaDB mysql] # scripts / mysql_install_db - user = mysql --datadir = / mydata / data /
After initialization is complete, some basic file exists
[Root @ MariaDB mysql] # ll / mydata / data /
total 36
-rw-rw ---- 1 mysql mysql 16384 Jun 16 01: 22aria_log.00000001
-rw-rw ---- 1 mysql mysql 52 Jun 16 01:22 aria_log_control
-rw-r ----- 1 mysql root 80 Jun 16 01:18 MariaDB.err
drwx ------ 2 mysql root 4096 Jun 16 01:22 mysql
drwx ------ 2 mysql mysql 4096 Jun 16 01:22 performance_schema
drwx ------ 2 mysql root 4096 Jun 16 01:22 test
The second day of the binary log file into sql file
[Root @ MariaDB ~] # mysqlbinlog / backup / bin-log / mysql-bin.000005> /backup/all.2.sql
Recovery process
Before starting the recovery Mysql service, or you can not import a backup file
[Root @ MariaDB ~] # service mysqld start
Introducing the first day of full backup file, just because of the completion of the initialization data, this time the database is not password
[Root @ MariaDB ~] # mysql < /backup/ALL-2015-06-16.sql
Log in to view the database, all the databases have been restored, but the first day of the contents of the database changes or not recovery
MariaDB [(none)]> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Hellodb |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
5 rows in set (0.00 sec)
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 21 |
| 22 |
| 23 |
+ ------ +
6 rows in set (0.00 sec)
Introducing the first day of incremental backup files
[Root @ MariaDB ~] # mysql -u root -p < /backup/all.1.sql
I found the first day of the deleted file does not exist, but the next day added content or not
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 23 |
+ ------ +
4 rows in set (0.00 sec)
The next day the import binary log converted sql file.
1 [root @ MariaDB ~] # mysql -u root -p < /backup/all.2.sql
After the import is completed, the database will return to the state before failure
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 23 |
| 1000 |
| 9000 |
+ ------ +
6 rows in set (0.00 sec) |
|
|
|