Create Cronjob for automated MariaDB Database Backup

Keywords: LAMP/MAMP/WAMP - AWS - How to - Services (Apache, MariaDB, MySQL…)

Description:
Hello Dears,
I have bought a bitnami LAMP server from AWS with mariadb server.
I used to create a daily database backup for mysql server before using cronjob as following code and all work fine:

00 07 * * * /opt/bitnami/mysql/bin/mysqldump --defaults-extra-file=/home/bitnami/.mylogin.cnf -u username databasename --single-transaction --quick --lock-tables=false > /home/bitnami/dbb/mydatabase_db_date +\%F.sql

today bitnami changed their database to mariaDB, and when I used the same code to do an automated daily backup, the file is generated but with 0 file size ( means nothing inside it) … see below screen shoot for the.mylogin.cnf file I but the database details as below:

image

please any help here, does mariadb path differ from mysql ?? and what should I change exactly?

I tried also to export the database using mysql workbench and it give following error:

Hi @ghina.altal,

The mariadb path is /opt/bitnami/mariadb. The commands should work just the same.

Regards,
Michiel

what should the cronjob command be now??

00 05 * * * /opt/bitnami/mariadb/bin/mysqldump --defaults-extra-file=/home/bitnami/.mylogin.cnf -u username databasename --single-transaction --quick --lock-tables=false > /home/bitnami/dbb/mydatabase_db_date +\%F .sql

I tried the above command, the database file is generated in dbb folder but with 0 size, means it is empty, see below screenshoot:

Hi @ghina.altal,

Can you try to log in mysql using the credentials in .mylogin.cnf?

Regards,
Michiel

@michiel

yeah I tried and it is opened normally as below:

I noticed that when I used the root user the database file is generated correctly, but when I used the new user and password created based on your documentation in below link, it generate a 0 file size database backup.

https://docs.bitnami.com/ibm/infrastructure/lamp/configuration/create-database-mariadb/

Hi @ghina.altal,

The root user has access to the bitnami_wordpress database but if you create a new user and a new database you would need to import data there. Did you do that?

Regards,
Michiel

@michiel, yes sure.
I create a new database and user using below commands:

create database database_name;
grant all privileges on database_name.* TO ‘new_user’@‘localhost’ identified by ‘user_password’;
grant all privileges on database_name.* TO ‘new_user’@’%’ identified by ‘user_password’;
flush privileges;

the issue is that when I create a cron job using the new user credentials, it generates 0 file size database backups.

I tried to use this command to check if the database file is created and it works fine:
mysqldump -u new_user -p bitnami_app > backup.sql

the file is genrated correctly, the issue is with the cron job only :frowning:

Hi @ghina.altal,

Could you try using sudo su:

00 07 * * *  sudo su bitnami -s /bin/sh -c " /opt/bitnami/mysql/bin/mysqldump -u new_user -p bitnami_app > /path/to/backup.sql"

Regards,
Michiel

@michiel

I tried it and it generates 0 file backup as below:

also I tried to run the command in the CLI as shows below error:

@michiel

the server is using mariadb, so I changed the command to:

00 07 * * * sudo su bitnami -s /bin/sh -c " /opt/bitnami/mariadb/bin/mysqldump -u new_user -p bitnami_app > /path/to/backup.sql"

also when use it in cron job it generate 0 file backup, but when use the command in the CLI like:
sudo su bitnami -s /bin/sh -c " /opt/bitnami/mariadb/bin/mysqldump -u new_user -p bitnami_app > /path/to/backup.sql"

it prompts for user password and then generate the correct file, but in crontab it is not working

Hi @ghina.altal,

Could you try with root and in the following format:

00 07 * * * /your_installdir/mariadb/bin/mysqldump -u root -pYOUR_PASSWORD bitnami_wordpress > /path/to/your//backup.sql

And with the new user and database:

    00 07 * * * /your_installdir/mariadb/bin/mysqldump -u new_user -pYOUR_PASSWORD new_database > /path/to/your//backup.sql

Regards,
Michiel

@michiel

yeah, the above commands worked for both the root user and the new user and the file is generated as below:

but is it safe to keep the password in the command itself? is it better to hide it in a configuration file?

Hi @ghina.altal,

Yes, sorry, that was just an example. Hiding it is always better.

Regards,
Michiel

@michiel, so how can I do this please?

Hi @ghina.altal,

Using the " --defaults-extra-file" option from your first reply:

mysql --defaults-extra-file=<absolute path to the file> 

The file would look like this:

[client]
user=foo
password=P@55w0rd

Regards,
Michiel

@michiel and what to name the file?