Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Graphite SQLite to MySQL migration
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: toni.moreno@xxxx.com
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
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: toni.moreno@xxxx.com
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
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;"}'
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;"}'
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment