 | ||||
| ||||
UltraBB Forums > Data 1 Systems Hosting > Hosting with Data 1 Systems > Backing up your database with SSH |
| | ||||
| ||||
UltraBB Forums > Data 1 Systems Hosting > Hosting with Data 1 Systems > Backing up your database with SSH |
| 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 | Page: 1 2   |
|
|||||||||||||
| Backing up your database with SSH | Rating: 
|
| Author | Post |
|---|
| Posted: Mon Nov 5th, 2007 02:35 pm |
|
1st Post |
|
Jim I work here 
|
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 01: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 01:49 am |
|
3rd Post |
|
Jim I work here
|
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 03: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 10:02 pm |
|
5th Post |
|
Jim I work here
|
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 09: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 03: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 05:30 am |
|
8th Post |
|
Jim I work here
|
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 09: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 11:36 pm |
|
10th Post |
|
Jim I work here
|
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: Mon Jan 28th, 2008 12:36 am |
|
11th Post |
|
Jim I work here
|
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 03:08 am |
|
12th Post |
|
Jim I work here
|
I just read this and 3/4 gig? HA I'm up to over 3 gigs using this method now.
|
|||||||||||||
| ||||||||||||||
| Posted: Thu Jun 2nd, 2011 04:46 am |
|
13th Post |
|
Jim I work here
|
Just did a 5+ gig.
|
||||||||||||||
| |||||||||||||||
| Posted: Thu Jun 2nd, 2011 09:26 am |
|
14th Post |
|
Robert Member 
|
Interesting if a bit daunting reading Jim. Do I have to concern myself with this as I know that you back up everything on a regular basis. Knowing my famed ability with computers I can see myself getting into awful trouble with having a go, even though your instructions are clear.
|
|||||||||||||
| ||||||||||||||
| Posted: Thu Jun 2nd, 2011 04:51 pm |
|
15th Post |
|
Marty Helpy Helper 
|
SSH can be really dangerous if you make a mistake. I am familiar with SSH, but trust Jim with my Backups.... but then again.. If he messes up.... I know where he LIVES... Just Kidding Jim.... or am I? :-)
|
||||||||||||||
| |||||||||||||||
| Posted: Fri Jun 3rd, 2011 06:39 pm |
|
16th Post |
|
wingnutter Forever Learning 
|
Would a mirror server make more sense than backups? One of my forum members asked me about this a while back, saying that a lot of sites use mirror servers so that if one breaks or gets hacked the other one is still up and 100% current, so you don't have to remember to make backups. Apart from the obvious double-hosting costs, what are the pros and cons to this?
|
|||||||||||||
| ||||||||||||||
| Posted: Fri Jun 3rd, 2011 07:17 pm |
|
17th Post |
|
Marty Helpy Helper
|
The double costs is the only issue with it. Downloading your own backups is much cheaper (FREE!). I only see that as needed if your business is based on your site being online. If you are losing revenue from your site being down, then its a problem.
|
||||||||||||||
| |||||||||||||||
| Posted: Fri Jun 3rd, 2011 08:14 pm |
|
18th Post |
|
Jim I work here
|
I'm actually heading in that direction but it is in the future. I have to increase sales a little first and I'm an advertising turd. I hate advertising, I hate ads, banners, cold e-mails. But it looks like word of mouth is only going to get me so far. It's like setting up a raid array but instead of with drives with entire servers. Load balancing becomes possible and we all win.
|
|||||||||||||
| ||||||||||||||
| Posted: Fri Jun 3rd, 2011 10:39 pm |
|
19th Post |
|
Marty Helpy Helper
|
As a service (provided by Jim!), I think it works great. As an individual site, that is a different story.
|
||||||||||||||
| |||||||||||||||
| Posted: Tue Jun 7th, 2011 11:06 am |
|
20th Post |
|
wingnutter Forever Learning
|
Jim wrote:... I hate ads, banners..... A lot of us do. But I think most of us wouldn't mind putting a small UltraBB logo on the bottom our forums in place of the current text link. Logos are proven to attract more hits than text links.
|
|||||||||||||
| ||||||||||||||
| Current time is 07:43 pm | Page: 1 2 |
| UltraBB Forums > Data 1 Systems Hosting > Hosting with Data 1 Systems > Backing up your database with SSH | Top |
 |
