MYSQL: How to set/configure/check backup using crontab

Case: In Ubuntu Server

Normally crontab file locate here:
# nano  /etc/crontab

The rules for crontab can refer in crontab file (backup.sh file)
:
PATH=/usr/local/sbin

or in command line

# m   h  dom mon dow user    command

   00 22   *      *      *   root    sh /usr/local/sbin/automysqlbackup.sh

** 00 22  is time to run the autobackup. The time can be change anytime if needed.

Check the rules in .sh file
# nano  /usr/local/sbin/automysqlbackup.sh

**Important Configuration:

# Username to access the MySQL server e.g. dbuser

USERNAME=dbuser

# Username to access the MySQL server e.g. password

PASSWORD=passworddb

# Host name (or IP address) of MySQL server e.g localhost

DBHOST=10.XX.XX.XX

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"

DBNAMES="dbname1 mysql"

#DBNAMES="all"

# Backup directory location e.g /backups

BACKUPDIR="/mnt/archives/backupdb/alimcloud/"

:

# Email Address to send mail to? (user@domain.com)

MAILADDR="email@domain.com.my"

** Just remain default setting for others.

.

Mysql: My Own Notes( Backup & rename )

1. Get db backup for www from daily backup server
2. Copy the db to the nearest linux server (eg: 75)
3. Bunzip the file:
   #bunzip  filename_xxx.bz2
4. Use vi command to rename the db
   #vi filename_xxx.sql
5. Esc  >  Shift + :
   :%s/oldurl.my/newurl.org/g
   ** If the cursor already can move up and down then its done.
   
   
6. Esc  >  Shift + :  >  :wq  >  Enter
   **Wait until the process finish
   
   
7. mkdir folder
8. mv  filename_xxx.sql  folder/
9. tar -cjf  folder.tar.bz2  folder
10. scp  folder.tar.bz2  your@locationserver.my:
    
11. Open related db server to restore the db.Copy the file from yourlocation server.
    #scp  your@locationserver.my:/home/you/ folder.tar.bz2  ./
    
12. Untar the file first:
    # tar -xjvf   folder.tar.bz2
    
13. Open folder and restore the db in server
    # mysql -uroot -p --default-character-set=utf8  <   filename_xxx.sql
    
14. Grant the privileges to the db
    # GRANT ALL PRIVILEGES on db.* to 'username'@'applicationserverhost' IDENTIFIED BY 'password'
    
    #FLUSH PRIVILEGES;
15. Check connection within the db and the application server.
    #telnet  applicationserverhost  3306;
16. From application server, test connection to db server
    #mysql -uUSERNAME -p -h  IP_databaseserver
    
    eg:  mysql -urepo_admin -p -h  172.XX.XXX.XX
    ** If error, connection refuse ask Network Admin to allow telnet both server in firewall setting.
    
17. Update the apps configuration file for the grant access. Check the application from browser.
18. Done.