View source for How to migrate database to partitions or optimize performance

= prerequisites = 

*We are recommending to upgrade MySQL to the version 5.6 (see this howto [[Mysql_5.6]]) which has optimized SQL planner resulting in much faster queries using joins which are extensively used in the GUI. Besides other optimizations.
*The voipmonitor sniffer automatically enables partitions for cdr tables which allows to store millions of data partitioned per day. Each day have standalone index which means that searching will be not slower with each days. Partitioning also allows to clean old data because deleting partitions is instant and has support directly in the voipmonitor sniffer [[Sniffer_configuration#cleandatabase]]. If your database is not partitioned (which you will see if you run "MySQL> show create table cdr" and at the end you will see partitions) it cannot be converted automatically.
*Another big performance impact is when you do not use innodb_file_per_table = 1 - without this option the ib_data will be giant for all tables and partitions which is not recommended. Thus we strongly recommend to set 

 innodb_file_per_table = 1

which means that you have to migrate your old data to new database (see next chapter). 
*Compression allows to save I/O writes and doubles space for storing data. This is only enabled if innodb_file_per_table is set to 1 and for mysql <= 5.1 innodb_file_format = barracuda (in >5.1 barracuda is default). The voipmonitor sniffer creates compressed tables automatically so you do not need to enable this feature. 
* I/O disk optimization - in mysql configuration set 

 innodb_flush_log_at_trx_commit = 2 

Now restart the database and proceed with data migration next chapter

= Data migration = 

Recent voipmonitor sniffer has feature which allows to automatically synchronize source voipmonitor database to another voipmonitor database including partition creation and syncing new data. This feature was developed primarily for backup purpose but it is also handy when you need to convert data to partitions on your local database. Follow next steps: 

Create temporary configuration file /etc/voipmonitormigrate.conf with this content and change username and password accordingly. You can also change IP addresses to match your deployment. You can also migrate your data to another database server or another database instance. mysqlhost and mysqldb is used for destination database. database_backup_from is the original database so do not mess it up! If you are unsure you can stop the databaes server and backup your whole data (cp -a /var/lib/mysql /var/lib/mysql-backup) - note that the migration will use the same disk space like your current data so be sure you have enough space for the migration. 

 [general]
 cdrproxy = yes
 nocdr = no
 sqldriver = mysql
 mysqlcompress = yes
 mysqlhost = 127.0.0.1
 mysqlport = 3306
 mysqldb = voipmonitorNEW
 cdr_partition = yes
 mysqltable = cdr
 mysqlusername = root
 mysqlpassword = rootpwd
 cdr_sipport = yes
 create_old_partitions_from = 2015-12-03
 database_backup_from_date = 2015-12-03
 database_backup_from_mysqlhost = 127.0.0.1
 database_backup_from_mysqlport = 3306
 database_backup_from_mysqldb = voipmonitor
 database_backup_from_mysqlusername = root
 database_backup_from_mysqlpassword = rootpwd
 database_backup_pause = 3
 database_backup_insert_threads = 3
 #cleandatabase = 120
 interface=lo
 disable_partition_operations = no
 database_backup_desc_dir = no
 
 ;this section is only for those who uses matchheader and custom headers which you can omit if you do not use them. 
 matchheader = myheader
 custom_headers_message = Comm-Correlation-ID ; Comm-ID
 custom_headers = Remote-Party-ID ; Comm-ID ; Comm-Correlation-ID; Comm-Session-Id
'''Run the voipmonitor sniffer''' 

 voipmonitor --config-file /etc/voipmonitormigrate.conf -k -v 1


the -k option will cause sniffer not detach from console and run in foreground. 


after the migration is done (same count of CDRs inn DBs) stop both sniffers and reconfigure /etc/voipmonitor.conf to use your new database name (voipmonitorNEW) and do not forget to reconfigure the GUI -> settings -> server settigns -> database name. Run the sniffer (/etc/init.d/voipmonitor start) and check if your new database contains new CDR. Now you can drop your old database (mysql> drop database voipmonitor)

In case you need to '''transfer also user's settings in a GUI''' (Like dashboard templates, network maps, alerts settings and reports,names and password of users, etc) use under GUI->tools->backup&restore to backup the file, and then restore it in new GUI with new db.

= settings migration requested =
Here is the sample script for [[ backup_and_restore_GUI_tables ]] peridically using crontab.