Effective MySQL Web Site Operations

It is very important for companies to be able to support and manage your MySQL installation and related technology stack.

We plan to cover the following topics.

- Be prepared for handling software, OS or dependency upgrades.
- Ensure the right backup and recovery procedures for failed hardware.
- The correct testing and verification of backup and recovery and how to avoid common pitfalls.
- What monitoring and alerting is necessary.
- New tools and instrumentation including the Performance Schema.
- Choosing the best repo or binary for your environment needs
- What are the security needs for your data, backups.
- How does the use of the public cloud affect your business data governance and compliance needs.

From this presentation, we will create a checklist of the key operations needs your business should undertake to ensure a smooth-running production system.

Transcript

1. 1 @RonaldBradford Helsinki 20 Aug Stockholm 21 Aug Riga 26 Aug Tallinn 27 Aug Effective Web Site Operations Ronald Bradford August 2014
2. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 2
3. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 3
4. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 4
5. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 5
6. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 6
7. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 7
8. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com 8
9. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations 1. Under to hood 2. MySQL Installation Ideals 3. Essential MySQL configuration 4. Disaster is inevitable 5. 24x7 Availability 6. The MySQL security model 9 Effective TIPS
10. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com MySQL Structure 10 1
11. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Components Binaries Configuration Data Logs Files 11
12. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Binaries mysqld mysqld_safe mysqlbinlog mysql_upgrade 12 40+ CLIENT mysql mysqldump mysqladmin
13. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations configuration my.cnf Server/Global MySQL $MYSQL_HOME/my.cnf Extras --defaults-extra-file=<file> User $HOME/.my.cnf Sections 13 $ more /etc/my.cnf # /etc/mysql/my.cnf [mysqld_safe] [mysqld] [client] [mysql] [mysqldump]
14. 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_file_per_table dir per schema common tablespace InnoDB redo logs .frm per table
15. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations data FIle Options Schema/Tables/Data --datadir --innodb_data_home_dir --innodb_data_file_path Data files/Auto extend --innodb_file_per_table --innodb_file_format 15
16. 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
17. 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
18. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations files Pid File --pid-file Socket --socket Tmp Files --tmp-dir 18
19. 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 Config placed in wrong [section] Tmp dir not large enough 19
20. 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 ...
21. Master --server_id --log_bin --binlog_format --sync_binlog --expire_logs_days EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations CONFIGURATION 21
22. 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
23. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com MySQL Installation and Management 23 2
24. 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/...
25. 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
26. 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 ++specific improvements
27. 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
28. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com MySQL Configuration 28 3
29. 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
30. 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
31. 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
32. 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
33. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com Disaster Recovery (DR) Backup & Recovery (B&R) 33 4
34. 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
35. 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
36. 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
37. 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
38. 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
39. 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
40. 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
41. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations using replication Replication is Asynchronous Delay Drift 41
42. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations 42
43. 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
44. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Instrumentation Performance Schema Information Schema MySQL Status InnoDB Status 44
45. 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
46. 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
47. Effective Web Site Operations #mysql #emea2014 EffectiveMySQL.com MySQL Permissions and Privileges 47 6
48. 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)
49. 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"
50. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Top Tips Necessary configuration 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
51. 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
52. EffectiveMySQL.com #mysql #emea2014 Effective Web Site Operations Conclusion Copies of these slides can be found at http://effectiveMySQL.com ronald@EffectiveMySQL.com 52