MySQL Would You Like Some Transactions With That Table?
www.brianhitchcock.net
Slides Are Available
Oct 29, 2009
www.brianhitchcock.net
Page 2
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... Oct 29, 2009
www.brianhitchcock.net
Page 3
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! Oct 29, 2009
www.brianhitchcock.net
Page 4
Oracle DBA Perspective What will we look at? Installing Start/stop How database/tables stored Backups
Transactions Storage engines
Oct 29, 2009
www.brianhitchcock.net
Page 5
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'? Oct 29, 2009
www.brianhitchcock.net
Page 6
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...
Oct 29, 2009
www.brianhitchcock.net
Page 7
MySQL Versions
Oct 29, 2009
www.brianhitchcock.net
Page 8
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 Oct 29, 2009
www.brianhitchcock.net
Page 9
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
Oct 29, 2009
www.brianhitchcock.net
Page 10
Some Differences – MySQL/Oracle Storage engines Transaction support Performance Hot backup Binary logging and recovery Table locking Corruption Misc. Oct 29, 2009
www.brianhitchcock.net
Page 11
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 Oct 29, 2009
www.brianhitchcock.net
Page 12
Storage Engines
Oct 29, 2009
www.brianhitchcock.net
Page 13
InnoDB Storage Engine Imagine mixing MyISAM, InnoDB tables in a statement What does 'transactional' mean? What does “recovery” mean?
Oct 29, 2009
www.brianhitchcock.net
Page 14
CSV Storage Engine CSV storage engine Stores data in text files Using comma-separated format
Oct 29, 2009
www.brianhitchcock.net
Page 15
Blackhole Storage Engine ď ŹMy favorite
This is where we store all the data relating to the Raiders and their 'Commitment to Excellence'
Oct 29, 2009
www.brianhitchcock.net
Page 16
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 Oct 29, 2009
www.brianhitchcock.net
Page 17
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
Oct 29, 2009
www.brianhitchcock.net
Page 18
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? Oct 29, 2009
www.brianhitchcock.net
Page 19
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
Oct 29, 2009
www.brianhitchcock.net
Page 20
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 Oct 29, 2009
www.brianhitchcock.net
Page 21
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 Oct 29, 2009
www.brianhitchcock.net
Page 22
Binary Logging Formats
Oct 29, 2009
www.brianhitchcock.net
Page 23
Binary Log Issues
Oct 29, 2009
www.brianhitchcock.net
Page 24
Table Locking MyISAM storage engine Does not support row level locking Table locks
Performance?
InnoDB storage engine Supports row level locking
Oct 29, 2009
www.brianhitchcock.net
Page 25
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? Oct 29, 2009
www.brianhitchcock.net
Page 26
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/
Oct 29, 2009
www.brianhitchcock.net
Page 27
MySQL Issues Misc Referential Integrity Some storage engines support
Triggers Row-level, not statement-level
Directload Not supported
Parallel Query Not supported
Oct 29, 2009
www.brianhitchcock.net
Page 28
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 Oct 29, 2009
www.brianhitchcock.net
Page 29
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? Oct 29, 2009
www.brianhitchcock.net
Page 30
Documentation Online
Oct 29, 2009
www.brianhitchcock.net
Page 31
Documentation Online
Oct 29, 2009
www.brianhitchcock.net
Page 32
Documentation Online
Oct 29, 2009
www.brianhitchcock.net
Page 33
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
Oct 29, 2009
www.brianhitchcock.net
Page 34
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
Oct 29, 2009
www.brianhitchcock.net
Page 35
Downloads?
Oct 29, 2009
www.brianhitchcock.net
Page 36
Downloads?
Oct 29, 2009
www.brianhitchcock.net
Page 37
Enterprise Version
Oct 29, 2009
www.brianhitchcock.net
Page 38
Community Version
Oct 29, 2009
www.brianhitchcock.net
Page 39
Windows Downloads
Oct 29, 2009
www.brianhitchcock.net
Page 40
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 41
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 42
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 43
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 44
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 45
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 46
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 47
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 48
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 49
Windows Installation
Uncheck
Oct 29, 2009
www.brianhitchcock.net
Page 50
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 51
Windows Installation
Check
Oct 29, 2009
www.brianhitchcock.net
Page 52
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 53
Windows Installation mysql
Oct 29, 2009
www.brianhitchcock.net
Page 54
Windows Installation
Oct 29, 2009
www.brianhitchcock.net
Page 55
Accessing MySQL Windows Click Start Programs MySQL MySQL Server 5.1 MySQL Command Line Client Right-click Send to Desktop (Create Shortcut)
Double-click desktop icon
MySQL Command Line Client Oct 29, 2009
www.brianhitchcock.net
Page 56
Accessing MySQL Windows Shortcut created at installation mysql
Oct 29, 2009
www.brianhitchcock.net
Page 57
mysql client utility ď ŹSimilar to SQL*Plus
Oct 29, 2009
www.brianhitchcock.net
Page 58
Now What? Stop, start MySQL server What databases are available show databases;
Set default database use <dbname>
What tables are in the database show tables;
Oct 29, 2009
www.brianhitchcock.net
Page 59
Windows Vista Stop/Start Services
Start Settings Control Panel System and Maintenance Administrative Tools Services Scroll down to MySQL, highlight Upper left, options for Stop, Pause, Restart If MySQL is down, option to Start
Oct 29, 2009
www.brianhitchcock.net
Page 60
Windows XP Stop/Start Command line net stop mysql, net start mysql
Services
Start Control Panel Administrative Tools Services Services window Scroll down to MySQL, highlight Upper left, options for Stop, Pause, Restart If MySQL is down, option to Start
Oct 29, 2009
www.brianhitchcock.net
Page 61
Oct 29, 2009
www.brianhitchcock.net
Page 62
Oct 29, 2009
www.brianhitchcock.net
Page 63
System Databases INFORMATION_SCHEMA Catalog database
MYSQL database User and privilege information
TEST For you to play in...
These databases are created at installation
Oct 29, 2009
www.brianhitchcock.net
Page 64
MySQL Server Configuration Controlled by 'option' file(s) Similar to Oracle pfile or spfile SHOW VARIABLES See current values
Standard location(s) Search order
One of the most common problems Persons don't know where these files are Make changes that don't take affect Can't find the file or files Oct 29, 2009
www.brianhitchcock.net
Page 65
Option File(s) MySQL searches multiple locations Uses most recent value of each parameter How to know all files being scanned by MySQL? mysql –help
Note: dash dash help
Oct 29, 2009
www.brianhitchcock.net
Page 66
Option File
Oct 29, 2009
www.brianhitchcock.net
Page 67
Option File
Oct 29, 2009
www.brianhitchcock.net
Page 68
Option File
Oct 29, 2009
www.brianhitchcock.net
Page 69
Option File
Oct 29, 2009
www.brianhitchcock.net
Page 70
Option File
Oct 29, 2009
www.brianhitchcock.net
Page 71
MySQL Filesystem Layout From option file (my.ini) basedir Where software is stored
datadir Where table data is stored
Oct 29, 2009
www.brianhitchcock.net
Page 72
MySQL basedir
Oct 29, 2009
www.brianhitchcock.net
Page 73
MySQL basedir/bin
Oct 29, 2009
www.brianhitchcock.net
Page 74
MySQL datadir
Oct 29, 2009
www.brianhitchcock.net
Page 75
MySQL Log Files General Query Log All client requests
Binary Log Statements that modify data
Slow Query Log Statements that take longer than 10 seconds
Error Log Server messages Oct 29, 2009
www.brianhitchcock.net
Page 76
MySQL Log Files Find log file locations
Oct 29, 2009
www.brianhitchcock.net
Page 77
Summary MySQL is 'free' May need other products to make it work for you Hot backup for example
MySQL is widely used MySQL is very different from Oracle DBA tasks and issues will be different
MySQL is not better or worse than Oracle Just very different Oct 29, 2009
www.brianhitchcock.net
Page 78
The End? You now have a MySQL server installed No excuses Learn it, live it, love it Update your resume
Next? Multiple MySQL servers on same machine Replication etc. Oct 29, 2009
www.brianhitchcock.net
Page 79