Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Thursday, 19 April 2018

How to Change MySQL table Engine of all Tables in Database ?

In this article I will show you how to change the table engine of all tables in database. We can change one by one table manually but this is not a way on place where we have large number of tables in database.

For example we have to change Engine from "Innodb" to "MyISAM" .

Using Information schema table , I will run a query as shown below and generate Script . After that copy the result set as Script in Script Tab and run all at a time.


SELECT  CONCAT('ALTER TABLE `',table_schema,'.', table_name, '` ENGINE=MyISAM ;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema ='inter15'
AND     `ENGINE` = 'innodb '
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;





Note: Please take the necessary backup before any changes.


Wednesday, 24 January 2018

Alter table operation in huge MySQL production table without table locking

Alter table Add/drop/Modify Column , Create/Drop INDEX in large database without any downtime:

MySQL 5.6: You can now perform read and write operations while an Alter Table operation is being perform even with InnoDB tables - http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Previously, modifying the table while an index is being created or dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on the table.

MySQL 5.6 and higher has introduce Online DDL, this feature becomes more general: you can read and write to tables while an index is being created, and many more kinds of ALTER TABLE operations can be performed without copying the table, without blocking DML operations, or both. Thus in MySQL 5.6 and higher, we typically refer to this set of features as online DDL rather than Fast Index Creation.

Example:-

ALTER TABLE getmysql .session ADD INDEX time_idx(timestamp), ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE `session` ADD `session_count` TINYINT(1) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE;


Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...