 | ||||
| ||||
| | ||||
| ||||
| Welcome to the UltraBB public support forum! Did you know there is an inexpensive totally integrated gallery available for UltraBB? Read more here: Gallery Details |
| Moderated by: Jim |
|
||||||||||||||
| Backing up your database with SSH | Rating: 
|
| Author | Post |
|---|
| Posted: Mon Nov 5th, 2007 01:35 pm |
|
1st Post |
|
Jim Father 
|
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
|
||||||||||||||
| |||||||||||||||
| Posted: Sun Nov 11th, 2007 12:43 am |
|
2nd Post |
|
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
|
|||||||||||||
| ||||||||||||||
| Posted: Sun Nov 11th, 2007 12:49 am |
|
3rd Post |
|
Jim Father
|
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.
|
||||||||||||||
| |||||||||||||||
| Posted: Tue Nov 13th, 2007 02:17 pm |
|
4th Post |
|
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.
|
|||||||||||||
| ||||||||||||||
| Posted: Tue Nov 13th, 2007 09:02 pm |
|
5th Post |
|
Jim Father
|
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.
|
||||||||||||||
| |||||||||||||||
| Posted: Thu Nov 15th, 2007 08:58 pm |
|
6th Post |
|
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. 
|
|||||||||||||
| ||||||||||||||
| Posted: Tue Nov 20th, 2007 02:23 pm |
|
7th Post |
|
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.
|
||||||||||||||
| |||||||||||||||
| Posted: Wed Dec 5th, 2007 04:30 am |
|
8th Post |
|
Jim Father
|
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!
|
|||||||||||||
| ||||||||||||||
| Posted: Mon Dec 10th, 2007 08:36 pm |
|
9th Post |
|
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!
|
||||||||||||||
| |||||||||||||||
| Posted: Mon Dec 10th, 2007 10:36 pm |
|
10th Post |
|
Jim Father
|
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-
|
|||||||||||||
| ||||||||||||||
| Posted: Sun Jan 27th, 2008 11:36 pm |
|
11th Post |
|
Jim Father
|
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.
|
||||||||||||||
| |||||||||||||||
| Posted: Thu Oct 29th, 2009 02:08 am |
|
12th Post |
|
Jim Father
|
I just read this and 3/4 gig? HA I'm up to over 3 gigs using this method now.
|
|||||||||||||
| ||||||||||||||
| Current time is 02:44 am | |
| UltraBB Forums > Hosting > Hosting with Data 1 Systems > Backing up your database with SSH | Top |
 |
