There are two types of backups called
1. Logical backups
2. Physical backups
1. Logical backups
Logical backups consist of the SQL statements necessary to restore the data, such as CREATE DATABASE, CREATE TABLE andINSERT.
2. Physical backups
Physical backups are performed by copying the individual data files or directories.
Logical vs Physical backups
The main differences are as follows:
- logical backups are more flexible, as the data can be restored on other hardware configurations, MariaDB versions or even on another DBMS, while physical backups cannot be imported on significantly different hardware, a different DBMS, or potentially even a different MariaDB version.
- logical backups can be performed at the level of database and table, while physical databases are the level of directories and files. In the MyISAM storage engine, each table has an equivalent set of files, while in the InnoDBstorage engine, by default, a number of tables are stored in the same file, in which case it is not possible to backup by table.
- logical backups are larger in size than the equivalent physical backup.
- logical backups takes more time to both backup and restore than the equivalent physical backup.
- log files and configuration files are not part of a logical backup
Backup tools
mysqldump
mysqldump performs a logical backup. It is the most flexible way to perform a backup and restore, and a good choice when the data size is relatively small.
For large datasets, the backup file can be large, and the restore time lengthy.
mysqldump dumps the data into SQL format (it can also dump into other formats, such as CSV or XML) which can then easily be imported into another database. The data can be imported into other versions of MariaDB, MySQL, or even another DBMS entirely, assuming there are no version or DBMS-specific statements in the dump.
Examples
A common use of
mysqldump is for making a backup of an entire database:shell> mysqldump db_name > backup-file.sql
You can load the dump file back into the server like this:
shell> mysql db_name < backup-file.sql
Or like this:
shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
mysqldump is also very useful for populating databases by copying data from one MariaDB server to another:shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
It is possible to dump several databases with one command:
shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
To dump all databases, use the
--all-databases option:shell> mysqldump --all-databases > all_databases.sql
For InnoDB tables,
mysqldump provides a way of making an online backup:shell> mysqldump --all-databases --single-transaction all_databases.sql
