MySQL for Oracle DBAs

Click here to load reader

  • date post

    09-May-2015
  • Category

    Technology

  • view

    233
  • download

    2

Embed Size (px)

description

Some explanations for Oracle DBAs how MySQL works and what MySQL features relate to what Oracle Features...

Transcript of MySQL for Oracle DBAs

  • 1.www.fromdual.com1 / 31MySQL fr Oracle DBAsDOAG Webinar 14. Juni 2013Oli SennhauserSenior MySQL Consultant, FromDual [email protected]

2. www.fromdual.com2 / 31ber FromDual GmbH FromDual bietet neutral und unabhngig: Beratung fr MySQL Support fr MySQL und Galera Cluster Remote-DBA Dienstleistungen fr MySQL MySQL Schulungen Oracle Silver Partner (OPN) Mitglied der SOUG, DOAG, /ch/openwww.fromdual.com 3. www.fromdual.com3 / 31InhaltHA SolutionsRead scale-outReplication set-up for HAActive/passive fail-overMySQL ClusterReplication ClusterStorage-Engine-ReplicationMySQL fr Oracle DBAsEinsatz von MySQLInstallation, Konfiguration, Starten/StoppenArchitektur, Storage EnginesInnoDBMonitoring, LoggingBackup, Restore, Point-in-Time-RecoveryReplikationHochverfgbarkeitRAC fr MySQL 4. www.fromdual.com4 / 31Einsatz von MySQLWo wird MySQL eingesetzt: Facebook 1 Mia User, 72 M QPS Google Adwords, Mia Umsatz/Jahr(MOMF1) Wikipedia z. Zt. #6 weltweit BrseGo Online Brsenhandel Playboy Drupal CMS EMKA ERP, 1000 MA V-Zug Hybris Webshop Buch.de #2 online Buchhndler in D Kikxxl Callcenter, 1000 MA Integrics VoIP Lsungen, 1000e Anschlssen RePower zig 1000 Windmhlen 5. www.fromdual.com5 / 31InstallationOracle: OUI (Oracle Universal Installer) MySQL: Windows: InstallerC:Programfilesmysqlmysqlserver5.6C:Programfilesmysqlmysqlserver5.6data MySQL Linux: Pakete: *.rpm, *.deb/usr//var/lib/mysql Binary Tar-Ball: mysql5.7.1linuxx86_64.tar.gz Quellen Kompilieren: cmake;make;makeinstall MySQL Community vs. Enterprise, Drittanbieter 6. www.fromdual.com6 / 31MySQL Plattform Exotische Plattformen fhren ausstatistischen Grnden eher zu Problemen! 85.7% Linux 10.5% Windows 1.7% Solaris 1.4% BSD 0.7% Others 7. www.fromdual.com7 / 31KonfigurationOracle: $ORACLE_HOME/dbs/init.oraMySQL: Windows:C:ProgramFilesmysqlmysqlserver5.6my.ini Linux:/etc/my.cnf,/etc/mysql/my.cnf,$basedir/my.cnf !includedir/etc/mysql/conf.d/ defaultsfile,defaultsextrafile 8. www.fromdual.com8 / 31Konfigurations-Parameter my.cnf/my.ini mysql>SHOWGLOBALVARIABLES; 5.1.69: 277 5.5.31: 317 5.6.11: 429 mysql>SETGLOBALvariable=value; Kein Persistieren (spfile) 9. www.fromdual.com9 / 31Starten / stoppenOracle: sqlplus/assysdbaSTARTUP MySQL Linux: Alt: shell>/etc/init.d/mysqlstart|stop|restart Neu: shell>servicemysqlstart|stop|restart von Hand: shell>bin/mysqld_safe& shell>bin/mysqld& shell>mysqladminuser=rootshutdown MySQL Windows Windows Service Utility cmd>netstart|stop|restartMySQL 10. www.fromdual.com10 / 31Tools Tools: sqlplus mysql srvmgrl mysqladmin MySQL Workbench Admin Query Browser ER - Diagramme Heidi SQL, phpMyAdmin 11. www.fromdual.com11 / 31Prozess-ArchitekturOracle: Multi-Prozess ModellShared MemoryPMON,SMON,RECO,DBW0,LGWR,ARC0, ... MySQL: Multi-Thread Modellmysqld Angel-Prozessmysqld_safe Vordergrund- und Hintergrund-Threads: 12. www.fromdual.com12 / 31MySQL Thread Architekturmysql> SELECT thread_id, name AS thread_name, type, processlist_user AS userFROM performance_schema.threads;+-----------+----------------------------------------+------------+------+| thread_id | thread_name | type | user |+-----------+----------------------------------------+------------+------+| 1 | thread/sql/main | BACKGROUND | NULL || 2 | thread/innodb/io_ibuf_thread | BACKGROUND | NULL || 3 | thread/innodb/io_log_thread | BACKGROUND | NULL || 4 | thread/innodb/io_read_thread | BACKGROUND | NULL || 11 | thread/innodb/io_write_thread | BACKGROUND | NULL || 14 | thread/innodb/srv_error_monitor_thread | BACKGROUND | NULL || 15 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | NULL || 16 | thread/innodb/srv_monitor_thread | BACKGROUND | NULL || 17 | thread/innodb/srv_master_thread | BACKGROUND | NULL || 18 | thread/innodb/srv_purge_thread | BACKGROUND | NULL || 19 | thread/innodb/page_cleaner_thread | BACKGROUND | NULL || 20 | thread/sql/signal_handler | BACKGROUND | NULL || 22 | thread/sql/one_connection | FOREGROUND | root || 28 | thread/sql/one_connection | FOREGROUND | oli |+-----------+----------------------------------------+------------+------+shell> ps -efL | egrep mysqld|PIDUID PID PPID LWP C NLWP STIME TTY TIME CMDmysql 3248 1 3248 0 1 16:28 pts/0 00:00:00 /bin/sh bin/mysqld_safemysql 3925 3248 3925 0 23 16:28 pts/0 00:00:00 bin/mysqld...mysql 3925 3248 4088 0 23 16:31 pts/0 00:00:00 bin/mysqld 13. www.fromdual.com13 / 31Connections / Connectors Verbindung In MySQL billig: oft KEIN Connection-Pooling 1 Verbindung = 1 Thread 1 Query 1 Core Thread Pool (1000e von Verbindungen) Connectors: JDBC/ODBC PHP, Perl, Python, Ruby, .NET 14. www.fromdual.com14 / 31User und Schema User [email protected] Unix Socket [email protected] TCP Port [email protected]% TCP von berall her Privilegien Global: *.*, pro Schema , pro Tabelle, pro Spalte Schema (= Database) Unabhngig vom User ( gehrt System) 15. www.fromdual.com15 / 31Storage EnginesmysqldApplication / ClientThreadCacheConnectionManagerUser Au-thenticationCommandDispatcherLoggingQuery CacheModuleQueryCacheParserOptimizerAccess ControlTable ManagerTable OpenCache (.frm, fh)Table DefinitionCache (tbl def.)Handler InterfaceMyISAM Memory NDB TokutekInnoDB ...AriaBright-houseFederated-X 16. www.fromdual.com16 / 31InnoDB (default SE >= 5.5.) Transaktionen (ACID) Isolation Level (repeatable-read vs read-committed) Tabelspaces System TS = ibdata1 Table TS (innodb_files_per_table=1) InnoDB: PK geclusterte Tabellen IOT InnoDB Buffer Pool (16k Block Buffer) REDO Logs: ib_logfile? (5M default) 17. www.fromdual.com17 / 31Logging Error Log (~ alert_.log) /var/log/mysql* General Query Loggeneral_log=1 Slow Query Log slow_query_log=1 Binary Log (~ archive.log) log_bin=1/binarylog DML + DDL aller SE (Transaktions-Log, ib_logfile?, SE abhngig) (~ redo.log) DML InnoDB 18. www.fromdual.com18 / 31Logisches BackupOracle: exp/imp (vor Datapump) MySQL: mysqldump/mysql Jede Row wird angelangt: Langsam Restore SEHR langsam!mysql>mysqldumpalldatabasessingletransactionmasterdata=1>full_dump.sqlmysql>mysqlinnobackupex/data/backupsshell>innobackupexapplylog/data/backups/20121113/shell>innobackupexcopyback/data/backups/20121113/shell>chownRmysql:mysql/var/lib/mysql Links:http://www.lenzg.net/mylvmbackup/ http://www.percona.com/doc/percona-xtrabackup 20. www.fromdual.com20 / 31Binary Log~ Oracle Archive Logs MySQL Binary-Log: DDL + DML aller SE (nicht nur InnoDB)! fr:Replikation Point-in-Time-Recovery (PiTR) 3 Varianten: Statement Based Replication (SBR) SHOWGLOBALSTATUS; Nagios / Icinga Links:http://www.mysql.com/products/enterprise/monitor.htmlhttp://www.fromdual.com/mysql-performance-monitorhttp://www.fromdual.com/download#nagios 28. www.fromdual.com28 / 31Performance Tuning mysql>SHOWGLOBALSTATUS; PERFORMANCE_SCHEMA Slow Query Log slow_query_log=1 long_query_time=0.5 shell>mysqldumpslowstslow.log>profile Query Execution Plan:mysql>EXPLAINSELECT*FROMtest; 29. www.fromdual.com29 / 31Stored ProgramsOracle: PL/SQL, Java MySQL: Stored Procedures Stored Functions User Defined Functions (UDF): C/C++ Plugin: C/C++ 30. www.fromdual.com30 / 31Volltext-SucheOracle: Kostenpflichtiges Modul? MySQL: Standardmssig dabei!ALTER TABLE test ADD FULLTEXT INDEX (data);SELECT * FROM test WHERE MATCH data AGAINST(DBA);+----+----------------------------------------+| id | data |+----+----------------------------------------+| 1 | Wir suchen zur Zeit einen Support DBA! |+----+----------------------------------------+ 31. www.fromdual.com31 / 31Q & AFragen ?Diskussion?Wir haben Zeit fr ein persnliches Gesprch... FromDual bietet neutral und unabhngig: Beratung Remote-DBA Support fr MySQL, Galera, Percona Server und MariaDB Schulungwww.fromdual.com/presentations