For several years, I have hosted a medium sized Invision Power Board (IPB) site. The site has roughly 9,000+ users and 1,000,000+ posts. I work with MariaDB on a daily basis, and often find myself recommending it to customers. It’s only appropriate I put my money where my mouth is, so to speak. This blog post has no benchmarks. It is just a very simple to follow set of steps I took to migrate my site from MySQL 5.1 to MariaDB 10 on CentOS 6.5 with very little downtime for the end users.
To do this, I setup a new virtual server for MariaDB, use mysqldump to export the data with master info, restored this backup to MariaDB, setup replication, set the site to offline, switched the conf_global.php database values, then set the site to online. Sounds easy, right? It was. Very easy. Here are the steps I took with more detail.
For these steps, we will call the original MySQL 5.1 server DB1 and the new MariaDB 10 server DB2.
1. Spin up a new server (DB2) to be the new home of my MariaDB installation, with CentOS 6.5.
2. Open the appropriate ports (MariaDB, SSH, etc) on DB2.
3. Configure the MariaDB repo for yum on DB2. Use the repository configuration utility available on MariaDB.org: https://downloads.mariadb.org/mariadb/repositories/. In my case, my repo configuration looks like this:
cat /etc/yum.repos.d/MariaDB10.repo # MariaDB 10.0 CentOS repository list - created 2014-09-23 23:43 UTC # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.0/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
4. Install MariaDB on DB2 via yum.
yum install mysql mysql-server
5. Edit /etc/my.cnf.d/server.cnf. Most values from the existing my.cnf on MySQL 5.1 will work to get started. Tune and configure as needed. Be sure to set a unique server-id value (MySQL 5.1 server can be 1, MariaDB 10 server can be 2). By default, the value is 0.
6. Start MariaDB using service on DB2.
service mysql start
7. Run the secure installation script to secure your MariaDB installation and set a root password on DB2.
8. On DB1, create your replication user. Replace repluser with the username you want to use for replication, hostname with the hostname or IP address of the slave server, and password with a secure password.
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'hostname' IDENTIFIED BY 'password';
9. On DB1, use mysqldump to dump the data with master info. For this, I dumped all databases, and set –master-data=2 so that the CHANGE MASTER TO statement would be commented out in the dump file (http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_m…).
mysqldump -u username -p --master-data=2 --all-databases > /db-backups/with_master_info_09232014.sql
10. Use head and grep to pull the CHANGE MASTER TO statement from the dump file on DB1. Keep these values as you will need them to setup the MariaDB slave.
head with_master_info_09232014.sql -n80 | grep "MASTER_LOG_POS" CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000673', MASTER_LOG_POS=83774469;
11. Compress the dump file on DB1, and copy to DB2.
gzip with_master_info_09232014.sql scp with_master_info_09232014.sql user@db2:/db-backups/
12. Decompress the backup, and import it (while disabling binary logging) on DB2. Use the root username and password from step 7.
gunzip with_master_info_09232014.sql (echo "SET SESSION sql_log_bin=0;" ; cat with_master_info_09232014.sql) | mysql -u root -p
If you’d prefer not to disable binary logging:
gunzip with_master_info_09232014.sql mysql -u root -p < with_master_info_09232014.sql
13. Log into MariaDB on DB2 using the username and password from DB1, and verify the databases are there. A quick spot check never hurts. If the username/password combinations from DB1 don’t work, use root username/password combination from step 7, issue the statement FLUSH PRIVILEGES;, then logout and try the DB1 username/password combo again.
Mysql -u username -p Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 127381 Server version: 10.0.13-MariaDB-log MariaDB Server Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ipbdatabase | | mysql | | percona | | performance_schema | +--------------------+ 10 rows in set (0.00 sec)
14. On DB2, change the master. Change the relevant values.
MASTER_HOST – hostname or IP address of DB1 MASTER – username for replication user setup in step 8 MASTER_PASSWORD – password for replication user setup in step 8 MASTER_PORT – MySQL port on DB1 MASTER_LOG_FILE – the MASTER_LOG_FILE value from step 10 MASTER_LOG_POS – the MASTER_LOG_POS value from step 10 MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='hostname', -> MASTER_USER='repluser', -> MASTER_PASSWORD='password', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000673', -> MASTER_LOG_POS=83774469;
15. Start the slave on DB2, and show slave status. Check the output and verify DB2 is replicating from DB1. Check both Slave_IO_Running and Slave_SQL_Running report Yes. Check the slave status multiple times should show the value of Seconds_Behind_Master decreasing.
MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status G
16. Once it has been verified DB2 is in sync with DB1 and all preparations for the switch have been made, IPB can be reconfigured to connect to the new database server. First, take the IPB site offline via the administration control panel. This is found under System Settings > Community Offline / Online. Click the radio button for Yes next to “Turn the community offline”, set an offline message if preferred, then click the “update settings” button at the bottom.
17. Edit IPB’s conf_global.php file with the below settings. Everything else should remain the same.
sql_host – the hostname or IP address of DB2 sql_port – the port for MariaDB on DB2 $INFO['sql_host'] = 'hostname'; $INFO['sql_port'] = 'port';
18. Stop MySQL on DB1. We want to make sure no currently open connections remain connected to DB1.
service mysql stop
19. Stop and reset slave on DB2. Resetting the slave removes all information about the master (DB1).
MariaDB [(none)]> stop slave; MariaDB [(none)]> reset slave;
20. Verify you can still access your IPB site and that everything is working as expected.
21. Go back to the same settings back in the IPB administration console as in step 16 and bring your IPB site back online.
If you run into database errors when navigating your IPB site after the switch, check the SQL Error Logs in the administrator console. They’re under Stats and Logs > Logs > SQL Error Logs. I ran into one error when trying to create an event in the calendar application. After checking the logs, I found this error: Error: 1292 – Incorrect datetime value: ‘0’ for column ‘event_end_date’ at row 1. This was caused by NO_ZERO_IN_DATE and NO_ZERO_DATE set in sql_mode. IPB’s calendar application when creating an all day event sets the dates like ‘2014-10-02 00:00:00’.
My site has been running on MariaDB 10.0.13 for almost a week now and so far no complaints.