Showing posts with label database administration. Show all posts
Showing posts with label database administration. Show all posts

Saturday, 7 December 2013

MyISAM to InnoDB Engine Conversion

We are doing lots of MyISAM to InnoDB migrations in our production environment and since the engine conversion needs to be done for each table, its good to generate a script to do so when you have huge number of databases each having several tables. Here is the quick script to generate script for MyISAM to InnoDB engine conversion.
mysql -u <user> -p -e "SELECT concat('ALTER TABLE \`',TABLE_SCHEMA,'\`.\`',TABLE_NAME,'\` ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA in ('database1', 'database2', 'databaseN') AND ENGINE = 'MyISAM' AND TABLE_TYPE='BASE TABLE'" | tail -n+2 > alter.sql

Once the SQL script is generated, all you need to do is run the sql file to your database server.
$ mysql -u <user> -p < alter.sql

Note that while InnoDB is generally the better engine than MyISAM and MySQL has InnoDB as default engine since 5.5, MyISAM has its own benefits and you should make performance analysis in preferably a test environment while converting the engine type.


Friday, 18 October 2013

Pattern Based Database GRANT In MySQL

At our workplace, we need to manage database access for different teams and rather than adding another grant on the addition of new database, I've been following a pattern based database access grants in MySQL.

We let different teams work on replicas of same database and hence append the terms such as _dev and _qa as the database prefix. And, we define GRANTS based on these patterns. An example would be something like below:
GRANT ALL ON `%\_dev`.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

I hope this proves useful for some of you guys :)