Shell batch modified MySQL storage engine type 2 ways

What are the MySQL storage engine types

MyISAM Manage non-transaction tables. Provides high speed retrieval and full-text search capabilities.
The memory storage Engine provides a "in-memory" table, and the merge storage engine allows the collection to be set up as a single table with a unified MyISAM table. Non-transaction table. Multiple MyISAM tables can be built into a single virtual table, making queries to these tables appear on a single table, improving query speed and repair efficiency, and saving disk space.

The INNODB,BDB storage engine provides a transaction security table.

The example storage engine is a "stub" engine, and it does nothing. You can use this engine to create a table, but no data is stored or retrieved from it, and the engine is designed to serve. For developers.

NDB cluster is a storage engine that is used by MySQL cluster to partition tables on multiple computers. Only supported by Linux,solaris,mac OS.
The archive store causes a large amount of data to be stored without indexing, very small.
The CSV storage engine stores data in a comma format in a text file.

The Blackhole storage engine has the data in the remote database. In 5.1 He only works with MySQL, using the MySQL C client API. In a future distribution, we would like to have it connect to another data source using a different drive or client connection method.

MyISAM Types of tables are stored on disk as three files

*.FRM File storage table definition
*. MyD (mydata) file storage table data
*. Myi (myindex) file to store the index established on the table.

The InnoDB type table provides the storage engine for submission, rollback, and crash recovery capabilities. Row-level locks. can be blended with other MySQL tables, even in the same query.

Maximum performance design for processing large amounts of data.

Batch modification MySQL Storage Engine type method

The method of shell script implementation

The code is as follows

Copy Code

#/bin/bash Db=test User=root Passwd=test host=192.168.0.11 Mysql_bin=/usr/local/mysql/bin S_engine=myisam D_engine=dbdcluster #echo "Enter MySQL bin Path:" #read Mysql_bin #echo "Enter Host:" #read HOST #echo "Enter uesr:" #read USER #echo "Enter Password:" #read PASSWD #echo "Enter DB Name:" #read DB #echo "Enter The original engine:" #read S_engine #echo "Enter The new engine:" #read D_engine $MYSQL _bin/mysql-h$host-u$user-p$passwd $DB-E "SELECT table_name from INFORMATION_SCHEMA. TABLES where table_schema= ' $DB ' and engine= ' $S _engine '; GREP-V "table_name" >tables.txt For T_name in ' Cat Tables.txt ' Todo echo "Starting convert table $t _name ..." Sleep 1 $MYSQL _bin/mysql-h$host-u$user-p$passwd $DB-e "ALTER TABLE $t _name engine= '" $D _engine "" If [$?-eq 0] Then echo "Convert table $t _name ended." >>con_table.log Sleep 1 Else echo "Convert failed!" >> con_table.log Fi Done

Like the interactive to take the Echo, read that section of the comments out, you can change according to the prompts. can also according to their own needs of DB, user, password, host and other information changes directly after the operation. The principle of this method is to loop through the statements engine=ndbcluster the ALTER TABLE name. The method also has a variant:

First, use the system table inside MySQL to get the SQL statement to execute:

The code is as follows	Copy Code
SELECT CONCAT (' ALTER TABLE ', table_name, ' engine=innodb; ') From Information_schema.tables WHERE table_schema= "db_name" and engine= "MyISAM";

Output the above results to a file. Then execute the file for the SQL statement. After execution, you can confirm the following statement:

Select CONCAT (table_name, ', engine) from Information_schema.tables WHERE table_schema= "db_name";

Method II, batch modification using stored procedure

The code is as follows

Copy Code

DELIMITER $$ DROP PROCEDURE IF EXISTS ' t_girl '. ' Sp_alter_db_engine ' $$ CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' sp_alter_db_engine ' ( In F_db_name varchar (255), in f_engine_name varchar (255)) BEGIN --Get the total number of tables. DECLARE cnt1 int default 0; declare i int; Set i = 0; Select COUNT (1) from information_schema.tables where Table_schema = F_db_name into cnt1; While I < Cnt1 Todo Set @stmt = Concat (' Select @tbname: =table_name from Information_schema.tables where Table_schema= ', F_db_name, ' ORDER BY table_name desc limit ', I, ', 1 into @tbname '); Prepare S1 from @stmt; Execute S1; deallocate prepare S1; Set @stmt = '; Set @tbname = Concat (F_db_name, '. ', @tbname); Call Sp_alter_table_engine (@tbname, f_engine_name); Set i = i + 1; End while; end$$ DELIMITER;

Call Method:

The code is as follows	Copy Code
Call Sp_alter_db_engine (' Baigan_cs ', ' InnoDB ');

The previous table is the name of the library, followed by the type of engine to be changed.