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.
Once the SQL script is generated, all you need to do is run the sql file to your database server.
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.
Read more...
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.
Read more...
MyISAM to InnoDB Engine Conversion
2013-12-07T14:36:00+05:45
Cool Samar
database administration|mysql|
Comments
Labels:
database administration,
mysql
Bookmark this post:blogger tutorials
Social Bookmarking Blogger Widget |
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:
I hope this proves useful for some of you guys :)
Read more...
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 :)
Read more...
Pattern Based Database GRANT In MySQL
2013-10-18T10:35:00+05:45
Cool Samar
database administration|mysql|tricks and tips|
Comments
Labels:
database administration,
mysql,
tricks and tips
Bookmark this post:blogger tutorials
Social Bookmarking Blogger Widget |
Subscribe to:
Posts (Atom)