MySQL as a database management system

This is the documentation for the deprecated product DNSmanager 5. It is no longer updated and may be irrelevant. Documentation for the current version of DNSmanager can be found in the DNSmanager 6 section.

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

Install and run MySQL:

CentOS

yum install mariadb-server
service mariadb start

Debian

apt-get install mysql-server
service mysqld start

Change the standard name of the core file:

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

Stop the core process:
```
pkill core
```

Create a new database in MySQL:

create database dnsmgr default character set utf8mb4;

Change "DBType sqlite" into "DBType mysql" in the DNSmanager configuration file (the default location is /usr/local/mgr5/etc/dnsmgr.conf)
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".

Import data from SQLite into MySQL if needed:

Create a database dump:

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

View details

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

Upload the dump :

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

View details

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

Return the standard name to the core file:

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

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