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:

  1. Install and run MySQL:


    yum install mariadb-server
    service mariadb start


    apt-get install mysql-server
    service mysqld start
  2. Enter the MySQL:

    mysql -u root -p
  3. Create the database on MySQL:

    create database ipmgr default character set latin1;
  4. Create user ipmgr and grant it database permissions:

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

    <password> — user password

    GRANT ALL PRIVILEGES ON ipmgr . * TO 'ipmgr'@'localhost';
  5. Change the root password in MySQL:

    update user set password=PASSWORD('<new_pass>') where user='root';

    <new_pass> — new password

    flush privileges;
  6. Create a configuration file /root/.my.cnf with the following content:

    user = root
    password = '<root_pass>'

    <root_pass> — root password

  7. 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',
            '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):
        # 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)
            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'`')
                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,

  8. Make the script executable:

    chmod +x convert.py

  9. 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
  10. Upload the generated dump to the database:

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

  11. 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

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

    <ipmgr_pass> — ipmgr user password

  12. Restart IPmanager:

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