MySQL data engine InnoDB and MyISAM convert each other

MySQL (or Community Open Source Fork's MariaDB) 5.5 or more supports the InnoDB engine as the default database engine. InnoDB brings a lot of improvements, but significantly increases the use of system resources.
Previously attached alumni return to the official website by DDOS hung up the database once, and then because of a small plug-in led to the database hung up again, query logs found that the memory is too high, InnoDB allocated memory and then led to the entire database shutdown.
In the absence of a temporary plan to upgrade the server and the traffic is not large necessary for InnoDB, it was decided to cut the entire site database back to the old and strong MyISAM.

Ps. If the memory does not have 1G do not play MySQL5.6 plus, memory 512M or below just hang a WordPress honest and practical MyISAM. Memory is exactly 1G but there are several things running and occasionally a little traffic (like me) also good use MyISAM bar.

Here's how.

Method 1: Knock on the command (find it online)
Go into MySQL command-line mode and execute the followi

1
2
3
4
5
6
7
8
ng instructions (in the case of database wordpress):

USE wordpress;
SHOW TABLES;
ALTER TABLE wp_commentmeta ENGINE-MYISAM;
ALTER TABLE wp_comments ENGINE-MYISAM;

SHOW TABLES can display the names of all the tables below wordpress, and the next tw

o lines will convert the wp_commentmeta and wp_comments tables into myISAM engines, and the remaining tables will be converted in the same way. The database is locked during the engine conversion process, and small databases are converted quickly, but large database conversions can take a long time, so try to choose to access the low peak period.

Method two: PHP
The above method of knocking commands to get a table to knock commands, I do not want to, so there is the followi

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
ng<?php
//连接数据库
$host='localhost';
$user='admin';   //数据库用户名
$passwd='admin'; //密码
$db='admin';  //数据库名称
$link= mysql_connect($host,$user,$passwd);
if (!$link) {
die('Could not connect: '.mysql_error() );
}
 
mysql_select_db( $db,$link ) or die ('can't use'. $db . mysql_error());
$result=mysql_query("show tables ") or die( mysql_error());
while($row=mysql_fetch_row($result)){
#echo $row[0];
#var_dump($row);
echo "$row[0] n";
mysql_query("alter table  $row[0] ENGINE=MYISAM") or die (mysql_error());
}
#var_dump($result);
mysql_close($link);
?>
P

HP script.

Just execute and wait for processing to complete.
PS. If you change myISAM from 18 of these lines to INNODB, you switch the data engine from MyISAM to InnoDB.

After converting the table, you can turn off the InnoDB engine and make MyISAM the default engine for mySQL in the future, edit/etc/my.c[mysqld]nf (or other path) files,

1
2
3
4
5
6
 and add the following two lines under segments:

default-storage-engine-MYISAM
innodb-OFF

Fin

ally, restart the database.

共有 0 条评论

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.