What I learned about MySQL in class !!
By cmani on Jul 09, 2009
I and my team, were fortunate enough to get sponsored to attend a 5 day MySQL for DBA's training in Bangalore recently. Even though, I am not a DBA, nor do I intend to become one, but there is upswell of interest for MySQL in India, every customer and partner seems to want to know more about it and there is ever growing linkage between other Sun Middleware products and MySQL.
Lets take the following products and how MySQL fits in:
- Glassfish and MySQL - Apart from being the perfect fit in terms of price/performance etc etc, MySQL makes for a perfect choice, when you are building Web applications, intranet/internet apps, Web 2.0 apps. All you need is the MySQL JDBC driver, Create a connection pool for MySQL and there you go !! If you are using any of the popular frameworks like Struts, Spring, Hibernate or iBatis, its all very easy.
- Sun GlassFish Web Space Portal Server and MySQL: By default, Web Space Server is bundled with an HSQL database that is intended for evaluation purposes only. In most production environments, you will want to use a more robust database, such as MySQL.
- Sun Identity Manager: MySQL Enterprise server is a supported repository for Sun IDM. This makes great sense, when you are in a really price sensitive situations. I had blogged about this sometime back.
- Sun OpenSSO Enterprise: MySQL can be used as one of the supported authentication JDBC based user repositories .
- Java CAPS and Glassfish ESB: Of course, MySQL can be used with both Java CAPS (using eWay adapters) and Glassfish ESB, if you are building SOA based applications.
There you have it, there is a fitment with MySQL with almost all of our middle ware products !!
The following are some key things (in my personal opinion) which makes MySQL stand out, from other commercial or open source databases:
- Everything and any server configuration settings can be changed/viewed using the my.ini (on Windows) or my.cnf (on Solaris and Linux) files. It is extremely simple. Just add it, under the appropriate headers and it will be picked up by MySQL when in starts. Plus, there are some sample, configuration files, which show sample settings for large or medium kind of databases.
- The concept of Storage engines, and switching storage engines, having different storage engines in a single database, blows you away !!! Moving databases, renaming databases is a snap, as simple as renaming files (for Innodb) or changing directory names (for ISAM). Knowledge of different Storage engines can be a little intimidating at first, but for now, there are a few parameters like - what kind of applications will be using it? do you need transactions/ACID compliance? etc etc. Google "compare MYSQL storage engines"
- Setting up a MySQL replication with a Master/Slave, is a breeze, and can be done in less than 10 mins for a simple setup. You will find good information in the MySQL documentation.
- Every configuration variable setting, can be found using "MySQL>show variables like '%keyword%';" command.
- Doing a backup and restore of MySQL data is so simple using mysqldump.
- Tuning of MySQL databases seemed to be straightforward, will write up more stuff, as I experiment more. But generally, there are plenty of aids, which explain where time is being spent on queries, and how we can improve them
- Creating Triggers, Stored procedures are simple, and like other databases.