How to backup a MariaDB database

This post explains how to backup a mariaDB database using the mysqldump command.

1. Create a backup SQL user

We will create a specific user for backup. This user will have access to one database only from your localhost. Connect your database server.

mysql -u root -p

Execute the following SQL commands.

USE mysql;

GRANT SELECT, LOCK TABLES ON 'yourdbname'.* TO 'yourbackupuser'@'localhost'

IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;

UPDATE mysql.user SET Reload_priv = 'Y' WHERE user = 'yourbackupuser';

FLUSH PRIVILEGES;

2. mysqldump client program description

We will use the mysqldump command to backup the database.

mysqldump yourdbname -u yourbackupuser --password=yourpassword -BFcq --single-transaction

> yourbackupfile.sql

Options :

  • u : SQL user
  • p : User's password
  • B : USE database name in generated SQL file
  • F : Flush the MySQL server log files before starting the dump
  • c : Use complete INSERT statements that include column names
  • q : Retrieve rows for a table from the server a row at a time (useful for dumping large tables)
  • single-transaction : Useful only with transactional tables because then it dumps the consistent state of the database

16th Jul, 2015

Technical