You can easily backup and restore the Matomo data using the following commands.
Export your MySQL/MariaDB database in a file
You can use tools such as phpMyAdmin or
mysqldump to do this. The simplest way to make a full copy of the MySQL database is to run the mysqldump command with this set of parameters:
$ time mysqldump --extended-insert --no-autocommit --quick --single-transaction MATOMO_DATABASE_NAME -uMATOMO_DATABASE_USERNAME -p > matomo_backup_database.sql
We can then compress this backup before sending it to the backup server:
$ tar zcf matomo-mysql-database-$(date +%Y-%m-%d-%H.%M.%S).sql.tar.gz matomo_backup_database.sql
Restore the backed up database dump
you can use phpmyadmin to import the file that you previously created, or restore the DB backup by running the following command:
$ time mysql MATOMO_DATABASE_NAME -uMATOMO_DATABASE_USERNAME -p < matomo_backup_database.sql
This should create more than a dozen Matomo tables in your database and restore all your data.
Note: if you restore the backup in a “test” instance, you may be interested in disabling all email notifications.
Advanced: when Matomo is setup in writer/reader configuration (DB replication)
There are several strategies to backup your Matomo MySQL database. The best strategy to use depends on how your database is setup. The recommended setup is to make the full backup on an unused slave or read replica (when MySQL database is replicated using master-slave or master-master). For reference, a 5GB DB will take ~2 min to backup and ~12 min to restore.
Multiple DB nodes: use mysqldump on a DB slave/read replica
It is recommended to run Matomo with a replicated MySQL database (master-slave or master-master replication). The MySQL backup is to be done on the database read replica (slave) as to not impact performance of the master database. The mysqldump command to use on the slave/read replica is the same as found in the next section.
Single DB node with a large database: use Percona XtraBackup.
When you are using a single database server and tracking a lot of data in Matomo (1M+ actions per month) or when your database is larger than 10Gb, using mysqldump may not be a suitable solution as it may negatively impact the performance of the overall system.
In such cases it is recommended to use the open source tool Percona XtraBackup instead.