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
Prev Previous Post   Next Post Next
  #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..
 


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 10:56 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 - 2025, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3