mysql auto backup ubuntu 22.04 using automysqlbackup

Aim;  daily backup mysql in root folder using cron and mysqldmp & rsync

Step One: Install AutoMySQLBackup

You can install AutoMySQLBackup from the Ubuntu repositories by running the following command as root:

sudo apt-get install automysqlbackup

Once installed, the script will automatically run once a day. Backups will be stored in the directory:

/var/lib/automysqlbackup

Step Two: Verify AutoMySQLBackup

To verify AutoMySQLBackup was configured properly, you can run it manually rather than waiting for it to be run the first time.

To run AutoMySQLBackup manually, execute the following command as root:

sudo automysqlbackup

Now list all of the files under /var/lib/automysqlbackup:

$ ls -R /var/lib/automysqlbackup/

automysqlbackup configuration file location:

/etc/default/automysqlbackup

Local Host mysql backup:

0 1 * * * /usr/local/mysql/bin/mysqldump -uroot -ppassword –opt database > /path/to/directory/filename.sql

(There is no space between the -p and password or -u and username – replace root with a correct database username.)

sudo mkdir /var/backup/db

vim script.sh or nano script.sh

# (1) set up all the mysqldump variables

DATE=`date +”%d_%b_%Y_%H%M”`

SQLFILE=/var/backup/db/db_backup_${DATE}.sql

DATABASE=<database_name>

USER=<db_user>

PASSWORD=<db_user_password>

# (2) in case you run this more than once a day,

# remove the previous version of the file

unalias rm    2> /dev/null

rm ${SQLFILE}    2> /dev/null

rm ${SQLFILE}.gz  2> /dev/null

# (3) do the mysql database backup (dump)

sudo mysqldump -u ${USER} -p${PASSWORD} ${DATABASE}|gzip > ${SQLFILE}.gz

edit the my.cnf

[mysqldump]

quick

quote-names

max_allowed_packet      = 16M

user=root

password=complexPassword

Traverse to the script’s folder:

cd /var/backup/

And execute the following command:

sudo ./script.sh

Scheduling Execution with Cron

Edit the server’s cron with the undermentioned command:

sudo crontab -e

0 1 * * * /var/backup/script.sh

delete backups older than 7 days

sudo find /var/backup/db/. -mtime +7 -exec rm {} \;

Once installed, the script will automatically run once a day. Backups will be stored in the directory:

confuguration file

sudo nano /etc/default/automysqlbackup

manual run

sudo automysqlbackup

verify mysql

ls -R /var/lib/automysqlbackup/

/var/lib/automysqlbackup/daily/jobsite:

jobsite_2022-12-14_13h04m.Wednesday.sql.gz

/var/lib/automysqlbackup/daily/phpmyadmin:

phpmyadmin_2022-12-14_13h04m.Wednesday.sql.gz

/var/lib/automysqlbackup/daily/sarkarijobz:

sarkarijobz_2022-12-14_13h04m.Wednesday.sql.gz

/var/lib/automysqlbackup/daily/sys:

sys_2022-12-14_13h04m.Wednesday.sql.gz

/var/lib/automysqlbackup/monthly:

run rm command weekly

@weekly rm  -r /var/lib/automysqlbackup/daily/*

@monthly rm  -r /var/lib/automysqlbackup/weekly/*

view crontab -l

crontab -e

service cron reload

sudo find /var/lib/automysqlbackup/daily/. -mtime +7 -exec rm {} \;

Automysqlbackup – Could not open required defaults file: /etc/mysql/debian.cnf

by default, assigns many variables by the MySQL file located at “/etc/mysql/debian.cnf”. This contains maintenance login information

From this file, it reads the user, password, and databases that must be backed up.

Create  /etc/mysql/debian.cnf file add below details

client

db name

db pass

[client]
host = localhost
user = root
password = "mysupersecretpass"
socket = /var/run/mysqld/mysqld.sock

optional
[mysql_upgrade]
host = localhost
user = root
password = mysupersecretpass
socket = /var/run/mysqld/mysqld.sock
basedir = /usr

root@murali:~# apt remove postfix
Reading package lists… Done
Building dependency tree… Done
Reading state information… Done
The following packages were automatically installed and are no longer required:
liblockfile-bin liblockfile1 libtokyocabinet9 mutt
Use ‘apt autoremove’ to remove them.
The following packages will be REMOVED:
automysqlbackup bsd-mailx postfix