1 / 79

MySQL

MySQL. Would You Like Some Transactions With That Table?. Slides Are Available. 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?

infinity
Download Presentation

MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MySQL Would You Like Some Transactions With That Table?

  2. Slides Are Available

  3. 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...

  4. 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!

  5. Oracle DBA Perspective • What will we look at? • Installing • Start/stop • How database/tables stored • Backups • Transactions • Storage engines

  6. 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'?

  7. 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...

  8. MySQL Versions

  9. 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

  10. 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

  11. Some Differences – MySQL/Oracle • Storage engines • Transaction support • Performance • Hot backup • Binary logging and recovery • Table locking • Corruption • Misc.

  12. 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

  13. Storage Engines

  14. InnoDB Storage Engine • Imagine mixing • MyISAM, InnoDB tables in a statement • What does 'transactional' mean? • What does “recovery” mean?

  15. CSV Storage Engine • CSV storage engine • Stores data in text files • Using comma-separated format

  16. Blackhole Storage Engine • My favorite This is where we store all the data relating to the Raiders and their 'Commitment to Excellence'

  17. 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

  18. 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

  19. 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?

  20. 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

  21. 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

  22. 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

  23. Binary Logging Formats

  24. Binary Log Issues

  25. Table Locking • MyISAM storage engine • Does not support row level locking • Table locks • Performance? • InnoDB storage engine • Supports row level locking

  26. 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?

  27. 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/

  28. MySQL Issues • Misc • Referential Integrity • Some storage engines support • Triggers • Row-level, not statement-level • Directload • Not supported • Parallel Query • Not supported

  29. 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

  30. 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?

  31. Documentation Online

  32. Documentation Online

  33. Documentation Online

  34. 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

  35. 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

  36. Downloads?

  37. Downloads?

  38. Enterprise Version

  39. Community Version

  40. Windows Downloads

  41. Windows Installation

  42. Windows Installation

  43. Windows Installation

  44. Windows Installation

  45. Windows Installation

  46. Windows Installation

  47. Windows Installation

  48. Windows Installation

  49. Windows Installation

  50. Windows Installation Uncheck

More Related