ep_sqlite_mysql_migration.md

Etherpad sqlite to MySQL migration

1. Install mysql and sqlite3 client

    apt install mysql-server-5.6 mysql-client-5.6 sqlite3
   

2. Create the etherpad database (replace PASSWORD with some password)

    mysql -u root -p
    create database `etherpad`;
    grant all privileges on `etherpad`.* to 'etherpad'@'localhost' identified by 'PASSWORD';
   
    use `etherpad`; 
   
    CREATE TABLE `store` (   
        `key` varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
        `value` longtext COLLATE utf8mb4_bin NOT NULL,   
        PRIMARY KEY (`key`) 
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; 
   
    insert into store values ("MYSQL_MIGRATION_LEVEL", "1"); 
   

3. Stop etherpad

    service etherpad-lite stop
   

4. Export sqlite database to tab-separated values

    cd /var/local/etherpad-lite/etherpad-lite/
    sqlite3 -column -csv -separator $'\t' var/sqlite.db "select * from store" > /tmp/sqlite.db.csv
    chgrp mysql /tmp/sqlite.db.csv
   

5. Import data into mysql

     mysql -u root -p etherpad -e "load data infile '/tmp/sqlite.db.csv' into table store character set utf8mb4 fields escaped by '\"' terminated by '\t' enclosed by '\"';"
   

6. Open the etherpad settings /var/local/etherpad-lite/etherpad-lite/settings.json and comment out:

    "dbType" : "sqlite",
    "dbSettings" : {
          "filename" : "var/sqlite.db"
    }, 
   

7. Replace with (and replace PASSWORD with the etherpad database password):

    "dbType" : "mysql",
    "dbSettings" : {
        "user": "etherpad",
        "host": "localhost",
        "database": "etherpad",
        "password": "PASSWORD",
        "port" : "/var/run/mysqld/mysqld.sock"
    },
   

8. Also, set maxAge to 99999999

9. Restart etherpad

    service etherpad-lite start
   

10. Clean up:

     rm /tmp/sqlite.db.csv 
     rm /var/local/etherpad-lite/etherpad-lite/var/sqlite.db
    

References

• [1] https://github.com/ether/etherpad-lite/wiki/How-to-use-Etherpad-Lite-with-MySQL
• [2] https://www.digitalocean.com/community/tutorials/how-to-install-etherpad-for-production-with-node-js-and-mysql-on-a-vps
• [3] https://github.com/ether/etherpad-lite/wiki/Optimizing-Etherpad