Usage of MySQL as a DBMS (database management system)

IPmanager uses SQLite as a database management system by default. It is convenient to use this DBMS with small data volumes and low loads, which is typical for IPmanager. However, if IPmanager contains a large number of networks and a large number of requests for the allocation and release of IP addresses are received, we recommend using MySQL.

Algorithm for switching from SQLite to MySQL

To migrate 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

Enter the MySQL:
```
mysql -u root -p
```

Create the database on MySQL:

create database ipmgr default character set latin1;

Create user ipmgr and grant it database permissions:

use mysql;

CREATE USER 'ipmgr'@'localhost' IDENTIFIED BY '<password>';

Comments to the command

GRANT ALL PRIVILEGES ON ipmgr . * TO 'ipmgr'@'localhost';

Change the root password in MySQL:
```
update user set password=PASSWORD('<new_pass>') where user='root';
```
Comments to the command
<new_pass> — new password
```
flush privileges;
```
Create a configuration file /root/.my.cnf with the following content:
```
[client]
user = root
password = '<root_pass>'
```
Пояснения
<root_pass> — root password

Create a convert.py script to convert the database:

Script content

#!/usr/bin/env python

import re
import fileinput

def this_line_is_useless(line):
    useless_es = [
        'BEGIN TRANSACTION',
        'COMMIT',
        'sqlite_sequence',
        'CREATE UNIQUE INDEX',
        'PRAGMA foreign_keys=OFF',
    ]
    for useless in useless_es:
        if re.search(useless, line):
            return True

def has_primary_key(line):
    return bool(re.search(r'PRIMARY KEY', line))

searching_for_end = False
for line in fileinput.input():
    if this_line_is_useless(line):
        continue

    # this line was necessary because '');
    # would be converted to \'); which isn't appropriate
    if re.match(r".*, ''\);", line):
        line = re.sub(r"''\);", r'``);', line)

    if re.match(r'^CREATE TABLE.*', line):
        searching_for_end = True

    m = re.search('CREATE TABLE "?(\w*)"?(.*)', line)
    if m:
        name, sub = m.groups()
        line = "DROP TABLE IF EXISTS %(name)s;\nCREATE TABLE IF NOT EXISTS `%(name)s`%(sub)s\n"
        line = line % dict(name=name, sub=sub)
    else:
        m = re.search('INSERT INTO "(\w*)"(.*)', line)
        if m:
            line = 'INSERT INTO %s%s\n' % m.groups()
            line = line.replace('"', r'\"')
            line = line.replace('"', "'")
    line = re.sub(r"([^'])'t'(.)", "\1THIS_IS_TRUE\2", line)
    line = line.replace('THIS_IS_TRUE', '1')
    line = re.sub(r"([^'])'f'(.)", "\1THIS_IS_FALSE\2", line)
    line = line.replace('THIS_IS_FALSE', '0')

    # Add auto_increment if it is not there since sqlite auto_increments ALL
    # primary keys
    if searching_for_end:
        if re.search(r"integer(?:\s+\w+)*\s*PRIMARY KEY(?:\s+\w+)*\s*,", line):
            line = line.replace("PRIMARY KEY", "PRIMARY KEY AUTO_INCREMENT")
        # replace " and ' with ` because mysql doesn't like quotes in CREATE commands 
        if line.find('DEFAULT') == -1:
            line = line.replace(r'"', r'`').replace(r"'", r'`')
        else:
            parts = line.split('DEFAULT')
            parts[0] = parts[0].replace(r'"', r'`').replace(r"'", r'`')
            line = 'DEFAULT'.join(parts)

    # And now we convert it back (see above)
    if re.match(r".*, ``\);", line):
        line = re.sub(r'``\);', r"'');", line)

    if searching_for_end and re.match(r'.*\);', line):
        searching_for_end = False

    if re.match(r"CREATE INDEX", line):
        line = re.sub('"', '`', line)

    if re.match(r"AUTOINCREMENT", line):
        line = re.sub("AUTOINCREMENT", "AUTO_INCREMENT", line)

    print line,

Make the script executable:
```
chmod +x convert.py
```

Convert the database:

sqlite3 /usr/local/mgr5/etc/ipmgr.db .dump | python convert.py > /root/ipmgr.db.mysql

replace "ON CONFLICT FAIL" "" -- /root/ipmgr.db.mysql

Upload the generated dump to the database:
```
mysql -uroot ipmgr < /root/ipmgr.db.mysql
```
Specify the type of DBMS and parameters for connecting to the database in the IPmanager configuration file /usr/local/mgr5/etc/ipmgr.conf:
```
DBType mysql
DBHost <mysql_server>
DBUser ipmgr
DBPassword <ipmgr_pass>
DBName ipmgr
```
Comments

<mysql_server> — MySQL server address where the database is located. By default is "localhost".
<ipmgr_pass> — ipmgr user password

Restart IPmanager:

/usr/local/mgr5/sbin/mgrctl -m ipmgr exit