Once installed, you have to go through the configuration process. A lot of the configuration process is similar to Oracle; data goes here, logfiles (transaction logs) go on a seperate filesystem, the message logs go another place, etc. Similar to Oracle, you setup memory parameters and other features you want installed in the instance.
One interesting feature of MySQL is that you can store your tables using different "Storage Engines". A storage engine is basically the method used to access your data. You can use any number of storage engines in your database. The main storage engines are MyISAM, InnoDB, Memory, Merge, Berkley, and NDBCluster.
Why would you want to choose your storage engine? You can assign the storage engine based on what type of activity the table will have and the features you need.
| Storage Engine | Advantages | Disadvantage |
| MyISAM | Extremely fast for Queries | Can't use transactions with, Concurrent INSERTs don't scale because the entire table is locked,Dirty Reads |
| InnoDB | Can use transactions, multi-versioning read consistency, DML scales well | Queries slower than MyISAM |
| Memory | Tables stored directly in memory. Access is really fast. Best for TEMP type tables | Tables stored directly in memory. DB Goes down, your data is toast. |
| Merge | Can present two identical tables as one table. Kind of like a materialized view, kind of like a partitioned table | Must be MyISAM tables |
| Berkley | Offers transactions. | Older technology |
| NDBCluster | Supports transactions and clusters, highly scalable | Not widely used. |
| Federated | Can store data on a seperate server | your I/O is limited by bandwidth |
Last, but not least, we talked about security. We went through the same things you would do to secure an Oracle database; protect the root OS user, protect the mysql "root" (or SYS) user, use strong passwords, protect remote root logins, etc. One interesting concept is that MySQL users are identified not only by a username, but by the host they can login from. For example, jeffh@localhost is a totally different identity than jeffh@webserver. You grant permissions to both user depending on how you want to want them to access your data. If you want the root user to only login on the server itself, you only create root@localhost. This lets you setup access rules such as "When user jeffh is logged into the database on an internal domain, he can INSERT, UPDATE, DELETE, SELECT from my tables. When user jeffh is logged in from the VPN, he can only SELECT from my tables". That's kind of cool.
Two side notes: I leared about google sets and gvim (vi for windows).
6 comments:
Something that I never sorted out: can you have multiple storage engines inthe same database? Because if not, then what is the point of "memory" for TEMP type objects if one can't put them anywhere else in the same db?
Yeah, google sets absolutely rawks. I've suggested to them they allow for sorted results. Darn useful little tool.
Something that I never sorted out: can you have multiple storage engines inthe same database?
yup, you can use multiple storage engines in the same instance. You can even turn certain storage engines off in your my.cnf file if you don't want your developers using them.
Oeh I really must comment on that storage engine list... a couple of points:
MyISAM:
- Concurrent inserts (at the "end" of the table" don't block selects, MyISAM actually exploys 3 different types of locks: read (shared, for selects), write (exclusive, for updates), and local (for concurrent inserts).
- Dirty reads can NOT occur. That's prevented by the locks, same as in any other RDBMS (concurrency control).
Think data warehousing, logging... lots of new data inserted, either separately or in bulk. Then, mostly selects.
This is, perhaps, where the myth comes from that MySQL would only be suitable for apps that mainly do selects.
Both by testing yourself and seeing different production setups out there, this is easily proven wrong. It's important to understand the differences in concurrency control. And of course, to pick the optimal storage engine for each individual table.
InnoDB:
- Will need more memory to attain the speed of MyISAM. That's all. It's not fundamentally slower as such.
Of course you can use InnoDB for everything, makes life easy. If you have the RAM. But there are definitely tasks for which MyISAM is more optimal.
>> Of course you can use InnoDB for everything,
Only if you don't need full text search and merge tables
hi Jeff - Nice articles on MySQL.
The link to the part IV of this series of articles is not working. Can you kindly check?
Thanks,
Naresh
You can navigate to the IV part by clicking on "Newer Post"
this is the actual link: http://marist89.blogspot.com/2005/09/mysql-to-oracle-dba-part-i_112743838580032638.html
Post a Comment