Backup/restore using phpMyAdmin/SSH

[Snake]

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.


Login to phpMyAdmin.
Select the database you wish to use.
On the navigation bar on the top, select "Export" (besides SQL, Structure, Search, Query... etc.)
You should now be presented with a nifty little page which shows all the tables in the database, SQL Options and Save Types. *
To backup the whole database, click "Select All" under the list of tables in the page.
To backup a selected few, just hold down CTRL on your keyboard and select the tables you wish to backup (release the key when all selecting is done).
SQL Options can be left as default, but I suggest ticking the "Add DROP TABLE" option as if you are restoring a backup on a database that already exists and has the table in it - you will get a lot of errors! This way, by ticking the "Add DROP TABLE" option you will avoid the errors. For more information on the options just click the little "?" icon after SQL Options.
If you want to save the backup as a file (recommended) then tick "Save as file" - Leave file name as is or edit for your needs. Select "gzipped" as compression. Now click "Go". If asked, choose "Save to disk" and save it wherever on your computer (it may take sometime depending on the size).
If you want to show the whole backup SQL on your browser window, don't tick "Save as file" - once you have selected the tables you wish to backup (explained in step 5/6) click "Go". The page will now change and should show the SQL information (it may take sometime depending on the size). Copy and paste it to a text file or do whatever you want with it! Note: Yes, the tables of the database will be shown to the far left in a frame, but they will also be displayed in a different form on this new page to the right in phpMyAdmin.

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.


Login to phpMyAdmin.
Select the database you wish to use.
On the navigation bar on the top select "SQL" (besides Export, Structure, Search, Query... etc.)
You should now be presented with a little page which allows you to run SQL query/queries on the database, either by inputting the query/queries to the input box or by locating a text file on your computer.
You now have 2 options: 1) Paste the SQL which you backed up earlier in the "Input Box" and click "Go" or 2) If you saved the file on your PC then use the option below the first one; Click Browse > Locate the File > Click "Go". (Note: Most servers set a "Max File Size" for uploading the SQL, the size appears beside the Browse button.)
It may take sometime for the file to be uploaded and fully run, so give it a chance. Once it worked, it will bring you back to the same page and should confirm if all went well "Your SQL-query has been executed successfully: The content of your file has been inserted." Congratulations! If all went well, you have successfully restored your backed up database!

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.

Connect to your host via SSH, login and run: mysqldump --opt -u user -p dbname > {path}/backup.sql
You will be asked to type out your password once you run that command, do so and click enter. It may take some time for it to respond, but once completed you should be brought back to where you started (bash/command line). Congratulations! You successfully backed up your database using SSH.

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.
Connect to your host via SSH, login & run: mysql -u user -p dbname < {path}/backup.sql
You will be asked to type out your password once you run that command, do so and click enter. It may take some time for it to respond, but once completed you should be brought back to where you started (bash/command line). Congratulations! You have successfully restored your database using SSH.

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 (http://www.metalgearforums.com)

[RFViet]

My server just allows me to upload file size = 8Mb max. My sql file = 200 Mb. How do i restore ???

[The Chief]

My server just allows me to upload file size = 8Mb max. My sql file = 200 Mb. How do i restore ???

You will have to use SSH, or ask your host to raise the max limit...

[Snake]

Or you can split every tables with the maximum of 8MB in each file... That's the best way to go for!

[Rhoads]

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.

[Snake]

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.

[RFViet]

Or you can split every tables with the maximum of 8MB in each file... That's the best way to go for!

Yeah , that's the way i want . How to do that ???

[Logan70]

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.

[Ramsesx]

I made it as described but my db file is only 1kb after downloading?

[sinaluna]

Should you close your board before backing up?

Thanks!

[LincolnForums]

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.


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

Should you close your board before backing up?

Thanks!


while its not neccassary, but you do run the chance of loosing any posts/changes that are made while backing up

[Snake]

Should you close your board before backing up?

Thanks!
Well I'd say yes just in case you won't loose any forum data (such as threads, posts, members, etc) while performing a backup. ;)

[RFViet]

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.
thanks !!:cool:

[imported_infitech]

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 run: mysql -u joeuser -p mydatabase < {this/is/my/database/path}/thisthenameofthebackup.sql

[Hornstar]

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)

[Bubble #5]

Select &quot;gzipped&quot; as compression.
Are you sure about that? vB suggests to not use any compression at all when saving the database...

[Gunshot]

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)

I notice that the database size is smaller when backing up with ssh...

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

[TrueDeath]

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.

[Bubble #5]

Some hosts also offer automated MySQL backups at your defined times/when you select
Site5 has a unique feature called 'Flashback' which automatically backs up every file that is one the server. You don't have to do anything. To restore a file simply click it from a master list and it's restored :bunny:

[Snake]

Whoa that's pretty hot. Is that a feature-already in cPanel 'cause I am always hoping for an automatic backup system for cPanel.

[Bubble #5]

Whoa that's pretty hot. Is that a feature-already in cPanel 'cause I am always hoping for an automatic backup system for cPanel.
They don't use cPanel, they use their own scripts (called netadmin) and I must say it's the nicest (and easiest to use) control panel that we've ever seen :banana:

[furst]

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?

[Chevy II]

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?


SQL query:

_sitedb_.sql

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_sitedb_.sql' at line 1

[SixteenOhNine]

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 :).

[G_Man]

Site5 has a unique feature called 'Flashback' which automatically backs up every file that is one the server. You don't have to do anything. To restore a file simply click it from a master list and it's restored :bunny:

Site5 still recommends you use phpAdmin to backup your databases though.....


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:

This value is interpreted using strftime, so you can use time formatting strings. Additionally the following transformations will happen: __SERVER__/server name, __DB__/database name. Other text will be kept as is.


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

[Bubble #5]

Site5 still recommends you use phpAdmin to backup your databases though.....
That's because Flashback doesn't back up databases yet ;)

[frandy]

how do you find the correct path??

[webgeek247]

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:

[CBrown]

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

[webgeek247]

@dj

look here:
http://www.vbulletin.com/docs/html/blank_pages

Thanks for that CBrown, it got the homepage displaying however I'm getting the following error now on the top of my forums homepage :hurt:

Warning: Call-time pass-by-reference has been deprecated - argument passed by value; If you would like to pass it by reference, modify the declaration of fsockopen(). If you would like to enable call-time pass-by-reference, you can set allow_call_time_pass_reference to true in your INI file. However, future versions may not support this any longer. in /websites/LinuxPackage02/xx/xx/xx/xxxxxx/public_html/forums/index.php(485) : eval()'d code on line 18

[mojo8850]

I am getting an error Access Denied For User ****

Help Me Only Small Database.

[Snake]

Were you trying to restore the database through phpMyAdmin or via SSH?

[Aclikyano]

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

[Snake]

Uhh I'm not really familiar with SSH but why don't you just use phpMyAdmin or the mySQL dumb tool?

[taumau]

Which way is more safe?

Through phpMyAdmin or SSH? :confused:

Thank you :)

[Snake]

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.

[taumau]

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

[Snake]

Well, if you haven't got any major mods and stuff like that installed on your forum then yeah, how it cannot be right? ;)

[Delphiprogrammi]

hi,
I don't get the "hassle" people make about this.Just login to SSH and type


mysqldump --opt -Q -u mysql_username -p mysql_databasename > file.sql
enter password:Mysql_databasepassword


done as easy as that

[Shazz]

Some hosts/sites don't have access to SSH?
Interesting...

I use SSH, Its very easy,quick and nice

[Coders Shack]

To login and backup a database in one line:
mysqldump --password=[password] -h [hostname] -u [username] [databasename] > [backupfile.sql]To restore a database in one line:
mysql --password=[password] -h [hostname] -u [username] [database_to_restore] < [backupfile]

[rooney72]

I followed step by step the tutorial, SOmehow I might be doing something wrong.

When I imported my very small testing database onto a new server it said it succeeded. When I go into the forum page I expect to see the old members and old threads. Right? After all if I want to transfer my forum onto a new server, then what's the scope of backup.

But in my case NOT. I only see the freshly installed vbulletin on my new server but not the treads and users.

What am I doing wrong?

In just a few words: I want to export my data and transfer it on a new database/server so I won't lose anything from my old forum

[Mazoo]

That's very useful tutorial .. thanks Snake ...

I'm running a big forum ( DB =~ 2 GB )
and for security I need to know the SSH command that enables me to save a redundant copy of the database their ..
could anyone tell us about their experience in that field ?
And if by chance anyone made a SSH or Linux script or maybe Cron that's automate that process , I hope that I know about it ..

Regards ,

[Snake]

I followed step by step the tutorial, SOmehow I might be doing something wrong.

When I imported my very small testing database onto a new server it said it succeeded. When I go into the forum page I expect to see the old members and old threads. Right? After all if I want to transfer my forum onto a new server, then what's the scope of backup.

But in my case NOT. I only see the freshly installed vbulletin on my new server but not the treads and users.

What am I doing wrong?

In just a few words: I want to export my data and transfer it on a new database/server so I won't lose anything from my old forum Did you try restoring the database via phpMyAdmin or SSH?

That's very useful tutorial .. thanks Snake ...

I'm running a big forum ( DB =~ 2 GB )
and for security I need to know the SSH command that enables me to save a redundant copy of the database their ..
could anyone tell us about their experience in that field ?
And if by chance anyone made a SSH or Linux script or maybe Cron that's automate that process , I hope that I know about it ..



Regards , Uhh I'm not really sure what you're talking about.

[Masiello]

Hello, please any other split tool DB a part BigDumb? That I tried some month a go
For some reason it dont works good for me, I need to remove some queries from my DB that block the importing process.

[ryanj19]

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.

I noticed someone asked this question, but it doesn't look like it got answered. Anyway, the MySQL command could be more complete. In some cases the server might not be localhost. Therefore, the command should look like this:

mysql -u MyUserName -p MyDBName -h mysql.myhost.com < /full/path/to/sqldump.sql

[letsjoy]

thanks for post

[Raptor]

for those with SSH access its very easy to backup and to compress (gzip took my DB from 1.4GB to 327MB - Took about 3 minutes

mysqldump --opt -Q -u <username> -p <database name> | gzip > database.sql.gz

to restore....

gunzip < database.sql.gz | mysql -u <username> -p <database name>

hope this is clear and helps someone out

[Snake]

Yeah, but I don't think most of the hosts out there provide SSH access especially when they're running a small forum. That's why the either way to backup and/or restore is by phpMyAdmin or some other SQL tool. Shell is pretty much useful for a very large forum.

You're quite welcome, letsjoy.

[mokujin]

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.
Connect to your host via SSH, login & run: mysql -u user -p dbname < {path}/backup.sql
You will be asked to type out your password once you run that command, do so and click enter. It may take some time for it to respond, but once completed you should be brought back to where you started (bash/command line). Congratulations! You have successfully restored your database using SSH.

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 (http://www.metalgearforums.com)

How does bash/command line. look like or what is it???

[nickson]

my host has 2mb of limited to upload sql, and my database is worth 22mb, how could i go about this now? I am noob for restoring :(

[dr.msk]

thanks

[kabir]

To login and backup a database in one line:
mysqldump --password=[password] -h [hostname] -u [username] [databasename] > [backupfile.sql]To restore a database in one line:
mysql --password=[password] -h [hostname] -u [username] [database_to_restore] < [backupfile]

I use This Command But Server Says Me

gsmrapid@gsmrapid.com [~]# mysql --password=bH>-cgSlQ9d/ -h localhost -u gsmrapid_forum gsmrapid_forum < gsmrapid_forum.sql
-jailshell: -cgSlQ9d/: Is a directory
gsmrapid@gsmrapid.com [~]#

[ehabfouad22]

I've created a backup zipped file in (MYI, MYD, FRM) formats. Its size was about 120 MB. Then I created a backup of the same database through phpMyAdmin in a .sql format, with a size of 16 MB only! Does that mean that my backup with phpMyAdmin was incomplete? Or it's normal to have a .sql file with a size much less than the actual size of direct database files?

[basketmen]

guys i want to move to another host, i already backup the mysql using ssh and get the .sql file, downloading to computer, and i want to uploading to new host using ftp and restore it there using ssh too

my question is in uploading to new host using ftp part, do i need using ASCII mode or binary mode when uploading the .sql file?

[almajrouh]

my be use scrip like bigdump.php