Complete MySQL/MariaDB data backup and data recovery

Mariadb ssl mode

The MariaDB database management system is a branch of MySQL. It is mainly maintained by the open-source community and licensed by GPL. One of the reasons for developing this branch is that Oracle has the potential risk of shutting down the source of MySQL after purchasing MySQL. Therefore, the community uses the branch method to avoid this risk.

Data is no longer important to us. How can we ensure the security of data as much as possible? What should we do when our data is lost, therefore, data backup is too important for our data security.

Data is no longer familiar to us, but it is also most common. We are exposed to various types of data every day, and the data records our common business information, therefore, data is very important for us. If such important data is lost, will it be impossible for us to do the relevant business, this should be a very troublesome issue. How can we protect the security of our data? We need to back up our data.

How to perform Backup Recovery: backup and recovery are the top priority in the work environment. Why is backup and recovery required:

Mariadb ssl ca

1. Disaster Recovery is generally implemented, such as natural disasters.

2. audit can be performed, such as what a data is like in the past.

3. For example, whether a new data storage method in the Business architecture is accessible to the business

The purpose of backup is to recover data. If the backup data cannot be recovered when it is used, it is necessary to perform a recovery test on the backup data. In addition, we need to determine the sequence for testing.

Backup Type: there are many types

Based on whether the database server is online during Backup:

mariadb ssl replication 

Cold backup: cold backup. The server must be offline, which means that read and write operations cannot be performed. This is the safest backup method and the most unreliable one.

Warm backup: warm backup, which applies a shared lock globally and is readable only. A non-writable backup is called a warm backup.

Hot backup: hot backup, database offline, read/write operations can be performed

When InnoDB records data, a serial number is assigned to the Data. Therefore, the snapshot is automatically accelerated Based on the MVCC (Multi-version Concurrency Control) mechanism during Backup, each time a transaction is started, a quick snapshot of the current set is created, and then each serial number is recorded for it based on the MVCC mechanism. During the backup, only the data before the serial number or serial number is backed up, future operations will not be backed up. If the isolation level of the transaction is not very high, it will not affect the read/write operations of the transaction, the data backed up in this way must be consistent at the time point. Therefore, to complete hot backup, it is usually completed by the transaction-based storage engine.

Classify data based on the data set during Backup:

Full backup: full backup refers to the data of the entire database and the entire database of the current dataset.

Partial backup: only one copy of a table or a table is backed up. Sometimes it is necessary to back up a single table.

Based on the backup interface (back up data files directly or export data through the mysql server)

Physical backup: the backup method for directly copying (archiving) data files; the cross-platform capability is not as good as logical backup, physucal backup. It is better to use big data sets.

Logical backup: stores the files in the database as text files. The commonly used tools are mysqldump and logical backup, which is not suitable for large data volumes. Recovery is slow and takes up a lot of space

Whether to back up the entire data or only the changed data during Backup:

Full backup: full backup is the same as full backup in the backup data set. It is also used to back up the entire database.

Incremental backup: incremental backup. incremental backup is used to back up the changed data after the last full backup. For example, backup is performed on Monday, backup is performed on Tuesday, and backup is performed on Wednesday, in this way, Incremental backup is accumulated every day. Saves space.

Differential backup: differential backup. For example, if a backup is performed on Monday, the backup is performed on Monday and Tuesday by Tuesday, and the backup is performed on Monday, Tuesday and Wednesday by Wednesday, this is called differential backup. Recovery is easier.

Backup policy: Considerations

1. Select the backup mode and the method based on the needs of our production environment;

2. It is reasonable to select the minimum access time for backup;

3. recovery costs: Recovery duration;

4. Backup Cost: Considering the lock time, backup duration, and backup load;

Backup object: What do we need to back up for backup?

1. Backing up data in the database is the most important;

2. MySQL configuration file, which is also the backup object

3. MySQL Code also needs to be backed up: stored procedures, storage functions, and triggers

4. OS-related configuration files, such as the crontab configuration plan and related scripts

5. In the scenario of master-slave replication, copy-related information should also be backed up.

6. To ensure data reliability, binary log files must be backed up.

Common backup tools:

Mysqldump: the logical backup tool is a single-thread backup tool. Therefore, when a backup is performed on a server, it can only start one CPU to start one thread for backup, with poor performance.

For InnoDB hot backup, for MyISAM, only warm backup and for Aria are allowed, and the backup and recovery process is slow;

Mysqldumper: multi-threaded mysqldump, which can be performed on multiple databases or tables at the same time to improve performance;

Mysqldump and mysqldumper are both logical backup tools. Generally, it is difficult to implement differential or Incremental backup. Only full backup can be performed, but partial backup can be performed, for example, you can back up only one table;

When using cold backup: cp, which needs to be backed up based on the lvm-snapshot logical volume snapshot, is similar to the hot backup tool, because the global lock is requested first, and then the snapshot is created, after creating a snapshot, release the global lock. Then, use tools such as cp and tar for physical backup (because the copied source data file), so the data backup and recovery speed is fast, disadvantages: it is difficult to implement Incremental backup, and it takes some time to request a global lock, especially on busy servers.

SELECT clause into outfile '/path/to/somefile'; Save the selected clause to a file. It is a part of backup tool and does not define the backup relationship, only the data in the table is backed up, but this is also a logical backup tool, which is faster than mysqldump and cannot implement Incremental backup.

Load adta infile '/path/from/somefile'; indicates where to read the data for restoration;

Innobase: Provides the commercial backup tool Innobackup to support Incremental backup for InnoDB hot backup. However, MyISAM does not support Incremental backup and can only implement full backup, which is a physical backup, fast.

Xtrabackup: an open-source backup tool provided by Percona, featuring physical backup and high speed;

Mysqlhostcopy: almost cold standby, bragging tool, not applicable;

Mysqldump: A common backup tool and a logical backup tool for small data backup. Generally, small data under 5 GB is backed up. You can use text for secondary processing; equivalent to MySQL client Tool

Format: mysqldump [options] [db_name [tbl_name...]

This tool is used to back up a single database: mysqldump [option] db_name

If the target database does not exist during restoration, you must manually create

-- All-databases: Back up all databases -- databases db1 db2: Back up multiple specified databases, separated by Spaces -- lock-all-tables: request to lock all tables before backing up, generally, only MySQL is used for temperature backup, but InnoDB and Aria can also be used for temperature backup. -- Events: Backup event scheduler code -- routines: Backup stored procedure and storage function -- triggers: Backup trigger -- flush-logs: Rolling logs before and after the request is locked, when rolling logs during backup, you must apply the lock manually -- master-data = [0 | 1 | 2] Synchronization position mark during replication. 0 indicates no record, 1. Record the carnge master statement. 2. Record is annotated as the change master statement -- single-transaction: enables hot backup for the InnoDB Storage engine and starts a single large transaction, based on MVCC (Multi-version Concurrency Control) to implement hot standby for the InnoDB Storage engine, it will automatically lock and should not be used together with -- lock-all-tables;

# Mysqldump -- databases hellodb -- lock-all-tables>/tmp/hellodb. SQL: Back up the database and request the table lock. All write operations backed up here will be blocked. This method is not ideal.

# Mysqldump -- databases hellodb -- lock-all-tables -- flush-logs>/tmp/hellodb. SQL

# Mysqldump -- databases hellodb -- single-transaction -- flush-logs>/tmp/hellodb. SQL: ensure that the storage engines of all tables in this database are InnoDB and use -- single-transaction for hot backup.

View binary logs in different time periods and redirect them to a file:

# Mysqlbinlog -- start-porition = 367 -- stop-position = 669 master-bin.000005>/tmp/hellodb. inc. SQL

Define a start point and an end point to redirect the binary file to a file and restore it;

When using mysqldump for backup:

Request lock: -- lock-all-tables -- single-transaction for innodb hot backup;

Rolling log: -- flush-logs

Select the database to be backed up: -- databases

Specify the binary log file and location: -- master-data = 2

Note: locks are required before backup. We recommend that you disable binary logs and other user connections during restoration.

Backup policy: mysqldump + binary log file:

Recovery: Full backup + events in various binary log files to the moment. events occurred during the recovery process are not required to be written to the binary log file; therefore, you must temporarily disable binary log files during recovery:

MariaDB [(hellodb)]> set session SQL _log_bin = 0: temporarily disable binary log files

MariaDB [(hellodb)]> source/tmp/hellodb. SQL; read the backup file directly on the Database Command Line

The MySQL configuration file and the MySQL-related OS configuration file should be backed up directly after each modification.

Lvm-snapshot: LVM-based snapshot Backup

1. There is an advance in snapshot-based backup. Transaction logs and data files must be on the same volume;

2. Before creating a snapshot volume, request the MySQL global lock and release the lock after the snapshot is created;

3. Perform a log scroll when the global lock is requested. Mark, time record, and binary log file and location mark (manual) are required );

Note:

1. Data and backup are stored on different disk devices, which is ideal for backup storage on different machines or in different regions;

2. the backup data should be regularly restored for testing;

3. Make a full backup immediately after each disaster recovery

4. Customize backup policies for data volumes of different scales or levels;

5. Binary logs should be stored on different disks from data files, and binary log files should be backed up periodically;

Steps to restore data from a backup:

1. Stop the MySQL server;

2. Record server configuration and file permissions;

3. migrate data from the backup to the MySQL data directory. The execution method depends on the tool;

4. Change the configuration and file permissions;

5. Restart the server by limiting access to the module. The -- skip-network option of mysqld can skip the network function;

Method: edit the my. cnf configuration file and add the following items:

Skip-networking

Socket =/tmp/mysql-recovery.sock

6. Load the logical backup (if any) and then check and replay the binary log.

7. Check the restored data;

8. Restart the server in full access mode;

Use mysqldump to back up data and use binary logs to restore data. Here we take the database hellodb on the current system as an example:

Step 1: first make a full backup of the hellodb database. Of course, if the database has a large amount of data, for example, if it is larger than 10 Gb, we do not recommend using the mysqldump tool for backup, here we only want to explain the problem:

#-U indicates the user,-p indicates the user password, -- databases indicates the database to be backed up, and -- lock-all-tables indicates the table lock requested during backup, -- flush-logs rolling log

[Root @ node0 ~] # Mysqldump-uroot-plinux -- databases hellodb -- lock-all-tables -- flush-logs -- master-data = 2>/root/hellodb. SQL

[Root @ node0 ~] # Ll-h hellodb. SQL-rw-r -- 1 root 7.8 K May 2 hellodb. SQL

[Root @ node0 ~] #

Step 2: Modify or create some tables or data in the database hellodb, so that the data backed up earlier is different from the data in the existing database. After that, data is restored using binary logs:

MariaDB [hellodb]> create table newtable;

ERROR 1113 (42000): A table must have at least 1 column

MariaDB [hellodb]> create table newtable (Name CHAR (20 ));

Query OK, 0 rows affected (1.11 sec)

MariaDB [hellodb]> insert into newtable values ('Tom '), ('Jerry'), ('Lucy ');

Query OK, 3 rows affected (0.13 sec)

Records: 3 Duplicates: 0 Warnings: 0

Step 3: delete the hellodb database:

MariaDB [hellodb]> create table newtable;

ERROR 1113 (42000): A table must have at least 1 column

MariaDB [hellodb]> create table newtable (Name CHAR (20 ));

Query OK, 0 rows affected (1.11 sec)

MariaDB [hellodb]> insert into newtable values ('Tom '), ('Jerry'), ('Lucy ');

Query OK, 3 rows affected (0.13 sec)

Records: 3 Duplicates: 0 Warnings: 0

Step 4: view our binary log file and save the modified and created binary log information so that we can restore the new data, we performed log scrolling during backup, so we can view the last log information;

[Root @ node0 ~] # Cd/mydata/data/[root @ node0 data] # mysqlbinlog -- start-position = 403 -- stop-position = 663 mysql-bin.000004>/tmp/hellodb. binlog. SQL

Step 5: recover data, go to the mysql command line mode, and turn off the binary log. When restoring data, you do not need to record the recovery information to the binary log;

# Disable the binary log file of the current full talk

MariaDB [(none)]> SET session SQL _log_bin = 0;

MariaDB [(none)]> source/root/hellodb. SQL

MariaDB [hellodb]> show master status; check whether the binary file has been changed twice; + ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000004 | 815 |

+ ------------------ + ---------- + -------------- + -------------------- + MariaDB [hellodb]> show tables;

MariaDB [hellodb]> source/tmp/hellodb. binlog. SQL

MariaDB [hellodb]> SELECT * FROM newtable; + ------- +

| Name |

+ ------- +

| Tom |

| Jerry |

| Lucy |

+ ------- + 3 rows in set (0.00 sec)

MariaDB [hellodb]> SET session SQL _log_bin = 1; bring binary online

OK. The data is completely restored Based on the mysqldump + binary log. The binary log can help us recover data that may not be urgently backed up, this is important to our data security.

Lvm-snapshot: Back up a snapshot based on a logical volume snapshot. Note that a snapshot is not a backup, but at most a snapshot is used for data backup. the snapshot itself is not a backup;

After a snapshot is created, once the data in the source volume needs to be modified, copy the data in the source volume to the snapshot volume, all the modified data is accessed through snapshot storage, and those that are not modified are also accessed through the source volume. Snapshots only store changed data;

1. It is only an access path for the source volume;

2. When a new snapshot volume is created, there is no data in the snapshot volume. It only points all data to the source volume, so the accessed data comes from the source volume;

3. Once the data in the source volume needs to be modified, You need to copy the data to the snapshot volume before the change, therefore, some of the data that will be accessed through the snapshot volume will come from the snapshot volume and one from the source volume. The modified data comes from the snapshot volume and the unmodified data comes from the source volume; the Snapshot volume is only an access path that provides us with a time consistency file;

Notes for creating snapshot volumes:

1. During snapshot-based backup, transaction logs must be on the same volume as data files;

2. Before creating a snapshot volume, request the mysql global lock and release the lock after the snapshot is created;

3. A log scroll is performed after the global lock is requested to record the time point. The binary log file and position mark is the master-data;

Step 1: First, check whether your data directory is a logical volume. mount: if it is not a logical volume, do not play it like this;

[Root @ node0 ~] # Mount/dev/mapper/vg0-root on/type ext4 (rw)

Proc on/proc type proc (rw)

Sysfs on/sys type sysfs (rw)

Devpts on/dev/pts type devpts (rw, gid = 5, mode = 620)

Tmpfs on/dev/shm type tmpfs (rw)/dev/sda1 on/boot type ext4 (rw)/dev/mapper/vg0-usr on/usr type ext4 (rw) /dev/mapper/vg0-var on/var type ext4 (rw)/dev/mapper/mydata-mysqldata on/mydata/data type ext4 (rw, noatime)

None on/proc/sys/fs/binfmt_misc type binfmt_misc (rw)

[Root @ node0 ~] # Lvs

Lv vg Attr LSize Pool Origin Data % Move Log Cpy % Sync Convert

Mysqldata mydata-wi-ao ---- 8.00g

Root vg0-wi-ao ---- 20.00g

Swap vg0-wi-ao ---- 2.00g

Usr vg0-wi-ao ---- 10.00g

Var vg0-wi-ao ---- 20.00g

[Root @ node0 ~] # Vgs

VG # PV # LV # SN Attr VSize VFree

Mydata 1 1 0 wz -- n-10.00g 2.00g

Vg0 1 4 0 wz -- n-59.99g 7.99g

[Root @ node0 ~] #

Step 2: Request lock. If the lock is in the production environment, we can use the script to complete the lock, because the request time can be faster;

MariaDB [(none)]> flush tables with read lock; # request LOCK

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush logs; # scroll down the log

Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> show master status; # view log position information + -------------------- + ------------ + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000002 | 365 |

+ ------------------ + ---------- + -------------- + -------------------- + 1 row in set (0.04 sec)

MariaDB [(none)]>

Step 3: Create a snapshot volume within the valid size range of your logical volume, which cannot exceed the logical volume size:

[Root @ node0 ~] # Mkdir backup

[Root @ node0 ~] # Mysql-e 'show master status' + ------------------ + ---------- + -------------- + -------------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000002 | 365 |

+ ------------------ + ---------- + -------------- + -------------------- + [Root @ node0 ~] # Mysql-e 'show master status'>/root/backup/binlog. pos

[Root @ node0 ~] # Lvcreate-L 100 M-s-n mydata-snap-p r/dev/mydata/mysqldata

Logical volume "mydata-snap" created

[Root @ node0 ~] # Lvs

Lv vg Attr LSize Pool Origin Data % Move Log Cpy % Sync Convert

Mydata-snap mydata sri-a-s --- 100.00 m mysqldata 0.01

Mysqldata mydata owi-aos --- 8.00g

Root vg0-wi-ao ---- 20.00g

Swap vg0-wi-ao ---- 2.00g

Usr vg0-wi-ao ---- 10.00g

Var vg0-wi-ao ---- 20.00g

[Root @ node0 ~] #

Step 4: Release the lock in the mysql command line:

MariaDB [(none)]> unlock tables;

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>

Step 5: Mount the snapshot volume and back up the snapshot volume. You only need to copy a copy of the data directory. In fact, you can back up the volume as needed. If you are not sure, you can copy all the data, for security:

[Root @ node0 ~] # Mount/dev/mydata-snap/data-o ro

[Root @ node0 ~] # Cd/data/[root @ node0 data] # ls aria_log.00000001 ibdata1 multi-master.info mysql-bin.000002 node0.tanxw.com. pid

Aria_log_control ib_logfile0 mysql mysql-bin.index test

Hellodb ib_logfile1 mysql-bin.000001 node0.tanxw.com. err

[Root @ node0 data] #

Step 6: Make a real backup. You only need to copy the data directly. After the backup, you can unmount the snapshot volume, you can delete the snapshot volume if you do not need it:

[Root @ node0 data] # cp-a/data // root/backup/data-2014-05-02 #-a archive and copy data

[Root @ node0 data] # cd/root/backup/data-2014-05-02 [root @ node0 data-2014-05-02] # ls aria_log.00000001 ibdata1 multi-master.info mysql-bin.000002 node1.tanxw.com. pid

Aria_log_control ib_logfile0 mysql mysql-bin.index test

Hellodb ib_logfile1 mysql-bin.000001 node1.tanxw.com. err

[Root @ node0 data-2014-05-02] # pwd/root/backup/data-2014-05-02 [root @ node0 data-2014-05-02] # umount /data/[root @ node0 ~] # Lvremove/dev/mydata-snap # delete a snapshot

Do you really want to remove active logical volume mydata-snap? [Y/n]: y

Logical volume "mydata-snap" successfully removed

[Root @ node0 ~] #

Note: if data is modified at the moment of backup, the data backed up will not save the data modified after the snapshot;

End:

We all know the importance of data backup and recovery. Of course, backup is also one of the most important measures to protect data security, therefore, mastering data backup and recovery is also crucial for our work.