Download as PDF, PPTX
Transitioning From SQL Server to MySQL - Presentation from Percona Live 2016
- 1. Transitioning From SQLServer to MySQL: Lessons Learned Along The Way Dylan Butler
- 2. • Who amI? • Our MySQL project • My team • Our goals • Final Architecture • Findings along the way • SQL Server vs. MySQL • The Future Agenda
- 3. Dylan Butler Senior DatabaseAdministrator SAS Institute dylan.butler@sas.com Twitter: @dylanbutler linkedin.combutlerdylan
- 4. Requirements: • Take oversupport for databases of a critical web based application • Migrate existing databases from MySQL 5.1 • Provide HA across data centers • Must keep in MySQL • Go live by end of year (approx. 9 months) Our MySQL Project
- 5. • 3 SQLServer DBA’s • 4 Oracle DBA’s • Two sides of the team are very different • How do we support a third database platform? • Why us? The Team SQL Server Oracle ?? MySQL
- 6. • Use thisas an opportunity to bring two sides of team closer together • Build on processes already in place where possible • Consolidate monitoring wherever possible • Automate as much as we can • Educate all team members along the way • Documentation! Goals
- 7. • Five nodeGalera (Percona XtraDB Cluster) • Two in one data center • Two in the other • An arbitrator (garbd) in a third building • F5 using clustercheck • Also using Percona Server • For less critical apps that don’t need HA Final Architecture Bldg 1 Bldg 2 Bldg 3 F5 VIP App Servers
- 8.
- 9. Options: • Percona XtraBackup •mysqldump • Other • mysqlhotcopy • MySQL Enterprise Backup • Other physical options Backups
- 10. Percona XtraBackup • Onlinebinary backup • Great for disaster recovery of entire instance • Not best solution for data recovery at database or table level • Still would like to explore further Backups
- 11. mysqldump • Best fitfor our needs.. Portability, non-blocking, data recovery, DR • We are using InnoDB for all tables • Using --single-transaction to avoid blocking (with InnoDB) • Other options in use: • --flush-logs • --master-data=2 • --routines • --triggers • --quick • --hex-blob • --all-databases Backups
- 12. Jobs and otherconsiderations • Nightly full dump • Hourly binary log flush and copy • All backups and logs copied to network storage device • Written to tape each night • Another job to clean up older files Backups
- 13.
- 14. • Goal: Utilizeexisting tools where possible • Zenoss • Linux Monitor ZenPack (ssh) • MySQL Database Monitor (Core) ZenPack (Python) • Server/Database attributes re-modeled every 12 hours • 5 minute collection interval for monitors • Event triggering • Notification is built in.. we call a custom notification app Monitoring
- 15. Goal: Take fulladvantage of what is included in MySQL • Performance Schema • Enabled by default as of 5.6.6 • Sys Schema • Included starting in 5.7.7 but backwards compatible to 5.6 • Workbench • Zenoss Performance Data
- 19.
- 20. • We didn’tget it all right the first time • Examples: • Collation • SQL mode • Federated storage engine • Configuration (MySQL and Linux) - Swappiness (VM’s) – 1 for Percona Server, 10 for XtraDB Cluster - For Galera clusters, increase the size of gcache - Slow query log – turned off log_queries_not_using_indexes - Timeout settings – net_read_timeout and net_write_timeout Rework Needed
- 21. • Backups • Security •Performance Tuning • Monitoring improvements • More automation and documentation • Patching Still Needs Work
- 22. • Good andbad • SQL Server is a robust platform and toolset, but licensing adds up quickly • MySQL offers most of the same functionality and features • Security • I miss Active Directory • Slight differences, but very similar permissions • Need to lock down MySQL after install • Backups • Lots more options in MySQL.. can be good and bad SQL Server vs MySQL
- 23. • Jobs • cronis fine, but I do miss SQL Agent • Quirks • Single line comments using -- must have a space after the – • Functions – enable bin-log-trust-function-creators • Tools • MySQL Workbench is decent, but it’s no SSMS SQL Server vs MySQL
- 24. • Lots ofgrowth in MySQL • Probably more platforms • At least the addition of a NoSQL offering Our Future
- 25.