Use mysqldump to back up mariadb

Mysqldump backup description mysqldump is a utility used by mysql to store databases. It mainly generates an SQL script, including the CREATETABLEINSERT command required to re-create a database from scratch, and is suitable for backing up databases with a small amount of data. Advantages: simple backup and easy recovery. Disadvantages of backup: schema and data storage

Mysqldump backup description 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 storage

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: MariadbSerious dependence on data recovery and bin-logLog, so to prevent data files and bin-log caused by disk failureThe file is lost together, so it is best to set the bin-logLogs are stored 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.cnflog-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-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

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`.sqlEnterpassword: [root@MariaDB~]# ll /backup/total8-rw-r--r--1 root root 7950 Jun 16 11:59 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 | # the position 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: 1End_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 out 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;DatabasechangedMariaDB[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 in Mariadb.Command Line import, ifCommand Line imports record binary logs.

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`.sqlEnter 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.sqldrwxr-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 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 |+------+

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. 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 ||1000 ||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 stopMySQL server PID file could 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-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.errdrwx------ 2 mysql root   4096 Jun 16 01:22 mysqldrwx------ 2 mysql mysql  4096 Jun 16 01:22 performance_schemadrwx------ 2 mysql root   4096 Jun 16 01:22 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
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.
[Root @ MariaDB ~] # Mysql-u root-p
After the import is complete, the database returns to the status before the fault.
MariaDB [hellodb]> select * from tb1;+------+| id   |+------+|    1 ||    2 ||    3 ||   23 || 1000 || 9000 |+------+6 rows in set (0.00 sec)