Jak przekonwertować wszystkie tabele z MyISAM na InnoDB?

Wiem, że mogę wydać tabelę zmian indywidualnie, aby zmienić pamięć tabeli z MyISAM na InnoDB.

Zastanawiam się, czy istnieje sposób, aby szybko zmienić je wszystkie na InnoDB?

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

Uruchom tę instrukcję SQL (w kliencie MySQL, phpMyAdmin lub gdziekolwiek), aby pobrać wszystkie tabele MyISAM w bazie danych.

Zamień wartość name_of_your_dbzmiennej na nazwę bazy danych.

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;

Następnie skopiuj dane wyjściowe i uruchom jako nowe zapytanie SQL.

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

Działa jak marzenie.

Spowoduje to wyświetlenie listy wszystkich tabel z zapytaniami alter, które można uruchomić w partii

W poniższych skryptach zastąp <nazwa użytkownika>, <hasło> i <schemat> swoimi konkretnymi danymi.

Aby wyświetlić instrukcje, które można skopiować i wkleić do sesji klienta mysql, wpisz następujące polecenie:

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

Aby po prostu wykonać zmianę, użyj tego:

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>

KREDYT: Jest to odmiana tego, co zostało przedstawione w tym artykule .

Jedna linia:

 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

Użyj tego jako zapytania SQL w swoim phpMyAdmin

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

Możesz wykonać tę instrukcję w narzędziu wiersza poleceń 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

Konieczne może być podanie nazwy użytkownika i hasła za pomocą: mysql -u nazwa użytkownika -p Wynikiem jest skrypt sql, który można przesłać z powrotem do mysql:

mysql name-of-database < convert.sql

Zamień „nazwa-bazy danych” w powyższej instrukcji i wierszu poleceń.

To bardzo proste, jest tylko DWA krok, wystarczy skopiować i wkleić:

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

(skopiuj i wklej wszystkie wyniki w zakładce sql)

krok 2: (skopiuj wszystkie wyniki w zakładce sql) i wklej poniżej w linii

ROZPOCZNIJ TRANSAKCJĘ;

POPEŁNIĆ;

na przykład. ROZPOCZNIJ TRANSAKCJĘ;

ZMIEŃ admin_filesSILNIK TABELI = InnoDB;

POPEŁNIĆ;

Aby wygenerować instrukcje ALTER dla wszystkich tabel we wszystkich schematach niesystemowych, uporządkowane według tych schematów / tabel, wykonaj następujące czynności:

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;

Następnie uruchom te zapytania za pośrednictwem klienta, aby wykonać zmianę.

• Odpowiedź opiera się na powyższych odpowiedziach, ale poprawia obsługę schematu.

Nie zostało jeszcze wspomniane, więc napiszę to dla potomności:

Jeśli przeprowadzasz migrację między serwerami DB (lub masz inny powód, dla którego zrzucisz i przeładujesz dta), możesz po prostu zmodyfikować dane wyjściowe z mysqldump:

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

Następnie załaduj go ponownie:

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

(Ponadto, według mojego ograniczonego doświadczenia, może to być znacznie szybszy proces niż zmiana tabel „na żywo”. Prawdopodobnie zależy to od rodzaju danych i indeksów.)

Oto sposób, aby to zrobić dla użytkowników 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

Dodaj to do swojej aplikacji w folderze zarządzanie / komendy / Następnie możesz przekonwertować wszystkie tabele za pomocą komendy manage.py:

python manage.py convert_to_innodb

Z poziomu mysql możesz użyć funkcji wyszukiwania / zamiany za pomocą edytora tekstu:

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

Uwaga: Prawdopodobnie powinieneś zignorować Information_schema i mysql, ponieważ „Bazy danych mysql i information_schema, które implementują niektóre wewnętrzne elementy MySQL, nadal używają MyISAM. W szczególności nie można przełączyć tabel grantu na InnoDB”. ( http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html )

W każdym razie zwróć uwagę na tabele, które należy zignorować i uruchomić:

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

Teraz wystarczy skopiować / wkleić tę listę do edytora tekstu i wyszukać / zamienić „|” z „ALTER TABLE” itp.

Będziesz wtedy mieć taką listę, którą możesz po prostu wkleić do terminala 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;

Jeśli twój edytor tekstu nie może tego łatwo zrobić, oto inne rozwiązanie, aby uzyskać podobną listę (którą można wkleić do mysql) tylko jednego prefiksu bazy danych z terminala linux:

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

Prosta wersja MySQL.

Możesz po prostu uruchomić plik wykonywalny mysql, użyć bazy danych i skopiować i wkleić zapytanie.

Spowoduje to konwersję wszystkich tabel MyISAM w bieżącej bazie danych na tabele 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;

użyj tego wiersza, aby zmienić silnik bazy danych dla pojedynczej tabeli.

  ALTER TABLE table_name ENGINE = INNODB;

Jestem nowicjuszem i musiałem znaleźć własne rozwiązanie, ponieważ polecenia mysql w Internecie są zazwyczaj pełne błędów pisowni, co stanowi koszmar dla ludzi, którzy dopiero zaczynają. Oto moje rozwiązanie ....

Zamiast 1 polecenia na tabelę, przygotowałem kilkadziesiąt poleceń (gotowych do skopiowania i wklejenia) jednocześnie przy użyciu programu Excel.

W jaki sposób? rozwiń okno kitu i wpisz mysql, a następnie uruchom polecenie „POKAŻ STATUS TABELI”; i skopiuj / wklej dane wyjściowe do programu Microsoft Excel. Przejdź na kartę Dane i użyj funkcji „tekst do kolumn”, aby oddzielić kolumny klawiszem spacji. Następnie posortuj kolumny według dowolnej kolumny, która pokazuje typy tabel i usuń wszystkie wiersze, które tabele są już w formacie InnoDb (ponieważ nie musimy uruchamiać komend przeciwko nim, są już gotowe). Następnie dodaj 2 kolumny po lewej stronie kolumny tabeli i 2 kolumny po prawej stronie. Następnie wklej pierwszą część polecenia w kolumnie 1 (patrz poniżej). Kolumna 2 powinna zawierać tylko spację. Kolumna 3 to kolumna twoich tabel. Kolumna 4 powinna zawierać tylko spację. Kolumna 5 to ostatnia część twojego polecenia. To powinno wyglądać tak:

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;

Następnie skopiuj i wklej około 5 wierszy na raz do mysql. Spowoduje to konwersję około 5 naraz. Zauważyłem, że jeśli zrobię więcej niż to naraz, polecenia się nie powiodą.

W moim przypadku migrowałem z instancji MySQL z domyślną wersją MyISAM do instancji MariaDB z DOMYŚLNĄ InnoDB.

Dokumenty migracyjne według MariaDB.

Na starym serwerze Uruchom:

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

--Skip-create-options zapewnia, że ​​serwer bazy danych używa domyślnego silnika pamięci podczas ładowania danych, zamiast MyISAM.

mysql -u root -p < migration.sql

To spowodowało błąd podczas tworzenia mysql.db, ale teraz wszystko działa świetnie :)

Właśnie przetestowałem inny (prosty?) Sposób i działałem dla mnie.

Wystarczy wyeksportować plik DB jako plik .sql, edytować go za pomocą gedit lub notatnika;

Wymień ENGINE=MyISAMsię ENGINE=INNODBi zapisz plik edytowany

Liczba lub dokonana wymiana powinna być liczbą twoich tabel

Zaimportuj go do MySQL (phpMyAdmin lub wiersz poleceń)

I Voila!

Możesz napisać skrypt, aby zrobić to w swoim ulubionym języku skryptowym. Skrypt wykonałby następujące czynności:

1. Kwestia SHOW FULL TABLES.
2. Dla każdego zwracanego wiersza sprawdź, czy druga kolumna mówi, 'BASE TABLE'a nie 'VIEW'.
3. Jeśli nie 'VIEW', wydaj odpowiednie ALTER TABLEpolecenie.

Wypróbuj ten skrypt powłoki

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

Niektóre poprawki tego skryptu użytkownika

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;

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

To jest prosty skrypt 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();

?>

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

Jeszcze inna opcja ... Oto jak to zrobić w ansible. Zakłada się, że nazwa Twojej bazy danych jest już dostępna dbnamei że masz już skonfigurowany dostęp.

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

cd / var / lib / mysql / DBNAME

ls | grep ".frm" | cut -d "." -f1 | xargs -I {} -n1 mysql -D DBNAME -e "zmień tabelę {} ENGINE = INNODB;" -uroot -pXXXXX