mysql on windows: a survival guide for linux-based dbas · mysql on windows: a survival guide for...
TRANSCRIPT
![Page 1: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/1.jpg)
MySQL on Windows: A survival guide for Linux-based DBAs
Fernando IparPercona Webinars 2013
Wednesday, November 6, 13
![Page 2: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/2.jpg)
About me...
... or why should you care about what I have to say?
Wednesday, November 6, 13
![Page 3: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/3.jpg)
About this talk...
... or why should you be here?
Wednesday, November 6, 13
![Page 4: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/4.jpg)
Two choices - Get frustrated, angry, etc. - Embrace the opportunity to learn
Wednesday, November 6, 13
![Page 5: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/5.jpg)
“By the end of this day, I’ll either have smashed my computer against the wall, or will have become a better human being”
/me to a colleague during an emergency case involving Windows and a web based remote access system
Wednesday, November 6, 13
![Page 6: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/6.jpg)
So let’s embrace this opportunity
Wednesday, November 6, 13
![Page 7: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/7.jpg)
Rough agenda...
- Biggest differences
- How to feel (more) at home
- Percona Toolkit: what works, what doesn’t?
Focus on plain MySQL (not NDB or Galera)
Wednesday, November 6, 13
![Page 8: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/8.jpg)
Biggest differences
Wednesday, November 6, 13
![Page 9: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/9.jpg)
Case sensitivity
- Though identifiers must be consistent on the same statement
select a from tbl where A=1 -- won’t work
Wednesday, November 6, 13
![Page 10: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/10.jpg)
Path separator
- ‘\’ is path separator on Windows- ‘\’ is escape character on MySQL- Use ‘/’ instead: LOAD DATA INFILE ‘c:/tmp/table.dump’ ...
Wednesday, November 6, 13
![Page 11: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/11.jpg)
CLI differences
- no pager- no readline- use ‘mysql’ package from cygwin instead
Wednesday, November 6, 13
![Page 12: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/12.jpg)
Symlinks
- On Unix, you can symlink to MY[ID] files- On Windows, you can symlink to database directory - Does not work with innodb_file_per_table
Wednesday, November 6, 13
![Page 13: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/13.jpg)
Config differences
- --standalone- --install / --install-manual / --remove- --slow-start-timeout - --socket / --enable-named-pipe - --shared-memory / --shared-memory-base-name- option separator ‘;’ (instead of ‘:’ on Unix) - i.e. for paths specified on tmpdir- innodb_flush_method : async_unbuffered
Wednesday, November 6, 13
![Page 14: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/14.jpg)
No Percona binaries
- No date for this yet- Not impossible to build from source- No xtrabackup - mysqldump - MySQL Enterprise Backup
Wednesday, November 6, 13
![Page 15: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/15.jpg)
Feeling more at home
Wednesday, November 6, 13
![Page 16: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/16.jpg)
cygwin- Includes ‘mysql’ package (for unix client) - supports pager - supports readline - ~/.my.cnf will work- perl, bash (Percona Toolkit!)- vim, vmstat, etc...
Wednesday, November 6, 13
![Page 17: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/17.jpg)
Measuring during troubleshooting
- vmstat from cygwin is decent- Resource Monitor- Performance Monitor
Wednesday, November 6, 13
![Page 18: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/18.jpg)
Wednesday, November 6, 13
![Page 19: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/19.jpg)
Wednesday, November 6, 13
![Page 20: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/20.jpg)
Wednesday, November 6, 13
![Page 21: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/21.jpg)
Percona Toolkit
Wednesday, November 6, 13
![Page 22: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/22.jpg)
Rule of thumb
- Perl tools tend to work- Bash tools may or may not- Prefer Cygwin. ActivePerl may work too.
Wednesday, November 6, 13
![Page 23: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/23.jpg)
What I have used (successfully)
- pt-query-digest- pt-table-checksum- pt-mysql-summary- pt-stalk - band aid:edit check_disk_space() to always return 0 - pt-stalk --no-stalk --iterations 1
Wednesday, November 6, 13
![Page 24: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/24.jpg)
What was not on the previous slide, I have not used on Windows
Wednesday, November 6, 13
![Page 25: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/25.jpg)
Wednesday, November 6, 13
![Page 26: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/26.jpg)
Wednesday, November 6, 13
![Page 27: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/27.jpg)
Bonus Track: innotop
Wednesday, November 6, 13
![Page 28: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/28.jpg)
Wednesday, November 6, 13
![Page 29: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/29.jpg)
Wednesday, November 6, 13
![Page 30: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/30.jpg)
Wednesday, November 6, 13
![Page 31: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/31.jpg)
Conclusion
Wednesday, November 6, 13
![Page 32: MySQL on Windows: A survival guide for Linux-based DBAs · MySQL on Windows: A survival guide for Linux-based DBAs Fernando Ipar Percona Webinars 2013 Wednesday, November 6, 13](https://reader035.vdocuments.us/reader035/viewer/2022062509/60ff0854fe797219df7d7d2d/html5/thumbnails/32.jpg)
A hotel will never be your home, but you can still make it a bit cozy :-)
Wednesday, November 6, 13