MariaDB - database backup - mysqldump

Colleagues hello to all.

In today's article, we'll talk about how you can back up MariaDB databases using the mysqldump utility. The mysqldump utility can be used to dump all databases or a single database. The dump contains SQL statements. mysqldump does not consume much CPU on the server because it uses only one thread by default.

Article content:

1. Utility syntax.
2. Usage examples.

1. Utility syntax.

mysqldump -u [user_name] –p [password] [options] [database_name] [tablename] > [filename.sql]

2. Use examples.

2.1. A simple example of creating all databases.

$. mysqldump -u root -p --all-databases --result-file=/app/mariadb_backup/dump.sql

The --all-databases option means that the backup includes all databases.

2.2. With this example, we are backing up the test database.

$. mysqldump -u root -p --databases test --result-file=/app/mariadb_backup/dump.sql

The --databases parameter is just responsible for backing up a specific database.

2.3. In this example, we are backing up the database test and test2.

$. mysqldump -u root -p --databases test test2 --result-file=/app/mariadb_backup/dump.sql

You can pass multiple database names to the --databases parameter at once, separated by a space for backup.

2.4. With this example, we create a backup with the ability to log warnings and errors that may occur during the backup process.

$. mysqldump -u root -p --databases test test2 --log-error=/app/mariadb_backup/log_error.log --result-file=/app/mariadb_backup/dump.sql

2.5. In this example, we create a backup with binary log position and filename added, this is useful for dumping the database of the primary replication server to create a dump file.

$.  mysqldump -u root -p --databases test test2 --master-data=2 --result-file=/app/mariadb_backup/dump.sql

When using the  --master-data option, you must have binary binlog logging enabled or you'll get a Binlogging on server not active error.

2.6. In this example, we create a backup that will download data in a consistent state. Should only be used with InnoDB tables.

$. mysqldump -u root -p --databases test --single-transaction --result-file=/app/mariadb_backup/dump.sql

2.7. In this example, we create a backup copy with verbose mode enabled, you will see what does the program at various stages.

$. mysqldump -u root -p --databases test --verbose --result-file=/app/mariadb_backup/dump.sql

Thank you all, I hope that my article helped you in some way.