You are not logged in. Click here to log in.

codebeamer Application Lifecycle Management (ALM)

Search In Project

Search inClear

Tags:  Database MySQL

Create and Load MySQL Dumps

Supported since codebeamer release 21.04 (DOROTHY).


There are two recommended ways to dump database, using:

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

Remember to replace <placeholders> in the examples with appropriate values.

Create Dump

Mysqldump

To use mysqldump, execute the following command:

mysqldump --user <codebeamer_database_user> -p --routines <codebeamer_database_name> > codebeamer_dump.sql
To create a dump, use the cbroot codebeamer database user instead of the MySQL root user.

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.

When creating a dump, Windows Server users should use Command Line instead of PowerShell. PowerShell's utf-16 encoding can cause problems. When importing the dump, utf-8 encoding is needed.

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.

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.

Run the following command against MySQL server:

mysqlcheck --analyze --optimize --all-databases

In the output users must see 'ok' messages for all tables, except mysql.general_logs and mysql.slow_logs.

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