How to backup and restore mysql database

Database backup mysql db restore mysql db 90 times1 answer1 follower
0
Mel Khamlichi 103 Rep. Avishek Kumar edited question

Please  answer the bellow  questions :

 

How to   export  and  restore  database  ?

how to  export   all   databases  ?

How  to  make  small cronjob script  to export databases  with  date +%Y-%m-%d-%H.%M.%S  ?

♥ Spread the love ♥
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
0
Avishek Kumar 449 Rep. Avishek Kumar edited answer
To export/backup/dump a MySQL Database, you need to run
mysqldump -u [user_name] -p [Database_name] > [dbdump.sql]
Example to export database dokws to dokws.sql using user root
mysqldump -u root -p dokws > dokws.sql
If export/dump is from remote server
mysqldump -u [user_name] -h [remote_host_name_or_IP] -p [Database_name] > [dbdump.sql]
Example to export database dokws to dokws.sql from server 147.33.242.50
mysqldump -u root -h 147.33.242.50 -p dokws > dokws.sql
To restore database dump to mysql
mysql -u [user_name] -p [Database_name] < [dbdump.sql]
Example to restore database dump dokws.sql to schema dokws using user root.
mysqldump -u root -p dokws < dokws.sql

Similar to the above example, you need to add

-h [remote_host_name_or_IP]

if database server is on remote host.

A shell script to dump mysql database

#!/bin/bash
# Written by Avishek Kumar
date=$(date +%Y-%m-%d-%H.%M.%S)
mkdir -p /opt/database_dump
path=/opt/database_dump
mysqldump --user='root' --password='123456_root' dokws > $path/$date.sql

All we need to do now, is to make the above script executable and schedule a cron job. Say to run the above script everyday at 08:30 PM, add the below to your crontab (crontab -e)

30 20 * * * /home/dokws/script_name.sh
♥ Spread the love ♥
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
×

Login