In MariaDB both logical and physical backups are possible. Logical backups are made up of SQL statements that actually performed CREATE, SELECT, UPDATE, INSERT, etc. operations on the database and Physical backups are copying the data and index files that store the table information including structure and data. While logical backups are highly portable, physical backups are less portable and compatible between not only different hardware, OS, etc. but also between different storage engines of the same DBMS. This is due to the difference in file structure used by different intra-database storage engines and between different OS, hardware, drivers etc.
Performing logical backup using mysqldump tool
mysqldump is the favorite DBA tool for creating quick backups in different output formats such as SQL, CSV, XML, etc. Triggers are dumped as part of the table structure while stored procedures, events and views can be included in the dump file (output backup file) using mysqldump options like –routines and –events.
Typically the username and password are specified as options along with mysqldump:
shell> mysqldump –u user_name –p[password el_id='599c0ffea9f20'] db_name [tbl_name ...] > backup-file.sql
To compress the dump file using pipe:
shell> mysqldump –u user –p[password el_id='599c0ffea9f5d'] dbname | gzip -9 > backupfile.sql.gz
To decompress before restore, use gunzip backupfile.sql.gz.
Below are the basic usages of mysqldump to backup tables, single database and all databases:
shell> mysqldump [options el_id='599c0ffeaa64a'] db_name [tbl_name ...] > backup-file.sql
shell> mysqldump [options el_id='599c0ffeaa696'] --databases db_name ... > backup-file.sql
shell> mysqldump [options el_id='599c0ffeaa6d0'] --all-databases > backup-file.sql
To restore a dump file into a database:
shell> mysql -u user_name –p[password] db_name < backup-file.sql
Note: a CREATE DATABASE statement will be included only if –databases option is included.
Below are some default options that are needed to perform a valid backup.
-add-drop-table: drop table if exists before a CREATE TABLE statement.
--add-locks: add table lock statements with INSERT statements so that during restore, INSERTS become fast.
--create-options: to include all options related to table creation with CREATE TABLE statements.
--disable-keys: Keys are not created during CREATE TABLE and key checks are not performed during INSERTs (non-unique indexes only) to speed up INSERTs. After completing all INSERTs, indexes (keys) are created.
--extended-insert: to use multiple rows in one INSERT statement to speed up INSERTs and to reduce dump file size.
--lock-tables: to lock all tables before creating the INSERT statements to create the table dumps.
--quick: to speed up dumping on larger tables. Tells mysqldump to read and dump one row at a time rather than dumping the entire row set, reading from memory and placing in buffer prior to insertion.
--set-charset: to use the default charset for the tables.
The above options are grouped in the –opt option and this group is the default option set with a mysqldump command if no other options are specified. These options and the option group are set as default to make the dumping faster and to create a dump file that can be restored quickly.
These default options can be negated or skipped by specifying corresponding skip options.
Examples:
--skip-add-locks option disables --add-locks option.
--skip-create-option disables --create-options option.
--skip-disable-keys option disables --disable-keys
To apply the default option group –opt with some individual group options skipped, use appropriate skip options along with –opt.
Example: to skip extended inserts and quick options.
shell> mysqldump --opt –skip-extended-insert --skip-quick db_name > backup-file.sql
The above statement can be replaced by:
shell> mysqldump --skip-extended-insert --skip-quick db_name > backup-file.sql
Since –opt is ON by default.
To skip the entire –opt:
shell> mysqldump --skip-opt db_name > backup-file.sql
To skip –opt, but at the same time use some of the default options, for example:
--add-drop-table and --disable-keys, use:
shell> mysqldump --skip-opt --add-drop-table --disable-keys db_name > backup-file.sql
The order of parameters is important in selective enabling/disabling of options. In the above statement, instead of specifying–skip-opt in the beginning, if it is included at the end, it will skip all –opt group options including the –add-drop-table and –disable-keys, that precedes the skip opt.
Another option group is –compact that includes the below options:
--skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys and --skip-set-charset
This option group disables structure comments, header and footer statements like DROP TABLE IF EXISTS, enables key building and checks, add table locks etc. These are intended for a debugging operation to analyze and troubleshoot and database.
Below are some options that have importance in specific scenarios:
Debugging
--debug-check: before exiting, mysqldump will check memory and prints file usage stats.
--debug-info: before exiting, print debug information
--log-error=file_name: to log all errors and warnings encountered during the dumping operation, in the specified file name.
--log-queries: if logging is turned on, the slow queries will be recorded in the slow query log and general queries in query log.
Master Slave Replication
–master-data[=#]: this option is used to create a dump file from a master server for creating a slave server or modify an existing slave for the master server from which the dump is being taken. The binary log position and filename (binary log coordinates) needed for replication slave setup is appended to the output dump file. Setting this option to 1 will add the CHANGE MASTER statement in the dump file, so that the receiving slave will use the parameters for connecting with and replicating the master. An option value of 2 will comment out the above command. Read master-slave replication implementation article here.
shell> mysqldump --master-data=1 --all-databases > dumpfile.sql
–delete-master-logs: this option is used on a master server that has binary logging enabled. This option appends a PURGE BINARY LOGS statement to delete all binary log files older than the log file specified in the PURGE BINARY LOGS command.
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
Deletes all log files older than the file ‘log_name’ or the datetime specified. In effect, –delete-master-logs option enables –master-data to create a dump file for a slave server.
To take an old backup for a slave and replay the old changes since that backup at the master:
shell> mysqldump --all-databases --master-data=2 > all_databases.sql
Creating mysqldump backups for text file importing
–tab=directory_name: this option is used for creating tab-separated text-format (.txt) data files along with the SQL file (.sql) that holds the table’s CREATE TABLE statement in the specified directory name value. So the output will be an SQL file and a TXT file. This text file can be used by the LOAD DATA INFILE statement to dump data into a database table. You must have the FILE permission and mysqldump should be in the same server that runs mysqld and it should have file write permissions on the specified directory. By default, this option places tabs between field values and newline (\n) at the end of line, in the .txt file.
The LOAD DATA INFILE statement imports rows from a text file in to the database at high speed. This text file can be created using mysqldump by adding delimiters and line separators to the rows and fields in the dump file.
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
When LOAD DATA INFILE statement is used along with the text file that contains the rows, MariaDB identifies fields by:
- A string that terminates a field, mainly tab or space.
- (Optionally) enclosed by a char like single or double quotes.
- Escaped by chars like \t – tab or \s – space.
Individual rows or text file lines are identified by:
- Starting by a string, generally quotes.
- Terminated by string, generally newline (\n) and carriage return (\r).
To make such an INFILE, below options are to be used along with mysqldump that works with above –tab option to produce a .txt formatted data file.
--fields-terminated-by=name : used with --tab option to specify a field terminator, usually tab.
--fields-enclosed-by=name : used with --tab option to specify field enclosing string, usually space.
--fields-optionally-enclosed-by=name : same as above, but depends on --fields-terminated-by value.
--fields-escaped-by=name : used with --tab option to specify an escape character (\) for the fields, if they contain instances of tab, newline, or \ that occur within field values.
--lines-terminated-by=name : used with --tab option to specify a string as the end of line (row), generally \r\n.
Hexadecimal codes can also be used to specify the delimiters.
Example: using the hexadecimal 0x0d0 for \r\n (EOL) character as line termination markers
shell> mysqldump --tab=/tmp --fields-terminated-by=’\t’,
--fields-enclosed-by='"' --lines-terminated-by=0x0d0a database1
Transactional Table Backups
When taking backups of transactional tables like InnoDB, the option –single-transaction is used to dump and restore the operation as a single transaction to get a consistent state of the database.
shell> mysqldump --all-databases --single-transaction all_databases.sql