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

   BASH

   Debian

   apt-get install mysql-server
   service mysqld start

   BASH

2. Change the standard name of the core file:

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

   CODE

3. Stop the core process: 

   pkill core

   CODE

4. Create a new database in MySQL:

   create database dnsmgr default character set utf8mb4;

   BASH
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

      BASH
       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

      BASH

   3. Upload the dump :

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

      BASH
       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

   CODE

10. Restart the core process: 

    pkill core

    CODE

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