MySQL Database Backup

Hello guys

In this post we are going see how to take MySQL database backup and schedule it for daily backup.

1. Create backup user and grant appropriate permissions.

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup'; GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost'; GRANT LOCK TABLES ON *.* TO 'backup'@'localhost';

2. Create .my.cnf file with below content. Create this file in home directory of database owner. Set 600 permission for this file.

[mysqldump] user=mysqluser password=secret

3. Copy below files to desired locations.
mysql_database_backup.sh mysql_backup_schedule.sh

4. Make changes to mysql_backup_schedule.sh file
/home/mysql/isdba/mysql_database_backup.sh /home/mysql/mysql/bin /home/mysql/database_dumps/dumps db_name >> /home/mysql/database_dumps/logs/db_name.log

• first parameter to mysql_database_backup.sh is location of mysql bin directory
• second parameter is location of backup
• third parameter is database name

5. For daily backup schedule mysql_backup_schedule.sh file execution in crontab
00 23 * * * /home/mysql/isdba/mysql_backup_schedule.sh

---