Effective MySQL Web Site Operations

Slide 1

Slide 1 text

1 @RonaldBradford Helsinki 20 Aug Stockholm 21 Aug Riga 26 Aug Tallinn 27 Aug Effective Web Site Operations Ronald Bradford August 2014

Slide 2

Slide 2 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 2

Slide 3

Slide 3 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 3

Slide 4

Slide 4 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 4

Slide 5

Slide 5 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 5

Slide 6

Slide 6 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 6

Slide 7

Slide 7 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 7

Slide 8

Slide 8 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 8

Slide 9

Slide 9 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations 1. Under to hood 2. MySQL Installation Ideals 3. Essential MySQL conﬁguration 4. Disaster is inevitable 5. 24x7 Availability 6. The MySQL security model 9 Effective TIPS

Slide 10

Slide 10 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com MySQL Structure 10 1

Slide 11

Slide 11 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Components Binaries Conﬁguration Data Logs Files 11

Slide 12

Slide 12 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Binaries mysqld mysqld_safe mysqlbinlog mysql_upgrade 12 40+ CLIENT mysql mysqldump mysqladmin

Slide 13

Slide 13 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations configuration my.cnf Server/Global MySQL $MYSQL_HOME/my.cnf Extras --defaults-extra-file= User $HOME/.my.cnf Sections 13 $ more /etc/my.cnf # /etc/mysql/my.cnf [mysqld_safe] [mysqld] [client] [mysql] [mysqldump]

Slide 14

Slide 14 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations DATA File Structure 14 /data ibdata1 ib_logfile0 ib_logfile1 /mysql /test /performance_schema /data/test stats.frm /data/test stats.frm stats.MYD stats.MYI stats.TRG trigger.TRN /data/test stats.frm stats.ibd MyISAM Triggers innodb_ﬁle_per_table dir per schema common tablespace InnoDB redo logs .frm per table

Slide 15

Slide 15 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations data FIle Options Schema/Tables/Data --datadir --innodb_data_home_dir --innodb_data_file_path Data ﬁles/Auto extend --innodb_file_per_table --innodb_file_format 15

Slide 16

Slide 16 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations data file options Schema/Tables/Data InnoDB Redo Logs --innodb_log_file_size --innodb_log_buffer_size Binary Logs (Archive Redo) --log-bin 16

Slide 17

Slide 17 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations logs Error Log --log-error Slow Query Log --slow_query_log[_file] General Query Log --general_log[_file] 17

Slide 18

Slide 18 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations files Pid File --pid-file Socket --socket Tmp Files --tmp-dir 18

Slide 19

Slide 19 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations common File mistakes Do not place error log in [datadir] Do no place .pid/.socket in [datadir] Do not place binary logs in [datadir] Do no roll error log Conﬁg placed in wrong [section] Tmp dir not large enough 19

Slide 20

Slide 20 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations MySQL Replication Master Slave 20 /binlog /mysql-bin.index /mysql-bin.00000N ... /data /auto.cnf # MySQL 5.6+ /data /master.info /relay-log.info /relay-bin.index /relay-bin.00000N ...

Slide 21

Slide 21 text

Master --server_id --log_bin --binlog_format --sync_binlog --expire_logs_days EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations CONFIGURATION 21

Slide 22

Slide 22 text

Slave --server_id --read_only --master_info_file --relay_log_info_file --relay_log_purge EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations CONFIGURATION 22 pre 5.6 not slave crash safe

Slide 23

Slide 23 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com MySQL Installation and Management 23 2

Slide 24

Slide 24 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Repos ORACLE PROVIDED RedHat/CentOS/OL Yum Debian/Ubuntu Apt 3rd PARTY Legacy mysql.com rpm’s Others 24 Migration complexities Server/Connectors/ Workbench/Utilities/...

Slide 25

Slide 25 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations installation ideals 25 /mysql /etc /data /log /binlog /tmp /mysql/etc/my.cnf /mysql/etc/mysqld /mysql /mysql /bin /lib /scripts /.... You may want use a dedicated /tmp and /binlog dir

Slide 26

Slide 26 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations installation ideals sudo su - mkdir -p /mysql/{data,log,binlog,etc,tmp} mkdir -p /var/run/mysqld chown -R mysql:mysql /mysql /var/run/mysqld chmod 700 /mysql/{data,binlog} chmod 750 /mysql/log chmod 755 /mysql/etc/ /var/run/mysqld rm -rf /var/log/mysql /var/log/mysql*log* /etc/my.cnf ln -s /mysql/log /var/log/mysql ls -s /mysql/etc/my.cnf /etc/my.cnf ls -s /mysql/etc/mysqld /etc/init.d/mysqld 26 Dedicated /mysql partition ext4/xfs ++speciﬁc improvements

Slide 27

Slide 27 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations installation ideals [mysqld] datadir=/mysql/data socket=/var/run/mysqld/mysql.sock pid-file=/var/run/mysqld/mysql.pid log-bin=/mysql/binlog/mysql-bin log-error=/mysql/log/error.log slow-query-log=1 long-query-time=2 slow-query-log-file=/mysql/log/slow.log general-log=0 general-log-file=/mysql/log/general.log ... [client] socket=/var/run/mysqld/mysql.sock 27

Slide 28

Slide 28 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com MySQL Conﬁguration 28 3

Slide 29

Slide 29 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Essential config log_bin sql_mode= binlog_format=MIXED sync_binlog=1 (5.6+) innodb_strict_mode=1 (5.1) 29 See Architecture Presentation

Slide 30

Slide 30 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations timezones Timezone data is not loaded by default Will your application data ever support multiple timezones? CONVERT_TZ(date,from_tz,to_tz) Does not work by default 30

Slide 31

Slide 31 text

EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL Timezones 31 $ mysql_tzinfo_to_sql /usr/share/zoneinfo | \ mysql -u root mysql mysql> SELECT COUNT(*) AS cnt FROM mysql.time_zone; +------+ | cnt | +------+ | 1738 | +------+ mysql> SELECT NOW(), -> CONVERT_TZ(NOW(),'America/New_York','UTC') AS utc; +---------------------+---------------------+ | NOW() | utc | +---------------------+---------------------+ | 2014-08-02 16:14:04 | 2014-08-02 20:14:04 | +---------------------+---------------------+ http://dev.mysql.com/doc/refman/5.6/en/mysql-tzinfo-to-sql.html

Slide 32

Slide 32 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations 5.6 Data Recovery (DR) improvements master_info_repository relay_log_info_repository Not enabled by default 32 data integrity Crash Safe Slave

Slide 33

Slide 33 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com Disaster Recovery (DR) Backup & Recovery (B&R) 33 4

Slide 34

Slide 34 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations common solution 34 “Using MySQL replication is not a backup and recovery solution. MySQL replication is essential component”. Ronald Bradford August 2014

Slide 35

Slide 35 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations default Options mysqldump is the only supplied tool Poor defaults Limited use for large DB’s Essential for schema/objects 35

Slide 36

Slide 36 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations mysqldump DEFAULT --lock-tables in --opt 36 Anti defaults NOT DEFAULT --single-transaction --master-data --dump-slave (5.5) --routines

Slide 37

Slide 37 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations operations process mysqldump --no-data --skip-triggers --add-drop-database > schema.sql --no-data --no-create-info --triggers -- routines > objects.sql --no-create-info --skip-triggers --single- transaction --master-data=2 > data.sql 37

Slide 38

Slide 38 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations backup options Use InnoDB Only mysqldump good for 5-10G Slow to restore with larger versions XtraBackup MySQL Enterprise Backup 38

Slide 39

Slide 39 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations binary logs Regardless of backup option No recovery option work without backups of binary logs Remote binlog backup (5.6) 39

Slide 40

Slide 40 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations recovery options Always test end to end triggers large ROW based transactions Foreign key errors Create a Time Machine Rebuild data deleted Create additional stats 40

Slide 41

Slide 41 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations using replication Replication is Asynchronous Delay Drift 41

Slide 42

Slide 42 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations 42

Slide 43

Slide 43 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Monitoring/Alerting Free/Limited Free New Relic Graphite Logstash Sensu Pingdom 43 Commercial New Relic Sumologic Pingdom Pagerduty http://j.mp/newrelic-mysql

Slide 44

Slide 44 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Instrumentation Performance Schema Information Schema MySQL Status InnoDB Status 44

Slide 45

Slide 45 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Performance Schema Starting in MySQL 5.5 5.5 17 tables 5.6 52 tables 5.7.4 75 tables 45

Slide 46

Slide 46 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Monitoring SQL Statement Instrumentation (5.6) 46 http://dev.mysql.com/doc/refman/5.6/en/performance-schema-statement-tables.html https://github.com/MarkLeith/mysql-sys/#statement_analysis--xstatement_analysis Discuss in New Features Presentation

Slide 47

Slide 47 text

Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com MySQL Permissions and Privileges 47 6

Slide 48

Slide 48 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations default security $ mysql_secure_installation 48 mysql_install_db --random-passwords RPM installs are now secure (5.7.4) Password expiry (5.6)

Slide 49

Slide 49 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Instance IDENTIFY For sudo usage 49 $ mysql mysql> $ export MYSQL_PS1=`hostname`" [\d]> " $ mysql prod-server-name [prod_db]> $ cat /etc/sudoers.d/mysql Defaults env_keep += "MYSQL_PS1"

Slide 50

Slide 50 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Top Tips Necessary conﬁguration to avoid failure Test Backup & Recovery - end to end Monitoring - Free Products available Become proactive rather then reactive Move to using MySQL 5.6 50

Slide 51

Slide 51 text

EffectiveMySQL.com #mysql #emea2014 Effective Software Development with MySQL options per versions mysqld Option/Variable Reference http://dev.mysql.com/doc/mysqld-version- reference/en/mysqld-version-reference- optvar.html 51

Slide 52

Slide 52 text

EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Conclusion Copies of these slides can be found at http://effectiveMySQL.com ronald@EffectiveMySQL.com 52