Using mysqldump

mysqldump is the default backup method that comes with MariaDB. It is an old and popular method. Here is an example of a classical dump with some options:

> mysqldump -uroot -p -P3306 --opt --routines --triggers --events --single-transaction --master-data=2 -A > alldb.sql

Here is the explanation:

• -u: This is the username.

• -p: This is the password (leave it empty if you want to be prompted).

• -P: This is the port number.

• --opt: This is an all-in-one option. It includes the following:

  • --add-drop-table: This will add the drop queries before creating a new table

  • --add-locks: This gives faster insertions on restore

  • --create-options: This adds all MariaDB options in the create statement

  • --disable-keys: This speeds up the restore because indexes are created after and not during import

  • --extended-insert: This uses multirow inserts to speed up the import

  • --lock-tables: This locks tables before dumping them

  • --quick: This is used for large tables

  • --set-charset: This adds the charset...