MySQL as a database management system

By default, DNSmanager uses SQLite as a database management system. This database management system is a perfect solution for managing small amounts of data typical for DNSmanager. However, if you have a large number of domains and concurrent queries, we recommend using MySQL.

How to switch from SQLite to MySQL

1. Install and run MySQL:

   CentOS

   yum install mariadb-server
   service mariadb start

   Debian

   apt-get install mysql-server
   service mysqld start

2. Change the standard name of the core file:

   mv /usr/local/mgr5/bin/core /usr/local/mgr5/bin/core2

3. Stop the core process: 

   pkill core

4. Create a new database in MySQL:

   create database dnsmgr default character set utf8mb4;

5. Change "DBType sqlite" into "DBType mysql" in the DNSmanager configuration file (the default location is /usr/local/mgr5/etc/dnsmgr.conf)

6. In the configuration file enter the following parameters for connecting to the database:

   • DBHost - MySQL database server. The default value is "localhost";
   • DBUser - database username. The default value is "root";
   • DBPassword - password of the database user
   • DBName - database name. The default value is "dnsmgr".
7. Import data from SQLite into MySQL if needed:

   1. Create a database dump:

      sqlite3 /usr/local/mgr5/etc/dnsmgr.db .dump > /root/dnsmgr.db.sqlite

      View details

      /root/dnsmgr.db.sqlite — the dump will be added into this file. 

   2. Make changes in the dump file: 

       replace "BEGIN TRANSACTION" "START TRANSACTION" -- /root/dnsmgr.db.sqlite
       replace "PRAGMA foreign_keys=OFF;" "SET NAMES utf8mb4;" -- /root/dnsmgr.db.sqlite
       replace "ON CONFLICT FAIL" "" -- /root/dnsmgr.db.sqlite
       replace "usage VARCHAR(64)" "\`usage\` VARCHAR(64)" -- /root/dnsmgr.db.sqlite
       replace "noauto VARCHAR(3)" "\`noauto\` VARCHAR(3)" -- /root/dnsmgr.db.sqlite
       replace "DEFAULT \`off\`" "DEFAULT 'off'" -- /root/dnsmgr.db.sqlite
       awk 'FS = "\"" {if($1=="INSERT INTO ") {print($1$2$3)} else {print}}' /root/dnsmgr.db.sqlite > /root/dnsmgr.db.mysql

   3. Upload the dump :

      mysql -uroot -p dnsmgr < /root/dnsmgr.db.mysql

      View details

      /root/dnsmgr.db.sqlite — the dump file.

8. Restart DNSmanager. DNSmanager will be started with the clean database if the data were not imported from SQLite into MySQL.

9. Return the standard name to the core file:

   mv /usr/local/mgr5/bin/core2 /usr/local/mgr5/bin/core

10. Restart the core process: 

    pkill core

Conversion script 

You can use the following script to complete the steps 2–6:

#!/bin/bash
mv /usr/local/mgr5/bin/core /usr/local/mgr5/bin/core2
pkill core
sqlite3 /usr/local/mgr5/etc/dnsmgr.db .dump > /root/dnsmgr.db.sqlite
replace "BEGIN TRANSACTION" "START TRANSACTION" -- /root/dnsmgr.db.sqlite
replace "PRAGMA foreign_keys=OFF;" "SET NAMES utf8mb4;" -- /root/dnsmgr.db.sqlite
replace "ON CONFLICT FAIL" "" -- /root/dnsmgr.db.sqlite
replace "usage VARCHAR(64)" "\`usage\` VARCHAR(64)" -- /root/dnsmgr.db.sqlite
replace "noauto VARCHAR(3)" "\`noauto\` VARCHAR(3)" -- /root/dnsmgr.db.sqlite
replace "DEFAULT \`off\`" "DEFAULT 'off'" -- /root/dnsmgr.db.sqlite
awk 'FS = "\"" {if($1=="INSERT INTO ") {print($1$2$3)} else {print}}' /root/dnsmgr.db.sqlite > /root/dnsmgr.db.mysql
mysql -e "create database dnsmgr default character set utf8mb4;"
mysql -uroot dnsmgr < /root/dnsmgr.db.mysql
grep -qE "DBType sqlite" /usr/local/mgr5/etc/dnsmgr.conf && replace "DBType sqlite" "DBType mysql" -- /usr/local/mgr5/etc/dnsmgr.conf
grep -qE "DBType mysql" /usr/local/mgr5/etc/dnsmgr.conf || echo "DBType mysql" >> /usr/local/mgr5/etc/dnsmgr.conf
rm -f /usr/local/mgr5/var/.db.cache.*
mv /usr/local/mgr5/bin/core2 /usr/local/mgr5/bin/core
pkill core