mariabak

mariabak is a command-line front-end program for mysqldump, created to facilitate some backup operations on MariaDB and MySQL databases.

All features were created because to achieve the same result using mysqldump directly, the user needs to run more than one command. mariabak was developed to perform all of them in a single command.

• One dump file for each database (mysqldump doesn't create files, just output the data).
• You can opt out dump data from specific tables. The tables structure will be preserved (mysqldump –-ignore-table doesn't keep the table structure).
• Select which databases to backup, or all, using the * wildcard.
• One directory with a timestamp will be created for each backup operation. This directory is created in the same directory the command was executed.
• It backups all the user grants, system variables and events to a separate file.

Do you have a comment or a question? Post it on GitHub Discussions.

Hands-on

This presentation assumes install.sh was used for the installation.

If you want to use mariabak.php directly, switch mariabak to php mariabak.php

Minimum requirements

• PHP 7+ on PATH with pdo_mysql extension enabled.
• mysqldump on PATH (it's part of MariaDB or MySQL client).
• You must have credentials (username and password) with enough privileges (check footer note) to perform a backup from the selected server.

Install

You have two options:

• ^(Linux) Use the installer. Download or clone this repository and execute the install.sh (run as normal user. if necessary sudo password will be asked).

• ^{(Linux/Windows)} Download the latest version of the standalone script mariabak.php (right click, save link as)

Note: The installer will copy and rename mariabak.php to /usr/bin/mariabak and make it executable, so after install you just need to type mariabak from anywhere to use it.

Quick start

List databases:

If you used the installer:

$ mariabak -list     # default localhost, user root, empty password

If you prefer to use the .php directly:

$ php mariabak.php -list

Backup all databases. A directory will be created into the current directory:

$ mariabak --databases=*

Backup one database, asking for the server password interactively:

$ mariabak --databases=db1 --host=localhost --user=root -p

Backup some databases, ignore some table's data, but preserve its structure:

$ mariabak --databases=db1,db2,db3 --ignore-tables=db2.table1,db2.table2,db3.table_a --user=root -p

Backup one database, passing the server password inline:

$ mariabak --databases=db1 --host=localhost --user=root --port=3306 --password=hunter2  # Caution with this one. The shell can save command history.

Configuration file (optional)

When using the install.sh, a configuration file called .mariabak.conf will be copied to the user home directory. You can edit it to change the name format of backup directory.

The default format is backup-db_{date}_{time}_{host}_{port}

Documentation

Options available:

• All options that require a parameter start with two dashes.
• All options that NOT require a parameter start with one dash.

--databases : Comma separated databases to backup. Use * for all.

$ mariabak --databases=*
$ mariabak --databases=mydatabase,otherdatabase,db3

--ignore-tables : Comma separated tables to NOT backup data. The table structure will be preserved.

$ mariabak --ignore-tables=mydatabase.log,mydatabase.photos,db3.cache --databases=mydatabase,otherdatabase,db3

-list : Just list all available databases. Doesn't perform a backup.

$ mariabak -list

--host : The server IP or hostname. If ommited, assume "localhost".

$ mariabak --host=localhost -list

--port : The server port. If ommited, assume "3306".

$ mariabak --port=3306 --databases=mydb

--user : The user that will connect to server. If ommited, assume "root".

$ mariabak --user=root --databases=db1,db2

--password : The server password. If ommited assume empty string.

$ mariabak --password=hunter2 --databases=* # CAUTION, the shell can save command-line history

-p : Ask user to type the password.

$ mariabak -p --databases=*

---

NOTE: Required privileges to perform a backup

Backup operations on MariaDB or MySQL server can be made authenticating as 'root' user. If you want to use a different user to perform a backup he needs the following permissions on all databases that you need to backup:

• select
• show view
• lock tables
• reload
• replication client