opera&onal dba in a nutshell - percona€¦ · what is mariadb server • fork of mysql...

Opera&onalDBAInANutshell

• TomDeCooman <[email protected]>

• DimitriVanoverbeke <[email protected]>

mailto:[email protected]


Introduc&on

• Welcome!• Whoarewe?

– FansofdevopsreacAons.tumblr.com• Whatwillwetalkabout?

2

BasicHousekeeping

• FullDayTutorial• HandsOn!LaptopRequired!• Breaks• WirelessSSID=??• Howtoaskforhelp• UsingthedocumentaAon

3

Beware!

• Youknowwhatreplicate_same_server_idis!

• YouknowallGRANTsbyheart!• pt-query-digestisyourfavoritetool!• You’vebeendealingwithreplicaAoninconsistenciesforawhilealready!

• Yourbackupenvironmentisthemostawesomethereis!

4

Thistutorialisnotforyouif……


• SeTngUpToday’sEnvironment

• InstallaAon• LoggingIn• MySQLPrivileges• DiagnosAcs• TroubleshooAng• Backups• ReplicaAon• SchemaChanges

• ConfiguraAonOpAmizaAon

5

HowdoingoperaAonsfeels

http://devopsreactions.tumblr.com/post/46924953930/how-doing-operations-feels


• SeAngUpTodaysEnvironment



6



SeAngUpOurVM

• CopyFilesFromUSBSAck• InstallVirtualBox,openthevboxfiles,startallVMs• TestConnecAvity

– ssh-p2221root@localhost(password:vagrant)– ssh-p2222root@localhost(password:vagrant)– hdp://localhost:8080/(youshouldseeanapachetestpage)

– VBoxManage.exemodifyvm<machinename>--hwvirtexoff

7

http://localhost:8080/my-movies/


• SeTngUpTodaysEnvironment

• Installa&on• LoggingIn• MySQLPrivileges• DiagnosAcs• TroubleshooAng• Backups• ReplicaAon• SchemaChanges


8



WhatversionsofMySQLdoyourun?

• MySQL5.7CommunityediAon?

• PerconaServer5.7?• MariaDB10.1?• WebScaleSQL?• MySQLCluster?• MySQL5.7EnterpriseediAon?

9

WhatisMySQLcommunityEdi&on

• StandardOraclecommunityVersion(previouslyMySQLAB,Sun)

• ContainsastandardMySQLversionwithInnoDBandMyISAMasastandard

• Theoriginalsource• PackagesforalargearrayofoperaAngsystemsincludingWindowsandMacOS

• OpenSource

10

WhatisPerconaServer

• EnhancedversionofMySQLcommunityediAon• Addedpluginsfor

– authenAcaAon(PAM)– audiAng– diagnosAcs– ImprovedandExtrastorageengines

• ImprovedPerformance(Patches)• ImprovedbackupandmanagementcapabiliAes• HotbackupusingPerconaXtraBackup• MonitoringusingPMMorothertools• PackagesforLinuxdistribuAons

11

WhatisMariaDBserver

• ForkofMySQLcommunityEdiAon• ImprovedFuncAonaliAes

– Connectstorageengine– AddiAonalstorageengines– AddiAonalplugins

• PackagesforLinuxandWindows

12

Otherop&ons

• WebScaleSQL– databasedevelopedbylargeweborganisaAonsbasedonMySQLcommunity5.6

– Noreadyavailablepackages• MySQLCluster

– NDBstorageengine(specificusecase)• MySQL5.7EnterpriseediAon?

– MySQLcommunityediAonwithaddiAonalplugins• AuthenAcaAon• Firewall• MySQLenterprisebackupandmonitor

13

ThePerconatoolkit

• CommandlineTools• Systemtasks• Datasetmanagementandtooling• PerconaServerforMySQL,MySQL,MariaDB,PerconaServerforMongoDBandMongoDB

• OpenSource• PackagesforRHELandDebianbasedsystems• hdps://www.percona.com/somware/database-tools/percona-toolkit

14

https://www.percona.com/software/database-tools/percona-toolkit

https://www.percona.com/software/database-tools/percona-toolkit

Installa&onOfMySQL-HandsOn!

• InstallPerconaServer# yum install Percona-Server-server-57# service mysql start <ONLY ON MASTER>

• Verifyifyoucanconnect# mysql

• VerifytheapplicaAon:– hdp://localhost:8080/my-movies/• Pingtheothernodes– 192.168.70.2– 192.168.70.3

15

[root@node1 ~]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 270Server version: 5.7.11 Percona Server (GPL), Release rel30.2, Revision 500

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.

node1 mysql>

http://localhost:8080/my-movies/

Today’sApplica&on-BasicIMDBInterface16





17



WhatareDMLstatements?

• DataManipulaAonLanguage• Example:

• UsageIt’showyoucaninteractwithaDatabase.FetchinformaAon,updateinformaAon,removeoraddinformaAonasin:SELECT,UPDATE,DELETE,ADD

18

INSERTintoresto_visitorvalues(5,'Julian',’highway5’,12);INSERTINTOtbl_name(col1,col2)VALUES(15,col1*2);UPDATEresto_visitorsetname='Evelyn',age=17whereid=103;

WhatareDDLstatements?

• Data Definition Language• Example:

• UsageDDL queries define the structure on which you develop your application. Your structure will also define how the database server searches for information in a table.

19

CREATE TABLE Accounts(Account_number Bigint(16) ,Account_name varchar(255),Amount Bigint(16),PRIMARY KEY (Account_number),UNIQUE(Account_name),);

Otherquerytypes?

• DCL (Data control language)• GRANT, REVOKE, …

• TCL (Transaction control language)-- start a new transactionstart transaction;

-- DebitUPDATE sb_accountsSET balance = balance - 1000WHERE account_no = 932656;

-- CreditUPDATE ca_accountsSET balance = balance + 1000WHERE account_no = 933456 ;

-- commit changes commit;

• Overview: https://www.percona.com/blog/2016/12/29/query-language-type-overview/

20

https://www.percona.com/blog/2016/12/29/query-language-type-overview/

LoggingIn-HandsOn!

# mysqlmysql> helpmysql> SHOW DATABASES;mysql> show schemas;mysql> USE imdb;mysql> SHOW TABLES;mysql> SELECT * FROM users LIMIT 1;mysql> SELECT * FROM users LIMIT 2 \Gmysql> SHOW PROCESSLIST;mysql> exit

21

MySQLCLI-BasicFeatures-HandsOn!

• Prompt

mysql> prompt Master > PROMPT set to 'Master > ‘Master > prompt mysql>PROMPT set to ‘mysql>’• Editmysql> edit

Opens up a beautiful little editor:use sakila;select * from citylimit 10;

22


• Teemysql> tee /tmp/tee.log Logging to file ‘/tmp/tee.log'mysql> select * from sakila.city limit 10;mysql> exit# cat /tmp/tee.log

• Formatting help• help!• \g• \G

• Statusmysql> status--------------mysql Ver 14.14 Distrib 5.6.15, for Linux (x86_64) using EditLine wrapper Connection id: 13149Current database: sakilaCurrent user: root@localhostSSL: Not in use…

23


• Statusmysql> status;mysql> show engine innodb status \Gmysql> show global status;mysql> show processlist;

24


• Pagermysql> pager grep queriesmysql> show engine innodb status \G0 queries inside InnoDB, 0 queries in queue

mysql> pager md5sumPAGER set to 'md5sum'

mysql> select * from city limit 10;449d5bcae6e0e5b19e7101478934a7e6 -10 rows in set (0.00 sec) mysql> select city_id, city, country_id, last_update FROM city LIMIT 10 ;449d5bcae6e0e5b19e7101478934a7e6 -10 rows in set (0.00 sec)

mysql> pagerDefault pager wasn't set, using stdout.

25

MySQLWorkbench26

• Visual Tool• Database Design & Modeling• SQL Development• Database Administration• Database Migration• Editions (Webpage)

• Community• Standard• Enterprise

https://www.mysql.com/products/workbench/features.html

MySQLWorkbench27

MySQLWorkbench28

MySQLWorkbench29





30



MySQLPrivileges

• Users• Grants• mysqldatabase• PerconaToolkit

• DefaultPermissions• CreaAngApplicaAonuser

31

AskingthesecurityteamforafirewallexcepAon

Users

• IdenAfyusersbasedon:user@host– user:username– host:hostname/ip/networkoftheclientthatconnects

• differenthost,differentuser,different‘grants’• useofwildcards

• Examples:‘dim0’@‘localhost’, ‘root’@‘localhost’ ‘tommeketoch’@‘app0001’, ‘kenju’@‘192.168.%’ ‘ledijkske’@‘192.168.1.212’, ‘fredjen’@‘app.fq.dn’

• CreaAngAUser:>CREATE USER 'dim0'@'app0001';

• Dropuser:changeCREATEintoDROP

32

Grants

• Granttheusersomekindofprivilege• Grant...to:

• Example:INSERT, SELECT, UPDATE, DELETE

• SQLCommand:• >GRANTSELECTONdb.*TO‘dim0’@‘app0001’;• >GRANTINSERTON*.*TO‘dim0’@‘app0001’;

• Revokingprivileges:changeGRANTintoREVOKE

server,database,table,column,

trigger,storedprocedure,view,index

33

Table/ColumnLevelGrants

• Possible:> GRANT SELECT ON db.table TO ‘dim0’@‘app’; > GRANT SELECT (col) ON db.table to ‘monsieur’@‘app’;

• UsingtoomanycolumnsmightmakeauthenAcaAonslower

• Notcommonlyused

34

Grants

• Completelistofgrants:CREATEDROPGRANTOPTIONLOCKTABLESEVENTALTERDELETEINDEXINSERTSELECTUPDATECREATETEMPORARYTABLESTRIGGERCREATEVIEWSHOWVIEW

ALTERROUTINECREATEROUTINEEXECUTEFILECREATEUSERPROCESSPROXYRELOADREPLICATIONCLIENTREPLICATIONSLAVESHOWDATABASESSHUTDOWNSUPERALL[PRIVILEGES]USAGE

35

Password

> SET PASSWORD FOR ‘dim0’@‘app0001’ = PASSWORD(‘pass’); > SET PASSWORD FOR ‘dim0’@‘app0001’ = ‘*196BDEDE2...’;

> SELECT PASSWORD('pass')\G PASSWORD('pass'): *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7

> CREATE USER ‘dim0’@‘app’ IDENTIFIED BY PASSWORD ‘*196BDEDE2...’;

> GRANT SELECT ON db.* TO ‘dim0’@‘app’ IDENTIFIED BY ‘pass’;

36

> SET PASSWORD FOR ‘dim0’@‘app0001’ = ‘pass’;

Mysql>5.7.6

SHOWGRANTS37

• Showgrants:manual• Checkthemysql.usertable• Checkprivilegesforacertaincombo> select user, host from mysql.user;+-------+-----------+| user | host |+-------+-----------+| root | % || root | 127.0.0.1 || root | ::1 || plmce | localhost |

> show grants for ‘plmce’@‘localhost’;

SHOWGRANTS38

• Showgrants:ThePerconaWay!• pt-show-grants

[root@ps1 ~]# pt-show-grants-- Grants dumped by pt-show-grants-- Dumped from server Localhost via UNIX socket, MySQL 5.6.34-79.1-log at 2017-04-10 16:02:56-- Grants for 'plmce'@'localhost'GRANT USAGE ON *.* TO 'plmce'@'localhost' IDENTIFIED BY PASSWORD '*221D28791318A46BA8556B182ADF26D8D360220F';GRANT ALL PRIVILEGES ON `imdb`.* TO 'plmce'@'localhost';

mysqlDatabase39

node1 mysql> show tables;+---------------------------+| Tables_in_mysql |+---------------------------+| columns_priv || db || event || func || general_log || help_category || help_keyword || help_relation || help_topic || host || ndb_binlog_index |

| plugin || proc || procs_priv || proxies_priv || servers || slow_log || tables_priv || time_zone || time_zone_leap_second || time_zone_name || time_zone_transition || time_zone_transition_type || user |+---------------------------+24 rows in set (0.00 sec)

Defaultuser/permissions

• IfusingCentOS/RHELapasswordiscreatedforrootautomagically:• /var/log/mysql/mysql.log

[Note]Atemporarypasswordisgeneratedforroot@localhost:8)13mQG5OYl

• Debian/UbuntuwillpromptduringinstallaAon,ifnopasswordincluded,itwillonlylistentothelocalsocket.

• hdps://www.percona.com/blog/2016/05/18/where-is-the-mysql-5-7-root-password/

40

mysql_secure_installa&on

• Reworkedsince5.7• PasswordvalidaAonpluginincluded• Youcansetapasswordforrootaccounts.• Youcanremoverootaccountsthatareaccessiblefromoutsidethelocalhost.

• Youcanremoveanonymous-useraccounts.• Youcanremovethetestdatabase(whichbydefaultcanbeaccessedbyallusers,evenanonymoususers),andprivilegesthatpermitanyonetoaccessdatabaseswithnamesthatstartwithtest_.

41

MySQLPrivileges(1)-HandsOn!

• InstallPerconaToolkit# yum install percona-toolkit# pt-show-grants

– showallgrantsonasystem(inorder)• goodforversioncontrol

42

[root@node1 lib]# pt-show-grants -- Grants dumped by pt-show-grants-- Dumped from server Localhost via UNIX socket, MySQL 5.7.12-30.2-log at 2017-03-21 00:18:28-- Grants for ''@'localhost'GRANT USAGE ON *.* TO ''@'localhost';-- Grants for ''@'node1'GRANT USAGE ON *.* TO ''@'node1';-- Grants for 'cactiuser'@'localhost'GRANT USAGE ON *.* TO 'cactiuser'@'localhost' IDENTIFIED BY PASSWORD '*43DD7940383044FBDE5B177730FAD3405BC6DAD7';GRANT ALL PRIVILEGES ON `cacti`.* TO 'cactiuser'@'localhost';

DefaultPermissionsAreBad-HandsOn!43

root@ip-172-31-0-120:~# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

VALIDATE PASSWORD PLUGIN

Press y|Y for Yes, any other key for No: NoUsing existing password for root.Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y

Remove anonymous users? (Press y|Y for Yes, any other key for No) : YSuccess.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y - Dropping test database...Success. - Removing privileges on test database...Success.

Reloading the privilege tables will ensure that all changesmade so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : YSuccess.

All done!

MySQLPrivileges(2)-HandsOn!

• Nowwehave‘secure’privileges# mysql -u root -p

• Createauserfor‘my-movies’applicaAonmysql> GRANT ALL PRIVILEGES ON imdb.* TO 'mymovies'@'localhost' IDENTIFIED BY 'password';Changeuser&password: /var/www/html/my-movies/lib/config.inc.php

• VerifyApplicaAon:hdp://localhost:8080/my-movies

• Doublecheckwithpt-show-grants# pt-show-grants -u root --ask-pass

• Create a .my.cnf file[mysql]user=password=

44

localhost:8080/my-movies



• InstallaAon• LoggingIn• MySQLPrivileges• Diagnos&cs• Troubleshoo&ng• Backups• ReplicaAon• SchemaChanges


45



Troubleshoo&ng

• DiagnosAcs– OperaAngSystem– MySQL

• PerconaToolkit• Queries• PMM• AlerAng

46

TroubleshootingaproblemthatIdon’tunderstand

GeAngready!-HandsOn!

• Open up New Terminal window

# yum install pmm-client innotop# pmm-admin config --server 192.168.70.3 --server-user plmce --server-password plmce# pmm-admin add linux:metrics# pmm-admin add mysql# pmm-admin list

• Services:– mysql:queries– linux:metrics– mysql:metrics

• CheckPMM:hdp://localhost:8443(plmce/plmce)• Add load# time /root/percona/add_load.py

47

http://localhost:8443

Diagnos&csOS-HandsOn!

• OSmetrics(memory,cpu...)– vmstat– free– mpstat– top– ps– sar– # cat /proc/meminfo

48


• vmstat:VirtualMemoryStaAsAcs[root@master percona]# vmstat 5procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 2 0 158260 9280 0 217852 3 354 23450 453 795 1224 19 7 74 0 0 0 0 158260 9332 0 217852 0 0 0 2 221 166 5 1 94 0 0

• free[root@master ~]# free -m total used free shared buff/cache availableMem: 488 278 8 3 201 169Swap: 2015 1 2014

• mpstat: Processor Statistics[root@master percona]# mpstat 5Linux 3.10.0-514.10.2.el7.x86_64 (master) 04/17/2017 _x86_64_ (1 CPU)

02:09:05 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle02:09:10 PM all 4.72 0.00 0.82 0.00 0.00 0.00 0.00 0.00 0.00 94.46

49


• iostatcommonlyused:iostat-x-m1avg-cpu: %user %nice %system %iowait %steal %idle 0.24 0.00 0.63 0.07 0.00 99.06

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.02 1.54 9.26 2.07 0.31 0.05 64.10 0.06 5.48 2.31 19.69 0.50 0.56 dm-0 0.00 0.00 6.49 1.96 0.22 0.02 59.37 0.20 23.38 2.47 92.82 0.61 0.51 dm-1 0.00 0.00 0.15 0.23 0.00 0.00 11.26 0.00 9.63 0.83 15.47 0.56 0.02 dm-2 0.00 0.00 1.02 0.20 0.06 0.02 135.74 0.01 5.82 4.12 14.62 0.56 0.07

• %uAl:howmany%ofAmeatleastonerequestwasbusy• await+svctm:

– responseAme– writesandreadscombined

50

DiskSubsystemSta&s&cs-HandsOn!

• pt-diskstats• similartoiostat• moredetailed• interacAve• hideinacAvedisks• spaceforheader• /forregex• ?forhelp• reads/proc/diskstats• showrd/wrresponseAme

51

pt-diskstats-HandsOn!

• reads/proc/diskstats,showswr/rdresponseAme

# pt-diskstats

#ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg io_s qtime stime

1.0 dm-2 932.2 127.5 116.1 0% 4.7 5.0 0.0 0.0 0.0 0% 0.0 0.0 25% 0 932.2 4.8 0.3

1.0 dm-2 1021.3 127.4 127.1 0% 5.5 5.4 0.0 0.0 0.0 0% 0.0 0.0 30% 25 1021.3 5.0 0.3

52

Diagnos&csMySQL-HandsOn!

• MySQL:– Errorlog:/var/lib/mysql/error.log– SHOW GLOBAL STATUS– mysqladmin extended-status– SHOW ENGINE INNODB STATUS

– SHOW PROCESSLIST

• innotop• mysqladminmysqladmin proc stat

53

pt-mext-HandsOn!

# pt-mext -r -- mysqladmin -p ext -i 10 -c 3 Binlog_cache_disk_use 0 0 0 Binlog_cache_use 0 0 0 Bytes_received 2875788973602 1738235 346057 Bytes_sent 863929033790 588078 536398 Com_begin 6298644573 3516 5102 Com_delete 23721852 26 51 Com_insert 4454794705 1518 3287 Com_replace 527848577 197 121 Com_select 6993291133 8114 7594 Com_set_option 5112076 250 262 Connections 7331059 250 262 Created_tmp_disk_tables 113568 0 0 Created_tmp_files 7803 0 0 Created_tmp_tables 729281259 1816 479

54

pt-mext

• Lookatcurrentglobalbehaviorofdatabase• QueryOpAmizaAonnecessary?(sorAng_%,handler_%,range_%,tmp_table_%)

• Innodbmisbehaving?• ...

55

EnhancedSlowLogSta&s&cs-HandsOn!

> SET GLOBAL slow_query_log=on; > SET GLOBAL slow_query_log_file='/var/lib/mysql/slow.log'; > SET GLOBAL long_query_time=0; > SET GLOBAL log_slow_verbosity=full;# cat /root/percona/queries.sql | mysql# tail -n 200 /var/log/mysql/slow.log

56

# Time: 100924 13:58:47# User@Host: root[root] @ localhost []# Thread_id: 10 Schema: imdb Last_errno: 0 Killed: 0# Query_time: 399.563977 Lock_time: 0.000110 Rows_sent: 1 Rows_examined: 46313608 Rows_affected: 0 Rows_read: 1# Bytes_sent: 131 Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 25194923# InnoDB_trx_id: 1403# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes# Filesort: Yes Filesort_on_disk: Yes Merge_passes: 5# InnoDB_IO_r_ops: 1064749 InnoDB_IO_r_bytes: 17444847616 # InnoDB_IO_r_wait: 26.935662# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000# InnoDB_pages_distinct: 65329SET timestamp=1285336727;select STRAIGHT_JOIN count(*) as c, person_id FROM cast_info FORCE INDEX(person_id) INNER JOIN title ON (cast_info.movie_id=title.id) WHERE title.kind_id = 1 GROUP BY cast_info.person_id

pt-query-digest

• generatereportsfrom– slow query log pt-query-digest /var/lib/mysql/slow.log | less

– generallog(notsouseful)– tcpdump

57

Usingtcpdump

pt-query-digest-HandsOn!

# Profile # Rank QID Response time Calls R/Call Apdx V/M Item # ==== === =============== ===== ==== ===== ==== # 1 ... 1349.6240 62.4% 11976 0.1127 1.00 0.03 SELECT table1 table9 table2 table3 table4 # 2 ... 114.9014 5.3% 437 0.2629 1.00 0.50 SELECT table5 table6 table8 table7 table6 table8 table10 # 3 ... 92.9441 4.3% 791 0.1175 1.00 0.06 SELECT table13 # 4 ... 77.5712 3.6% 43 1.8040 0.65 0.73 SELECT table11 table12 table9 table2 table14 table15 table16 table14 table17 # 5 ... 67.1673 3.1% 296 0.2269 1.00 0.17 SELECT table8 table4 table14 table8 table18 # 6 ... 49.0330 2.3% 15630 0.0031 1.00 0.00 ADMIN CONNECT # 7 ... 43.4990 2.0% 274 0.1588 1.00 0.12 SELECT table19 # 8 ... 30.0898 1.4% 416 0.0723 1.00 0.07 SELECT table13 # 9 ... 19.6506 0.9% 13424 0.0015 1.00 0.01 UPDATE table20

58

– slow query log pt-query-digest /var/lib/mysql/slow.log | less


# Overall: 11 total, 8 unique, 0.30 QPS, 0.99x concurrency _______________ # Time range: 2017-04-20T18:37:07 to 2017-04-20T18:37:44 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 37s 7us 19s 3s 9s 6s 6ms # Lock time 39ms 0 30ms 4ms 4ms 8ms 495us # Rows sent 21 0 9 1.91 5.75 2.69 0.99 # Rows examine 39.75M 0 21.16M 3.61M 8.86M 6.31M 0.99 # Rows affecte 0 0 0 0 0 0 0 # Bytes sent 4.65k 0 805 432.73 755.64 277.09 487.67 # Merge passes 0 0 0 0 0 0 0 # Tmp tables 1 0 1 0.09 0 0.29 0 # Tmp disk tbl 1 0 1 0.09 0 0.29 0 # Tmp tbl size 16.00k 0 16.00k 1.45k 0 4.59k 0 # Query size 645 27 199 58.64 65.89 44.08 42.48

59


Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ======= ===== ============== # 1 0x212DEB0A59444468 18.7334 50.9% 1 18.7334 0.00 SELECT imdb.cast_info imdb.title # 2 0x8C9D4708FCE85CF3 18.0220 49.0% 2 9.0110 0.00 SELECT imdb.movie_info # MISC 0xMISC 0.0444 0.1% 8 0.0056 0.0 <6 ITEMS>

60


# Query 2: 0.22 QPS, 2.00x concurrency, ID 0x8C9D4708FCE85CF3 at byte 2567 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2017-04-20T18:37:16 to 2017-04-20T18:37:25 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 18 2 # Exec time 48 18s 9s 9s 9s 9s 147ms 9s # Lock time 6 3ms 257us 2ms 1ms 2ms 2ms 1ms # Rows sent 71 15 6 9 7.50 9 2.12 7.50 # Rows examine 46 18.59M 9.30M 9.30M 9.30M 9.30M 0 9.30M # Rows affecte 0 0 0 0 0 0 0 0 # Bytes sent 23 1.10k 515 612 563.50 612 68.59 563.50 # Merge passes 0 0 0 0 0 0 0 0 # Tmp tables 0 0 0 0 0 0 0 0 # Tmp disk tbl 0 0 0 0 0 0 0 0 # Tmp tbl size 0 0 0 0 0 0 0 0 # Query size 16 104 52 52 52 52 0 52

61


# Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s ################################################################ # 10s+ # Tables # SHOW TABLE STATUS FROM imdb LIKE 'movie_info'\G # SHOW CREATE TABLE imdb . movie_info \G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM imdb.movie_info WHERE movie_id = 93221\G

62

Op&mizingThisApplica&on-HandsOn!

• TheproblemisthatthedatabaseisnotopAmised,indexingisnotdoneproperly

63


• Indexes– tofindtherowsmatchingaWHEREclausequickly– reduceamountofdatatobeexamined– helpsavoidsorAngandtemptables– randomIOget’sturnedintosequenAalIO

• ExamplesSELECT * FROM imdb.title WHERE `id` = 455;SELECT * FROM imdb.movie_info WHERE movie_id = 635;

• EXPLAIN!• WehaveSomeSuggesAons:

#less /root/percona/billkarwin-opt.sql

64


• Applythechanges:# cat /root/percona/billkarwin-opt.sql | mysql -D imdb

• Haveanotherlookwithpt-query-digest# cd /var/lib/mysql/; mv slow.log slow.log_old # killall -HUP mysqld # cat /root/percona/queries.sql | mysql

# pt-query-digest /var/lib/mysql/slow.log | less

65

Aler&ng

• ThingsToAlerton:– CanConnecttoMySQL.– ReplicaAonIOThreadisOK– ReplicaAonSQLThreadisOK– ReplicaAonSecondsBehindMaster.– ConnecAons– InnoDBisenabled.– Notablesaremarkedascorrupt.

• Notthebest:tmptables,raAos,qps• Focusonwhatmaders:usuallyinupperlayers

66

AvailableTools

• ManySoluAonsAvailable:– MonYog– SeveralninesClusterControl– MySQLEnterpriseMonitor– CacA,Munin,Graphite,OpenTSDB,MRTG– Nagios,Icinga– NewRelic– Zabbix– ZenOSS– PerconaMonitoringandManagement– …

67

MonitoringandManagement:PMM68

MonitoringandManagement:PMM

• Percona Monitoring and Management• Open Source• MySQL and MongoDB

• Tooling• Metrics

• Prometheus• Grafana

• QAN: Query Analytics• Orchestrator: Topology management

69

MonitoringandManagement:PMM70

PMM:Deploying71

• PMM Server• Docker images• Amazon amis• Virtualbox images

• PMM Client• packages available for

• RHEL/CentOS• Ubuntu/Debian

PMM:Usage72

• PMM Landing page• Add load: /root/percona/add_load.py• https://localhost:8443• Links to

• Query Analytics• Metrics Monitor• MySQL Replication Topology Manager

https://localhost:8443

PMM:Usage73

• Metrics DEMO

PMM:Usage74

• Query Analytics DEMO

PMM:Handson!75

• http://localhost:8443• Check out Grafana• Check out QAN

http://localhost:8443





76



BackingupyourData-Challenges

• MySQLismulA-engine– Differentstorageenginescallfordifferentstrategies

• MySQLcanbequitebusy– Doweneedtoshutitdown?– Howmuchloadwillwegenerate?– Howquicklycanwerestore?

77

BackingupyourData-Concepts

• ImpactofBackup– Hotbackup-doesnotpreventreadersorwritersfromperformingoperaAons.

– Warmbackup-readersmayconAnue,butwriteacAvitymustbequeuedunAlthebackupiscompleted.

– Coldbackup-systemisunavailableduringbackupwindow.

78

BackingupyourData-Concepts

• TypeofBackup– Physical-storesdatafilesinthe“naAve”format.– Logical-storesdatainagenericrepresentaAon,suchasaSQLdump.

79

Afewavailablebackupsolu&ons80

Impact(Perf) Warmth BackupTime RestoreTime

ColdBackup veryhigh cold veryfast fast

mysqldump high warm medium slow

mydumper high warm medium medium

SnapshoTng high/medium hot/warm fast fast

MySQLEnterpriseBackup low/medium warm fast fast

XtraBackup low/medium warm fast fast

mysqldump

• IncludedbydefaultwithMySQL• LogicalbackupsoluAon-createsSQLfiletoreproducedata

• Pro’s– Easytouse,reliable

• Cons– Highimpact,longrestoreAme

81

mysqldump-HandsOn!

• Takebackup:# mysqldump --all-databases --single-transaction > /var/backup/dump.sql

– --all-databasesforeverydatabase(includingmysql).– --single-transacAonisonlysafewhenallInnoDB.– Thisensuresahotbackup.– --master-data=1(opAonal)recordsthebinarylogcoordinates.

• Restoreasfollows(don’tdothis):# mysql < dump.sql

82

mysqldump-HandsOn!83

[root@node1 ~]# vmstat 5procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 1 1 41024 3108 828 69764 3 10 12292 1723 306 1613 7 5 82 6 0 0 2 42280 3704 644 69968 2 255 5422 31890 1132 1418 63 14 0 22 0 1 2 48212 10372 1532 42024 87 1193 12894 23186 1609 3254 55 27 0 18 0 3 1 49620 6992 744 47788 0 282 2782 12288 1859 5983 57 43 0 0 0 2 1 53540 20300 512 55436 28 790 2918 29270 1236 1517 74 16 0 10 0 2 4 53992 28872 344 46988 70 106 2297 28966 1095 1386 71 11 0 18 0 1 6 53892 4016 664 71856 46 0 2948 33727 1119 1916 64 11 0 25 0 1 6 53860 4020 588 71916 12 0 2262 30219 1083 1830 63 11 0 26 0 2 4 54192 3340 576 72936 10 70 3054 29504 1127 1822 64 10 0 26 0 2 9 54664 3324 648 73668 2 102 3980 28035 1187 2116 61 11 0 28 0 2 6 55016 3980 440 73724 5 78 4618 26222 1220 2149 64 10 0 26 0 1 8 55580 5608 436 72176 3 119 5265 27130 1232 2318 59 11 0 30 0 2 7 56012 3608 412 75092 3 88 5745 25135 1237 2319 60 10 0 30 0 2 7 55968 4084 440 74572 1 0 5637 27538 1294 2468 58 12 0 30 0 2 9 55968 3380 408 75352 0 3 6869 23874 1238 2418 53 11 0 36 0 2 6 55880 3496 412 74808 64 0 6806 27942 1312 2732 53 12 0 34 0 2 6 55852 3440 408 74888 0 0 7103 27166 1298 2619 54 11 0 35 0 3 6 55852 3672 388 74648 0 3 6821 27295 1304 2676 54 11 0 35 0 2 7 55824 4052 372 74352 3 3 7539 23926 1347 2592 56 11 0 33 0 1 9 55836 3708 388 74652 4 7 8014 26310 1331 2698 54 12 0 34 0 2 8 55820 3524 348 74900 1 0 8590 24613 1322 2712 54 11 0 35 0 4 6 38096 3976 1640 51804 4033 0 14980 21471 1730 3325 48 22 0 30 0

WaiAngonMySQLdumptoFinish


PerconaXtraBackup

• Completelyfreeandopensource• PhysicalbackupsoluAon-Storesdatainsimilarformatastheoriginal.

• Pro’s– Powerful,scriptable,feature-packed

• Cons– MulAplestepsrequiredtorestore,complexity

84

PerconaXtraBackup-Features

• Non-blocking• SupportforMyISAM• Compression• ParAalBackups• Throdling• IncrementalBackups• ExporAng/ImporAngindividualtables

• Streaming

• Parallelcopying• CompactBackups(since2.1!)

• EncrypAon(since2.1!)• ...

85

PerconaXtraBackup-Opera&on

• Twoseparate"manual"stepsarerequiredfortakingandstoringthebackup:– (1)-Backingup– (2)-PreparingInnoDBfilesforrecovery

• Restoring it happens in a single step:– (3)-Restoringthefiles

86

PerconaXtraBackup-Opera&on(2)

• Performingandrestoringamixedfullbackup:– Backingup(1):

• CopydatafileswhilerecordingtransacAonlogchanges

– FLUSHTABLESWITHREADLOCK;– GetbinlogposiAon– Copyall.MYD,.MYI,.TRG,.TRN,...files– StoprecordingtransacAonlogchanges

– UNLOCKTABLES;• Preparing/Restoring:

– Preparebackupbyapplyingrecordedtlogchanges(2)• RestoringfilestooriginallocaAon(3)

87

PerconaXtraBackup-HandsOn!

• Simplefullbackupandrestore:InstallXtrabackup#yuminstallpercona-xtrabackup-24(1)# innobackupex /var/backup(2)# innobackupex --apply-log /var/backup/[backupfolder]/(3)# innobackupex --copy-back /var/backup/[backupfolder]/

# chown -R mysql:mysql /var/lib/mysql

88

PerconaXtraBackup-HandsOn!89

[root@node1 ~]# innobackupex /var/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Ireland Ltd 2009-2012. All Rights Reserved.

This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

150221 18:26:20 innobackupex: Starting mysql with options: --unbuffered --150221 18:26:20 innobackupex: Connected to database with mysql child process (pid=6317)150221 18:26:26 innobackupex: Connection to database server closedIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!".

innobackupex: Using mysql Ver 14.14 Distrib 5.6.23, for Linux (i686) using readline 5.1innobackupex: Using mysql server version Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /var/backup/2015-02-21_18-26-26150221 18:26:26 innobackupex: Starting mysql with options: --unbuffered --150221 18:26:26 innobackupex: Connected to database with mysql child process (pid=6347)

(1)-FullBackup

PerconaXtraBackup-HandsOn!90

[root@node1 2015-02-21_18-26-26]# lsbackup backup-my.cnf cacti ibdata1 imdb mysql performance_schema xtrabackup_binary xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile

(2)-ApplyingthetransacAonlogs

BackupFolderStructure

[root@node1 2015-02-21_18-26-26]# innobackupex --apply-log /var/backup/2015-02-21_18-26-26/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Ireland Ltd 2009-2012. All Rights Reserved.

This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".

150221 18:34:38 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/var/lib/mysql/backup/2015-02-21_18-26-26/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/var/lib/mysql/backup/2015-02-21_18-26-26 --tmpdir=/tmp

xtrabackup_55 version 2.2.9 for Percona Server 5.6.23 Linux (i686) (revision id: 521)xtrabackup: cd to /var/lib/mysql/backup/2013-02-21_18-26-26xtrabackup: This target seems to be not prepared yet.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(8915647136)xtrabackup: Temporary instance for recovery is set as followings.xtrabackup: innodb_data_home_dir = ./

UnderstandingBackupRequirements

• RecoveryTimeObjecAve-HowquicklymustIrestore?

• RecoveryPointObjecAve-HowmuchdatacanIlose?

• Risks-Whatfailuresshouldthisdatabeprotectedagainst?

91

AdvancedBackupTopics

• IncrementalBackups– HandsOn!

• DelayedSlaves– Usingpt-slave-delaytokeepaslavebehindonitsmasterbyacertainamountofAme.

– GoodhelpagainstpotenAaldisastroususermistakes

– Example:# pt-slave-delay --delay 1m --interval 15s

92

AdvancedBackupTopics-HandsOn!

• IncrementalBackup• Create a full backup# innobackupex /var/backup/

• Create incremental backup # innobackupex --incremental /var/backup/inc/ --incremental-basedir=/var/backup/[backupfolder]

• IncrementalRestore• Applylog

# innobackupex --apply-log --redo-only /var/backup/[backupfolder]

• Applyincrementalbackuptothefullbackup# innobackupex --apply-log --redo-only /var/backup/[backupfolder] —incremental-dir=/var/backup/inc/[incbackupfolder]

• Finish preparing the backup# innobackupex --apply-log /var/backup/[backupfolder]

• Restore as usual

93


• Point-In-Timerecovery– Makinguseofthebinarylog– mysqlbinlogcanbeusedtopipecommandsintomysql

– UseincombinaAonwithincrementalbackupstorestorerightuptothepointwherethedisasteroccurred.

94


• CheckingbackupbinlogposiAon[root@node1 2013-04-21_18-58-22]# cat xtrabackup_binlog_info

node1-bin.000006 243118

• Checkingbinlogcontents[root@node1 mysql]# mysqlbinlog --start-position=243118 --stop-position=243300 node1-bin.000006

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

[...]

• Applyingcontentstomysql[root@node1 mysql]# mysqlbinlog --start-position=243118 --stop-position=243300 node1-bin.000006 | mysql

95


• Managing&Retainingyourbackups– Storingoffsiteincaseofdisaster– RecoverytesAng– RetenAonpolicies– MonitoringBackup&Restoresuccessrate

96



• InstallaAon• LoggingIn• MySQLPrivileges• DiagnosAcs• TroubleshooAng• Backups• Replica&on• SchemaChanges


97



MySQLReplica&on

• ReplicaAon?• BinaryLogs• SeTngUpReplicaAon• Commands• Breaking&FixingReplicaAon• Inconsistencies

98

WhatisReplica&on?

ReplicaAonenablesdatafromoneMySQLdatabaseserver(themaster)tobereplicatedtooneormore

MySQLdatabaseservers(theslaves)(Source:hdp://dev.mysql.com/doc/refman/5.6/en/replicaAon.html)

99

Replica&on

• HappensatMySQLlevel,notStorageEngineLevel• Asynchronous!(Semi-syncavailablein5.5)• AservercanhavemulAplemasters(SinceMySQL5.7)

• IOThread:Fetchesfrommaster• SQLThread:Executesonslave• SingleThreadedExecuAon(UnAl5.6)

100

ExampleAsynchronousReplica&on101

AsynchronousReplica&onhowdoesitwork?102

Othertypesof“replica&on”

• Semi-synchronousreplicaAon• GalerareplicaAon(MySQLwithGalera,PXC,MariaDBCluster)

• MySQLCluster• MySQLgroupreplicaAon• MySQLInnoDBcluster

103

SemiSynchronousReplica&on(S&llthesame)104

SemiSynchronousReplica&onHow?105

GroupReplica&on106

Galera(PerconaXtraDBCluster,MariaDBCluster)107

BINLOG.000001BINLOG.000002

BINLOG.000003BINLOG….

BINLOG.index

BINLOG.000001BINLOG.000002BINLOG.000003

BinaryLogs108

BINLOG.000001BINLOG.000002

BINLOG.000003BINLOG….

BINLOG.index

BINLOG.000001BINLOG.000002BINLOG.000003

BinaryLogs

• Setoffiles• Containsallwritesandschemachanges• IncremenAngnumbers(000001,000002,000003,...)

• 2Formats:– StatementBased(SBR)– RowBased(RBR,sinceMySQL5.1)– PriortoMySQL5.7.7,statement-basedloggingformatwasthedefault.InMySQL5.7.7andlater,row-basedloggingformatisthedefault.

• Binaryfiles(extractwithMySQLbinlog)

109

BinaryLog-Formats(binlog_format)

• StatementBasedReplicaAon(SBR):– Writesstatementstobinarylogs,slaveexecutesthestatement• eg:DELETEFROMwp_opAonsWHEREopAon_name='_transient_doing_cron'

110

– omefuncAonsarenon-determinisAcandcauseinconsistencies:• UUID(), SYSDATE(), FOUND_ROWS(), UPDATE .. LIMIT

without ORDER BY...• WorksforNOW():Amestampincludedinbinarylog

BinaryLog-Formats(binlog_format)

• RowBasedReplicaAon(RBR,since5.1):– Writerowchanges(largerbinlogs)– Doesnotneedtoparse/executequeries,justmakethechangesnecessary

– Mixed:CombinaAonofboth:defaultstoSBR,useRBRwhennecessary

111

LookingatBinaryLogContents

• mysqlbinlog• SHOW BINLOG EVENTS

112

ExampleSBR> SHOW GLOBAL VARIABLES LIKE 'binlog_format';+---------------+-----------+| Variable_name | Value |+---------------+-----------+| binlog_format | STATEMENT |+---------------+-----------+1 row in set (0.00 sec)

> CREATE DATABASE replication;Query OK, 1 row affected (0.14 sec)

> use replicationDatabase changed

> CREATE TABLE repl (a int) ENGINE=innodb;Query OK, 0 rows affected (0.25 sec)

> INSERT INTO repl VALUES (1); Query OK, 1 row affected (0.14 sec)

113

ExampleSBR-mysqlbinlog# mysqlbinlog mysql-bin.000193...# at 106#120106 15:19:13 server id 9999 end_log_pos 203 Querythread_id=11CREATE DATABASE replication/*!*/;# at 203#120106 15:19:32 server id 9999 end_log_pos 312 Querythread_id=11 exec_time=1 error_code=0use replication/*!*/;SET TIMESTAMP=1325859572/*!*/;CREATE TABLE repl (a INT) ENGINE=innodb/*!*/;# at 312#120106 15:19:55 server id 9999 end_log_pos 387 Querythread_id=11 exec_time=0 error_code=0SET TIMESTAMP=1325859595/*!*/;BEGIN/*!*/;

114

ExampleSBR-SHOW BINLOG EVENTS

> SHOW BINLOG EVENTS FROM 106\G*************************** 1. row *************************** Log_name: mysql-bin.000193 Pos: 106 Event_type: Query Server_id: 1End_log_pos: 203 Info: CREATE DATABASE replication*************************** 2. row *************************** Log_name: mysql-bin.000193 Pos: 203 Event_type: Query Server_id: 1End_log_pos: 312 Info: use `replication`; CREATE TABLE repl (a INT) ENGINE=innodb*************************** 3. row *************************** Log_name: mysql-bin.000193 Pos: 312 Event_type: Query Server_id: 1End_log_pos: 387 Info: BEGIN*************************** 4. row *************************** Log_name: mysql-bin.000193

115

ExampleRBR

> SHOW GLOBAL VARIABLES LIKE 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW |+---------------+-------+1 row in set (0.00 sec)

> CREATE DATABASE replication;Query OK, 1 row affected (0.14 sec)

> use replicationDatabase changed

> CREATE TABLE repl (a int) ENGINE=innodb;Query OK, 0 rows affected (0.25 sec)

> INSERT INTO repl VALUES (1); Query OK, 1 row affected (0.14 sec)

116

ExampleRBR-mysqlbinlog

/*!*/;# at 703#120106 15:55:02 server id 1 end_log_pos 812 Query thread_id=11

exec_time=0 error_code=0use replication/*!*/;SET TIMESTAMP=1325861702/*!*/;CREATE TABLE repl (a int) ENGINE=innodb/*!*/;# at 812...# at 937#120106 15:55:06 server id 1 end_log_pos 937 Table_map: `replication`.`repl` mapped to number 17#120106 15:55:06 server id 1 end_log_pos 971 Write_rows: table id 17 flags: STMT_END_F

BINLOG 'SgsHTxMBAAAAMgAAAKkDAAAAABEAAAAAAAEAC3JlcGxpY2F0aW9uAARyZXBsAAEDAAE=SgsHTxcBAAAAIgAAAMsDAAAAABEAAAAAAAEAAf/+AQAAAA=='/*!*/;# at 971#120106 15:55:06 server id 1 end_log_pos 998 Xid = 34COMMIT/*!*/;

117

ExampleRBR-mysqlbinlog --verbose

# mysqlbinlog mysql-bin.000193 --verbose --verbose...# at 937#120106 15:55:06 server id 1 end_log_pos 937 Table_map: `replication`.`repl` mapped to number 17#120106 15:55:06 server id 1 end_log_pos 971 Write_rows: table id 17 flags: STMT_END_F

BINLOG 'SgsHTxMBAAAAMgAAAKkDAAAAABEAAAAAAAEAC3JlcGxpY2F0aW9uAARyZXBsAAEDAAE=SgsHTxcBAAAAIgAAAMsDAAAAABEAAAAAAAEAAf/+AQAAAA=='/*!*/;### INSERT INTO replication.repl### SET### @1=1 /* INT meta=0 nullable=1 is_null=0 */# at 971

118

ExampleRBR-SHOW BINLOG EVENTS

Log_name: mysql-bin.000193 Pos: 703 Event_type: Query Server_id: 1End_log_pos: 812 Info: use `replication`; CREATE TABLE repl (a int) ENGINE=innodb...*************************** 4. row *************************** Log_name: mysql-bin.000193 Pos: 887 Event_type: Table_map Server_id: 1End_log_pos: 937 Info: table_id: 17 (replication.repl)*************************** 5. row *************************** Log_name: mysql-bin.000193 Pos: 937 Event_type: Write_rows Server_id: 1End_log_pos: 971 Info: table_id: 17 flags: STMT_END_F*************************** 6. row *************************** Log_name: mysql-bin.000193 Pos: 971 Event_type: Xid

119

SeAngupReplica&on

• Installtheslave• Changemaster/slaveMySQLconfiguraAon• Prerequisites• ConfigureReplicaAon• StartReplicaAon/CheckStatus

120

Changemaster/slaveConfigura&on-HandsOn!

• Onboththemasterandslave,makethefollowingchangesto/etc/my.cnf:server-id=<unique number>log-slave-updateslog-bin=log-bin

• After making the changes, restart the master[root@node1 ~]# service mysql restart

• For future steps, drop the IMDB databasenode1 mysql> DROP DATABASE IMDB;

121

Whyserver-id?

• Avoideventstobewridenmorethanonce• IdenAfierforyoursystemtokeeptrackofreplicaAon

122

Prerequisites(1)-HandsOn!

• Pleaseperformthefollowingstepsonthemaster

[root@node1 ~]# rm -rf /var/backup/*

[root@node1 ~]# innobackupex /var/backup/

[root@node1 ~]# innobackupex —apply-log /var/backup/[backupfolder]

[root@node1 ~]# ln -s /var/backup/[backupfolder] /var/backup/last_full

123

Prerequisites(2)-HandsOn!

• Nowaytoeasilycreateslavewith1command• It’srequiredtoCreate/Restoreconsistentbackup

node2# rm -rf /var/lib/mysql/* node2# scp -r [email protected]:/var/backup/last_full/* /var/lib/mysql/ node2# chown -R mysql:mysql /var/lib/mysql/node2# yum install Percona-Server-server-57node2# service mysql startnode2# nano ~/.my.cnf

124


ConfigureReplica&on-HandsOn!

• OnMaster,addpermissions:node1 mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@‘%’ IDENTIFIED BY ‘slaveme’;

• GetBinlogfile/PosiAonfrombackupontheslave:node2#cat/var/lib/mysql/xtrabackup_binlog_info

• Example outputFilename log_positionlog-bin.000001 311

• OnSlave,configurereplicaAon:node2 mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.70.1’, MASTER_USER=‘repl’, MASTER_PASSWORD=‘slaveme’,MASTER_LOG_FILE=[logfile],MASTER_LOG_POS=[position];

125

StartReplica&on

mysql> show master status \G*************************** 1. row *************************** File: log-bin.000001 Position: 246938 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

BinlogacAve

StartReplica&on

node2 mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.70.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log-bin.000001 Read_Master_Log_Pos: 246938 Relay_Log_File: slave-relay-bin.000003 Relay_Log_Pos: 246445 Relay_Master_Log_File: log-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 246938 Relay_Log_Space: 247316 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error:

HowFarIsSlaveBehind?

Slaverunning?

WhatisitexecuAng?

Commands

• SQLCommands– AdministraAveCommands– DiagnosAcsCommands

• ShellCommands– mysqlbinlog

128

Administra&veCommands

• Rotatebinarylog:FLUSH BINARY LOGS• Rotaterelaylog:FLUSH RELAY LOGS• Removebinarylogs:PURGE MASTER LOGS TO ‘mysql-bin.000005’; PURGE MASTER LOGS BEFORE ‘2012-01-01 00:00:00’;

• Removeallbinarylogs:RESET MASTER• RemoveslaveconfiguraAonandfiles:RESET SLAVE

129

Diagnos&csCommands

• OnMaster– SHOW MASTER STATUS– SHOW PROCESSLIST– SHOW SLAVE HOSTS– SHOW BINLOG EVENTS

• OnSlave– SHOW SLAVE STATUS– SHOW PROCESSLIST

130

OnMaster:SHOWMASTERSTATUS-HandsOn!

• CurrentbinarylogfileandposiAon:mysql> show master status \G File: log-bin.000001 Position: 246938 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

131

OnMaster:SHOWPROCESSLIST-HandsOn!

• FindConnectedSlavesusingSHOWPROCESSLIST:master> SHOW PROCESSLIST\G*************************** 1. row *************************** Id: 1171 User: repl Host: 192.168.70.2:48590 db: NULL Command: Binlog Dump Time: 3720 State: Master has sent all binlog to slave; waiting for more updates Info: NULL Rows_sent: 0Rows_examined: 0

132

OnSlave:SHOWPROCESSLIST-HandsOn!

• SlaveThreadStatus:mysql> show processlist \G*************************** 1. row *************************** Id: 6 User: system user Host: db: NULL Command: Connect Time: 3782 State: Waiting for master to send event Info: NULL Rows_sent: 0Rows_examined: 0*************************** 2. row *************************** Id: 7 User: system user Host: db: NULL Command: Connect Time: 3317 State: Slave has read all relay log; waiting for more updates Info: NULL Rows_sent: 0Rows_examined: 0

133

OtherCommonConfigura&onOp&ons

• Don’tstartreplicaAonatstart:skip_slave_start

• Putrelaylogeventsinit’sownbinarylog:log_slave_updates

• DisallowwriAngonslaves:read_only• AutomaAcallyremovebinlogs:expire_logs_days

• Changebinlogformat:binlog_format– STANDARDROWin5.7

134

Breaking&FixingReplica&on-HandsOn!

• CreatetableonSlaveFirst,thenMaster:CREATE TABLE sakila.plmce ( id int auto_increment primary key, name varchar(20) );

• CheckSlaveStatus:node2> SHOW SLAVE STATUS\G

• ReplicaAonbroke:(

135

Breaking&FixingReplica&on-HandsOn!

• ReplicaAonisBroken.• Hereweunderstandwhatwentwrong,solet’sjustskipthisrecord!(ONLYDOTHISWHENYOUARESURE)node2 mysql> STOP SLAVE;node2 mysql> SET GLOBAL sql_slave_skip_counter=1 node2 mysql> START SLAVE;node2 mysql> SHOW SLAVE STATUS\G

136

Listeningtosomeonesayingthatheskippeda

recordandnowreplicaAonworksfine

Inconsistencies-HandsOn!

• Let’sdeletesomedatafromtheslave:mysql> DELETE FROM sakila.film_actor LIMIT 5;

• OntheMaster:node1 mysql> GRANT ALL PRIVILEGES ON *.* to ‘pttc’@‘%’ identified by ‘password’;# pt-table-checksum h=localhost,u=pttc,p=password --no-check-binlog-format

• AmerChecksumming,let’sverify:# pt-table-checksum h=localhost,u=pttc,p=password --no-check-binlog-format --replicate-check-only

137

AlmostrananUPDATEwithoutWHEREclause





138



SchemaChanges-Techniques

• Why?– AchievingschemaperfecAoncanbepainful– PerformanceopAmizaAons–MigraAons–…

• MySQL5.5vs5.6and5.7–MySQL5.6/5.7supportsonlineschemachanges– PriortoMySQL5.6usept-online-schema-change

139

OnlineSchemaChanges-HandsOn!

• ConnecttoyourMySQLinstance• Opensecondterminalnode1> insert into sakila.actor (first_name, last_name) values ('LeT','watDim0ooootjeen');

• altertableonyoursystemifyouwantthechangeslocally# node1> ALTER TABLE sakila.actor ADD INDEX new_idx (first_name, last_name);

140

OnlineSchemaChanges

• In5.5,usept-online-schema-changeifyouwanttoreplicatechanges# time pt-online-schema-change \ --execute --alter \ “DROP INDEX new_idx" \ D=sakila,t=actor

141

OnlineSchemaChanges-HandsOn!

NewinMySQL5.6-sweethotaltertables

mysql> ALTER TABLE sakila.actor DROP INDEX new_idx, LOCK=NONE;

Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0

• However:hdps://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid

142

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid




• Configura&onOp&miza&on

143



Op&miza&on-CheckVariables

• Get variablesmysql> show global variables like ‘innodb_buffer_pool_size';mysql> select @@innodb_buffer_pool_size;mysql> show global variables like ‘innodb_%’;

• Setmysql> select @@table_open_cache;mysql> set global table_open_cache = 3000;

• Makethechangespersistent– my.cnf

144

Op&miza&on-ThingsNotToOp&mize

• innodb_io_capacity, innodb_io_capacity_max: don’t oversize

• sort_buffer_size,join_buffer_size, read_buffer_size, read_rnd_buffer_size: leave default globally

• innodb_thread_concurrency: Default is fine for most environments

• query_cache_type, query_cache_size: disable or do not oversize (+64MB)

• innodb_flush_method=O_DIRECT and ext3

145

Op&miza&on-ThingsToOp&mize

• innodb_log_file_size: Increase InnoDB Transaction Logs

• innodb_buffer_pool_size• innodb_io_capacity(_max)• innodb-flush-method=O_DIRECT• innodb_flush_log_at_trx_commit

– increases performance– DECREASES reliability! (no more ACID compliant)

146

Op&miza&on-ThingsToOp&mize

• Read more at• https://www.percona.com/blog/2016/10/12/

mysql-5-7-performance-tuning-immediately-after-installation/

• https://www.percona.com/blog/2016/08/19/top-overlooked-mysql-performance-optimizations-q/

147

https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/



https://www.percona.com/blog/2016/08/19/top-overlooked-mysql-performance-optimizations-q/

https://www.percona.com/blog/2016/08/19/top-overlooked-mysql-performance-optimizations-q/

Keepinmindthefollowingop&misa&onprocess148

Low Hanging Fruit • MySQL

Configuration • OS settings • Indexes • Caching

Medium Level • InfraStructure • MySQL Version

HardChanges Majorschemachanges ApplicaAonarchitecturechanges

Wanttolearnmore?

• Some good resources to help you along:– Blogs

• PlanetMySQL:planet.mysql.com• MySQLPerformanceBlog:www.mysqlperformanceblog.com

– RecordedWebinars• www.percona.tv

149

http://planet.mysql.com

http://www.mysqlperformanceblog.com

http://www.percona.tv

Opera&onalDBAInANutshell-Q&A

• SeAngUpTodaysEnvironment

• Installa&on• LoggingIn• MySQLPrivileges• Diagnos&cs• Troubleshoo&ng• Monitoring• Backups• Replica&on

• SchemaChanges• Configura&onOp&miza&on

150

HowdoingoperaAonsnowfeels


Wanttolearnmore?

• Some good resources to help you along:– Books

• HighPerformanceMySQL,3rdEdiAon(BaronSchwartz,PeterZaitsev,VadimTkachenko)

• EffecAveMySQLseries(RonaldBradford)• InstantInnoDB(MadReid)

151

opera&onal dba in a nutshell - percona€¦ · what is mariadb server • fork of mysql...

Documents