Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Effective MySQL Web Site Operations

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.

Ef8a4161c1e7ce34ea50c491ad99a67e?s=128

Ronald Bradford

August 09, 2014
Tweet

More Decks by Ronald Bradford

Other Decks in Technology

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