Join the community today
Register Now

MariaDB Convert all tables to Innodb with only one command

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by pamamolf, Feb 27, 2016.

  1. pamamolf

    pamamolf Well-Known Member

    2,529
    231
    63
    May 31, 2014
    Ratings:
    +394
    Local Time:
    12:41 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Hi

    Is there anyway to avoid converting one by one the database tables from MyISAM to InnoDB?

    Thanks :)
     
  2. connectivityengineer

    connectivityengineer New Member

    15
    5
    3
    Feb 27, 2016
    Ratings:
    +10
    Local Time:
    5:41 PM
    1.9.12
    10
    might try something like this


    Code:
    <?php
    //connect your database here first //
    
    // Actual code starts here
    
    $sql ="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'your_database_name'
    AND ENGINE = 'MyISAM'";
    
    $rs = mysql_query($sql);
    
    while($row= mysql_fetch_array($rs)){$tbl =$row[0];$sql ="ALTER TABLE `$tbl` ENGINE=INNODB";
    mysql_query($sql);}?>
     
    Last edited: Feb 27, 2016
  3. connectivityengineer

    connectivityengineer New Member

    15
    5
    3
    Feb 27, 2016
    Ratings:
    +10
    Local Time:
    5:41 PM
    1.9.12
    10
    might be better if it does not include ALL innodb as well (perhaps this instead)

    "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine <> 'InnoDB'"
     
  4. pamamolf

    pamamolf Well-Known Member

    2,529
    231
    63
    May 31, 2014
    Ratings:
    +394
    Local Time:
    12:41 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Can i just run it from inside the mysql enviroment with one or two commands ?
     
  5. connectivityengineer

    connectivityengineer New Member

    15
    5
    3
    Feb 27, 2016
    Ratings:
    +10
    Local Time:
    5:41 PM
    1.9.12
    10
    for mySQL - i'd suggest 2 things

    1. grab your backups always

    2. run in screen session just in case

    mysql -u root -p


    SET@DATABASE_NAME ='name_of_your_db';

    SELECT CONCAT('ALTER TABLE `', table_name,'` ENGINE=InnoDB;')AS sql_statements
    FROM information_schema.tables AS tb
    WHERE table_schema =@DATABASE_NAME
    AND`ENGINE`='MyISAM'AND`TABLE_TYPE`='BASE TABLE'ORDERBY table_name DESC;

    then copy output to a new sql query

    the first answer i gave could be posted into a file and then ran from command line as well ​
     
    • Like Like x 1
  6. eva2000

    eva2000 Administrator Staff Member

    29,031
    6,588
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,780
    Local Time:
    7:41 AM
    Nginx 1.13.x
    MariaDB 5.5
  7. connectivityengineer

    connectivityengineer New Member

    15
    5
    3
    Feb 27, 2016
    Ratings:
    +10
    Local Time:
    5:41 PM
    1.9.12
    10
    was mobile... but yeah - good point :)
     
  8. pamamolf

    pamamolf Well-Known Member

    2,529
    231
    63
    May 31, 2014
    Ratings:
    +394
    Local Time:
    12:41 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Is this the only edit i should do and add my database name to get it work?

    Code:
    DATABASE_NAME
    As i am getting :
    Code:
    You have an error in your SQL syntax; check right syntax to use near 'ORDERBY table_name DESC' at line 4
    Do i have to specify the tables one by one?

    I thought that i will be able as i want it for all tables to use something like *.* or not?
     
  9. connectivityengineer

    connectivityengineer New Member

    15
    5
    3
    Feb 27, 2016
    Ratings:
    +10
    Local Time:
    5:41 PM
    1.9.12
    10
    Try this -


    Code:
    SET @DATABASE_NAME='YourDBName';
    
    CREATE TEMPORARY TABLE IF NOT EXISTS AlterTablesInnoDB_TEMP
    (
        SELECT
            CONCAT('ALTER TABLE `',@DATABASE_NAME,'`.`', table_name, '` ENGINE=InnoDB; ') AS STMT
        FROM
            information_schema.tables
        WHERE
            table_schema=@DATABASE_NAME
        AND
            `ENGINE`='MyISAM'
        AND
            `TABLE_TYPE`='BASE TABLE'
        ORDER BY
            table_name
        ASC
    );
    
    SET @NUMROWS=(SELECT COUNT(*) FROM AlterTablesInnoDB_TEMP);
    ALTER TABLE AlterTablesInnoDB_TEMP ADD `ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
    
    DROP PROCEDURE IF EXISTS AlterTablesInnoDB;
    DELIMITER $$
    CREATE PROCEDURE AlterTablesInnoDB( IN NUMROWS INT(11) UNSIGNED )
    BEGIN
        DECLARE i INT DEFAULT 1;
        WHILE i<=NUMROWS DO
            SET @QRY='';
            SET @QRY=(SELECT `STMT` FROM AlterTablesInnoDB_TEMP WHERE ID=i);
            PREPARE stmt FROM @QRY;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SET i=i+1;
        END WHILE;
    END$$
    CALL AlterTablesInnoDB(@NUMROWS);
    once done - then might as well clean up and do this:

    Code:
    DROP PROCEDURE IF EXISTS AlterTablesInnoDB;
    worse case - we can do a shared screen over skype or similar depending upon time factors - thus you do not need to give me access to your equipment - / network - but I can still help in real time.

    Today sadly I am putting up a network tower - - kinda hard to do mysql from here
     

    Attached Files:

    • Informative Informative x 4