Redmine Database Migration DB by Someshwar Mirge - June 22, 2021June 27, 20210 Often times it happens that we need to migrate from one Redmine installation to another Redmine. Specially when upgrading to latest version. But we want to keep our database intact and want to migrate all the data to new setup. There are lot of guides on internet about redmine database migration, this blog post talks about traditional datbase migration along with case where projects in redmine instance needs to be merged with projects running in production environment. Step 1: BACKUP the existing database First of all take a backup of existing database.Backup is necessary in both cases .Whether we are installing new redmine on same system or if it is on new system . Use following command to create a backup in form of .gz file . /usr/bin/mysqldump --no-tablespaces -u mysql_user -p'password' database | gzip > /var/data/redmine/backup/redmine_date +%Y-%m-%d.gz you can change username , password ,database name , and location where backup is created . Note : You can use scp command to copygenerated backup file from one server to another . Follow below syntax. scp [email protected]:filepath/mysqlbackup.sql path_to_new_location scp local_path/mysqlbackup.sql [email protected]:filepath/ First is used when want to import database , second one is used when want to send database to new server. step 2: Setup of Migration Plugin Redmine Merge plugin can be found Here. You can follow installation steps from github repository README file . Step 3 : Executing Migration Before executing migration make sure you have both databases on same system and you have added source database entry in database.yml file . Then cd to root of redmine and run following command to get details of database migration. rake redmine:data_report RAILS_ENV=production Output should look like this . User: 109 CustomField: 93 Tracker: 40 IssueStatus: 48 Role: 22 Project: 35 Version: 47 News: 1 IssueCategory: 56 Enumeration: 11 Issue: 1102 IssueRelation: 217 Journal: 1455 JournalDetail: 2288 TimeEntry: 47 Document: 4 Wiki: 25 WikiPage: 0 WikiContent: 0 Attachment: 25 Run this commands to start migrating . rake redmine:merge_redmine RAILS_ENV=production Note : If you have stopped using old redmine and if the new redmine DB is completely empty then migration will have no errors at all. But if you have used new and old redmine and then want to migrate , it becomes a little tricky , because tables from both databases will have entries with same id . Which is not allowed as table ID is a primary key . So we have to manually fix in that case . Step 4 : Manually fixing DB issues ( optional ) This step is only necessary if your migration did not complete successfully . You can manually open mysql console as follows . 1- Note down the entry which has the same ID . 2-remove entry with that ID from source table and add it to bottom to table with new ID . OR we can use REPLACE INTO and INSERT INTO to merge tables from both databases one by one. mysql> REPLACE INTO redmine.issues SELECT * FROM redmine_mig.issues; ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> USE redmine_mig; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> ALTER TABLE issues ADD sprint_id int; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE issues ADD position int; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> REPLACE INTO redmine.issues SELECT * FROM redmine_mig.issues; Query OK, 1431 rows affected (0.09 sec) Records: 1129 Duplicates: 302 Warnings: 0 mysql> DELETE FROM redmine_mig.trackers WHERE id=31; Query OK, 1 row affected (0.16 sec) mysql> INSERT INTO `trackers` (`id`, `name`, `description`,`position`, `fields_bits`, `default_status_id`) VALUES (46, 'Star of Ethosh-May 2021', NULL ,29, 255, 1) -> ; Query OK, 1 row affected (0.11 sec) mysql> REPLACE INTO redmine.news SELECT * FROM redmine_mig.news; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> REPLACE INTO redmine.journal_details SELECT * FROM redmine_mig.journal_details; Query OK, 2695 rows affected (0.04 sec) Records: 2314 Duplicates: 381 Warnings: 0 mysql> REPLACE INTO redmine.documents SELECT * FROM redmine_mig.documents; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> REPLACE INTO redmine.attachments SELECT * FROM redmine_mig.attachments; Query OK, 32 rows affected (0.01 sec) Records: 26 Duplicates: 6 Warnings: 0 Your old Database is now merged to new database. Please login to new rdemine instance to confirm if old data is there in projects along with the new data.