Basic External Troubleshooting Tools

1 .Basic External Troubleshooting Tools March, 15, 2018 Sveta Smirnova 

2 .Table of Contents •MySQL Sandbox, DBdeployer and Docker •Tools from Standard MySQL Server Distribution •MySQL Utilities •Percona Toolkit •PMM 2 

3 .What This Webinar is About? • Instruments for troubleshooting • Which I use • Helps to discover and solve problems 3 

4 .What This Webinar is Not? • Complete guide • Cover of • All existent tools • MySQL Test Framework • Build-related tools, such as mysql config • Third-party benchmarking tools 4 

5 .MySQL Sandbox, DBdeployer and Docker 

6 .Ideal For Quick Installation • Software deploys • Development servers • Upgrade/downgrade tests 6 

7 .MySQL Sandbox and DBdeployer A tool that deploys MySQL database servers easily. c 7 

8 .MySQL Sandbox and DBdeployer A tool that deploys MySQL database servers easily. c • Installs • Single server • Replicated Servers Supports Group Replication • Multiple instances 7 

9 .MySQL Sandbox and DBdeployer A tool that deploys MySQL database servers easily. c • Installs • Single server • Replicated Servers Supports Group Replication • Multiple instances • Supports custom configuration 7 

10 .MySQL Sandbox Usage • Installation perl Makefile.PL make [make test] - optionally sudo make install 8 

11 .MySQL Sandbox Usage • Single Server $ make_sandbox Percona-Server-5.7.21-20-Linux.x86_64.ssl100.tar.gz unpacking /home/sveta.smirnova/Percona-Server-5.7.21-20-Linux.x86_64.ssl100.tar.gz Executing low_level_make_sandbox ... do you agree? ([Y],n) # Starting server ... sandbox server started # Loading grants Your sandbox server was installed in $SANDBOX_HOME/msb_5_7_21 $ cd $SANDBOX_HOME/msb_5_7_21 $ ./use -e "select @@innodb_buffer_pool_size/1024/1024" +-------------------------------------+ | @@innodb_buffer_pool_size/1024/1024 | +-------------------------------------+ | 128.00000000 | +-------------------------------------+ 8 

12 .MySQL Sandbox Usage • Replicated Setup $ make_replication_sandbox Percona-Server-5.7.21-20-Linux.x86_64.ssl100.tar.gz installing and starting master installing slave 1 installing slave 2 starting slave 1 . sandbox server started starting slave 2 .. sandbox server started initializing slave 1 initializing slave 2 replication directory installed in $SANDBOX_HOME/rsandbox_Percona-Server-5_7_21 8 

13 .MySQL Sandbox Usage • Multiple Instances $ make_multiple_sandbox --how_many_nodes=5 --circular --sandbox_base_port=8000 \ > Percona-Server-5.7.21-20-Linux.x86_64.ssl100.tar.gz installing node 1 installing node 2 installing node 3 installing node 4 installing node 5 ... Circular replication activated group directory installed in $SANDBOX_HOME/multi_msb_Percona-Server-5_7_21 8 

14 .MySQL Sandbox Usage $ cd $SANDBOX_HOME/multi_msb_Percona-Server-5_7_21 $ ls -l total 100 -rwxr-xr-x check_slaves -rwxr-xr-x clear_all -rw-r--r-- connection.json -rw-r--r-- default_connection.json -rwxr-xr-x n1 -rw-r--r-- README -rwxr-xr-x n2 -rwxr-xr-x restart_all -rwxr-xr-x n3 -rwxr-xr-x send_kill_all -rwxr-xr-x n4 -rwxr-xr-x set_circular_replication.sh -rwxr-xr-x n5 -rwxr-xr-x start_all drwxr-xr-x node1 -rwxr-xr-x status_all drwxr-xr-x node2 -rwxr-xr-x stop_all drwxr-xr-x node3 -rwxr-xr-x use_all drwxr-xr-x node4 drwxr-xr-x node5 8 

15 .MySQL Sandbox Usage • Custom Options $ cat > webinar.cnf [mysqld] innodb_buffer_pool_size=1G ^C $ make_sandbox Percona-Server-5.7.21-20-Linux.x86_64.ssl100.tar.gz -- --sandbox_port=35721 \ > -m ./webinar.cnf ... # Loading grants Your sandbox server was installed in $SANDBOX_HOME/msb_5_7_21 $ cd $SANDBOX_HOME/msb_5_7_21 $ ./use -e "select @@innodb_buffer_pool_size/1024/1024" +-------------------------------------+ | @@innodb_buffer_pool_size/1024/1024 | +-------------------------------------+ | 1024.00000000 | 8 +-------------------------------------+ 

16 .DBdeployer Usage • Installation c $ VERSION=0.3.1 $ origin=https://github.com/datacharmer/dbdeployer/releases/download/$VERSION $ wget $origin/dbdeployer-$VERSION.linux.tar.gz $ tar -xzf dbdeployer-$VERSION.linux.tar.gz $ chmod +x dbdeployer-$VERSION.linux $ mv dbdeployer-$VERSION.linux dbdeployer 9 

17 .DBdeployer Usage • Prepare Server $ ./dbdeployer --unpack-version=5.7.21 unpack Percona-Server-5.7.21-20-Linux.x86_64.ssl100.tar.gz Unpacking tarball Percona-Server-5.7.21-20-Linux.x86_64.ssl100.tar.gz to /home/sveta.smirnova/opt/mysql/5.7.21 .........100.........200.........300.........400.........500.........600.........700.........800.. .......900.........1000.........1100.........1200.........1300.........1400.........1500......... 1600.........1700.........1800.........1900.........2000.........2100.........2200.........2300... ... 17400.........17500.........17600.........17700.........17800.........17900.........18000......... 18100.........18200.........18300.........18400..18421 9 

18 .DBdeployer Usage • Single Server $ ./dbdeployer deploy single 5.7.21 --port 35721 run ’dbdeployer usage single’ for basic instructions’ . sandbox server started $ cd /bigdisk/sveta/sandbox_webinar/msb_5_7_21 $ ./use -e "select @@innodb_buffer_pool_size/1024/1024" +-------------------------------------+ | @@innodb_buffer_pool_size/1024/1024 | +-------------------------------------+ | 128.00000000 | +-------------------------------------+ 9 

19 .DBdeployer Usage • Replicated Setup $ ./dbdeployer deploy replication 5.7.21 Installing and starting master . sandbox server started Installing and starting slave 1 . sandbox server started Installing and starting slave 2 . sandbox server started /bigdisk/sveta/sandbox_webinar/rsandbox_5_7_21/initialize_slaves initializing slave 1 initializing slave 2 Replication directory installed in /bigdisk/sveta/sandbox_webinar/rsandbox_5_7_21 run ’dbdeployer usage multiple’ for basic instructions’ 9 

20 .DBdeployer Usage • Multiple Instances $ ./dbdeployer deploy multiple 5.7.21 Installing and starting node 1 . sandbox server started Installing and starting node 2 . sandbox server started Installing and starting node 3 . sandbox server started multiple directory installed in /bigdisk/sveta/sandbox_webinar/multi_msb_5_7_21 run ’dbdeployer usage multiple’ for basic instructions’ 9 

21 .DBdeployer Usage • Custom Options $ ./dbdeployer deploy single 5.7.21 --port 35721 --my-cnf-options=innodb_buffer_pool_size=1G run ’dbdeployer usage single’ for basic instructions’ . sandbox server started $ cd /bigdisk/sveta/sandbox_webinar/msb_5_7_21 $ ./use -e "select @@innodb_buffer_pool_size/1024/1024" +-------------------------------------+ | @@innodb_buffer_pool_size/1024/1024 | +-------------------------------------+ | 1024.00000000 | +-------------------------------------+ 9 

22 .DBdeployer Usage • Run command globally $ ./dbdeployer global use "select @@server_id" # Running "use" on msb_5_7_21 @@server_id 0 # Running "use_all" on multi_msb_5_7_21 # Running "use_all" on rsandbox_5_7_21 # server: 1 # master @@server_id @@server_id 100 100 # server: 2 # server: 1 @@server_id @@server_id 200 200 # server: 3 # server: 2 @@server_id @@server_id 300 300 9 

23 .Docker The world’s leading software container platform to modernize applications without disruption. c 10 

24 .Docker The world’s leading software container platform to modernize applications without disruption. c • You can create any pre-defined setup 10 

25 .Docker The world’s leading software container platform to modernize applications without disruption. c • You can create any pre-defined setup • Ready-to-use containers by • Oracle MySQL Team • Percona • Docker MySQL • Docker MariaDB 10 

26 .Docker Usage $ docker run --name percona-server -e MYSQL_ROOT_PASSWORD=secret -d percona/percona-server 676c502c72aa2e5137f3bd81a13ad99329e52f4f38ee24ea354956acfba7cd74 $ docker exec -it percona-server bash mysql@676c502c72aa:/$ mysql -uroot -psecret mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.21-20 Percona Server (GPL), Release ’20’, Revision ’ed217b06ca3’ Copyright (c) 2009-2018 Percona LLC and/or its affiliates Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the current input statement. mysql> \q 11 Bye 

27 .When to Use? Feature Sandbox Docker Single Server X X Replication X Scriptable Complex Setup X Scriptable PXC/Galera No support X Custom Options X Scriptable 12 

28 .Tools from Standard MySQL Server Distribution 

29 .Installation and Setup • Come with standard distribution • Located in bin directory • No additional maintenance required 14