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