Migrating Bolt CMS from SQLite to MySQL (MariaDB)
You'll need to be confortable with the cli / terminal, but not an expert.
It is assumed you already have MySQL / MariaDB running on localhost and have Python installed.
1. Install SQLite 3
- Debian Linux: apt-get install sqlite3
- OS X: brew install sqlite3 (you'll need Brew)
2. Download SQLite 3 MySQL converter script
An updated version a python script to convert SQLite dumps to MySQL dumps. Place it to your app/database/ directory and make it executable (e.g. chmod 755 sqlite3-to-mysql.py).
Note that some versions no not convert CLOB fields to LONGTEXT, resulting in error similar to:
ERROR 1064 (42000) at line 3264 in file: 'dump.sql': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CLOB NOT NULL, html CLOB NOT NULL, geolocation CLOB NOT NULL, image VARCHAR(256)' at line 1
3. Create a new database
Login to MySQL and execute creation and permission commands:
- CREATE DATABASE bolt;
- GRANT ALL ON bolt.* TO 'bolt'@'localhost' IDENTIFIED BY 'SOMEPASSWORD';
- FLUSH PRIVILEGES;
4. Export and convert SQLite database to MySQL dump
- cd app/database/
- sqlite3 bolt.db .dump | ./sqlite3-to-mysql.py > bolt.sql
5. Import MySQL dump to your server
Execute:
- mysql -ubolt -pSOMEPASSWORD bolt
- source bolt.sql
6. Change Bolt CMS Configuration
Edit the configuration file: app/config/config.yml
Comment out the SQLite configuration:
#database:
# driver: sqlite
# databasename: bolt
Add the MySQL configuration:
database:
driver: mysql
username: metropolitan
password: metropolitan1
databasename: metropolitan
host: 127.0.0.1
7. Clear caches and update DB
Execute:
- php app/nut cache:clear
- php app/nut database:check
- php app/nut database:update
- php app/nut cache:clear
8. Test and verify your configuration change
- Open your site and it opens
- Go to the admin and create some content
- Open a MySQL console and verify new rows appear when you enter content (e.g. select id from bolt_entries;)
I your site does not work you can revert your DB configuration to SQLite to keep your site working.
More on SQLite MySQL migrations