Backing up MySQL database

[dtwilliamsWork]

I need to reinstall DevLake onto a new EKS cluster and don't want to reconfigure all my projects, connections, etc.

I have connected to the MySQL pod using kubectl -n dora exec -it pod/devlake-mysql-0 -- /bin/sh and run the following command

cd && mkdir dump && cd dump
mysqldump --host=localhost --user=merico --password=merico --quote-names --create-options --skip-comments --force --databases lake > devlake.sql

Once the backup has been completed, I can copy it to my local PC using

kubectl cp -n dora devlake-mysql-0:root/dump/devlake.sql ./devlake.sql --retries 10

Once it's finished copying (it's 13GB), I can connect to our other EKS, connect to the MySQL pod using kubectl -n dora exec -it pod/devlake-mysql-0 -- /bin/sh, create a new /root/dump folder, and then upload the devlake.sql image from my local PC.

kubectl cp -n dora devlake.sql devlake-mysql-0:root/dump/devlake.sql --retries 10

Once this has finished uploading, I connect to MySQL pod again and restore the db.

kc -n dora exec -it pod/devlake-mysql-0 -- /bin/sh
cd /root/dump
mysql -u merico -pmerico lake < devlake.sql

This seems to work to an extent, but I am missing all my projects. It has restored the connections, blueprints, and pipelines, but my projects list is empty. The blueprints even show the project names, but when you click on them, it goes to the projects page but can't find the project and just has a spinning loading icon.

Is there a way to backup just the tables required so the backup isn't so large, making it easier and quicker to restore?

[dosubot]

Hey @dtwilliamsWork! I'm here to assist you with any bugs, questions, or contributions. Let's tackle this challenge together!

To ensure that your projects are included in the backup and restore process, you need to specifically include the tables responsible for storing project information. In DevLake, the relevant tables are:

1. project
2. project_metric_settings
3. project_mapping

These tables store various details about projects, including their names, descriptions, and mappings to other data scopes ^[1].

To create a backup that includes only these tables, you can modify your mysqldump command to specify these tables explicitly:

mysqldump --host=localhost --user=merico --password=merico --quote-names --create-options --skip-comments --force lake project project_metric_settings project_mapping > devlake_projects.sql

This command will create a dump file containing only the necessary project-related tables, which should be smaller and quicker to restore. When restoring, use the same process you described, but with the devlake_projects.sql file. This should ensure that your projects are correctly backed up and restored.

To continue talking to Dosu, mention @dosu.

[dtwilliamsWork]

unfortunately, that didn't work. Still not seeing and projects.