Go Back   EQEmulator Home > EQEmulator Forums > Archives > Archive::Support > Archive::Tutorials/Howto's > Tutorials--Outdated Use the Wiki > Tutorials::Submissions

Tutorials::Submissions Submit your tutorials here to be reviewed

 
 
Thread Tools Display Modes
  #1  
Old 04-04-2005, 07:28 PM
sysadmin
Hill Giant
 
Join Date: Feb 2005
Posts: 163
Default backup, restore and dump tables and databases in mysql

Here is a little guide to help those who do not know how to source in or backup stuff with mysql. You can backup or restore a database or table or just a few records to mysql text files that you can always go back to when something goes wrong. Here I will post a few examples of how to use mysql to backup / restore a database or table and how to drop them.

---------------------Mysqldump--------------------------

Mysqldump.exe is a command much like mysql.exe that has to be executed from c:\mysql\bin directory, it has a lot of options but we will see only the ones we need to generate a text file named <filename> where <filename> is the name you give to the dump:

Examples:

Dump doors table:
Quote:
mysqldump -u username -p eq doors > doors_backup.sql
Dump eq database to eqbackup.sql:
Quote:
mysqldump -u username -p eq > eqbackup.sql
Dump object table from velius database:
Quote:
mysqldump -u username -p velius object > object_backup.sql
MySqlDump also can dump subsets of a table:

Dump doors table conditionally with only nadox doors:
Quote:
mysqldump -u username -p "--where=zone='nadox'" velius doors > nadox_doors_backup.sql
Dump doors table conditionally with only poknowledge doors:
Quote:
mysqldump -u username -p "--where=zone='poknowledge'" velius doors > poknowledge_doors_backup.sql
--------Source in and Drop Databases and Tables----------

To restore a sql backup you type this commands from mysql, where you would sign in as root or username with proper rights:

Steps:
1- login. You can log in as root or user with rights to drop database.
Example:
Quote:
c:\mysql\bin\mysql -u root<enter>
2- drop database. DROP DATABASE <DbName>.
Example:
Quote:
DROP DATABASE velius;
3- Source in database. Command: SOURCE <filename> where filename is a valid sql source file.
Example:
Quote:
SOURCE velius_backup.sql;
Drop and restore a database example:
Quote:
mysql> DROP DATABASE eq;
mysql> SOURCE My_leet_db.sql;
You can also drop a table from database velius or any database. Just remember to change database by using the USE command with the database name::
Quote:
mysql> USE velius;
mysql> DROP TABLE items;
and source back in the new table:
Quote:
mysql> SOURCE leet_items.sql;
Note: I usually put the files in c:\mysql\bin because mysql is a little quirky about the slash symbol since it is an escape sequence for it. If you put your files there you will not have any trouble with file paths.

Always remember to backup your data before doing anything with it!!!
__________________
Sysadmin.

Last edited by sysadmin; 04-05-2005 at 01:51 PM..
  #2  
Old 04-05-2005, 06:46 PM
Koshoji
Sarnak
 
Join Date: Jul 2004
Location: Oregon
Posts: 69
Default

Thank you Thank you THANK YOU Sysadmin!!!

This will really help me
__________________
"Always move twice"
-Bruce Juchnik Hanshi
  #3  
Old 04-06-2005, 03:26 AM
sysadmin
Hill Giant
 
Join Date: Feb 2005
Posts: 163
Default

welcome
__________________
Sysadmin.
  #4  
Old 09-26-2005, 03:32 PM
ubatch
Sarnak
 
Join Date: Nov 2002
Posts: 88
Thumbs up

Just wanted to express my appreciation for this short tutorial! VERY AWSOME!
__________________
Malkum [Ex]
Owner of "Caster's Haven"
6.0-DR2 (Velious RC1)
  #5  
Old 09-28-2005, 05:04 AM
GeorgeS
Forum Guide
 
Join Date: Sep 2003
Location: California
Posts: 1,474
Default in process

I'm in the process of making a backup tool this week - among other little db editing tools I'm going to release this week. I hope it will help those who always need a quick backup method.

GeorgeS
  #6  
Old 09-28-2005, 05:33 AM
Koshoji
Sarnak
 
Join Date: Jul 2004
Location: Oregon
Posts: 69
Default

Awesome, Looking foreward to checking it out!!!
__________________
"Always move twice"
-Bruce Juchnik Hanshi
  #7  
Old 09-29-2005, 05:45 AM
sdabbs65
Dragon
 
Join Date: Dec 2003
Location: Earth
Posts: 818
Default all u need

Quote:
Originally Posted by Koshoji
Awesome, Looking foreward to checking it out!!!
mysqldump -u username -p eq > eqbackup.sql

whats so hard about that >?
__________________
hosting Eqemu/Runuo/wow Emulators.

www.cheaterz.info
  #8  
Old 09-29-2005, 06:21 AM
Koshoji
Sarnak
 
Join Date: Jul 2004
Location: Oregon
Posts: 69
Default

Who said it was hard? =) I'm just excited about checking out his new utility, the fruit of his hard work. And if I could back up a db or a table or anything else with a gui instead of by command line I'd probably choose the former. I at least want to check it out. It doesn't interest you at all?

I think it's awesome that people can make all these programs by hand. Wow!
__________________
"Always move twice"
-Bruce Juchnik Hanshi
  #9  
Old 09-30-2005, 10:13 AM
GeorgeS
Forum Guide
 
Join Date: Sep 2003
Location: California
Posts: 1,474
Default easy

As soon as I figure out the ADODB command set for retrieving the schema, then the utility was be next...

The plan is you can make as many backups of any table or the entire db and restore - all in a simple form and button.

GeorgeS
  #10  
Old 10-02-2005, 06:39 PM
GeorgeS
Forum Guide
 
Join Date: Sep 2003
Location: California
Posts: 1,474
Default see link

Just released my database editor

http://www.eqemulator.net/forums/sho...099#post114099


GeorgeS
 


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 03:27 AM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3