MySQL in IPmanager

From ISPWiki
Jump to: navigation, search

Starting from version 5.26 MySQL can be used as a database for IPmanager.

We use Sqlite by default, as this database management system is a perfect solution for managing small amounts of data typical for IPmanager. However, if you have many networks and concurrent IP allocation/release queries, we recommend using MySQL:

create a database in MySQL

create database ipmgr default character set utf8;

Add the following string into the /usr/local/mgr5/etc/ipmgr.conf file

DBType mysql

You can connect to the database with following parameters:

  • DBHost - MySQL database server. The default value - localhost
  • DBUser - databse username. The default value - root
  • DBPassword - password of the database user
  • DBName - database name. The default value - ipmgr

Restart the panel. IPmanager will start with the clean database.

You can import data from sqlite into mysql, if needed. Make the database dump into the file:

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

In the dump file you need to change syntax of several variables:

"ON CONFLICT FAIL" is changed into  ""
"usage VARCHAR(64)" is changed into  "`usage` VARCHAR(64)"
"range VARCHAR(128)" is changed into  "`range` VARCHAR(128)"
"noauto VARCHAR(3)" is changed into  "`noauto` VARCHAR(3)"
"DEFAULT `off`" is changed intoа "DEFAULT 'off'"

Now you can upload the dump to Mysql:

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

Conversion script

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