SQL for beginners! How do you backup and restore a db?

eski2 · #1 01-04-2010, 05:13 PM

I haven't seen any decent sites on it yet and I can't find anyone locally who uses it still (everyone says "I used to know how to do that...")

Looking at what ChaosSlayerZ did below I can see how to multiply something by a value, but I'm not sure how to set something to a constant.

"update loottable_entries set multiplier=multiplier*2;"

I suspect it's something like

Code:

UPDATE `PEQ`.`items` SET `loregroup`='0';

if i wanted to remove the lore tag from everything but i don't know how to backup and restore my version of the peq db if i am stuffing up, and the intro web sites never seem to think a student would need to.

Can anyone tell me how to backup/restore, and if they think my code makes sense? Or at least of a decent training website that covers that sort of thing? I guess backup/restore is the main thing.

**Derision** · #2 01-04-2010, 05:31 PM

I do my backups/restores via the command prompt.

To backup, use mysqldump. My database in this example is called ykesha rather than peq, so wherever you see ykesha, change it to peq or whatever you called your database:

Code:

C:\Users\Steve>mysqldump -u root -p ykesha > ykesha-backup.sql
Enter password: ******

C:\Users\Steve>

Your entire database is now backed up to the file called ykesha-backup.sql It's a text file, so you can browse through it with notepad.

In the event you wanted to restore that backup, you would go into the mysql command line tool, drop the database and re-create it (empty) and then source in the backup you made earlier, e.g.:

Code:

C:\Users\Steve>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.31-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database ykesha;
Query OK, 0 rows affected (0.00 sec)

mysql> create database ykesha;
Query OK, 1 row affected (0.00 sec)

mysql> use ykesha;
Database changed
mysql> source ykesha-backup.sql


<lots of output snipped>

Also, your syntax for changing loregroups is fine. You can omit the `PEQ` prefix if you execute the use peq command first, e.g.:

Code:

C:\Users\Steve>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.31-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use peq
Database changed
mysql> update items set loregroup = 0;
Query OK, 33176 rows affected (1.91 sec)
Rows matched: 76214  Changed: 33176  Warnings: 0

mysql>

eski2 · #3 01-04-2010, 05:40 PM

Thanks very much, I'll give it a go!

**trevius** · #4 01-04-2010, 06:44 PM

Also, if you use Navicat for managing your database, you can just right click on the database and select the "Dump to SQL" option. Then, chose a name to save it as and run it. That will back the DB up just like the command prompt way that Derision described.

Then, to restore your database, you can just right click on the database in Navicat and select the "Run Batch File" option (not sure if that is the exact name of the option, but it is something like that). Then, select the backup you wish to restore to and run it.

Secrets · #5 01-04-2010, 06:52 PM

Something else to note is the --opt flag in MySQL. It makes sourcing a lot faster with MySQLDump as it does more than one entry per line, amongst other things.

Code:

mysqldump -u root -p ykesha --opt

**Rogean** · #6 01-05-2010, 07:49 PM

http://lmgtfy.com/?q=mysqldump