Migrating your MR database from SQLite to MySQL

Migrating your MR database from SQLite to MySQL

If like me, you've been using sqlite as your primary database and are considering moving to MySQL, the following process might offer some assistance. I'm in no way a DB expert, so you might be able to tweak the process to best suit your environment.

You'll need to put your MR instance in maintenance mode while you carry out the migratrtion. The size of your initial DB will determine the amount of time it takes to complete the migration.

Stopping access to sqlite and creating a valid MySQL DB

• Put the instance in maintenance mode

$ touch /munkireport/storage/framework/down

• Ensure no process is still connected to the database

fuser /munkireport/app/db/sqlite

• Create a .sql dump of the database

sqlite3 db.sqlite .dump > filename.sql

• Ensure mysql is installed on your server and port 3306 is open.
• If you haven't setup a 'root' password, you can find the temporary password as below

grep 'temporary password' /var/log/mysqld.log

• Connect as 'root' using the temporary password and reset it to one of your choice

mysql -u root -p

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

• Create a new MySQL DB called munkireport

mysql> CREATE DATABASE munkireport;

• Edit the .env file to use your newly created munkireport DB
• Run migrations (this will test the connection to the DB and create a valid schema)

If you get an error saying "The server requested authentication method unknown to the client", this can be resolved by running the following within mysql (where password is your new root password).

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Processing data and setting up table queries for your MySQL DB

In the following steps, we'll be working with the '.sql' dump you created earlier. You can open the file in an editor of your choice (my recommendation would be Visual Studio Code).

In our case the .sql dump file had over 2M lines. To help troubleshoot errors as part import process, we converted the single .sql file into multiple .sql files, where each file represented a specific DB table.

Note: As you've already created a new MySQL DB with a valid schema, you wont need any lines that start with 'CREATE TABLE'. These should be commented out to ensure there is no conflict.

To seperate the file into easier table specific queries, do a find all on 'CREATE TABLE' and use the data between recurring statements to create individual files.

All files you generate should start and end with the following syntax:

BEGIN;
INSERT INTO `<table_name>` ...
INSERT INTO `<table_name>` ...
COMMIT;

Once you have your individual files, you're ready to run them as queries against the DB. In our case we used MySQL Workbench to connect to the remote DB over ssh and run the queries.

• Launch MySQL Workbench
• Connect to your DB (either locally or over ssh)
• Load individual .sql file in the 'Query' tab
• Run Query.

Time taken for each query will vary based on file size. For us a 400MB query took approximately 20 minutes to complete.

note You do not need to run a query against the migration table. Also, keep an eye out for other tables like machine_group_orig that no longer exist.

If you get truncation errors while running the query, you can add the following to your .sql file just after the BEGIN statement.

SET @@global.sql_mode= '';

Tip from @tuxudo : Emptying the hash table will force all clients to reupload all their data to the server.

$ mysql -u root -p
mysql> USE munkireport;
mysql> TRUNCATE TABLE hash;
mysql> \q

Once all your queries are complete re-run migrations against the database. Now bring MunkiReport out of maintenance mode.