backup, restore and dump tables and databases in mysql

[sysadmin]

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:

mysqldump -u username -p eq doors > doors_backup.sql

Dump eq database to eqbackup.sql:

mysqldump -u username -p eq > eqbackup.sql

Dump object table from velius database:

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:

mysqldump -u username -p "--where=zone='nadox'" velius doors > nadox_doors_backup.sql

Dump doors table conditionally with only poknowledge doors:

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:

c:\mysql\bin\mysql -u root<enter>

2- drop database. DROP DATABASE <DbName>.
Example:

DROP DATABASE velius;

3- Source in database. Command: SOURCE <filename> where filename is a valid sql source file.
Example:

SOURCE velius_backup.sql;

Drop and restore a database example:

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

mysql> USE velius;
mysql> DROP TABLE items;

and source back in the new table:

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!!!

[Koshoji]

Thank you Thank you THANK YOU Sysadmin!!!

This will really help me

[sysadmin]

welcome

[ubatch]

Just wanted to express my appreciation for this short tutorial! VERY AWSOME!

[GeorgeS]

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

[Koshoji]

Awesome, Looking foreward to checking it out!!!

[sdabbs65]

Originally Posted by Koshoji

Awesome, Looking foreward to checking it out!!!

mysqldump -u username -p eq > eqbackup.sql

whats so hard about that >?

[Koshoji]

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!