How to Back Up (Dump) MySQL Database

How do you back up a MySQL 5.5 database.

DISCLAIMER:
This knowledgebase (KB) article is provided for informational purposes only and as a courtesy service to you, our customer. GWAVA Technical Support does not have any database administration (DBA) expertise, nor does it provide DBA services or support. GWAVA is not responsible for the results of implementing any of the concepts contained in this KB article. Implementation of any of the concepts suggested in this KB article shall be done entirely at your own and sole risk, and GWAVA does not provide any kind of warranties whatsoever resulting from your decision of implementing any of the KB article’s concepts. It is up to you to do any research and to ensure yourself that any implementation and setup of any of the KB article’s concepts in your database system is correctly and properly executed. It is imperative that you have backups of your database system and storage directory before making any implementation. If you don’t have any DBA expertise, you should consult with a DBA expert before any implementation of the KB article’s concepts.  Under no circumstances, shall GWAVA, or any of its employees, be liable, in contract, tort, delict or otherwise, whether negligence is provable or not, for any direct, indirect, incidental, special, punitive, consequential or other damages, loss, cost or liability whatsoever that would result from or are related to the implementation of any of the concepts suggested in the KB article.

To the extent permitted by applicable law, GWAVA shall not be liable to you for any special, consequential, direct, indirect or similar damages, including any loss of data, arising out from migrating any type of messages, attachments, database, metadata in your Retain system to another server and/or location.

Resolution:

For a comprehensive guide on how to do a mysql dump check out their website: https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

Before dumping the database, ensure that no archive or deletion jobs are running!*

1.  Stop Tomcat

2.  With MySQL running, perform a dump of the database:  mysqldump -u root -p[database name] > [/path]/[dump file name]

Linux Example: 

1. Open the command line.
2. Type*: mysqldump -u root -pretain >/retain/backup/mysql/retain.2013-10-08.sql

To view the progress of the dump:

1. Open a new terminal window and change to the dump directory.
   Example: cd/retain/backup/mysql/
2. Type: watch ls -lh [ENTER]

You will see the file grow.  It will end up being somewhere around half the size of the original database (give or take).  You will know it is done when the file size stops progressing for a while. 

You can also tell it is done when the dump brings you back to a command prompt.  You will not see any messages indicating it is done unless you use one of the verbose modifiers (it is very verbose, not recommended because of the amount of data that will show on screen).  However, if there was an error that prevented it from completing (extremely rare), you'll see the error message above the command prompt it left you at.

Windows Example: 

1.  Open a command line window.

2.  Change directories to:  C:\Program Files\MySQL\MySQL Server 5.x\bin  (the beginning of the path depends upon where MySQL was installed).

3.  Type*:  mysqldump -u root -pretain > d:\retain\backup\mysql\retain.2013-10-08.sql

You can tell it is done when the dump brings you back to a command prompt.  You will not see any messages indicating it is done.  However, if there was an error that prevented it from completing (extremely rare), you'll see the error message above the command prompt it left you at.

The dump file It will end up being somewhere around half the size of the original database (give or take)  That's because all it contains is the SQL commands to add the data back into the database (create table, inserts, etc).

3.  Start up Tomcat.

* Note: If you're using a script to do a backup of the database while Retain is live, consider using the --single-transaction switch.  This prevents you from having to stop Tomcat while the dump is being made (reference https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction)

Example: mysqldump --single-transaction -u root -p retain > /retain/backup/mysql/retain.2013-10-08.sql

This article was originally published in the GWAVA knowledgebase as article ID 2217.