MySQL Would You Like Some Transactions With That Table?
What Is MySQL? • Most popular open source RDBMS • Open source means 'free' • Really? No cost at all? • Widely used • Sabre – airline reservations • Google • LinkedIn • What does Oracle DBA need to know? • Many differences...
Why Should I Care? • I'm an Oracle DBA • MySQL means nothing to me • It isn't a 'real' RDBMS • Why should I learn about MySQL? • MySQL is being used by real businesses • Can't hurt your employability • Many jobs expect DBA to know multiple RDBMS • Why not learn one that is widely used? • And it's free – you can download and install right now!
Oracle DBA Perspective • What will we look at? • Installing • Start/stop • How database/tables stored • Backups • Transactions • Storage engines
My Message • Compare MySQL to Oracle • Not to be critical of MySQL • Not to praise Oracle • Make Oracle DBAs aware • How much we assume about RDBMS • Because Oracle does things a certain way • MySQL is very different • Assuming it is like Oracle can cause serious problems • Does your business understand the differences? • The cost of being 'free'?
MySQL Versions • MySQL Enterprise • Have to buy support • MySQL Community Edition • The 'free' version • MySQL Community Edition Preview Release • Available for download • Ready for prime time? • Oracle doesn't offer 12g for trial download...
MySQL – Where Did It Come From? • Used to store web site logs • Transactions • What's that? • If we lose some records • So what? • Supported by an individual • Do your own thing • Recovery • Not to worry, plenty more web logs every day
Some Differences – MySQL/Oracle • Oracle • Database • Schema • a group of database objects • Database user • Maps to a database schema • MySQL • Server • Databases • Group of database objects • Users don't map to database
Some Differences – MySQL/Oracle • Storage engines • Transaction support • Performance • Hot backup • Binary logging and recovery • Table locking • Corruption • Misc.
Storage Engines • MySQL gives you a choice • Oracle, all transactional all the time (OATATT) • MySQL storage engines • MyISAM (IBM, Indexed Sequential Access Method) • Very fast read • InnoDB • Transactional, like Oracle • Separate software, owned by Oracle • Many others • See next chart
InnoDB Storage Engine • Imagine mixing • MyISAM, InnoDB tables in a statement • What does 'transactional' mean? • What does “recovery” mean?
CSV Storage Engine • CSV storage engine • Stores data in text files • Using comma-separated format
Blackhole Storage Engine • My favorite This is where we store all the data relating to the Raiders and their 'Commitment to Excellence'
Yeah, But... • No problem • I'll just make all the tables InnoDB • Cool, but • MySQL catalog (system?) tables are MyISAM • Can't change them • Table storage engine can be changed at any time • Privileged users • No one should do this, but...stuff happens • Create table syntax • Supports overriding default storage engine
Transaction Support • MyISAM • Default table type • Table type of system databases • Does not support transactions • Example • Writing to MyISAM tables and server crashes • Who know what was and wasn't written to tables • No recovery
Performance • Many large MySQL servers in use • Performance must be good or good enough for 'free' • Optimizer • Different storage engines • How to optimize statement that has • MyISAM tables • InnoDB tables • Does it support all storage engines you are using?
Hot Backup • MySQL does not have this • Backup option #1 • Shutdown server, copy all files, startup server • Backup option #2 • Lock all tables • Dump all data to a dump file • Dump file is set of SQL to recreate database • Very slow to recover • Backup option #3 • Buy hot backup software product • Not free
Binary Logging and Recovery • MySQL • Default, no logging • You can configure binary logging • Three formats • Statement-based • Row-based • Mixed • Binary log filenames have format • binlog.000001, binlog.000002, etc. • You must apply them in correct order
MySQL Binary Logs • MySQL does not have any way of knowing when replaying the binary logs which statements need to be executed and which are already committed. • Oracle DBA doesn't worry about this • Oracle checks and tells you which archived redo log is needed next • Oracle won't allow archived redo logs to be applied out of order
Table Locking • MyISAM storage engine • Does not support row level locking • Table locks • Performance? • InnoDB storage engine • Supports row level locking
Corruption • Oracle • I have rarely seen any cases • But all tables support transactions • MySQL • What does it mean to rollback • When different storage engines involved • Some support transactions, some don't • Some tables will be rolled back • Others won't • Is this corruption?
MySQL Corruption Issues • Good read • Discusses corruption issues in MySQL engines • I'm not saying this happens often • But you need to be aware of these issues • Another difference from Oracle • Different storage engines, different corruption mechanisms • http://www.mysqlperformanceblog.com/2006/07/30/mysql-crash-recovery/
MySQL Issues • Misc • Referential Integrity • Some storage engines support • Triggers • Row-level, not statement-level • Directload • Not supported • Parallel Query • Not supported
MySQL Issues • White Paper • Compares MySQL to PostgreSQL • PostgreSQL, another open source RDBMS • More like Oracle than MySQL • Discusses issues with MySQL • I'm not endorsing PostgreSQL • But the whitepaper is very good • Google • PostgreSQL vs. MySQL A Comparison of Enterprise Suitability
MySQL Table Storage • MyISAM • Each table has three files • <filename>.frm – Data dictonary information • <filename>.MYD – table data • <filename>.MYI – table indexes • InnoDB • Data and indexes in a tablespaces • Made up of one or more datafiles • Sound familiar?
Talk Is Cheap • A little less conversation • A little more action • Download and install MySQL to your laptop now • Slides show the steps • Windows • You will have a fully functional MySQL server • Start learning right now
Install MySQL – Windows • Windows Vista • 6.0 Service Pack 1 • Screenshots shown • Windows XP • Professional Version 5.1 • Same steps • Very minor differences in what you see
Windows Installation Uncheck