WIXLIB

MySQL for OracleDBAs and Developers

MySQL for Oracle DudesHow can you tell anOracle DBAhas touched yourMySQL Installation?Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 2

MySQL for Oracle DudesMYSQL HOME /home/oracle/products/mysql-versionmysqld safe –user oracle &Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 3

MySQL for Oracle DudesOutline DBA Tips, Tricks, Gotcha's & Tools Key Differences for Developers Migrating from Oracle to MySQL Questions & AnswersRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 4

MySQL for Oracle DudesMy Background 18 years in Database Technologies (1989) 10 years Oracle Experience (1996) 8 years MySQL Experience (1999) Active in MySQL, Java, XP User Groups Joined MySQL Professional Services (2006)Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 5

MySQL for Oracle DudesDBARonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 6

MySQL for Oracle DudesImportant DBA Stuff Terminology MySQL Data Dictionary Installation Backup Directories Tools Log Files Inherited LAMP Stack Processes Ports & SocketsRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 7

MySQL for Oracle DudesTerminologyDatabase (files)-- Database Server InstanceDatabase Instance (memory)-- Database Server InstanceSchema User-- DatabaseUser-- UserTable Space-- Table Space-- Storage EngineRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 8

MySQL for Oracle DudesMySQL Installation OS packages place files in many areas and varies- e.g. /usr/lib, /var/lib, /var/log, /etc TipRecommend installing using .tar.gz- Centrally Manageable e.g. /opt/mysql-version- Upgradeable- Multiple Versions possible MYSQL HOMERonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 9

MySQL for Oracle DudesMySQL ConfigurationGOTCHA Multiple MySQL Instances my.cnf- Watch out for /etc/my.cnf, /etc/mysql/my.cnf- Best Practice MYSQL HOME/my.cnf- --defaults-file file s.htmlRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 10

MySQL for Oracle DudesMySQL Directoriesmy.cnf options basedir( MYSQL HOME)- e.g. /opt/mysql-5.1.16-beta-linux-i686-glib23 datadir(defaults to MYSQL HOME/data) tmpdir(important as mysql behaves unpredictability if full) innodb [.] home dir- mysql SHOW GLOBAL VARIABLES LIKE '%dir'Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 11

MySQL for Oracle DudesMySQL Ports & Sockets Configured to listen on TCP/IP Port (default 3306) Additional Instances- Different Ports- Different IP's using default Port Local connection uses Socket- Even specifying Port, local client may use socketRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 12

MySQL for Oracle DudesMySQL Log Files my.cnf optionsmysqld argError Log- log-error Binary Log- log-bin (my.cnf or mysqld arg)Slow Query Log- sing-indexes General Log- s.htmlRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 13

MySQL for Oracle DudesMySQL Meta DataNew Feature mysql Database- general log, slow log (5.1)mysql SET GLOBAL GENERAL LOG 1;mysql .mysql SELECT * FROM mysql.general les.htmlRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 14

MySQL for Oracle DudesMySQL Data Dictionary[DBA USER ALL] tables, V INFORMATION SCHEMA- TABLES, COLUMNS, VIEWS, USER PRIVILEGES- PROCESSLIST (5.1)- [GLOBAL SESSION] [STATUS VARIABLES] mation schema/5.1/MySQL 5 1 INFORMATION SCHEMA.htmlRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 15

MySQL for Oracle DudesRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 16

MySQL for Oracle DudesMySQL Data Dictionary SQL ExamplesSELECT TABLE SCHEMA, SUM((DATA LENGTH INDEX LENGTH) / (1024 * 1024)) AS SIZE MBFROM INFORMATION SCHEMA.TABLESGROUP BY TABLE SCHEMA ORDER BY SIZE MB DESCSELECT ROUTINE TYPE, ROUTINE NAME FROM INFORMATION SCHEMA.ROUTINESWHERE ROUTINE SCHEMA 'dbname';SELECT TRIGGER NAME,EVENT MANIPULATION,EVENT OBJECT TABLE,ACTION STATEMENTFROM INFORMATION SCHEMA.TRIGGERS WHERE TRIGGER SCHEMA 'dbname';SELECT CONCAT('DROP TABLE ',table name,';')INTO OUTFILE '/sql/drop tables.sql'FROM INFORMATION SCHEMA.TABLES WHERE TABLE SCHEMA 'test';Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 17

MySQL for Oracle DudesMySQL Data Dictionary SQL ExamplesSELECT s.schema name, CONCAT(IFNULL(ROUND((SUM(t.data length) SUM(t.index length))/1024/1024,2),0.00),'Mb') total size,CONCAT(IFNULL(ROUND(((SUM(t.data length) SUM(t.index length))SUM(t.data free))/1024/1024,2),0.00),'Mb')data used,CONCAT(IFNULL(ROUND(SUM(data free)/1024/1024,2),0.00),'Mb') data free,IFNULL(ROUND((((SUM(t.data length) SUM(t.index length))SUM(t.data free))/((SUM(t.data length) SUM(t.index length)))*100),2),0) pct used,COUNT(table name) total tablesFROM information schema.schemata sLEFT JOIN information schema.tables t ON s.schema name t.table schemaWHERE s.schema name ! 'information schema'GROUP BY s.schema name ORDER BY pct used DESC\GRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 18

MySQL for Oracle DudesSHOW Commands SHOW TABLES; SHOW WARNINGS; SHOW STATUS; FLUSH STATUS; SHOW VARIABLES; SHOW VARIABLES LIKE '%size%'; SHOW VARIABLES LIKE 'sort buffer size'; SHOW GLOBAL VARIABLES LIKE 'sort buffer size';Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 19

MySQL for Oracle DudesBackupMissing Functionality Commercial strength – unbreakable (Planned 6.0) Storage Engine Driven InnodbAlso PBXT, Falcon- Hot Backup- mysqldump --single-transaction --master-data- InnoDB Hot BackupRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 20

MySQL for Oracle DudesBackup MyISAM Only- Cold Backup via File Copy- LVM Snapshot SE Mixture- Use Replication SlaveRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 21

MySQL for Oracle DudesOnline Backup/RecoveryMySQL 6.0 Demo Cross-engine. All major internal engines supported. Online, non-blocking for DML. DDL still blocked. SQL-command driven. Run from any mysql client. Backup to local disk, tape or remote file system. Full Server, database, and point-in-time recovery.Backup ALL Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 22

MySQL for Oracle DudesToolsMissing Functionality -Enterprise Level Monitoring SHOW PROFILE (5.0.38 - Community) Microsecond Patch mytop/innotop/ndbtop MySQL Toolkit phpMyAdmin(5.0.33 - Slow query oolkitRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 23

MySQL for Oracle DudesSHOW PROFILEmysql show profile SOURCE,MEMORY for query 4; -------------------- ------------ ----------------------- --------------- ------------- Status Duration Source function Source file Source line -------------------- ------------ ----------------------- --------------- ------------- Opening tables 0.00013200 open tables sql base.cc 2106 System lock 0.00001800 mysql lock tables lock.cc 153 Table lock 0.00000600 mysql lock tables lock.cc 162 init 0.00001300 mysql select sql select.cc 2073 optimizing 0.00004800 optimize sql select.cc 617 statistics 0.00002500 optimize sql select.cc 773 preparing 0.00005200 optimize sql select.cc 783 executing 0.00002200 exec sql select.cc 1407 Sending data 0.00000500 exec sql select.cc 1925 end 0.00786600 mysql select sql select.cc 2118 query end 0.00001400 mysql execute command sql parse.cc 5085 freeing items 0.00000700 mysql parse sql parse.cc 5973 closing tables 0.00001900 dispatch command sql parse.cc 2120 logging slow query 0.00001000 log slow statement sql parse.cc 2178 cleaning up 0.00000500 dispatch command sql parse.cc 2143 -------------------- ------------ ----------------------- --------------- ------------- 15 rows in set (0.01 sec) 95% time in one step Reference to Source Code “poor” Status names (internal code)Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 24

MySQL for Oracle DudesGUI Tools MySQL Administrator Quest Spotlight Toad MySQL Network Monitoring & Network ServicesMySQLEnterpriseRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 25

MySQL for Oracle DudesToolsTIP mysqladmin -r -i 1 extended-status- Gives change in status variables per second- Lacks timestamp Monitor specifics-Com *Innodb *, Innodb buffer pool *ConnectionsCreated tmp *Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 26

MySQL for Oracle DudesToolsExample Idle- mysqladmin -r -i 1 extended-status grep -v “ 0 “ ----------------------------------- ------------ Variable name Value ----------------------------------- ------------ Bytes received 35 Bytes sent 6155 Com show status 1 Created tmp tables 1 Handler read rnd next 246 Handler write 245 Questions 1 Select scan 1 Uptime 1 ----------------------------------- ------------ Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 27

MySQL for Oracle DudesTools Under load- mysqladmin -r -i 1 extended-status grep -v “ 0 “ ----------------------------------- ------------ Variable name Value ----------------------------------- ------------ Bytes received 1020909 Bytes sent 195358 Com insert 274 Com select 132 Com set option 264 Handler read key 505 Handler update 252 Handler write 519 Questions 1356 Table locks immediate 536 Table locks waited 2 ----------------------------------- ------------ Erroneous CommandsCausing Round TripsBuried in JDBC UsagePreparedStatement.setMaxRows()Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 28

MySQL for Oracle DudesInherited LAMP Stack ProductTIP Problem: Frozen, some functions work- Lack of Free Disk Space Problem: Running slow- Increase Buffers- Change Storage Engine Problem: Can't connect- ConnectionsRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 29

MySQL for Oracle DudesInherited LAMP Stack Product A lot of products are non-transactional Not designed for large volume enterprisesRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 30

MySQL for Oracle DudesDefault InstallationGOTCHA No 'root' user password Anonymous users mess with host based security Improve overall security mysql secure /mysql-secure-installation.htmlRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 31

MySQL for Oracle DudesAUTO COMMITGOTCHA By Default enabled in MySQL- Ops I deleted the wrong data, I'll just ROLLBACK- Non Transactional Storage Engines- SET AUTOCOMMIT {0 1};Ronald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 32

MySQL for Oracle DudesSQL*Plus Reporting No Alternative Nice Feature- Vertical Output Display- SELECT columns FROM table \G Write your own in MySQL SourceRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 33

MySQL for Oracle DudesNice MySQL FeaturesTIP SELECT INTO OUTFILE . LOAD DATA FILE . DROP [object] IF EXISTS . ALTER TABLE . ADD . AFTER [column] Query CacheRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 34

MySQL for Oracle DudesQuery CacheSELECT Cache (great for high read environments) -Being Added to Oracle che.htmlRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 35

MySQL for Oracle DudesContrasting BuffersAreaMySQLOracleMicrosoft SQL ServerMemory Caches MyISAM key caches InnoDB data cache InnoDB log cache Dictionary cache Falcon caches Query Cache User caches Data cache (variants) Log buffer Shared Pool Java Pool Large Pool PGA Buffer cache SQL cache Misc caches (lock,connection, workspace, etc.)Redo/Undo Logs InnoDB Undo Space InnoDB Logs Falcon Log Binary Log Undo Tablespace (9i ) Redo Logs Archive Logs TempDB (2005 ) Transaction LogsData Storage Tablespaces Table/Index Files Format files Tablespaces Datafiles Filegroups FilesOptimizer Cost-based Cost-based Cost-basedRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 36

MySQL for Oracle DudesDEVELOPERRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 37

MySQL for Oracle DudesImportant Developer Stuff Sequence Replacement SQL MODE No DUAL Necessary TIMESTAMP Data Type Data Comparison New things DDL Syntax Stored Procedures LockingRonald Bradford, MySQL IncMySQL Conference & Expo 2007Page: 38