| Not logged in - | |
| Not logged in - | |
| Moderated by: Jim |
|
||||||||||||||
| Backing up your database with SSH - Hosting with Data 1 Systems - Hosting - WowUltra Forums | |||||||||||||||
| Author | Post | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||||||
|
Jim Administrator 
|
If you have your hosting at Data 1 Systems then you have SSH access for sure. A lot of other hosts have SSH access also, and it is THE ONLY WAY to back up large databases. Here's a short but precise tutorial- You need to download a program called "Putty" from here: http://the.earth.li/~sgtatham/putty/latest/x86/putty.exe When you open putty the first screen will prompt you for a host name or IP address. Use your sites name without the www- example: whateveryoursiteis.com. The next screen will be a warning (the servers host key is not cached in the registry), click YES to accept the temp key. It will take you to a black terminal screen with the words "log in:" Put yout cpanel username in there and hit enter. It will prompt you then for a password. Type in your password but be warned THE CURSOR WILL NOT MOVE DURING THIS PROCESS. Even if you copy and paste it will not move but the info is being recorded. press enter and you are in. After replacing the items in red with your personal database info put this line after the prompt in the black window: mysqldump database_name -u database_user -ppassword >dump.sql Exact spacing is critical and yes the -p is crammed beside the password, it has to be there exactly like that. Hit enter and do not do anything else until the prompt comes back, it could take up to 5 minutes depending on the size of your database. It will give you no indication that it has done anything but trust that it has. Type exit and hit enter- the window will disappear. NOW go to your FTP..there will be an extremely large file called dump.sql in the main directory (the one that the public_html and the public_ftp is in) Download that to your computer then delete it off your server....it could take you over your file size limit on your account if you leave it on there. Like I said this is the extreme advanced class so be very careful. Next lesson: restoring a large database
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
Seaside License Holder 
|
Hello Jim, Would you recommend using phpmyadmin for doing backups? Thats what i use at the moment, im not sure if im doing it correctly, can you assist with a tutorial or direct me to one. thank you
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
Jim Administrator
|
phpMyAdmin works excellent for backups. It doesn't work well for restores, which is part 2. I have never been able to restore a database over 50M with phpMyAdmin regardless of the server config. Depending on how large the attachments are in the database sometimes it will time out restoring even seemingly small backups.
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
Seaside License Holder
|
thats not cool news at all, i do not have ssh at my host, is there an alternate options, regarding backing up my db.
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
Jim Administrator
|
Maybe I didn't make myself clear. You can make a reliable backup with phpMyAdmin. If you attempted to restore that backup (let's say you moved your board to another host or the hard drive crashed on the server) then it would not work using phpMyAdmin to RESTORE the saved backup file. SSH even is limited to what the host has the "Max packet size" set in MySQL. There are third party solutions like Big Dump which I have used. The poor thing is not many hosts deal with extremely large databases so they are not equipped to handle them. If you have a half gig database and want to come to Data 1 hosting, we welcome you with open arms.
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
Seaside License Holder
|
Thanks for the offer, jim. i will certainly keep data1sys in mind... at the moment my hosts are behaving,, so im going to stick with them for a bit. 
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
Paragon Member
|
Jim wrote:
I am looking forward to the next lesson as well as future ones, especially in light of what I have and am experiencing with my board's database and backups.
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
Jim Administrator
|
Update- I ran into a situation tonight where I simply could not back up the attachments table with phpMyAdmin. So I'm retracting my earlier statement where I said backups with phpmyadmin were OK, evidently it is not especially if you have large attachments. In the above tutorial, if you need to back up a SINGLE TABLE from a database instead of the entire database change the command to this: mysqldump database_name specific_table_name -u database_user -ppassword >dump.sql This will create a file containing the contents of that one table. Good stuff!
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
javal License Holder 
|
Hi Jim, Just curious, do you have any opinions on MySQL Admin found here? Seems to backup my rather large database OK, but thankfully I haven't had to try to restore one yet!
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
Jim Administrator
|
I'll give it a look at, I imagine if it's by the MySQL people it would do the trick. Only thing is it's a GUI that has to be downloaded and installed, usually SSH is already there. I will look at it-
|
||||||||||||||
| |||||||||||||||
| |||||||||||||||
|
Jim Administrator
|
FINALLY Part 2, restoring a saved database. First read the top post here to get the basics of SSH. The only change.... for security reasons we have changed the access port number for SSH; the normal port is 22. I don't want to openly post the new port, if you need to use SSH send me a PM or put a request in private support and I will be happy to give you our port number. Place the backup SQL file in the root directory, which is the first one you see if you log in FTP (the directory with both the public_html and public FTP folder in it). You can put it else where but the paths I am going to give you are for the root directory. If the SQL backup is on a different server, you can easily transfer it to your server using SSH. First drag the backup file in to a web accesible directory (one that your web files are in) on the server with the backup. Open a putty session on the receiving server (where you want the backup file to go) and type:
Once the backup file is in the new server, make sure you have created a blank database for your new account and that you know the log in for it. Here is the magic: mysql -u database_user -ppassword database_name < dump.sql Exact spacing is critical and yes the -p is crammed beside the password, it has to be there exactly like that. Hit enter and do not do anything else until the prompt comes back, it could take up to 5 minutes depending on the size of your database. It will give you no indication that it has done anything but trust that it has. Type exit and hit enter- the window will disappear. I have backed up and restored databases in the 3/4 gig range this way. Again please be careful, SSH is a useful and powerful tool.
|
||||||||||||||
| |||||||||||||||
| Current time is 08:56 am | |
| WowUltra Forums > Hosting > Hosting with Data 1 Systems > Backing up your database with SSH | |
