BACKING UP MYSQL DATABASE USING TELNET
STEP 1:
FTP into your web site where you want to backup the database. Create a folder named ‘backup’ (or any name you want) in your public_html directory. Chmod it to 777. (Note: If your server API is CGI, you will get ‘Internal server error’ with 777. So if this is your case, chmod it back to 755.)
STEP 2:
Telnet into your web site using Putty.
STEP 3:
At the command prompt type the backup query like this - (single line)
mysqldump -u mysite_User -ptest123 mysite_database
> /home/path/to/backup_folder_name/backup.sql
Press ENTER.
- Website - mywebsite.com
- Database name - user_database
- Database Username - user_User
- Password - test123
If everything is OK, you will go to the command prompt again. Check your ‘backup’ folder. You will see backup.sql file.
Note: There is no space between -p and your password.
Tip: Copy the above command on a notepad and modify it with your details. Then copy and paste at command prompt.
STEP 4:
Download it to your computer in ASCII mode of your ftp software. (Not in AUTO mode.)
RESTORING YOUR DATABASE
You need to restore the data into the database if you lost your data or if your database is corrupted.
STEP 1:
Create a folder ‘restore’ (or whatever you like) on your site. Upload the ‘backup.sql’ that is saved on your computer to this folder in ASCII mode.
STEP 2:
Login into your web host panel and go to Phpmyadmin. Select the database into which you want the data restored to. Delete ALL Tables. Remember, you need to restore data into the SAME database. So your database should be empty with no tables and no data.
STEP 3:
Now telnet into your web site as you did during database backup process above.
Run this command at command prompt - (single line)
mysql -u mysite_User -ptest123 mysite_database
< /home/path/to/restore_folder_name/backup.sql
Press ENTER.
- Website - mysite.com
- Database name - mysite_database
- Database Username - mysite_User
- Password - test123
If everything is OK, you will go to the command prompt again. Check your phpmyadmin interface. All data will be restored into the database again.
Note: There is no space between -p and your password.
Tip: Copy the above command on a notepad and modify it with your details. Then copy and paste at command prompt..
Do a test practice on a test database first. Create a test database and import some data and try following above instructions
