Comment convertir toutes les tables de MyISAM en InnoDB?

Exécutez cette instruction SQL (dans le client MySQL, phpMyAdmin ou n'importe où) pour récupérer toutes les tables MyISAM de votre base de données.

Remplacez la valeur de la variable name_of_your_db par votre nom de base de données.

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;

Ensuite, copiez la sortie et exécutez - la en tant que nouvelle requête SQL.

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

Fonctionne comme un charme.

Cela vous donnera la liste de toutes les tables avec l'alter requêtes que vous pouvez exécuter dans un lot

Dans les scripts ci-dessous, remplacez , et par vos données spécifiques.

Pour afficher les instructions que vous pouvez copier-coller dans une session client mysql, tapez ce qui suit:

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

Pour simplement exécuter la modification, utilisez ceci:

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édit: ceci est une variation de ce qui a été décrit dans Cet article .

Utilisez ceci comme une requête sql dans votre phpMyAdmin

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

Une ligne:

 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

Vous pouvez exécuter cette instruction dans la ligne de commande 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

Vous devrez peut-être spécifier le nom d'utilisateur et le mot de passe en utilisant: mysql - u username-p Le résultat est un script sql que vous pouvez rediriger vers mysql:

mysql name-of-database < convert.sql

Remplacez "name-of-database" dans l'instruction et la ligne de commande ci-dessus.

Il n'a pas encore été mentionné, donc je vais l'écrire pour la postérité:

Si vous migrez entre des serveurs DB (ou si vous avez une autre raison de vider et de recharger votre dta), vous pouvez simplement modifier la sortie de mysqldump:

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

Puis chargez-le à nouveau:

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

(en outre, dans mon expérience limitée, cela peut être un processus beaucoup plus rapide que de modifier les tables ‘live'. Cela dépend probablement du type de données et d'index.)

C'est très simple il n'y a que deux étapes il suffit de copier et coller:

Étape 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;

(copiez et collez tous les résultats dans l'onglet sql)

Étape 2: (copiez tous les résultats dans l'onglet sql) et coller ci-dessous dans la ligne

DÉBUT DE LA TRANSACTION;

COMMETTRE;

Par exemple. DÉMARRER LA TRANSACTION;

Modifier la TABLE admin_files ENGINE = InnoDB;

COMMETTRE;

Voici un moyen de le faire pour les utilisateurs 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

Ajoutez cela à votre application sous la gestion des dossiers / commandes / ensuite, vous pouvez convertir toutes vos tables avec un manage.py commande:

python manage.py convert_to_innodb

Depuis mysql, vous pouvez utiliser search / replace en utilisant un éditeur de texte:

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

Note: vous devriez probablement ignorer information_schema et mysql car " les bases de données mysql et information_schema, qui implémentent certains des internes de MySQL, utilisent toujours MyISAM. En particulier, vous ne pouvez pas changer les tables grant pour utiliser InnoDB."( http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html )

Dans tous les cas, notez les tables à ignorer et à exécuter:

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

Maintenant juste copiez / collez cette liste dans votre éditeur de texte et recherchez / remplacez | / "par" ALTER TABLE " etc.

Vous aurez alors une liste comme celle-ci que vous pouvez simplement coller dans votre 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 votre éditeur de texte ne peut pas le faire facilement, voici une autre solution pour obtenir une liste similaire (que vous pouvez coller dans mysql) pour un seul préfixe de votre base de données, à partir du terminal linux:

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

Pour générer des instructions ALTER pour toutes les tables de tous les schémas non-système, ordonnés par ces schémas / tables, exécutez ce qui suit:

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;

Après cela, exécutez ces requêtes via un client pour effectuer la modification.

• la réponse est basée sur les réponses ci-dessus, mais améliore la gestion du schéma.

Une Version MySQL simple.

Vous pouvez simplement démarrer l'exécutable mysql, utiliser la base de données et copier-coller la requête.

Cela convertira toutes les tables MyISAM de la base de données actuelle en tables 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;

Vous pouvez écrire un script pour le faire dans votre langage de script préféré. Le script ferait ce qui suit:

1. problème SHOW FULL TABLES.
2. Pour chaque ligne retournée, vérifiez que la deuxième colonne, dit - 'BASE TABLE' et pas 'VIEW'.
3. si ce n'est pas 'VIEW', Exécutez la commande ALTER TABLE appropriée.

Essayez ce script 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

Je suis un débutant et j'ai dû trouver ma propre solution car les commandes mysql sur le web sont généralement criblées de fautes d'orthographe créent un véritable cauchemar pour les débutants. Voici ma solution....

Au lieu de 1 commande par table, j'ai préparé des dizaines de commandes (prêtes à copier et coller) à la fois en utilisant excel.

Comment? développez votre fenêtre de mastic et entrez mysql, puis exécutez la commande "Afficher L'état de la TABLE;" et le copier / coller la sortie à microsoft excel. Aller à la Onglet Données et utiliser le "texte aux colonnes" disposent d'une délimiter les colonnes par une touche d'espace. Ensuite, triez les colonnes selon la colonne qui montre vos types de table et supprimez toutes les lignes dont les tables sont déjà au format InnoDb (parce que nous n'avons pas besoin d'exécuter des commandes contre eux, elles sont déjà faites). Ajoutez ensuite 2 colonnes à gauche de la colonne tables et 2 colonnes à droite. Puis coller dans la première partie de la commande dans la colonne 1 (voir ci-dessous). La colonne 2 ne doit contenir qu'un espace. La colonne 3 est votre colonne de tableaux. La colonne 4 ne doit contenir qu'un espace. La colonne 5 est la dernière partie de votre commande. Cela devrait ressembler à ceci:

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;

Ensuite, copiez et collez environ 5 lignes à la fois dans mysql. Cela convertira environ 5 à la fois. J'ai remarqué que si je faisais plus que cela à la fois, les commandes échoueraient.

Quelques corrections à ce 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;

Dans mon cas, je migrais d'une instance MySQL avec une valeur par défaut de MyISAM, vers une instance MariaDB avec une valeur par défaut de InnoDB.

Par MariaDB migration Doc.

Sur L'ancien serveur Exécuter:

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

Le --skip-create-options garantit que le serveur de base de données utilise le moteur de stockage par défaut lors du chargement des données, au lieu de MyISAM.

mysql -u root -p < migration.sql

Cela a jeté une erreur concernant la création de mysql.db, mais tout fonctionne très bien maintenant :)

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

Utilisez cette ligne pour modifier le moteur de base de données pour une seule table.

  ALTER TABLE table_name ENGINE = INNODB;

Ceci est 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>';
    }

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

?>

Juste testé un autre (simple ? façon), et a travaillé pour moi.

Exportez simplement votre base de données sous .fichier sql, edit-it avec gedit ou notepad;

Remplacez ENGINE=MyISAM par ENGINE=INNODB et enregistrez le fichier modifié

Nombre ou remplacement effectué devrait être le nombre de vos tables

Importez-le dans MySQL (phpMyAdmin ou ligne de commande)

Et Voilà !

Encore une autre option... Voici comment le faire dans ansible. Il suppose que le nom de votre base de données est dans dbname et que vous avez déjà configuré l'accès.

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

Pour mysqli connecter;

<?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