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

BASH

Debian

apt-get install mysql-server
service mysqld start

BASH

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

Create the database on MySQL:

create database ipmgr default character set latin1;

BASH

Create user ipmgr and grant it database permissions:

use mysql;

BASH

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

BASH

Comments to the command

<password> — user password

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

BASH

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

CODE

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

Convert the database:

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

BASH

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

BASH

Upload the generated dump to the database:
```
mysql -uroot ipmgr < /root/ipmgr.db.mysql
```
BASH
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
```
CODE
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

BASH

Algorithm for switching from SQLite to MySQL

Cookie Notice