Backup/restore using phpMyAdmin/SSH
Backing Up & Restoring a MySQL Database using phpMyAdmin / SSH
================================================== ===== This is just a short mini-tutorial explaining how to backup parts of the mySQL database using phpMyAdmin and also via SSH (Secure Shell) access. This tutorial should be of use to most people, as it's an important part of any website/forum maintenance. So, let's get started... Step 1 - Backing up using phpMyAdmin Note: Most good hosts provide phpMyAdmin (mySQL administration). If you do not know where it is or even if it exists on your server, just get in touch with your host.
Congratulations! You have successfully backed up your database / selected tables! Step 2 - Restoring your database using phpMyAdmin Note: Most good hosts provide phpMyAdmin (mySQL Administration). If you do not know where it is or even if it exists on your server, just get in touch with your host.
Step 3 - Backing up using SSH Note: Some hosts do not offer SSH access, I suggest contacting your host and asking him/her if SSH access is enabled and if so the details. A good program to use for connecting via SSH is PuTTY (Get it at: http://www.chiark.greenend.org.uk/~sgtatham/putty/). Don't know how to use it? Search on Google for a tutorial.
Step 4 - Restoring using SSH Note: Some hosts do not offer SSH access, I suggest contacting your host and asking him/her if SSH access is enabled and if so the details. A good program to use for connecting via SSH is PuTTY (Get it at: http://www.chiark.greenend.org.uk/~sgtatham/putty/). Don't know how to use it? Search on Google for a tutorial.
That's basically it I think, I believe I have covered everything? Anything I missed? Comments or suggestions? Just let me know! Hope this tutorial was as useful to you as it is to me. :) Thanks, -Snake www.metalgearforums.com |
My server just allows me to upload file size = 8Mb max. My sql file = 200 Mb. How do i restore ???
|
Quote:
|
Or you can split every tables with the maximum of 8MB in each file... That's the best way to go for!
|
I tried but i get a error:
Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect -/bin/bash-2.05b$ Whats the problem, i use the command from your post, with my user and database name. |
Hmm that's weird. I'm not sure about that error because it has nothing to do with my tutorial. And I bet you have done something wrong while attempting to restore your forums.
|
Quote:
|
You can also try and use:
http://www.ozerov.de/bigdump.php It will allow for large dumps to be staggered, making it look like smaller pieces. |
I made it as described but my db file is only 1kb after downloading?
|
Should you close your board before backing up?
Thanks! |
Quote:
This is due to a hosting problem, chances are good your host doesnt have something configured correctly. I had the same problem while using dreamhost Quote:
while its not neccassary, but you do run the chance of loosing any posts/changes that are made while backing up |
Quote:
|
Quote:
|
Now i coul dnot backup using SSH FROM the old server because the old server did not support SSH, but i backed up using your phpmyadmin method and through admincp just in-case.
My new host offers SSH so i want to restore using SSH. Is this possible? Also to i type in Code:
run: mysql -u joeuser -p mydatabase < {this/is/my/database/path}/thisthenameofthebackup.sql |
this command mysqldump --opt -u user -p dbname > /path....
Does it compress the database? As my old database size was 929 megs (done through cpanel) and this one is 721 megs. (done through SSH) |
Quote:
|
Quote:
but having backed up and restored numerous times now using ssh and the command you have posted, I have found it to be very smooth and works perfectly. I was using myphpadmin for back ups and bigdump.php for restoring.......but as the database grew I began getting errors and this method became unusable... not to mention I can backup and restore a database in about 10 minutes using ssh, while the other method can take hours. It really sucks to spend 3 plus hours only to find you have a corrupted db |
Some hosts also offer automated MySQL backups at your defined times/when you select, you could always use https://vborg.vbsupport.ru/showthread.php?t=103259 if your worried about lost data.
|
Quote:
|
Whoa that's pretty hot. Is that a feature-already in cPanel 'cause I am always hoping for an automatic backup system for cPanel.
|
Quote:
|
I'm trying to move from one server to another. The server I am moving FROM doesn't have ssh access so I'll have to do it in phpmyadmin. The server I'm moving TO does not have phpmyadmin so I'll have to use the ssh method.
Can I use part 1 of the phpmyadmin tutorial and part 2 of the ssh tutorial? It's okay to combine the two like that, isn't it? |
Hi,
I'm restoring my database using phpMyAdmin and I followed step 2 in the first post when attempting to restored the db. When I do this I get the following error, does anyone know why or how to fix this? Quote:
|
Using SSH to backup your SQL file doens't specifically compress the database, the --opt command optimizes the database and clears the overhead on each table. While it may appear to be smaller, the database is actually the same size, without the overhead.
As for Site5, they actually do use CPanel, it is simply a skinned version of CPanel which is rebranded. They have indeed added their own features, such as FlashBack and such services, however it's easy to see they still use a rebranded version of CPanel :). |
Quote:
Which brings me to my question for anyone who can help - When phpAdmin asks for a filename template for the database backup file there is this message below: Quote:
Okay, I assume this means I can put something in the template which cause the filename to include the date/time, but I don't know what. Anyone help me with this?? Cheers. :D |
Quote:
|
how do you find the correct path??
|
My previous host had ssh access, my new host doesnt, so I used the bigdump method, which seemed to have restored the mysql file on my new database, now I cant view my forums homepage (its blank) however I can log into admin cp without a problem :confused: :confused:
|
I'm having some issues myself...
I'm all backed up via the export function on my old server. Fine. I have a new dedicated server. So I created the database, and user for sql. Problems. I don't think I'm setting up the permissions for that new user correctly... I'm using plesk to add the new database and user. Fine. I try to restore the database via ssh, and I get: Can't create database 'DB_Name'; database exists Ugh... So what is the issue there? If I delete the database, and do it that way, I get the errors saying no database exists. So I feel like i'm stuck. I know there is probably a simple answer, but I just cant find it. Thanks in advance folks. Carter Ah ha.. think I found the answer... in ssh.. on a new server... use the "-f" command to ignore the error messages. That fixed it. sql -f -u USERNAME -p DB_NAME < /Path_to/sql_dump_file.sql @dj look here: http://www.vbulletin.com/docs/html/blank_pages |
Quote:
Quote:
|
I am getting an error Access Denied For User ****
Help Me Only Small Database. |
Were you trying to restore the database through phpMyAdmin or via SSH?
|
OK, I have a problem..What if you dont know the exact path of the db.sql file what would be the best way to find this out through ssh
|
Uhh I'm not really familiar with SSH but why don't you just use phpMyAdmin or the mySQL dumb tool?
|
Which way is more safe?
Through phpMyAdmin or SSH? :confused: Thank you :) |
To be honest with you, I have to say that SSH is much safer and probably easier to use than phpMyAdmin. You might have access to shell on your server so you might wanna contact your host first and gain more information regarding this.
|
I followed all the steps and in the end I downloaded a file with 13,5Mb.
But my forum has 5800 topics and more than 150.000 posts. Can this be right? :confused: PS- I backup through phpMyAdmin. I don?t have SSH access |
Well, if you haven't got any major mods and stuff like that installed on your forum then yeah, how it cannot be right? ;)
|
hi,
I don't get the "hassle" people make about this.Just login to SSH and type Quote:
|
Some hosts/sites don't have access to SSH?
Interesting... I use SSH, Its very easy,quick and nice |
All times are GMT. The time now is 11:37 PM. |
Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|