Running online website or blog is very simple as compared to the its maintanence. When its question about the maintenance, its always something like dealing with taking huge directory backups and your Database backups. Directory backups are not much hard as we have to just zip all the file and save it. Backup and Restore the large mysql database is bit dirty work.
Lets Start Backing Up Large Database:-
Step1:-
Everything here we will deal in the Command Line of Linux Server. So we need Putty (Windows SSH Client). Make sure your Server or Shared Hosting have access to SSH.
Step2:-
change to the directory where you want to save your dump file:
Example:-
cd /home7/username/public_html/backupsql (make sure that directory exists)
(cpanel users) (your location might be different, find your exact location and then type command)
After creating directory move to next step to dump your mysql database.
mysqldump -u dbusername -p dbname > dbname.back.sql (you can write any name here )
You will be ask for the password. Enter the password and rest is done. The database.sql file will be saved in your given directory location.
This is the best method for saving any size of large mysql database. Now its time to learn how to Restore the saved database.sqlRestoring is little more dirty than backing up the mysql database.
Restoring Large Database:-
Step1:-
Importing big database.sql file is not pssible with phpmyadmin so try importing mysql database using SSH Client.
Procedure is somewhat similar to above step, but its completely opposite. In above steps we were saving the mysql file, while in this we have to upload/restore our large database.sql using SSH Client command line.
Step2:-
Just type the following command in the putty(Windows SSH Client).
mysql -u dbusername -p dbname < dbname.back.sql (your choice name)
You’re Database will be in restoring process. If you get any error then please do follow the below steps.
database upto 1GB i have already backup and restored i have not faced this error.But if you face such error. then do perform following commands.
mysql> set global max_allowed_packet=1000000000;
mysql> set global net_buffer_length=1000000;
Normally you’re not require to follow above steps, but only when you get error.That will increase your mysql import size. And if you still get error then you need to split up your database because your database is too much large.
You can use SQLDumpSplitter2 to split your large database into small pieces. Its a windows client. So you need to download the mysql database to your pc and then split it and upload all the splited parts accordingly to your backup directory online.
After upload all the files you need to run this command first.
mysql -u db_user -p db_name < yourbackup_DataStructure.sql (it contains the data split structure of your mysql database that is splitted)
Then your splited sql files:
mysql -u db_user -p db_name < yourbackup_1.sql
mysql -u db_user -p db_name < yourbackup_2.sql
and so on…
So here we finish with our backing up and restoring the large mysql database files.
If you want your Regular Database Backup and if you are lazy like me :-p, then do have a look at http://www.mysqldumper.net/ it helps to take auto backup of your Mysql Database at some regular interval.
I hope this helps 
