Problem : We don’t want to give plain password in mysql_dump command so no one can see password while taking backup
step 1 : Create Backup user (icm_backup) and give required access to user.
Create user 'icm_backup'@'localhost' IDENTIFIED BY 'backup@31'; GRANT LOCK TABLES, SELECT ON *.* TO 'icm_backup'@'localhost' IDENTIFIED BY 'backup@31';
Above user icm_backup has select and lock tables rights on all databases which is required to take backup
step 2 : To avoid giving password on command line create .my.cnf
- Start your terminal and login to your server
- Create .my.cnf file in the home directory
vi ~/.my.cnf --------COPY BELOW TEXT INTO .my.cnf FILE
Step 3: Copy the content below and replace with your mysql username and password.
[mysqldump] user = icm_backup password = xyz@123
Step 4: Now change the file permission to 600 to prevent other user from reading it
chmod 600 ~/.my.cnf
Step 5: Write cron job
crontab -e 59 23 * * * mysqldump -u icm_backup -h localhost --all-databases > /location/alldb.sql
Above command will take backup of all database from icm_backup and take all databases backup and store in /location/