Backing up a MariaDB database

MariaDB provides a tool called mariadb-dump for backup up a database to an SQL file. I struggled for a while to get it working, this is what I had to do (in the following examples the user is called foo and the database is called bar).

When I first tried running it I got the error

mariadb-dump: unknown option '--auto-rehash'

On Arch Linux the default configuration contains a setting auto-rehash which confuses mariadb-dump (as it doesn’t recognise it). It is set in the file /etc/my.cnf.d/client.conf. Simply commenting out the line auto-rehash fixes this (I’m not sure why this setting is a default so I uncommented it after making the backup).

I then tried backing up my database again but got the error message

"Access denied for user 'foo'@'localhost' to database 'bar'" when using LOCK TABLES

This is because I hadn’t given my user the LOCK TABLES privilege (run this command as the root user in the mariadb CLI):

GRANT LOCK TABLES ON bar.* TO 'foo'@'localhost';

I also need to have the SELECT privilege in order to lock it (but I had set that already).

The command to run to make a backup (to a file called backup.sql) is:

mariadb-dump -u foo -p bar > backup.sql