mysql - que - ¿Cómo convertir todas las tablas de MyISAM en InnoDB?

mysql tablas innodb

Question

Sé que puedo emitir una tabla de modificación individualmente para cambiar el almacenamiento de la tabla de MyISAM a InnoDB.

Me pregunto si hay una manera de cambiarlos rápidamente a InnoDB.

Answer 1

Algunas correcciones a este script util

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;

Answer 2

Aquí hay una forma 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 en la sección de administración de carpetas / comandos / Luego puede convertir todas sus tablas con un comando manage.py:

python manage.py convert_to_innodb

Answer 3

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 y 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 para 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.

Luego tendrás una lista como esta que simplemente puedes pegar en tu 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 linux terminal:

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

Answer 4

Ejecute esta declaració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 ejecútela como una nueva consulta SQL.

Answer 5

En los scripts a continuación, reemplace <usuario>, <contraseña> y <esquema> con sus datos específicos.

Para mostrar las declaraciones 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, usa 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 describió en este artículo .

Answer 6

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

Según los documentos de migración de MariaDB.

En el servidor antiguo ejecutado:

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

Las opciones -skip-create-asegura que el servidor de base de datos use el motor de almacenamiento predeterminado al cargar los datos, en lugar de MyISAM.

mysql -u root -p < migration.sql

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

Answer 7

Es muy simple, solo hay DOS pasos solo 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;

(Copia y pega todos los resultados en la pestaña sql)

paso 2: (copia todos los resultados en la pestaña sql) y pega abajo en la línea

COMENZAR LA TRANSACCIÓN;

COMETER;

p.ej. COMENZAR LA TRANSACCIÓN;

ALTER TABLE admin_files ENGINE = InnoDB;

COMETER;

Answer 8

Este es un script php simple.

<?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>'';
    }

Answer 9

No se ha mencionado aún, así que lo escribiré para la posteridad:

Si está migrando entre servidores de base de datos (o tiene otra razón para volcar y volver a cargar su dta), simplemente puede modificar la salida de mysqldump :

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

A continuación, vuelva a cargarlo:

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

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

Answer 10

Otra opción más ... He aquí cómo hacerlo en ansible. Asume que el nombre de su base de datos está en dbname y que ya ha configurado el acceso.

- 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 }}"

Answer 11

Para generar sentencias 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 modificación.

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

Answer 12

Prueba 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

Answer 13

Puede ejecutar esta declaración en la herramienta de línea de comandos 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 usando: mysql -u username -p El resultado es un script sql que puede canalizar nuevamente a mysql:

mysql name-of-database < convert.sql

Reemplace "nombre de la base de datos" en la declaración anterior y la línea de comando.

Answer 14

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

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

Answer 15

Solo probé otra forma (¿simple?), Y trabajé para mí.

Solo exporte su DB como archivo .sql, edítelo con gedit o bloc de notas;

Reemplace ENGINE=MyISAM con ENGINE=INNODB y guarde el archivo editado

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

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

Y voilá !

Answer 16

Soy un novato y tuve que encontrar mi propia solución porque los comandos de mysql en la web generalmente están plagados de errores ortográficos, lo que crea una verdadera pesadilla 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 MESA"; y copiar / pegar la salida a microsoft excel. Vaya a la pestaña Datos y use la función "texto a columnas" para delimitar las columnas con una tecla de espacio. Luego, ordene las columnas según la columna que muestre sus tipos de tablas y elimine todas las filas en las que las tablas ya están en formato InnoDb (ya que no necesitamos ejecutar comandos contra ellas, ya están listas). Luego agregue 2 columnas a la izquierda de la columna de 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 sólo un espacio. La columna 3 es tu columna de tablas. La columna 4 debe contener sólo un espacio. La columna 5 es la última parte de su comando. Debe tener un aspecto como este:

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. Noté que si hacía más de eso a la vez, los comandos fallarían.

Answer 17

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

Answer 18

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;

Answer 19

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'';

Answer 20

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

  ALTER TABLE table_name ENGINE = INNODB;

Answer 21

cd / var / lib / mysql / DBNAME

ls | grep ".frm" | corte -d "." -f1 | xargs -I {} -n1 mysql -D DBNAME -e "alterar tabla {} MOTOR = INNODB;" -uroot -pXXXXX

Answer 22

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);
} ?>

Answer 23

<?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");
    }
  }

Answer 24

<?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();

?>

Answer 25

<?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);
    }
?>

Answer 26

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

Funciona de maravilla.

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