Use mysqldump to back up MariaDB

Use mysqldump to back up MariaDB

Introduction to mysqldump backup
Mysqldump is a utility used by mysql to store databases. It mainly generates an SQL script, including the CREATE TABLE INSERT command required to re-CREATE a database from scratch. It is suitable for backing up databases with a small amount of data.
Advantages: simple backup and easy recovery.
Disadvantages of backup: schema and data are stored together, with huge SQL statements and a single huge backup file (the backup database and table are all in one file ).

Mysqldump: a mysql client command that connects to mysqld through the mysql protocol for Database Backup
Syntax format of the command:
Mysqldump [OPTIONS] database [tables]: backs up one or more tables specified by a single database or database.
Mysqldump [OPTIONS] -- databases [OPTIONS] DB1 [DB2 DB3. ..]: Back up one or more databases
Mysqldump [OPTIONS] -- all-databases [OPTIONS]: Back up all databases
Common options:
-A, -- all-databases # Back up all databases
InnoDB:
-- Single-transaction: Start a large single transaction to implement backup
-B, -- databases db_name1 db_name2...: Back up the specified database
-C, -- compress: Compressed transmission;
-X, -- lock-all-tables: lock all tables
-L, -- lock-tables: lock the backup table

-- Flush-logs,-F: Execute the flush logs command after locking the table;
Other options:
-E, -- events: Back up the event scheduler of the specified database;
-R, -- routines: Backup stored procedures and storage functions;
-- Triggers: Backup trigger
 
-- Master-data [= #]:
1: records the change master to statement. This statement is not commented out;
2: The record is a comment statement;

Note: Mariadb's data recovery relies heavily on bin-log logs. To prevent data files and bin-log files from being lost due to disk faults, therefore, it is best to store bin-log in shared storage.
Setting Method: Modify the Mariadb configuration file, direct the location of the log file to the path where the local network storage is attached, and restart the Mariadb service.
[Root @ MariaDB ~] # Vim/etc/my. cnf
Log-bin = "/backup/bin-log/mysql-bin.x"

Verify that the Mysql binary log file and log index file are available in the/backup/bin-log/directory after the service is restarted.
[Root @ MariaDB ~] # Ll/backup/bin-log/mysql-bin .*
-Rw ---- 1 mysql 245 Jun 16/backup/bin-log/mysql-bin.000001
-Rw ---- 1 mysql 33 Jun 16 00:00/backup/bin-log/mysql-bin.index

Backup and recovery for a single database
There is one hellodb table in the database. You need to back up the hellodb table to restore the database in time when the database fails or is deleted by mistake.
The backup solution is to completely back up the database every Sunday and incrementally back up the database from Monday to Saturday.
The backup process is as follows:
Backup on the first day: the hellodb database is fully backed up, and the table and scroll binary logs are locked during full backup.
[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 -- 1 root 7950 Jun 16 hellodb-2015-06-16. SQL

Because binary logs are rolled during full backup, some database changes are recorded in the new binary logs, it is known from the view that subsequent logs are recorded in the mysql-bin.000002.
MariaDB [(none)]> show master logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000001 | 288 |
| Mysql-bin.000002 | 245 |
+ ------------------ + ----------- +
2 rows in set (0.00 sec)

One day after the backup is complete, a table is created and some data is inserted into the table.
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 |
+ ------ +

Incremental backup on the first day: All statements for database changes on the current day will be recorded in the binary log file. You only need to scroll the binary log and back up the binary log, all statements that change the database the next day will be recorded in the 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)

Convert a binary log file to an SQL File
[Root @ MariaDB ~] # Mysqlbinlog/backup/bin-log/mysql-bin.000002>/backup/1. SQL

Operation on the next day: Continue to insert data to tb1
MariaDB [hellodb]> insert into tb1 values (21), (22), (23 );
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 21 |
| 22 |
| 23 |
+ ------ +

Fault Simulation
The hellodb database was accidentally deleted:
MariaDB [hellodb]> DROP database hellodb;

Preparations before recovery
Viewing binary logs records the third log.
MariaDB [(none)]> show master logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000001 | 288 |
| Mysql-bin.000002 | 577 |
| Mysql-bin.000003 | 533 | # record location of the Current Log
+ ------------------ + ----------- +
3 rows 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 of record
5 rows in set (0.00 sec)

ERROR: No query specified

Because the entire hellodb database was deleted by mistake, you need to use the full backup file at the beginning and the Incremental backup file on the first day to restore the hellodb database and modify the database on the first day.
Restore the modified content of the database the next day: If you directly import the binary log of the current day to the database, the database will still be deleted because the database contains the delete statement; therefore, when importing binary logs for the next day, you need to delete the DROP statement in the log.
 
Convert the binary log file of the next day into an SQL file and put it in the backup directory.
[Root @ MariaDB ~] # Mysqlbinlog/backup/bin-log/mysql-bin.000003>/backup/2. SQL

2. Open the SQL file and you can see the DROP statement. 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 # delete or comment this row

Recovery Process:
To avoid useless binary logs, you can disable binary log records before restoring them.
MariaDB [(none)]> set session SQL _log_bin = 0;
Import the first full backup data to the database
MariaDB [hellodb]> SOURCE/backup/hellodb-2015-06-16. SQL;

Check that the database has been imported, but the tb1 table does not exist.
MariaDB [(none)]> use hellodb;
Databasechanged
MariaDB [hellodb]> show tables;
+ ------------------- +
| Tables_in_hellodb |
+ ------------------- +
| Classes |
| Coc |
| Courses |
| Scores |
| Students |
| Teachers |
| Toc |
+ ------------------- +

Import the 1. SQL file of the Incremental backup on the first day.
MariaDB [hellodb]> SOURCE/backup/1. SQL;

Check the tb1 table and find that the data inserted on the first day exists.
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
+ ------ +

2. SQL file imported on the current day
MariaDB [hellodb]> SOURCE/backup/2. SQL;

Check that the data has been completely recovered
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 21 |
| 22 |
| 23 |
+ ------ +

Recovery complete. Enable recording of binary logs
MariaDB [hellodb]> set session SQL _log_bin = 1;

NOTE: If binary logs are disabled during restoration, the imported data must be imported in the Mariadb command line. If binary logs are imported in the shell command line, binary logs are recorded.

Full-Database Backup Recovery
Backup
The first full backup locks the table during Backup and rolls binary logs.
[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 -- 1 root 1980 Jun 16 00:46 1. SQL
-Rw-r -- 1 root 1957 Jun 16 00:52 2. SQL
-Rw-r -- 1 root 521774 Jun 16 01: 04ALL-2015-06-16. SQL
Drwxr-xr-x 2 mysql 4096 Jun 16 bin-log
-Rw-r -- 1 root 7950 Jun 16 00: 43hellodb-2015-06-16. SQL

Since binary logs are rolled during backup, all actions that make changes to the database after the backup are 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)

Some operations were performed on the day when the backup was completed.
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 |
+ ------ +

First-day bulk backup
All statements for database changes on the current day will be recorded in the binary log file. You only need to scroll the binary log and back up the binary log. After the binary log is rolled, all statements that change the database the next day will be recorded in the 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)

Convert the binary log file of the first day to an SQL File
[Root @ MariaDB ~] # Mysqlbinlog/backup/bin-log/mysql-bin.000004>/backup/all.1. SQL

The next day, I performed some operations on the data.
MariaDB [hellodb]> insert into tb1 values (1000), (9000 );
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 23 |
| 1, 1000 |
| 1, 9000 |
+ ------ +

Fault Simulation
Delete all files in the data directory to simulate database faults:
[Root @ MariaDB ~] # Rm-rf/mydata/data /*

You can log on to the database at this time, but the database is no longer
MariaDB [hellodb]> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
+ -------------------- +

Preparations before recovery
When a database fault is detected, the database is shut down first, but the database cannot be shut down normally. Only processes can be shut down.
[Root @ MariaDB ~] # Service mysqld stop
MySQL server PID file cocould not be found! [FAILED]
[Root @ MariaDB ~] # Killall mysqld

 
Because all the contents of the database data directory are deleted, even if a full backup file is imported, some files are missing. The solution is to reinitialize the database.
[Root @ MariaDB ~] # Cd/usr/local/mysql/
[Root @ MariaDB mysql] # scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data/

After initialization, some basic files exist.
[Root @ MariaDB mysql] # ll/mydata/data/
Total 36
-Rw ---- 1 mysql 16384 Jun 16 01: 22aria_log.00000001
-Rw ---- 1 mysql 52 Jun 16 0:22 aria_log_control
-Rw-r ----- 1 mysql root 80 Jun 16 0:18 MariaDB. err
Drwx ------ 2 mysql root 4096 Jun 16 mysql
Drwx ------ 2 mysql 4096 Jun 16 performance_schema
Drwx ------ 2 mysql root 4096 Jun 16 test

 
Convert the binary log file of the next day into an SQL File
[Root @ MariaDB ~] # Mysqlbinlog/backup/bin-log/mysql-bin.000005>/backup/all.2. SQL

Recovery Process
Start Mysql before restoration; otherwise, the backup file cannot be imported.
[Root @ MariaDB ~] # Service mysqld start

Import the full backup file on the first day. Because the data has just been initialized, the database has no password at this time.
[Root @ MariaDB ~] # Mysql </backup/ALL-2015-06-16. SQL

Log on to the database and check that all databases have been recovered, but the changes to the database on the first day have not been restored.
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)

Import the Incremental backup file on the first day
[Root @ MariaDB ~] # Mysql-u root-p </backup/all.1. SQL

The object deleted on the first day does not exist, but the content added on the second day does not exist.
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 23 |
+ ------ +
4 rows in set (0.00 sec)

Import the SQL file after the second day of binary log conversion.
1 [root @ MariaDB ~] # Mysql-u root-p </backup/all.2. SQL

After the import is complete, the database returns to the status before the fault.
MariaDB [hellodb]> select * from tb1;
+ ------ +
| Id |
+ ------ +
| 1 |
| 2 |
| 3 |
| 23 |
| 1, 1000 |
| 1, 9000 |
+ ------ +
6 rows in set (0.00 sec)

Quickly build a slave Database Based on mysqldump

Restore the backup set created by mysqldump

Mysqldump missing-q Parameter causes MySQL to be killed by oom

Install LAMP (Apache with MariaDB and PHP) in CentOS/RHEL/Scientific Linux 6)

Implementation of MariaDB Proxy read/write splitting

How to compile and install the MariaDB database in Linux

Install MariaDB database using yum in CentOS

Install MariaDB and MySQL

How to migrate MySQL 5.5 database to MariaDB 10 on Ubuntu

Install MariaDB on the Ubuntu 14.04 (Trusty) Server