Create and Load MySQL Dumps

There are two recommended ways to dump database, using:

• mysqldump, or
• mydumper

Only these methods are supported to create database dumps. Creating archives by zip or tar files directly from the MySQL databases are not working.

Create Dump

Mysqldump

To use mysqldump execute the following command:

mysqldump --user <codebeamer_database_user> -p --routines <codebeamer_database_name> > codebeamer_dump.sql

If the MySQL server runs on a different machine, the mysqldump can be configured with host and port values. For example:

mysqldump --host <mysql_server_host> --port <mysql_server_port> --user <codebeamer_database_user> -p --routines <codebeamer_database_name> codebeamer > codebeamer_dump.sql

Both mysqldump commands above produce a codebeamer_dump.sql file which can be compressed with zip, tar or tar.gz.

For more details of the mysqldump command please click here.

Mydumper

You can use the following alternative MySQL backup method to create database dumps in a multi-threaded and more sophisticated way. This approach relies on mydumper, an open source tool that has significant benefits over the standard MySQL dump command.

For more information on mydumper, please visit: https://github.com/maxbube/mydumper/blob/master/README.md

Case study: https://medium.com/tensult/mydumper-myloader-and-my-experience-of-migrating-to-aws-rds-ff74fc9c1add

Related MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html

Installation

You can use prebuild packages (recommended) from the official mydumper repository or use a dockerized version (or compile from source). Please visit https://github.com/maxbube/mydumper/releases for more details.

Creating database dumps using mydumper

To create database dumps with mydumper, run the following command:

mydumper --host=localhost \
--user=<username> \
--password=<password> \
--database=<codebeamer_database_name> \
--outputdir=/var/tmp/mydump \
--triggers \
--events \
--routines \
--threads=4 \
--trx-consistency-only \
--less-locking \
--chunk-filesize=1000 \
--use-savepoints \
--verbose=3

The result is exported in the /var/tmp/mydump directory. The result can be compressed with zip, tar or tar.gz..

Import Dump

There are two supported ways to import database dumps.

Mysql

Using mysql:

mysql --user <codebeamer_database_user> -p <codebeamer_database_name> < <path_to_codebeamer_dump_sql>

If the MySQL server runs on a different machine, the command can be configured with host and port values.

mysql --host <mysql_server_host> --port <mysql_server_port> --user <codebeamer_database_user> -p <codebeamer_database_name> < <path_to_codebeamer_dump_sql>

Myloader

The myloader is the pair of the mydumper. To import database dumps with myloader, run the following command:

myloader --host=<mysql_server_host> \
--user=<codebeamer_database_user> \
--password=<password>\
--database=<codebeamer_database_name> \
--directory=/var/tmp/mydump\
--compress-protocol \
--overwrite-tables \
--verbose=3 \
--threads=8 > <path_to_import_log> 2>&1

For more information please check the link in the mydumper section.

Obfuscate sensitive data

1. Create a copy of the codebeamer system you want to obfuscate if necessary.
2. Stop the codebeamer test instance.
3. Run the following script against the test database obfuscate_data_mysql. The following command can be used:
   

   mysql --host=<host> --user=<user> --password=<passsword> --database=<your_obfuscated_db> < ./obfuscate_data_mysql.sql

4. Dump the obfuscated database as described above.

Check the obfuscated database

1. After the script execution it is not possible to login to codebeamer because all user credentials (usernames and passwords) are removed.
   • Use the following mysql command in order to set all user passwords to '007':
     

     UPDATE users SET registrydate = null, passwd = lower(sha2(concat('007', id, '700101010000'), 512));

2. Delete the directories under /repository/docs.
3. Start codebeamer.
4. Login to codeBeamer with user-<user-id>/007. For example: user-1/007