Basic External Troubleshooting Tools

在我的疑难解答网络研讨会系列中,我通常喜欢讨论通过SQL接口提供的内置工具。虽然它们是有效的,有助于理解正在发生的事情,但是外部工具的设计也使数据库管理员的生活更容易。
在本次网络研讨会中,我将讨论支持团队和客户最重视的外部工具、工具包和图形工具。我将展示这些工具的主要优势,并提供如何有效地使用它们的示例。
我将介绍Percona工具包、MySQL实用程序、MySQL沙盒、Percona监控和管理(PMM)以及其他一些工具。

展开查看详情

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