MariaDB - database backup - mysqldump

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

mariadb_mysqldump

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

mariadb_mysqldump

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

mariadb_mysqldump

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

mariadb_mysqldump

 

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

mariadb_mysqldump

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

mariadb_mysqldump

 

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

mariadb_mysqldump


 

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

SIMILAR ARTICLES

MariaDB - ERROR Incorrect definition of table
MariaDB - ERROR Incorrect definition of table
MARIADB
Read more
Mariabackup backup - gzip compression
Mariabackup backup - gzip compression
MARIADB
Read more
Mariabackup error - Error writing file UNKNOWN errno 32 Broken pipe
Mariabackup error - Error writing file UNKNOWN errno 32 Broken pipe
MARIADB
Read more