today i wanted to backup a database but i had no idead had to backup and restore it again. I was moving a mysql database from one server to another. a customer was moving their server from another carrier to www.webune.com they had a dedicated server but wanted all the database also migrated. Linux make this task very simple. so today i will show you on this little tutorial on how to backup and then restored a database from a tarball gzip file. or zip file.

ok so here we go. make sure you know the old and the new server's root mysql password. there is a post here at wallpaperama that show you how you can set your mysql admin password but i dont' remember what the post url is, but i am sure you can find it in the search section of the forums.

OLD SERVER

1. login to the old server where the database to be imported and send this command:

CODE:
mysqldump --opt -u [DATABASE USER] -p [DATABASE NAME] | gzip > [NAME OF THE FILE].sql.gz

or

mysqldump --opt -u root -p --all-databases | gzip > all_databases_2_27_2007.sql.gz

[DATABASE USER] = your mysql user name (root if you have it)
[DATABASE NAME] = your mysql database name you want to backup
[NAME OF THE FILE] = name of the file you want to name it to. example: backup_2006.sq.gz

you will be prompted to enter the [DATABASE USER] password.
or you can enter the root password.

if your password is good, the file will be created. now you have to get the [NAME OF THE FILE].sql.gz into the new server, you can use FTP or HTTP if you want to. i used HTTP because its faster to download.

NEW SERVER

login to the web hosting control panel and create a database with the username and password exactly the same as the old server. and make sure you are able to login with myphpadmin

now login into the new server and to restore the database you backup from the old server send this command:

CODE:
gunzip < [NAME OF THE FILE].sql.gz | mysql -u [DATABASE USER] -p [DATABASE NAME]


[DATABASE USER] = your mysql user name from old server
[DATABASE NAME] = mysql database name from old serve
[NAME OF THE FILE] = name of the file you backed up. example: backup_2006.sq.gz


you will be prompted to enter the password. here i entered the root password. after i did, the shell command went through fine without any errors.


This is how my command looked like:
CODE:

[root@hostname backup]# ls
webX_dbX_1_18_06.sql.gz  webX_1_18_06.sql.gz

[root@hostname backup]# gunzip < webX_1*.sql.gz | mysql -u root -p webX_dbX
Enter password: ***************** (root password)



that's it, you have the old databse on the new server.

Thanks to www.webune.com for their support on this.