Graphite SQLite to MySQL migration

(OK method 1 --best--) sqlite.to.mysql.migration.procedure

0.- Stop graphite-web

/etc/init.d/apache2 stop

1.- Backup SQLite db

export GRAPHITE_ROOT=/opt/graphite
PYTHONPATH=$GRAPHITE_ROOT/webapp django-admin.py dumpdata  --settings=graphite.settings -e contenttypes --natural > sqlite_graphite_dump.json


2.- Install pytho/Dyango mysql suport ( by example on debian )

# apt-get install libmysqlclient-dev
# pip install mysql-python

2.- Create a new DB on mysql to use with Graphite 

# mysql

mysql> create database graphitedb;
mysql>  grant all on graphitedb.* to 'graphite_user'@'%' identified by 'graphitepass';
mysql>  grant all on graphitedb.* to 'graphite_user'@'localhost' identified by 'graphitepass';


3.- Configure Graphite DB

-edit /opt/graphite/webapp/graphite/local_settings.py

DATABASES = {
    'default': {
        'NAME': '/opt/graphite/storage/graphite.db',
        'ENGINE': 'django.db.backends.sqlite3',
        'USER': '',
        'PASSWORD': '',
        'HOST': '',
        'PORT': ''
    }
}

to


DATABASES = {
    'default': {
        'NAME': 'graphitedb',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'graphite_user',
        'PASSWORD': 'graphitepass',
        'HOST': 'localhost',
        'PORT': '3306'
    }
}


4.- Recreate Inicial Schema on Mysql

# PYTHONPATH=$GRAPHITE_ROOT/webapp django-admin.py syncdb --settings=graphite.settings
Creating tables ...
Creating table account_profile
Creating table account_variable
Creating table account_view
Creating table account_window
Creating table account_mygraph
Creating table dashboard_dashboard_owners
Creating table dashboard_dashboard
Creating table dashboard_template_owners
Creating table dashboard_template
Creating table events_event
Creating table auth_permission
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_groups
Creating table auth_user_user_permissions
Creating table auth_user
Creating table django_session
Creating table django_admin_log
Creating table django_content_type
Creating table tagging_tag
Creating table tagging_taggeditem

You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now? (yes/no): yes
Username (leave blank to use 'root'):
Email address: [email protected]
Password:
Password (again):
Superuser created successfully.
Installing custom SQL ...
Installing indexes ...
Installed 0 object(s) from 0 fixture(s)




5.- Import backup data


PYTHONPATH=$GRAPHITE_ROOT/webapp django-admin.py loaddata sqllite_graphite_dump.json --settings=graphite.settings 
Installed 392 object(s) from 1 fixture(s)

6.- Start graphite-web

#/etc/init.d/apache2 start

(OK method 2) sqlite.to.mysql.migration.procedure2

1.- Stop graphite-web

/etc/init.d/apache2 stop



2.- Install pytho/Dyango mysql suport ( by example on debian )

# apt-get install libmysqlclient-dev
# pip install mysql-python

2.- Create a new DB on mysql to use with Graphite 

# mysql

mysql> create database graphitedb;
mysql>  grant all on graphitedb.* to 'graphite_user'@'%' identified by 'graphitepass';
mysql>  grant all on graphitedb.* to 'graphite_user'@'localhost' identified by 'graphitepass';


3.- Configure Graphite DB

-edit /opt/graphite/webapp/graphite/local_settings.py

DATABASES = {
    'default': {
        'NAME': '/opt/graphite/storage/graphite.db',
        'ENGINE': 'django.db.backends.sqlite3',
        'USER': '',
        'PASSWORD': '',
        'HOST': '',
        'PORT': ''
    }
}

to


DATABASES = {
    'default': {
        'NAME': 'graphitedb',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'graphite_user',
        'PASSWORD': 'graphitepass',
        'HOST': 'localhost',
        'PORT': '3306'
    }
}


4.- Recreate Inicial Schema on Mysql

# PYTHONPATH=$GRAPHITE_ROOT/webapp django-admin.py syncdb --settings=graphite.settings
Creating tables ...
Creating table account_profile
Creating table account_variable
Creating table account_view
Creating table account_window
Creating table account_mygraph
Creating table dashboard_dashboard_owners
Creating table dashboard_dashboard
Creating table dashboard_template_owners
Creating table dashboard_template
Creating table events_event
Creating table auth_permission
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_groups
Creating table auth_user_user_permissions
Creating table auth_user
Creating table django_session
Creating table django_admin_log
Creating table django_content_type
Creating table tagging_tag
Creating table tagging_taggeditem

You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now? (yes/no): yes
Username (leave blank to use 'root'):
Email address: [email protected]
Password:
Password (again):
Superuser created successfully.
Installing custom SQL ...
Installing indexes ...
Installed 0 object(s) from 0 fixture(s)




5.- Import old data.

- get data in sql format.

./create.importdata.sh /opt/graphite/storage/graphite.db > graphite_data.sql
./create.resetdb.sh > reset.graphite.db.sql
- truncate tables.

- Truncate tables

mysql graphitedb < reset.graphite.db.sql

- import data 

mysql graphitedb <graphite_data.sql



6.- Start graphite-web

#/etc/init.d/apache2 start

create.importdata.sh

sqlite3 $1 .dump  | grep -i "^INSERT"   | sed 's/^INSERT INTO \"\([^"]*\)\"/INSERT INTO \1 /g' | awk ' BEGIN {print "SET foreign_key_checks = 0;"} { print $0 }  END {print "SET foreign_key_checks = 1;"}'

create.resetdb.sh

echo "show tables" | mysql graphitedb | grep -v "^Tables_in" | awk 'BEGIN {print "SET foreign_key_checks = 0;"} { print "TRUNCATE table "$1";" } END {print "SET foreign_key_checks = 1;"}'

reset.graphite.db.sql

SET foreign_key_checks = 0;
TRUNCATE  table account_profile;
TRUNCATE  table account_variable;
TRUNCATE  table account_view;
TRUNCATE  table account_window;
TRUNCATE  table account_mygraph;
TRUNCATE  table dashboard_dashboard_owners;
TRUNCATE  table dashboard_dashboard;
TRUNCATE  table dashboard_template_owners;
TRUNCATE  table dashboard_template;
TRUNCATE  table events_event;
TRUNCATE  table auth_permission;
TRUNCATE  table auth_group_permissions;
TRUNCATE  table auth_group;
TRUNCATE  table auth_user_groups;
TRUNCATE  table auth_user_user_permissions;
TRUNCATE  table auth_user;
TRUNCATE  table django_session;
TRUNCATE  table django_admin_log;
TRUNCATE  table django_content_type;
TRUNCATE  table tagging_tag;
TRUNCATE  table tagging_taggeditem;
SET foreign_key_checks = 1;