Berikut ini adalah perintah untuk melakukan backup dan restore basis data menggunakan perintah text pada linux
# Syntax for Single Database backup : # $ sudo mysqldump -u [username] -p [jangan masukkan password disini] [databasename] > [backupfile.sql] # Syntax for Single Database restore : # $ sudo mysql -u [username] -p [jangan masukkan password disini] [databasename] < [backupfile.sql] # Syntax for Multiple Database backup : # $ sudo mysqldump -u [username] -p [password] –databases [databasename1] [databasename2] > [backupfile.sql] # Syntax for full/all Database backup : # $ sudo mysqldump -u [username] -p [password] –-alldatabases > [backupfile.sql] # Details of above syntax: # UserName : Database username Password : Password for your database DatabaseName : The name of your database backupfile.sql : The file to which the backup should be written.
1) Backup & restore Examples
You can use the below examples to take a backup of single, multiple & all databases and restore the same.
# Single Database backup Example : # $ sudo mysqldump -u root -p testdb > testdb.sql [Enter the password when prompt for password] # Single Database restore Example : # $ sudo mysql -u root -p testdb < testdb.sql [Enter the password when prompt for password] # Multiple Database backup Example : # $ sudo mysqldump -u root -p –databases testdb1 testdb2 > multidatabasebackup.sql [Enter the password when prompt for password] # Multiple Database restore Example : # $ sudo mysql -u root -p testdb1 testdb2 < multidatabasebackup.sql [Enter the password when prompt for password] # Multiple Database restore single database from multiple database backup file : # $ sudo mysql -u root -p testdb1 < multidatabasebackup.sql [Enter the password when prompt for password] # All Database backup Example : # $ sudo mysql -u root -p < alldatabases.sql [Enter the password when prompt for password] # Backup Database with compressed format : # $ sudo mysqldump -u root -p testdb | gzip >testdb.sql.gz [Enter the password when prompt for password] # Restore compressed Database format : # $ sudo gunzip < testdb.sql.gz | mysql -u root-p testdb [Enter the password when prompt for password]
2) Shell Scripts for Backup automation
As a server administrator you won’t remember everything, so for routine works you can use the cron jobs to execute the program to your convenience.
Contoh
The below example is to take an automatic backup of “testdb” database and store it to “/backup/db/testdab19-09-2013.tar.gz” and keep 5 days backup.
create the file mysql_backup.sh on mysql bin directory and add the below code, the file permission should be 755 so that you can execute the file.
#!/bin/bash
date_format=`date +%d-%m-%Y`
cd /
mysqldump -u root testdb| gzip -9 > /backup/db/testdb$date_format.sql.gz
find /backup/db/test* -mtime +5 -exec rm {} \;
Cron for scheduling backups at your convenience
Cron Entry
The schedule cron will be executed every day 6’o clock
0 18 * * * /bin/mysql_backup.sh
Awriel's 