% mysqldump –user=user –password=pass –opt DBNAME > dumpfile.sql

You may also need to specify the –host= parameter to force the hostname you are connecting to. This depends largely on how you’ve setup your user security. This will produce a text file with a series of INSERT/DROP/CREATE SQL statements that will recreate the database.

The –opt flag is very important. This is shorthand to pass in many flags at once; –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset. This ensures that your database is in a good state while the backup is performed, including restricting all write access while the backup is in operation. Any locks placed will be automatically removed when this utility finishes.

Restoring a backup
Restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

This turns off all the checks and auto-commits. This is a safe operation to do if you are fully restoring a database since the previous dump has already been validated for legal keys. If however you are importing, or adding to an existing database, then this step is not advisable.

You can then easily import the SQL file into MySQL using:

% mysql –user=user –password=pass DBNAME < dumpfile.sql

or using

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name
mysql> SET FOREIGN_KEY_CHECKS = 1;
mysql> COMMIT;

Backing up without taking MySQL offline
You cannot perform this with Innodb with the tools supplied with MySQL. However you can get around this limitation by running two MySQL database instances. You setup another MySQL database running on another machine, that is a replication server. Replication features come part of the standard MySQL installation. This replication server can be taken offline for as long as you wish while the backup is performed from the replicated server. Once online, it will resync itself up with any operations that were done while it was offline.

Description:
Help Jinx and Dr. Frantic escape from an alternate universe ruled by Evil Santa and his toy-breaking trolls in this exciting sequel to the original game.

Genre: Adventure Download: 233Kb

Description:
Trick-or-treating takes a turn for the terrifying when our game’s hero Jinx investigates the spooky house on the hill, and discovers who’s been living there all these years

Genre: Adventure Download: 101Kb

http://www.clubpenguin.com/

This is my first post.

Best wishes to everyone.