Mysql – How to convert innodb engine tables to tokudb engine

innodbMySQLstorage-enginetokudb

Hi I want to migarate my server newly installed tokudb engine. i want to convert each table of database from innodb to tokudb. How can I do that using sed command ?

Best Answer

Instead of using sed, why not let mysql write the conversion script for you?

echo "SET SQL_LOG_BIN = 0;" > Convert_InnoDB_to_TokuDB.sql
MYSQL_USER=root
MYSQL_PASS=password
MYSQL_USERPASS="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_PORT=3306
MYSQL_CONN="-h127.0.0.1 -P${MYSQL_PORT} ${MYSQL_USERPASS}"
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',"
SQLSTMT="${SQLSTMT} table_name,' ENGINE=TokuDB;') InnoDBConversionSQL"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='InnoDB'"
SQLSTMT="${SQLSTMT} ORDER BY data_length"
MYSQL_OPTIONS="--skip-column-names -AN"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" >> Convert_InnoDB_to_TokuDB.sql
less Convert_InnoDB_to_TokuDB.sql

When you are satisfied with the conversion script, then run it

mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} < Convert_InnoDB_to_TokuDB.sql

Give it a Try !!!

Related Solutions

Mysql – TokuDB database size unknown in phptheadmin

Once the TokuDB storage engine was defined, it becomes responsible for updating the INFORMATION_SCHEMA database.

The first thing you should do it test TokuDB's INFORMATION_SCHEMA functionality.

First, run this query at a mysql client prompt:

SELECT
    IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
    CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,ENGINE,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,ENGINE,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

This will group and summarize MySQL data by engine usage per database.

If TokuDB stats do appear, then upgrade the MySQL drivers for phpMyAdmin.
If nothing comes up for TokuDB, try getting the latest version of the TokuDB storage engine. If you already have the latest version of TokuDB, contact Tokutek.
If you already have the latest version of the TokuDB storage and the latest MySQL drivers for phpMyAdmin and still get unknown, file a bug report with phpMyAdmin.

Mysql – InnoDB + TokuDB fills up RAM then start swapping

Why are you using InnoDB for some tables and TokuDB for others? As for memory usage, what is "top" reporting for the mysqld process on the server? Looking at the my.cnf you provided your MySQL server shouldn't be using more than 9G of RAM. If mysqld RSS is indeed growing to the 16G mark try lowering both the InnoDB and TokuDB cache sizes to see if that lowers the RSS.

Best Answer

Related Solutions

Mysql – TokuDB database size unknown in phptheadmin

Mysql – InnoDB + TokuDB fills up RAM then start swapping

Related Question