Mysql would you like transactions

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