Zend2016 dba tutorial

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | A MySQL DBA Primer Ligaya Turmelle Principal Technical Support Engineer -‐ MySQL ligaya.turmelle@oracle.com @lig , Oracle and/or its aﬃliates. All rights reserved. | 2 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direc@on. It is intended for informa@on purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or func@onality, and should not be relied upon in making purchasing decisions. The development, release, and @ming of any features or func@onality described for Oracle’s products remains at the sole discre@on of Oracle. 3 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Agenda -‐ Second Half • Log Files (con’t) • Security • Backup • Monitoring • Basic Troubleshoo@ng 5 6 7 8 9 5 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | MySQL.com • mysql.com - Commercial site • dev.mysql.com (AKA mysql.org) - Developer Zone • downloads.mysql.com 11 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | MySQL.com • mysql.com - Commercial site • dev.mysql.com (AKA mysql.org) - Developer Zone • downloads.mysql.com • dev.mysql.com/doc/ - Documentation 12 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | MySQL.com • mysql.com - Commercial site • dev.mysql.com (AKA mysql.org) - Developer Zone • downloads.mysql.com • dev.mysql.com/doc/ - Documentation • bugs.mysql.com 13 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | GeQng Help • Manual • MySQL Support • MySQL Forums (forums.mysql.com) • Mailing Lists (lists.mysql.com) • Search the Web 18 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | GeQng Help • Manual • MySQL Support • MySQL Forums (forums.mysql.com) • Mailing Lists (lists.mysql.com) • Search the Web • IRC on freenode -‐ #mysql 19 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Main Features • Works on mul@ple pla^orms • Mul@-‐layered server design • Pluggable architecture -‐ Storage engine -‐ Misc plugins (Ex: validate_password, SHA256_password) • Mul@-‐threaded • “Plays well” with most programming languages 20 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Enterprise EdiNon Add-‐Ons • MySQL Enterprise Backup (MEB) • MySQL Enterprise Monitor (MEM) • Workbench • Plugins • Threadpool, Audit, Firewall, TDE, Encryp@on, PAM, etc • Oracle Enterprise Manager for MySQL 22 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | OpNon Files • AKA configura@on files • Lists specific startup op@ons # The following options will be passed to all MySQL clients [client] #password! = your_password port! ! = 3626 socket!! = /tmp/mysql.5.6.26.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port! = 3626 socket!= /tmp/mysql.5.6.26.sock log-error = /Users/ligaya/mysql_installs/ mysql-5.6.26-osx10.9-x86_64/data/error.log server-id! = 1 table_open_cache = 4000 25 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Common LocaNons of OpNon File • Windows -INSTALLDIRmy.ini -INSTALLDIRmy.cnf • Linux & Mac -/etc/my.cnf -/etc/mysql/my.cnf 26 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Generic Unix/Linux Binary File Layout Directory Contents bin, scripts Client programs, mysqld server and u@lity programs data Log files and databases docs Manual in Info format man Unix manual pages include Include (header) files lib Libraries share Misc support files including error messages, sample op@on files and SQL for database installa@on 27 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Linux RPM File Layout Directory Contents /usr/bin Client programs and scripts /usr/sbin mysqld server /var/lib/mysql log files and databases /usr/share/info Manual in Info format /usr/share/man Unix manual pages /usr/include/mysql Include (header) files /usr/lib/mysql Libraries /usr/share/mysql Misc support files including error messages, character set files, sample op@on files and SQL for database installa@on 28 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Windows File Layout Directory Contents Notes bin, scripts Client programs, mysqld server and u@li@es scripts %ALLUSERSPROFILE% MySQLMySQL Server 5.7 Log files and databases (Windows XP, Windows Server 2003) The Windows system variable %ALLUSERSPROFILE% defaults to C:Documents and Settings All UsersApplication Data %PROGRAMDATA% MySQLMySQL Server 5.7 Log files and databases (Vista, Windows 7, Windows Server 2008 and newer) The Windows system variable %PROGRAMDATA% defaults to C: ProgramData examples Example programs and scripts include include (header) files lib Libraries share Misc support files including error messages, character set files, sample op@on files and SQL for database installa@on 29 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | InstallaNon • Well documented in the Manual • Each OS is documented -‐ some with mul@ple ways to do it -‐ Unix/Linux Generic Binaries -‐ Windows -‐ Mac -‐ Various Linux (MySQL Repositories, RPM, Na@ve Repositories) -‐ Solaris -‐ Source 31 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Generic Binary InstallaNon shell> groupadd mysql shell> useradd -r -g mysql mysql shell> cd /usr/local shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz shell> ln -s full-path-to-mysql-VERSION-OS mysql shell> cd mysql shell> mkdir mysql-files shell> chmod 770 mysql-files shell> chown -R mysql . shell> chgrp -R mysql . shell> bin/mysql_install_db --user=mysql # Before 5.7.6 shell> bin/mysqld --initialize --user=mysql # 5.7.6 and up shell> bin/mysql_ssl_rsa_setup # 5.7.6 and up shell> chown -R root . shell> chown -R mysql data mysql-files shell> bin/mysqld_safe --user=mysql & # optionally start the server # Next command is optional shell> cp support-files/mysql.server /etc/init.d/mysql.server 32 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | StarNng it up • Various methods -mysqld_safe -mysqld -mysql.server -service mysqld start -‐ Windows Service • Unix/Linux -‐ run as a non-‐root account • Windows -‐ admin rights 33 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | ShuQng it down • Manually -mysqladmin shutdown -/etc/init.d/mysql stop -service mysqld stop -NET STOP MySQL -Services Manager -SHUTDOWN (5.7) 34 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | CLI -‐ mysql client • Easy to use • Simple SQL shell • Server side help • Many op@ons -‐ Example: --i-am-a- dummy • Has its own commands mysql> select count(*) from inventory; +----------+ | count(*) | +----------+ | 4581 | +----------+ 1 row in set (0.08 sec) mysql> ALTER TABLE rental DROP FOREIGN KEY `fk_rental_inventory`; Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT rental_id from rental order by RAND() LIMIT 10; +-----------+ | rental_id | +-----------+ | 12539 | | 8592 | | 15699 | 36 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Workbench • GUI -‐ SQL Development -‐ Data Modeling -‐ Server Administra@on -‐ Data Migra@on -‐ Performance Tools and Reports -‐ Integrated with MEB and Audit -‐ MySQL U@li@es 37 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | InnoDB • General-‐purpose storage engine • As of 5.5, is the default engine • Features: -‐ ACID compliant -‐ Row level locking with consistent reads -‐ Foreign keys -‐ Clustered primary key index -‐ FullText Indexes (5.6) & Geospa@al data and indexes (5.7) 46 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | InnoDB (con’t) • Features Con@nued: -‐ Tablespaces • ﬁle per table -‐ transportable -‐ encrypted • undo logs • general -‐ Compressed Data 47 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | MyISAM • Default storage engine prior to 5.5 • Stored on disk as 3 ﬁles • Features -‐ FullText indexes -‐ Geospa@al datatypes support •Legacy -‐ Not recommended with new systems 48 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Error Log • --log-error[=file_name] • Default loca@on: host_name.err in datadir • Examples of stuﬀ logged -‐ Start and stops & Cri@cal errors -‐ MyISAM tables that need to be checked or repaired -‐ some OS’s -‐ stack trace if mysqld crashes • (5.7) log_syslog to send MySQL error log to syslog • (5.7) log_error_verbosity 51 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Slow Query Log • First line of offense for tuning queries • Why? -‐ Performance usually • Enable dynamically or with --slow-query-log -‐ Default file loca@on: host_name-slow.log in the datadir -‐ Can also go to a table • Mul@ple op@ons for controlling it • Aggregate its data with mysqldumpslow u@lity 52 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | mysqldumpslow • Parses slow query log and aggregates the data • Default -‐ groups queries that are similar -‐ “abstracts” out speciﬁc string and number data values • Op@ons for sor@ng: -‐ t, at -‐ sort by query @me or average query @me -‐ l, al -‐ sort by lock @me or average lock @me -‐ r, ar -‐ sort by rows or average rows sent -‐ c -‐ sort by count 53 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Example mysqldumpslow output Count: 6 Time=0.36s (2s) Lock=0.00s (0s) Rows=1.0 (6), agent[agent]@localhost SELECT @@version NOT LIKE "S" AND (SELECT COUNT(*) < N FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'S') AND (SELECT COUNT(*) = N FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE 'S') Count: 2 Time=0.24s (0s) Lock=0.10s (0s) Rows=1.0 (2), agent[agent]@localhost SELECT ROUND(SUM(sum_timer_wait)/N) total_wait_time_ms, SUM(count_star) total_statements, ROUND(MAX(max_timer_wait)/N) max_wait_time_ms, SUM(sum_errors) total_errors, SUM(sum_warnings) total_warnings, SUM(sum_rows_sent) total_rows_returned, ROUND(SUM(sum_lock_time)/N) total_lock_time_ms FROM performance_schema.events_statements_summary_by_digest Count: 100 Time=0.22s (22s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost CREATE TABLE `t1` (id serial,id0 varchar(N) unique key,intcol1 INT(N) ,intcol2 INT(N) ,intcol3 INT(N) ,intcol4 INT(N) ,intcol5 INT(N) ,charcol1 VARCHAR(N),charcol2 VARCHAR(N),charcol3 VARCHAR(N),charcol4 VARCHAR(N),charcol5 VARCHAR(N),charcol6 VARCHAR(N),charcol7 VARCHAR(N),charcol8 VARCHAR(N),charcol9 VARCHAR(N),charcol10 VARCHAR(N)) Count: 300 Time=0.20s (60s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost CREATE TABLE `t1` (id serial,id0 varchar(N) unique key,intcol1 INT(N) ,intcol2 INT(N) ,intcol3 INT(N) ,intcol4 INT(N) ,intcol5 INT(N) ,intcol6 INT(N) ,intcol7 INT(N) ,intcol8 INT(N) ,intcol9 INT(N) ,intcol10 INT(N) ,charcol1 VARCHAR(N),charcol2 VARCHAR(N),charcol3 VARCHAR(N),charcol4 VARCHAR(N),charcol5 VARCHAR(N),charcol6 VARCHAR(N),charcol7 VARCHAR(N),charcol8 VARCHAR(N),charcol9 VARCHAR(N),charcol10 VARCHAR(N)) 54 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | EXPLAIN • Displays query execu@on plan • Syntax: -‐ Prior to 5.6: • EXPLAIN [explain_type] SELECT select_options -‐ 5.6: • EXPLAIN [explain_type] explainable_stmt 56 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | EXPLAIN • Talk all by itself • 1 row == 1 table • Order mapers • EXTENDED + SHOW WARNINGS = rewripen query • Columns: -‐ id, select_type, table, par@@ons, type, possible_keys, key, key_length, ref, rows, ﬁltered, Extra (con’t) 57 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | General Query Log • General Record • Why: -‐ Order is important -‐ Exact query that came in -‐ minimal audit of what a connec@on did • Enable Dynamically or with --general-log -‐ Default loca@on: host-name.log in datadir • Mul@ple op@ons for controlling it 61 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Binary Log • Logs database *change* events • Why? -‐ Replica@on or Data Recovery • Enable with --log-bin • A LOT of op@ons • “Read” with mysqlbinlog • To disable binary logging for the current session, use sql_log_bin 62 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | mysqlbinlog • Display binary/relay logs in text format • Syntax: mysqlbinlog [options] logfile • Can work with any replica@on format -‐ Reading Row Based Replica@on • --verbose --base64-output=DECODE-ROWS • Can backup binary logs to remote server -‐ Sta@c or streaming • --read-from-remote-server 63 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Example mysqlbinlog output # at 316 #130512 20:22:57 server id 1 end_log_pos 364 CRC32 0xa7a9fe8c !GTID [commit=yes] SET @@SESSION.GTID_NEXT= '6e521de8-bb62-11e2-836e-821afda5425c:2'/*!*/; # at 364 #130512 20:22:57 server id 1 end_log_pos 443 CRC32 0xf9bf1aaa !Query! thread_id=3 ! exec_time=0!error_code=0 SET TIMESTAMP=1368404577/*!*/; BEGIN /*!*/; # at 443 #130512 20:22:57 server id 1 end_log_pos 542 CRC32 0x1b82a82c !Query! thread_id=3 ! exec_time=0!error_code=0 SET TIMESTAMP=1368404577/*!*/; insert into t1 values (1) /*!*/; # at 542 #130512 20:22:57 server id 1 end_log_pos 573 CRC32 0x22965d3b !Xid = 29 COMMIT/*!*/; # at 573 #130512 20:23:01 server id 1 end_log_pos 621 CRC32 0x89e5d253 !GTID [commit=yes] SET @@SESSION.GTID_NEXT= '6e521de8-bb62-11e2-836e-821afda5425c:3'/*!*/; 64 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | User Account • 2 parts -‐ username -‐ hostname -‐ Ex: • ‘root’@‘localhost’ • ‘’@‘127.0.0.1’ • ‘root’@‘192.168.1.%’ • ‘lig’@’%’ 67 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Securing a New InstallaNon Prior to 5.7 • Ini@al accounts -‐ Superuser (root) -‐ Anonymous (“”) • test database (con’t) 68 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Securing a New InstallaNon Prior to 5.7 • Ini@al accounts -‐ Superuser (root) -‐ Anonymous (“”) • test database • Securing the easy way -mysql_secure_installation (con’t) 69 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Securing a New InstallaNon Prior to 5.7 • Ini@al accounts -‐ Superuser (root) -‐ Anonymous (“”) • test database • Securing the easy way -mysql_secure_installation • Hard way (con’t) 70 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Securing a New InstallaNon in 5.7 71 • Done during data directory installa@on • mysqld --initialize -‐ Secure by default • Generate random password for `root`@`localhost` account and writes message with it • Password marked as expired • mysqld --initialize-insecure -‐ Empty password for `root`@`localhost` account -‐ Does *NOT* mark password as expired Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Securing a New InstallaNon in 5.7 72 • Assigning a new `root`@localhost` password (con’t) mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘new-password’; Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | User Accounts • CREATE USER • ALTER USER (5.6) 75 mysql> CREATE USER ‘lig’@‘localhost’ IDENTIFIED BY ‘S3cr3t’; mysql> ALTER USER ‘lig’@‘localhost’ IDENTIFIED BY ‘new-password’; Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Passwords • Expira@on -‐ Manually (5.6) or with a Policy (5.7) • Hashing -‐ Mul@ple authen@ca@on plugins available • Policy -‐ Use Password Valida@on plugin (validate_password) • cleartext supplied password checked against password policy • 3 levels of password checking which can be modiﬁed 77 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Access Control • 2 stages -‐ Stage 1: Connec@ng • Who are you? -‐ host -‐ username • Prove it! -‐ Stage 2: Request • For each request -‐ What are you doing and are you allowed to do that? 78 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | GRANT • Deﬁnes privileges and account characteris@cs • Mul@ple privileges -‐ Ex: SUPER, CREATE, ALTER, SELECT, INSERT • Mul@ple levels -‐ Ex: Global, Database, Table, Column • Account characteris@cs -‐ Ex: REQUIRE SSL or MAX_QUERIES_PER_HOUR 79 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Sample GRANTS mysql> SHOW GRANTS; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT ALL PRIVILEGES ON `mysql`.* TO 'root'@'localhost' | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SHOW GRANTS FOR 'test'@'localhost'; +--------------------------------------------------------+ | Grants for test@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'localhost' | +--------------------------------------------------------+ 2 rows in set (0.00 sec) 80 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | REVOKE • Removes the privileges GRANTed -‐ Does not extrapolate • Does not remove the user • If no host is given -‐ % is used -‐ Again -‐ Does not extrapolate 81 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | REVOKE mysql> SHOW GRANTS FOR 'test'@'localhost'; +--------------------------------------------------------+ | Grants for test@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'localhost' | +--------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> REVOKE DELETE ON test.t1 FROM 'test'@'localhost'; ERROR 1147 (42000): There is no such grant defined for user 'test' on host 'localhost' on table 't1' mysql> REVOKE USAGE ON *.* FROM 'test'@'localhost'; Query OK, 0 rows affected (0.02 sec) 82 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Logical Backups • Saved -‐ Logical structure -‐ Content • Machine Independent • Slower • Server Up/Warm • Full Granularity 85 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | mysqlpump • Logical • Similar to mysqldump • Parallel processing to speed up dump process • Dump user accounts with CREATE USER/GRANT • Default: I_S, P_S, ndbinfo and SYS not included • Reloading: faster secondary index crea@on for InnoDB 87 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | SELECT ... INTO OUTFILE and LOAD DATA INFILE • Logical • Data only • Be careful! You want a consistent backup • Column and line terminators can be speciﬁed • Lots of details -‐ see the manual 88 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Physical Backup • Raw copies • Faster then logical (orders of magnitude) • Compact • File based granularity • Usually server is down or locked 90 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Filesystem Snapshot • Physical • OS Dependent • Basic Steps -FLUSH TABLES WITH READ LOCK -‐ Take the snapshot -UNLOCK TABLES -‐ Copy ﬁles from snapshot 91 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | MySQL Enterprise Backup • Official physical backup solu@on -‐ MySQL 5.0 to 5.7 -‐ Can handle all official MySQL supported storage engines • Mul@-‐plaôrm • Binlog and Relay log backup • Fast Recovery 92 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | 3rd Party Backup • Xtra Backup -‐ Percona backup solu@on -‐ Open source -‐ Backs up • InnoDB, XtraDB and MyISAM • MySQL 5.1, 5.5 and 5.6 93 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Don’t Forget Your Binary Logs •Incremental backup -‐ Holds changes since the full backup -‐ roll it forward •Physical file copy -‐ Rotate binary log with FLUSH LOGS -‐ Copy the file •Logical copy to remote server -‐ Sta@c or streaming • mysqlbinlog --read-from-remote-server 94 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Examples Metadata SHOW DATABASES SHOW TRIGGERS SHOW PLUGINS SHOW CREATE PROCEDURE SHOW ENGINES SHOW VARIABLES SHOW INDEXES SHOW GRANTS SHOW BINARY LOGS Status SHOW SLAVE STATUS SHOW OPEN TABLES SHOW RELAYLOG EVENTS SHOW ENGINE INNODB STATUS SHOW FULL PROCESSLIST SHOW GLOBAL STATUS 97 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | INFORMATION_SCHEMA • Mostly metadata -PROCESSLIST -GLOBAL_VARIABLES / GLOBAL_STATUS -FILES / INNODB_SYS_TABLESPACES / INNODB_SYS_DATAFILES • But -INNODB_TRX / INNODB_LOCKS / INNODB_LOCK_WAITS -INNODB_TEMP_TABLE_INFO 98 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | SYS SCHEMA •Included in 5.7.7 -‐ Installed by default with --initialize •Originally known as ps_helper -‐ Originally created by Mark Leith -‐ hpp://www.markleith.co.uk/ps_helper -‐ Can work with 5.6 -‐ download from Github • hpps://github.com/mysql/mysql-‐sys • Easy, human readable access to P_S and I_S info for typical use cases 99 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | SYS SCHEMA •Pair views -‐ Ex: host_summary_by_file_io and x$host_summary_by_file_io •Example views -‐ statements_with_full_table_scans -‐ statements_with_runtimes_in_95th_percentile -‐ io_by_thread_by_latency -‐ memory_by_user_by_current_bytes -‐ schema_redundent_indexes -‐ 100 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | PERFORMANCE_SCHEMA • Monitors at a low level • Uses PERFORMANCE_SCHEMA storage engine • Available -‐ Current events -‐ Event histories / Event summa@ons • Conﬁgura@on is dynamic • Query using SQL 101 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | PERFORMANCE_SCHEMA • Using the PERFORMANCE_SCHEMA -‐ Manual • Diagnose Problems -‐ hpps://dev.mysql.com/doc/en/performance-‐schema-‐examples.html • Query Profiling -‐ hpps://dev.mysql.com/doc/en/performance-‐schema-‐query-‐profiling.html -‐ Blog posts • Marc Alff’s blog -‐ hpp://marcalff.blogspot.com • Mark Leith’s blog -‐ hpp://www.markleith.co.uk/ -‐ Many Presenta@ons/Webinars (con’t) 102 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | MySQL Enterprise Monitor -‐ MEM • Real @me monitoring -‐ Performance -‐ Replica@on -‐ Query analysis -‐ InnoDB -‐ Backup -‐ Disk and OS 103 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Oracle Enterprise Manager for MySQL • Monitoring of MySQL servers in familiar OEM interface • Real @me monitoring -‐ Performance -‐ Replica@on -‐ InnoDB -‐ Metric Collec@on •Won’t lie -‐ doesn’t do everything MEM does 108 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Step 1 • Deﬁne the problem • Must know what is “normal”, so you know when it is not -‐ and how much • Be exact! • Read and understand the manual for the area that is problema@c 113 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Step 2 • Collect the diagnos@c data -‐ OS data -‐ MySQL data • May need to collect the diagnos@c data during the problem -‐ Monitoring -‐ Maybe pt-stalk 114 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Server Crash • Error log -‐ Stack trace and error message(s) • OS error log around the crash @me • Any and all diagnos@c data available -‐ MySQL and OS monitoring data (graphs or raw data) -‐ core ﬁle? • Any informa@on about what was happening before and during the crash -‐ External Scripts running / Applica@ons / Cronjobs / Backups / Etc. 116 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | ReplicaNon Issues • Error log • SHOW MASTER STATUS • my.cnf / my.ini • SHOW GLOBAL VARIABLES • SHOW SLAVE STATUS 117 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Performance Issues • Error log • OS data (Ex: IO, CPU, Memory, NUMA, etc.) • SHOW GLOBAL VARIABLES • SHOW GLOBAL STATUS • SHOW FULL PROCESSLIST • SHOW ENGINE INNODB STATUS • Informa@on from PERFORMANCE_SCHEMA 118 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | Query Performance Issues • Error log • Slow Query Log • EXPLAIN • SHOW CREATE TABLE • SHOW TABLE STATUS • SHOW INDEXES • Proﬁle the query 119 Sunday, October 16, 16

Copyright © 2016, Oracle and/or its aﬃliates. All rights reserved. | A MySQL DBA Primer Ligaya Turmelle Principal Technical Support Engineer -‐ MySQL ligaya.turmelle@oracle.com @lig , Oracle and/or its aﬃliates. All rights reserved. | 121 Sunday, October 16, 16

Zend2016 dba tutorial

More Related Content

What's hot

Similar to Zend2016 dba tutorial

More from Ligaya Turmelle

Recently uploaded

Zend2016 dba tutorial