Cómo convertir todas las tablas de MyISAM en InnoDB?

Ejecute esta instrucción SQL (en el cliente MySQL, phpMyAdmin, o donde sea) para recuperar todas las tablas MyISAM en su base de datos.

Reemplace el valor de la variable name_of_your_db con el nombre de su base de datos.

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'
ORDER BY table_name DESC;

Luego, copie la salida y ejecute como una nueva consulta SQL.

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase';

Funciona como un encanto.

Esto le dará una lista de todas las tablas con las consultas alter que puede ejecutar en un lote

En los scripts siguientes, reemplace , y con sus datos específicos.

Para mostrar las instrucciones que puede copiar y pegar en una sesión de cliente mysql, escriba lo siguiente:

echo 'SHOW TABLES;' \
 | mysql -u <username> --password=<password> -D <schema> \
 | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
 | column -t \

Para simplemente ejecutar el cambio, use esto:

echo 'SHOW TABLES;' \
 | mysql -u <username> --password=<password> -D <schema> \
 | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
 | column -t \
 | mysql -u <username> --password=<password> -D <schema>

CRÉDITO: Esta es una variación de lo que se esbozó en este artículo .

Use esto como una consulta sql en su phpMyAdmin

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;') 
FROM information_schema.tables 
WHERE engine = 'MyISAM';

Una línea:

 mysql -u root -p dbName -e 
 "show table status where Engine='MyISAM';" | awk 
 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}'  | 
  mysql -u root -p dbName

Puede ejecutar esta instrucción en la herramienta de línea de comandos de mysql:

echo "SELECT concat('ALTER TABLE `',TABLE_NAME,'` ENGINE=InnoDB;')
FROM Information_schema.TABLES 
WHERE ENGINE != 'InnoDB' AND TABLE_TYPE='BASE TABLE' 
AND TABLE_SCHEMA='name-of-database'" | mysql > convert.sql

Es posible que deba especificar el nombre de usuario y la contraseña utilizando: mysql-u username-p El resultado es un script sql que puede canalizar de nuevo a mysql:

mysql name-of-database < convert.sql

Reemplace "nombre-de-base de datos" en la instrucción y línea de comandos anteriores.

Todavía no se ha mencionado, así que lo escribiré para la posteridad:

Si está migrando entre servidores DB (o tiene otra razón por la que volcar y recargar su dta), puede modificar la salida desde mysqldump:

mysqldump --no-data DBNAME | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > my_schema.sql;
mysqldump --no-create-info DBNAME > my_data.sql;

Luego cárgalo de nuevo:

mysql DBNAME < my_schema.sql && mysql DBNAME < my_data.sql

(También, en mi limitada experiencia, esto puede ser un proceso mucho más rápido que alterar las tablas 'en vivo'. Probablemente depende del tipo de datos e índices.)

Es muy simple solo hay DOS pasos simplemente copiar y pegar :

Paso 1.

  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' ORDER BY table_name DESC;

(copiar y pegar todos los resultados en la pestaña sql)

Paso 2: (copiar todos los resultados en la pestaña sql) y pegar abajo en la línea

INICIAR TRANSACCIÓN;

COMMIT;

Eg. INICIAR TRANSACCIÓN;

ALTER TABLE admin_files ENGINE = InnoDB;

COMMIT;

Aquí hay una manera de hacerlo para los usuarios de Django:

from django.core.management.base import BaseCommand
from django.db import connections


class Command(BaseCommand):

    def handle(self, database="default", *args, **options):

        cursor = connections[database].cursor()

        cursor.execute("SHOW TABLE STATUS");

        for row in cursor.fetchall():
            if row[1] != "InnoDB":
                print "Converting %s" % row[0],
                result = cursor.execute("ALTER TABLE %s ENGINE=INNODB" % row[0])
                print result

Agregue eso a su aplicación bajo administración de carpetas / comandos / Luego puede convertir todas sus tablas con un manage.py orden:

python manage.py convert_to_innodb

Desde dentro de mysql, puede usar buscar / reemplazar usando un editor de texto:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';

Nota: Probablemente debería ignorar information_schema y mysql porque "Las bases de datos mysql e information_schema, que implementan algunas de las funciones internas de MySQL, todavía usan MyISAM. En particular, no puede cambiar las tablas de concesión para usar InnoDB."( http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html )

En cualquier caso, tenga en cuenta las tablas a ignorar y ejecutar:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';

Ahora solo copie / pegue esa lista en su editor de texto y busque / reemplace " / "con" ALTER TABLE", etc.

Entonces tendrá una lista como esta que simplemente puede pegar en su terminal mysql:

ALTER TABLE arth_commentmeta           ENGINE=Innodb;
ALTER TABLE arth_comments              ENGINE=Innodb;
ALTER TABLE arth_links                 ENGINE=Innodb;
ALTER TABLE arth_options               ENGINE=Innodb;
ALTER TABLE arth_postmeta              ENGINE=Innodb;
ALTER TABLE arth_posts                 ENGINE=Innodb;
ALTER TABLE arth_term_relationships    ENGINE=Innodb;
ALTER TABLE arth_term_taxonomy         ENGINE=Innodb;
ALTER TABLE arth_terms                 ENGINE=Innodb;
ALTER TABLE arth_usermeta              ENGINE=Innodb;

Si su editor de texto no puede hacer esto fácilmente, aquí hay otra solución para obtener una lista similar (que puede pegar en mysql) para un solo prefijo de su base de datos, desde el terminal linux:

mysql -u [username] -p[password] -B -N -e 'show tables like "arth_%"' [database name] | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;"

Para generar instrucciones ALTER para todas las tablas en todos los esquemas que no son del sistema, ordenados por esos esquemas/tablas, ejecute lo siguiente:

SELECT  CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables
WHERE   TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'innodb', 'sys', 'tmp')
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, table_name DESC;

Después de eso, ejecute esas consultas a través de un cliente para realizar la alteración.

• La respuesta se basa en las respuestas anteriores, pero mejora el manejo del esquema.

Una versión simple de MySQL.

Simplemente puede iniciar el ejecutable mysql, usar la base de datos y copiar y pegar la consulta.

Esto convertirá todas las tablas MyISAM en la Base de datos actual en tablas INNODB.

DROP PROCEDURE IF EXISTS convertToInnodb;
DELIMITER //
CREATE PROCEDURE convertToInnodb()
BEGIN
mainloop: LOOP
  SELECT TABLE_NAME INTO @convertTable FROM information_schema.TABLES
  WHERE `TABLE_SCHEMA` LIKE DATABASE()
  AND `ENGINE` LIKE 'MyISAM' ORDER BY TABLE_NAME LIMIT 1;
  IF @convertTable IS NULL THEN 
    LEAVE mainloop;
  END IF;
  SET @sqltext := CONCAT('ALTER TABLE `', DATABASE(), '`.`', @convertTable, '` ENGINE = INNODB');
  PREPARE convertTables FROM @sqltext;
  EXECUTE convertTables;
  DEALLOCATE PREPARE convertTables;
  SET @convertTable = NULL;
END LOOP mainloop;

END//
DELIMITER ;

CALL convertToInnodb();
DROP PROCEDURE IF EXISTS convertToInnodb;

Puedes escribir un script para hacerlo en tu lenguaje de scripting favorito. El script haría lo siguiente:

1. Issue SHOW FULL TABLES.
2. Para cada fila devuelta, verifique que la segunda columna diga 'BASE TABLE' y no 'VIEW'.
3. Si no es 'VIEW', ejecute el comando ALTER TABLE apropiado.

Pruebe este script de shell

DBENGINE='InnoDB' ;
DBUSER='your_db_user' ;
DBNAME='your_db_name' ;
DBHOST='your_db_host'
DBPASS='your_db_pass' ;
mysqldump --add-drop-table -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME > mtest.sql; mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "SHOW TABLES;" | while read TABLE ; do mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "ALTER TABLE $TABLE ENGINE=$DBENGINE;" ; done

Soy un novato y tuve que encontrar mi propia solución porque los comandos mysql en la web suelen estar plagados de errores ortográficos que crean una pesadilla en la vida real para las personas que recién comienzan. Aquí está mi solución....

En lugar de en 1 comando por tabla, preparé docenas de comandos (listos para copiar y pegar) a la vez usando excel.

¿Cómo? expanda su ventana de masilla e ingrese mysql y luego ejecute el comando "MOSTRAR ESTADO DE LA TABLA;" y copie/pegue la salida en microsoft excel. Ir a la Ficha Datos y utilizar el "texto a columnas" característica un delimitar las columnas por una tecla de espacio. Luego ordene las columnas por la columna que muestre sus tipos de tabla y elimine todas las filas en las que las tablas ya estén en formato InnoDB (porque no necesitamos ejecutar comandos contra ellas, ya están hechas). Luego agregue 2 columnas a la izquierda de la columna tablas y 2 columnas a la derecha. Luego pegue la primera parte del comando en la columna-1 (ver más abajo). La columna 2 debe contener solo un espacio. La columna 3 es tu columna de tablas. La columna 4 debe contener solo un espacio. La columna 5 es la última parte de su orden. Debería verse así:

column-1        column-2            column-3         column-4     column-5
ALTER TABLE     t_lade_tr           ENGINE=InnoDB;
ALTER TABLE     t_foro_detail_ms    ENGINE=InnoDB;
ALTER TABLE     t_ljk_ms            ENGINE=InnoDB;

Luego copie y pegue alrededor de 5 filas a la vez en mysql. Esto convertirá alrededor de 5 a la vez. Me di cuenta de que si hacía más que eso a la vez, los comandos fallarían.

Algunas correcciones a este script útil

SET @DATABASE_NAME = 'Integradb';

SELECT  CONCAT('ALTER TABLE ', table_schema, '.', 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'
ORDER BY table_name DESC;

En mi caso, estaba migrando de una instancia MySQL con un valor predeterminado de MyISAM, a una instancia MariaDB con un valor PREDETERMINADO de InnoDB.

Por MariaDB Migration Doc's.

En el servidor antiguo Ejecutar:

mysqldump -u root -p --skip-create-options --all-databases > migration.sql

El --skip-create-options asegura que el servidor de base de datos utilice el motor de almacenamiento predeterminado al cargar los datos, en lugar de MyISAM.

mysql -u root -p < migration.sql

Esto arrojó un error con respecto a la creación de mysql.db, pero todo funciona muy bien ahora:)

<?php

  // connect your database here first

  mysql_connect('host', 'user', 'pass');

  $databases = mysql_query('SHOW databases');

  while($db = mysql_fetch_array($databases)) {
    echo "database => {$db[0]}\n";
    mysql_select_db($db[0]);

    $tables = mysql_query('SHOW tables');

    while($tbl = mysql_fetch_array($tables)) {
      echo "table => {$tbl[0]}\n";
      mysql_query("ALTER TABLE {$tbl[0]} ENGINE=MyISAM");
    }
  }

Utilice esta línea para alterar el motor de la base de datos para una sola tabla.

  ALTER TABLE table_name ENGINE = INNODB;

Este es un simple script php.

<?php
    @error_reporting(E_ALL | E_STRICT);
    @ini_set('display_errors', '1');


    $con = mysql_connect('server', 'user', 'pass');
    $dbName = 'moodle2014';

    $sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$dbName."';";
    $rs = mysql_query($sql, $con);

    $count = 0;
    $ok = 0;
    while($row = mysql_fetch_array($rs)){
            $count ++;
            $tbl = $row[0];
            $sql = "ALTER TABLE ".$dbName.".".$tbl." ENGINE=INNODB;";
            $resultado = mysql_query($sql);
            if ($resultado){
                    $ok ++;
                    echo $sql."<hr/>";
            }
    }
    if ($count == $ok){
            echo '<div style="color: green"><b>ALL OK</b></div>';
    }else{
            echo '<div style="color: red"><b>ERRORS</b>Total tables: '.$count.', updated tables:'.$ok.'</div>';
    }

<?php

// Convert all MyISAM tables to INNODB tables in all non-special databases.
// Note: With MySQL less than 5.6, tables with a fulltext search index cannot be converted to INNODB and will be skipped.

if($argc < 4)
    exit("Usage: {$argv[0]} <host> <username> <password>\n");
$host = $argv[1];
$username = $argv[2];
$password = $argv[3];

// Connect to the database.
if(!mysql_connect($host, $username, $password))
    exit("Error opening database. " . mysql_error() . "\n");

// Get all databases except special ones that shouldn't be converted.
$databases = mysql_query("SHOW databases WHERE `Database` NOT IN ('mysql', 'information_schema', 'performance_schema')");
if($databases === false)
    exit("Error showing databases. " . mysql_error() . "\n");

while($db = mysql_fetch_array($databases))
{
    // Select the database.
    if(!mysql_select_db($db[0]))
        exit("Error selecting database: {$db[0]}. " . mysql_error() . "\n");
    printf("Database: %s\n", $db[0]);

    // Get all MyISAM tables in the database.
    $tables = mysql_query("SHOW table status WHERE Engine = 'MyISAM'");
    if($tables === false)
        exit("Error showing tables. " . mysql_error() . "\n");

    while($tbl = mysql_fetch_array($tables))
    {
        // Convert the table to INNODB.
        printf("--- Converting %s\n", $tbl[0]);
        if(mysql_query("ALTER TABLE `{$tbl[0]}` ENGINE = INNODB") === false)
            printf("--- --- Error altering table: {$tbl[0]}. " . mysql_error() . "\n");
    }
}

mysql_close();

?>

Acaba de probar otro (simple ?) manera, y trabajó para mí.

Simplemente exporte su base de datos como.archivo sql, edit-it con gedit o bloc de notas;

Reemplace ENGINE=MyISAM por ENGINE=INNODB y Guarde el archivo editado

El número o reemplazo hecho debe ser el número de sus tablas

Importarlo a MySQL (phpMyAdmin o línea de comandos)

Y Voila !

Otra opción... Así es como se hace en ansible. Asume que el nombre de su base de datos está en dbname y que ya ha configurado access.

- name: Get list of DB tables that need converting to InnoDB
  command: >
    mysql --batch --skip-column-names --execute="SELECT TABLE_NAME
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = '{{ dbname }}' AND ENGINE = 'MyISAM';"
  register: converttables
  check_mode: no
  changed_when: False

- name: Convert any unconverted tables
  command: >
    mysql --batch --skip-column-names --execute="ALTER TABLE `{{ dbname }}`.`{{ item }}` ENGINE = InnoDB;"
  with_items: "{{ converttables.stdout_lines }}"

Para mysqli connect;

<?php

$host       = "host";
$user       = "user";
$pass       = "pss";
$database   = "db_name";


$connect = new mysqli($host, $user, $pass, $database);  

// Actual code starts here Dont forget to change db_name !!
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'db_name' 
    AND ENGINE = 'MyISAM'";

$rs = $connect->query($sql);

while($row = $rs->fetch_array())
{
    $tbl = $row[0];
    $sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
    $connect->query($sql);
} ?>

Cd/var/lib/mysql / DBNAME

Ls | grep ".frm "/ cut-d"."- f1 / xargs-I {} - n1 mysql-D DBNAME-e "alter table {} ENGINE = INNODB;" - uroot-pXXXXX