Sign in Subscribe
computers

Update All The Things - MySQL Migration

Trevor Steen

• 3 min read
Update All The Things - MySQL Migration

My Ghost installation recently informed me that an update was available. I usually try to take care of these as quickly as possible and have a little script the did all the updating for me. So I blindly kicked off my script and promptly bricked my Ghost install.

gateway

Ghost Update

Turns out that at some point I totally missed the major jump to Ghost v1 from v0. When I ran my script, the script attempted to make the major leap across breaking updates. Once I sorted that out and found the documentation, I realized there was a proper process for this activity.

I downgraded my current Ghost version back to what I had previously, backed up all my info through the web utility and the content folder, and followed the documentation on installing the 'new hotness'. This included installing and transitioning to MySQL from the previous database that was embedded with the old v0 installs.

After a few hours of firewall troubleshooting (damn you IPTables) I finally had my new Ghost installation running, imported my backups, and was back up in business.

Confluence Migration

Now that I had my new shiny MySQL database running, it seemed like a good idea to migrate my Confluence install from Postgres, which I was currently running, to MySQL.

As with all things Atlassian, the documentation on how exactly to do this was scattered across the internet. I had 7 or 8 tabs open for the hours that I was working on this migration. When really the steps were relatively simple and include a re-install of Confluence.

  1. Backup your data. Even though I do daily backups, I still followed the recommended process and created an XML backup from the "Backup & Restore" section of the admin page. This will create the backup on the host file system so DO NOT blow away your old install.
  2. If migrating, move your old installation to a backup directory.
    • mv /opt/atlassian/confluence /opt/atlassian/confluence-backup
    • mv /var/atlassian/application-data/confluence /var/atlassian/application-data/confluence-backup
  3. Confluene recommends taking note of any user-installed add-ons but my backup had them already.
  4. Install MySQL if you have not alrady and configure it. The configuration I needed only included a few commands but they gave me a ton of trouble. After many rounds of trying to make this all work, I started everything over and it miraculously worked. Perhaps it was that I did the database creation command as confluenceuser and root for the others (I really don't know, probably voodoo magic). https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html
    • SET default_storage_engine=INNODB;
    • CREATE DATABASE confluence CHARACTER SET utf8 COLLATE utf8_bin;
    • GRANT ALL PRIVILEGES ON confluence.* TO 'confluenceuser'@'localhost' IDENTIFIED BY 'password';
  5. Download the JDBC driver.
  6. Download and then run the Confluence Installer selecting the appropriate options, do NOT start the service at the end.
    • wget https://www.atlassian.com/software/confluence/downloads/binary/atlassian-confluence-6.4.3-x64.bin
    • chmod +x atlassian-confluence-6.4.3-x64.bin
    • ./atlassian-confluence-6.4.3-x64.bin
  7. Unzip the JDBC driver you downloaded earlier, go into that directory and copy the .jar file to /opt/atlassian/confluence/confluence/WEB-INF/lib.
  8. Now start Confluence. /etc/init.d/confluence start should do it unless you migrated and still have the old install hanging around, in this case, the service name will get a number ie. confluence1. You can see what you have available by listing out /etc/init.d.
  9. With Confluence running, navigate to your page and complete the configuration wizard selecting MySQL as the database and the JDBC connection option. If you get a timeout error, wait a few minutes and refresh the page to see if it clears as there is a fair amount of background work Confluence is doing. NOTE: I got a TON of errors at this step, not sure why, but recreating the database seems to be what finally did it.
  10. On the last step of the wizard, it will ask you if you want to restore. To do this, copy your backup file to /var/atlassian/application-data/confluence/restore/. Depending on when you do this, you may have to refresh the page. Follow the prompts.
  11. Lastly, update your Base URL in the General Config and you should be good to go.

Hope this is helpful to someone out there because it turned into a major pain for me.