Friday, April 30, 2010

MySQL: Backup and Restore MySQL

* Warning: Third technical entry? (Obviously, I graduated from Technical University :D).

Salam and good day,

We meet again in another technical entry as stated above. As you can see database are as important as the program itself. as programmer, you should not mingle to much with your database or face the upcoming consequences. It's like the heart of the program. It keep track of data stored in the applications. How do we keep our database as stated in our BRP? dont worry, K0m4nd3r once again come to the rescue.

Prereq- (From now on, Prerequisites will know as prereq, you determine it yourselves):

MySQL. (For sure...)

Unfortunately, this time, there are no step-by step guide. Only commands that we can use to make sure we have correctly backup and restore our database.

To backup:
Just assign this command: 

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

[uname] is database username
[pass] is database password for that particular username
[dbname] is database name that you want to enter as in use [dbname]
[backupfile.sql] is name of the file created once backup is done.
--opt is option available when you do the backup. for more information visit reference below.

[test@testsvr1 ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
# cd /home/testBackup
# mysqldump -u myname -p mypass mydb > mydbBackup.sql

To restore:
another command in your ssh:

$ mysql -u [uname] -p [db_to_restore] < [backupfile.sql]

[uname] is database username as above.
[pass] is also same as above.
[db_to_restore] have to refer to [dbname] above.
[backupfile.sql] is backupfile generated from above command.

# cd /home/testBackup
# mysqldump -u myname -p mydb < mydbBackup.sql
# enterin passwordnya dong!


Notice differences in ">" and "<" backup

">" use to backup.

"<" use to restore.

No comments: