MySQL Utilities -- PyTexas 2015

MySQL UtilitiesMySQL Utilities Dave Stokes MySQL Community Manager Email: David.Stokes@Oracle.com Twiter: @Stoker Slides: slideshare.net/davidmstokes

Safe Harbor Agreement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decision. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

So take anything on future products with a grain of

MySQL 5.7 ● http://www.thecompletelistoffeatures.com/ ● 150 new features – Native JSON data type – Group Replication – Replication ● Multi source ● Parallel within database – Secure by default install – GIS Support

Download & Documentation ● https://dev.mysql.com/downloads/utilities/ ● http://dev.mysql.com/doc/mysql- utilities/1.5/en/

What are they? ● It is a package of utilities that are used for maintenance and administration of MySQL servers. These utilities encapsulate a set of primitive commands, and bundles them so they can be used to perform macro operations with a single command. ● The utilities are written in Python, available under the GPLv2 license, and are extendable using the supplied library. They are designed to work with Python versions 2.6 or later and there is no support (yet) for Python v3.1.

What are they? ● MySQL Utilities is both a set of command- line utilities as well as a Python library for making the common tasks easy to accomplish. The library is written entirely in Python, meaning that it is not necessary to have any other tools or libraries installed to make it work. It is currently designed to work with Python v2.6 or later and there is no support (yet) for Python v3.1.

Prerequisites ● MySQL Utilities requires Python 2.6. All of the Python code is written to conform to this version of Python. ● For connecting to MySQL, MySQL Utilities requires a MySQL Connector/Python General Availability (GA) release (version 2.0.4/2.1.2 or later). If you do not have Connector/Python installed get it at http://dev.mysql.com/downloads/connector/p ython/

Install ● Source Code – shell> unzip mysql-utilities-1.5.6.zip – shell> cd mysql-utilities-1.5.6 – shell> python ./setup.py build – shell> sudo python ./setup.py install

Install ● Oracle Linux and Red Hat Linux 6 – sudo rpm -i mysql-utilities-1.5.6-el6.noarch.rpm ● Debian Linux – sudo dpkg -i mysql-utilities-1.5.6-debian6.0_all.deb ● OS X – The .dmg file available for OS X is built for x84-64 bit platforms, and supports OS X version 10.7 (Lion) and newer. After you download the .dmg file, install MySQL Utilities by opening it and double clicking the .pkg file.

Install ● Microsoft Windows – MySQL Installer: Download and execute the MySQL Installer MSI file. Select the MySQL Utilities product and then proceed with the installation. This is the recommended approach, and it will also automatically select and install the required prerequisites. See the MySQL Installer manual for additional details. – Standalone: Download and execute the MySQL Utilities standalone MSI file.

Overview ● Database Operations ● General Operations ● High Availability Operations ● Server Operations ● Specialized Operations

Database Operations ● mysqldbcompare – Compare databases on two servers or the same server – Compare definitions and data – Generate a difference report – Generate SQL transformation statements ● mysqldbcopy – Copy databases between servers – Clone databases on the same server – Supports rename ● mysqldbexport – Export metadata and/or data from one or more databases – Formats: SQL, CSV, TAB, Grid, Vertical ● mysqldbimport – Import metadata and data from one or more files – Reads all formats from mysqldbexport ● mysqldiff – Compare object definitions – Generate a difference report

General Operations ● mysqldiskusage – Show disk usage for databases – Generate reports in SQL, CSV, TAB, Grid, Vertical ● mysqlfrm – Reads .frm files, optionally in byte-by-byte diagnostic mode – Generates CREATE statements from table definition data ● mysqlindexcheck – Read indexes for one or more tables – Check for redundant and duplicate indexes – Generate reports in SQL, CSV, TAB, Grid, Vertical ● mysqlmetagrep – Search metadata – Regexp, database search – Generate SQL statement for search query ● mysqlprocgrep – Search process information – Generate SQL statement for search – Kill processes that match query ● mysqluserclone – Clone a user account, to the same or different server – Show user grants ● mysqluc – Command line client for running MySQL Utilities – Allows a persistent connection to a MySQL Server – Tab completion for utility names and options – Allows calling the commands with shorter names, such as using "serverinfo" instead of mysqlserverinfo

High Availability Operations ● mysqlfailover – Provides automatic failover on a replication topology – Uses Global Transaction Identifiers (GTID, MySQL Server 5.6.5+) ● mysqlreplicate – Setup replication – Start from beginning, current, specific binlog, pos ● mysqlrplms – Provides round-robin multi-source replication (a slave server continually cycles through multiple masters in order to store a consolidated data set) – Uses Global Transaction Identifiers (GTID, MySQL Server 5.6.9+) ● mysqlrpladmin – Administers the replication topology – Allows recovery of the master – Commands include elect, failover, gtid, health, start, stop, and switchover ● mysqlrplcheck – Check replication configuration – Tests binary logging on master ● mysqlrplshow – Show slaves attached to master – Can search recursively – Show the replication topology as a graph or list ● mysqlrplsync – Check data consistency between servers in a replicated setup – Uses Global Transaction Identifiers (GTID) – Requires MySQL Server 5.6.14 and higher

Server Operations ● mysqlserverclone – Start a new instance of a running server ● mysqlserverinfo – Show server information – Can search for running servers on a host – Access online or offline servers

Specialized Operations These utilities are designed to be used with a specific commercial extension. In this case, these utilities require the Audit Log Plugin (Comes with support contract) . ● mysqlauditadmin – Monitor the audit log – Copy, rotate, and configure the audit log ● mysqlauditgrep – Search the audit log – Output results to different formats

mysqldbcopy shell> mysqldbcopy --source=root@localhost:3310 --destination=root@localhost:3311 test123 --rpl=master --rpl-user=rpl # Source on localhost: ... connected. # Destination on localhost: ... connected. # Source on localhost: ... connected. # Stopping slave # Copying database test123 # Copying TABLE test123.t1 # Copying data for TABLE test123.t1 # Connecting to the current server as master # Starting slave #...done.

mysqlprocgrep Kill all queries created by user john that are less than 1 minute: shell> mysqlprocgrep --server=root@localhost --match-user=john --age=-1m --kill-query Kill all connections that have been idle for more than 1 hour: shell> mysqlprocgrep --server=root@localhost --match-command=sleep --age=1h --kill-connection

mysqlrplcheck shell> mysqlrplsync --master=user:pass@localhost:3310 --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312 # # GTID differences between Master and Slaves: # - Slave 'localhost@3311' is 15 transactions behind Master. # - Slave 'localhost@3312' is 12 transactions behind Master. # # Checking data consistency. # # Using Master 'localhost@3310' as base server for comparison. # Checking 'test_rplsync_db' database... # - Checking 't0' table data... # [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3311'. # [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'. # - Checking 't1' table data... # [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3311'. # [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'. # Checking 'test_db' database... # - Checking 't0' table data... # [OK] `test_db`.`t0` checksum for server 'localhost@3311'. # [OK] `test_db`.`t0` checksum for server 'localhost@3312'. # - Checking 't1' table data... # [OK] `test_db`.`t1` checksum for server 'localhost@3311'. # [OK] `test_db`.`t1` checksum for server 'localhost@3312'. # #...done. # # SUMMARY: No data consistency issue found.

mysqlserverclone shell> mkdir /source/test123 shell> mysqlserverclone --server=root:pass@localhost --new-data=/Users/cbell/source/test123 --new-port=3310 --root-password=pass --mysqld=--log-bin=mysql-bin # Cloning the MySQL server running on localhost. # Creating new data directory... # Configuring new instance... # Locating mysql tools... # Setting up empty database and mysql tables... # Starting new instance of the server... # Testing connection to new instance... # Success! # Setting the root password... # ...done.

MySQL Fabric ● MySQL Fabric is a system for managing a farm of MySQL servers. MySQL Fabric provides an extensible and easy to use system for managing a MySQL deployment for sharding and high-availability. ● Fabric organizes servers in groups (called high-availability groups) for managing different shards or simply for providing high availability. For example, if standard asynchronous replication is in use, Fabric may be configured to automatically monitor the status of servers in a group. If the current master in a group fails, it elects a new one if a server in the group can become a master. ● Besides the high-availability operations such as failover and switchover, Fabric also permits shard operations such as shard creation and removal. ● Fabric is written in Python and includes a special library that implements all of the functionality provided. To interact with Fabric, a special utility named mysqlfabric provides a set of commands you can use to create and manage groups, define and manipulate sharding, and much more.

MySQL Fabric ● Does my application need to do anything as part of the failover? ● No. The failover is transparent to the application as the Fabric-aware connectors will automatically start routing transactions and queries based on the new server topology. The application does need to handle the failure of a number of transactions when the Primary has failed but before the new Primary is in place but this should be considered part of normal MySQL error handling.

MySQL Router ● Connection mutliplexing ● Traffic splitter ● Minimizing aborts for Group Replication ● Connection load balancing ● Static routing ● Fabric support for non-Fabric aware connectors

MySQL Workbench ● Query tool – Visual Explain to aid in optimization ● Admin tool – Users, backup, imports, change settings – No more fat finger 'UPDATE user set 'SELECT_PRIV='Y',.... ● Dashboard and System Monitoring – Sys Schema ● Entity Relationship Mapper – Great tool for investigating new schemas – Print schema for documentation ● Migration tool ● And more!

Q&A ● Slides: slideshare.net/davidmstokes ● Twitter: @Stoker ● Email: David.Stokes@Oracle.com ● Blog: OpenSourceDBA.wordpress.com

MySQL Utilities -- PyTexas 2015

More Related Content

What's hot

Viewers also liked

Similar to MySQL Utilities -- PyTexas 2015

More from Dave Stokes

Recently uploaded

MySQL Utilities -- PyTexas 2015