Install  automysqlbackup via:

apt-get install automysqlbackup

Then update the config file:

vi /etc/default/automysqlbackup

Then change this line from:

DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | tr \\\r\\\n ,\ `

to

DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | grep -v ^performance_schema$ | tr \\\r\\\n ,\ `

This fixes the lock tables error. As referenced here and here.

You then need to set your configuration file’s backup directory. In the same file, change the BACKUPDIR variable.

You can then test your installation by running

sudo automysqlbackup

The job is initiated on daily basis in the /etc/cron.daily/automysqlbackup

Sources

• Simple automated MySQL backups on Ubuntu Linux
• how do I stop automysqlbackup throwing LOCK TABLES error?