Working with SQL, MySQL, MariaDB, mysqldump backups

Creating a backup (backup, dump)

Backup of one DATABASE 
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql 
or create a backup copy of all databases. 
mysqldump -u root -p password --opt >/tmp/alldatabases.sql

Create a backup copy of the database structure without the data itself. 
mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

If only one or more tables need to be dumped. 
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

Create a backup and archive it immediately. 
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Creating a backup with an indication of the creation date. 
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`  
 

Restoring a database or table from a backup

mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

To fill the backup archive to the database. 
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE 
or zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE  
or 7z x -so /path/to/outputfile.7z | mysql -u USER -pPASSWORD DATABASE

Creating a new database 
mysqladmin -u USER -pPASSWORD create NEWDATABASE

It is convenient to use backup with additional options -Q -c -e, i.e. 
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql, where:

• -Q - Wraps names with backquotes
• -c - Makes a full insert, including column names
• -e - Makes an extended insert. The final file is smaller and it is made a little faster

To view the list of databases, you can use the command: 
mysqlshow -u USER -pPASSWORD

You can also view the list of database tables: 
mysqlshow -u USER -pPASSWORD DATABASE

For InnoDB tables, you need to add single-transaction, this guarantees the integrity of the backup data. 
This is relevant for MyISAM tables, because they support transactionality.