• Home
  • WordPress
  • web Hosting
  • linux
  • mysql
  • nginx
  • apache2
  • devops

Raju Ginni

wordpress tutorials seo hosting etc

You are here: Home / MySQL Tutorial (create,connect database, update tables, export etc) Documentation & TIPS / mysql auto backup ubuntu 22.04 using automysqlbackup

mysql auto backup ubuntu 22.04 using automysqlbackup

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

Table of Contents

Toggle
  • Step One: Install AutoMySQLBackup
  • Step Two: Verify AutoMySQLBackup
  • automysqlbackup configuration file location:

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 {} \;
back Up MySQL Databases with AutoMySQLBackup
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
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:
delete mysql backups older than 7 days
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.
https://manpages.ubuntu.com/manpages/focal/man8/automysqlbackup.8.html
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

MySQL Tutorial (create,connect database, update tables, export etc) Documentation & TIPS

  • mysqldump import /export mysql database command line, phpmyadmin, Cpanel, mysql workbench, xamp
  • mysql commands
  • phpmyadmin install / configure on nginx ubuntu 20.04 apache2 debian 10
  • improve mysql performance wordpress my.cnf file configuration
  • innodb buffer pool size measure & adjust by pages, read requests etc
  • mysql workbench tutorials (Sql Development , modelling, server admin export & import)
  • mysql errors
  • Innodb vs myisam (table engines row lock vs table lock)
  • mysql max connections limit check increase how to decide thread cache size
  • MySQL innodb memory allocation & usage calculation
  • MySQL query cache vs redis vs memcached buffer pool database cache
  • mysql 8 installation on ubuntu 20.4
  • mysql configuration file location linux , windows , mac
  • mysql root password change reset update set A-Z info
  • mysql user creation, password , grant permissions user management guide
  • mysql slow query log enable disable set query time ideal mysql long query time
  • mysql access denied for user 'root'@'localhost' (using password yes) no Error 1045
  • monitoring mysql with new relic
  • mysql restart ubuntu status start stop in linux windows reload
  • mysql open_files_limit increase to raise open file cache table definition cache
  • mysql memory limit setting increase or decrease allocation
  • mysqltuner installation suggestions
  • mysql auto backup ubuntu 22.04 using automysqlbackup
  • mysql high cpu usage cases and limiting cpu usage
  • mysql oom killer
  • mysql memory parameters
  • check mysql database size and table size
  • mysql find replace wordpress posts content
  • mysql import export database command line linux ubuntu debian wamp xamp
  • mysql interview questions and answers fresher to experience

hi i am raju ginni, primalry i manage wordpress websites on GCP cloud platform as a cloud engineer, and create content on passionate things.
you can follow me on youtbe

© 2025 - All Rights Reserved Disclaimer & Privacy Policy