Wiki Spaces

Documentation
Projects
Resources

Get Help from Others

Q&A: Ask OpenMRS
Discussion: OpenMRS Talk
Real-Time: IRC Chat | Slack

Documentation

Page tree
Skip to end of metadata
Go to start of metadata

It is good practice to back up your server regularly. If you are using MySQL, the mysqldump command makes it very easy.

The typical command to back up your entire database is:

mysqldump -u root -p -e --single-transaction -q -r"/home/ben/backup.sql" openmrs

  • -uroot = use username of root
  • -p = ask for password
  • -e = extended inserts (makes the dump 10x smaller)
  • --single-transaction = run this in a single transaction (to prevent corruption)
  • -q = quick
  • -r = file location to store
  • openmrs = name of your database

If you want to automate it, you can provide the username and password for mysql in the command:

mysqldump -u root -pPA$$W0rd -e --single-transaction -q -r"/home/ben/backup.sql" openmrs

If you want to exclude a few of the extremely large tables, you can do that too

mysqldump -u root -p -e --single-transaction -q -r"/home/ben/backup.sql" --ignore-table=hl7_in_archive --ignore-table=formentry_archive openmrs

If you are on a linux variant, you can insert the date automatically into the backup sql filename:

mysqldump -uroot -p -e --single-transaction -q -r"/home/ben/backup`date +%Y%m%d`.sql" openmrs

If you are on windows, you can insert the date automatically too, its just a little uglier:

mysqldump -u root -p -e --single-transaction -q -r"C:\backup%date:~10,4%%date:~4,2%%date:~7,2%" openmrs

After creating the backup .sql file, you can zip it and store it for later use.

Restoring From A Backup File

The now get the data out of an archived sql backup file and into a new database its merely three simple steps:

  1. Create your new database
    • create database openmrsbackuprestoration default character set utf8;
  2. Tell mysql to work against that new database
    • use openmrsbackuprestoration
  3. Run the sql file
    • source backup.sql

Now wait for 1, 20, or 30 minutes and you'll have all your data back.

  • No labels

4 Comments

  1. Unknown User (xyntax)

    im using mysql 5.6 and the syntax wont seem to work..

    anyone knows the right syntax in backing up the database?

    1. Unknown User (k.joseph)

      I expect mysqldump db_name > /pathTo/backup-file.sql... to be working well, please send us any syntax error messages sent back from mysql 5.6, otherwise consider looking into the mysql 5.6 manual

      1. Unknown User (xyntax)

        hi kaweesi, i look back to mysql manual and found the answer. seems like im trying to enter it in the mysql>. thanks a lot for the support!
        kind regards

        1. Unknown User (k.joseph)

          O sorry, the command is not supposed to be entered into mysql shell/client/mysql> but rather the normal shell (e.g. terminal) without loading another shell inside it; since mysqldump is a seperate utility stored at:  .../bin/mysqldump of your mysql server package like: /opt/lampp/bin/mysqldump on my machine.

          Otherwise, glad you went about the problem Unknown User (xyntax).