Create and Load MySQL Dumps

Supported since codebeamer release 21.04 (DOROTHY).

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, click here.

Mydumper

The following alternative MySQL backup method can be used 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.

Use mydumper v14 or later versions.

For more information on mydumper, see 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

Prebuild packages can be used (recommended) from the official mydumper repository, or a dockerized version (or compile from source). For more details, see https://github.com/maxbube/mydumper/releases.

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:

1. Mysql
2. Myloader

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

Check the link in the mydumper section for more information.

Obfuscate Sensitive Data

THE FOLLOWING PROCESS MODIFIES THE DATA IN THE DATABASE, AND IT IS NOT POSSIBLE TO REVERT MODIFICATIONS. DO NOT USE IT ON PRODUCTION ENVIRONMENT!

1. Create a copy of the codebeamer system to be obfuscated, if necessary.
2. Stop the codebeamer test instance.
3. Run the following script against the test database obfuscate_data_mysql.sql. 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