Python Utilities for Managing MySQL Databases Mats Kindahl, Lars - - PowerPoint PPT Presentation

▶
python utilities for managing mysql databases
SMART_READER_LITE
LIVE PREVIEW

Python Utilities for Managing MySQL Databases Mats Kindahl, Lars - - PowerPoint PPT Presentation

May 04, 2023 31 likes •375 views

<Insert Picture Here> Python Utilities for Managing MySQL Databases Mats Kindahl, Lars Thalmann, Chuck Bell THE FOLLOWING IS INTENDED TO OUTLINE OUR GENERAL PRODUCT DIRECTION. IT IS INTENDED FOR INFORMATION PURPOSES ONLY, AND MAY NOT BE

slide-1
SLIDE 1

<Insert Picture Here>

Python Utilities for Managing MySQL Databases

Mats Kindahl, Lars Thalmann, Chuck Bell

slide-2
SLIDE 2

2

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.

slide-3
SLIDE 3

3

About the Speakers

  • Mats Kindahl, PhD
  • Replication Expert and Lead Developer
  • mats.kindahl@oracle.com
  • Lars Thalmann, PhD
  • Development Director, Replication, and Backup
  • lars.thalmann@oracle.com
  • Chuck Bell, PhD (not present)
  • MySQL Utilities and Replication
  • chuck.bell@oracle.com
slide-4
SLIDE 4

4

Topics

  • Introduction to MySQL Utilities
  • List of current utilities
  • Architecture of MySQL Utilities
  • Examples of usage
  • Using the MySQL Utilities Python Library
slide-5
SLIDE 5

5

What is MySQL Utilities?

  • A collection of Python utilities for managing MySQL

databases

  • Part of MySQL Workbench 5.2.31
  • Available under the GPLv2 license
  • Written in Python
  • Easily enhanced using a growing code library
  • Goal is to provide a Python library to grow solutions

for common administrative problems

slide-6
SLIDE 6

6

List of Utilities

  • mysqldbcompare – compare databases
  • mysqldbcopy – copy databases between servers
  • mysqldbexport – export metadata and data
  • mysqldbimport – import metadata and data
  • mysqldiff – compare object definitions
  • mysqldiskusage – show disk usage for databases
  • mysqlindexcheck – check for redundant indexes
  • mysqlmetagrep – search metadata
  • mysqlprocgrep – search process information
  • mysqlreplicate – setup replication
  • mysqlrplcheck – check replication configuration
slide-7
SLIDE 7

7

List of Utilities

  • mysqlserverclone – start a scratch server
  • mysqlserverinfo – show server information
  • mysqluc – console for utilities
  • mysqluserclone – clone a user account
slide-8
SLIDE 8

8

Utilities in MySQL Workbench

  • Launch the MySQL Utilities command window:

– Click on the Plugins menu item – Select “Start Shell for MySQL Utilities”

OR

  • From the Workbench main window:

– Click on the drop down arrow icon to the right of the main window – Scroll through screens to find the MySQL Utilities icon

slide-9
SLIDE 9

9

How do I access the utilities?

slide-10
SLIDE 10

10

How do I access the utilities?

slide-11
SLIDE 11

11

Structure of the MySQL Utilities

Scripts Command Module Common Module MySQL Utilities Library

mysqlprocgrep mysql.utilities.command mysql.utilities.common

slide-12
SLIDE 12

12

mysqldbcompare – compare databases

  • Find missing objects from either database
  • Find objects that differ in definition
  • Find differences in data among tables
  • Print difference in formats differ, ndiff, or context
  • Print output rows in GRID, TAB, CSV, or VERTICAL

formats

  • Scenarios
  • checking master and slave for consistency
  • checking production and development databases for

consistency

  • generating a difference report for expected differences

among new and old data

  • comparing backups for differences
slide-13
SLIDE 13

13

Sample execution

$ mysqldbcompare --server1=root@localhost --server2=root@backup_host:3310 \ > inventory:inventory --run-all-tests # server1 on localhost: ... connected. # server2 on localhost: ... connected. # Checking databases inventory on server1 and inventory on server2 WARNING: Objects in server1:inventory but not in server2:inventory: VIEW: finishing_up VIEW: cleaning Defn Row Data Type Object Name Diff Count Check

  • TABLE supplier pass FAIL FAIL

Row counts are not the same among inventory.supplier and inventory.supplier. Data differences found among rows:

  • -- inventory.supplier

+++ inventory.supplier @@ -1,2 +1,2 @@ code,name

  • 2,Never Enough Inc.

+2,Wesayso Corporation

<user>:<password>@<host>:<port>:<socket>

slide-14
SLIDE 14

14

mysqlmetagrep – search objects

  • Search for objects with names matching a pattern
  • Match using SQL patterns or POSIX regular

expressions

  • Search bodies of routines (procedures, events,

triggers)

  • Generate SQL for executing the query

– Can be used in applications – Can be stored in events or views

_”

slide-15
SLIDE 15

15

Searching for objects by name

Searching a database for objects starting with “t”

mysqlmetagrep --pattern="t_" --server=mats@localhost

$ mysqlmetagrep --pattern="t_" --server=mats@localhost +------------------------+--------------+--------------+-----------+-------------+----------+ | Connection | Object Type | Object Name | Database | Field Type | Matches | +------------------------+--------------+--------------+-----------+-------------+----------+ | mats:*@localhost:3307 | TABLE | t1 | test | COLUMN | th | | mats:*@localhost:3307 | TABLE | t1 | test | TABLE | t1 | | mats:*@localhost:3307 | TABLE | t2 | test | TABLE | t2 | | mats:*@localhost:3307 | TABLE | tt5 | test | COLUMN | t2,t1 | +------------------------+--------------+--------------+-----------+-------------+----------+

slide-16
SLIDE 16

16

Searching routine bodies

Searching a database for objects containing “l_host”

mysqlmetagrep --body --pattern="%l_host%" \

  • -server=mats@localhost

$ mysqlmetagrep --body --pattern="%l_host%" --server root@localhost:3307 +------------------------+--------------+----------------+-----------+-------------+----------------+ | Connection | Object Type | Object Name | Database | Field Type | Matches | +------------------------+--------------+----------------+-----------+-------------+----------------+ | root:*@localhost:3307 | PROCEDURE | switch_master | test | ROUTINE | switch_master | +------------------------+--------------+----------------+-----------+-------------+----------------+

slide-17
SLIDE 17

17

mysqlprocgrep – search processes

  • Search processes on multiple machines
  • Match by PROCESSLIST fields

– Id, State, User, Host, Database, Command, State, Info

  • Match by age

– Find long-running queries, or idle connections

  • Get SQL for performing the query or action

– Put in application – Put in events

  • Kill queries or connections

– Option: --kill-query – Option: --kill-connection

slide-18
SLIDE 18

18

Sample usage

+--------------------------+-----+-------+--------------+-------+----------+-------+------------+--------------------+ | Connection | Id | User | Host | Db | Command | Time | State | Info | +--------------------------+-----+-------+--------------+-------+----------+-------+------------+--------------------+ | mats:*@example.com:3306 | 53 | mats | example.com | user | Query | 2040 | executing | select … | +--------------------------+-----+-------+--------------+-------+----------+-------+------------+--------------------+

  • Find queries by 'www-data' that have been executing

for more than 20 minutes

mysqlprocgrep --server=mats@example.com

  • -match-user=www-data
  • -match-state=executing
  • -age=20m
slide-19
SLIDE 19

19

Sample usage

  • Find queries by 'www-data' that have been executing

for more than 20 minutes

mysqlprocgrep --server=mats@example.com

  • -match-user=www-data
  • -match-state=executing
  • -age=20m --kill-query

K i l l T h e m !

slide-20
SLIDE 20

20

mysqlreplicate – setup replication

  • Permits an administrator to start replication among

two servers.

  • User have to provide:

– Login information to the slave – Connection information for the master

  • Example: Setup replication between two instances

mysqlreplicate --rpl-user=rpl_user:xyzzy

  • -master=root@localhost:3306 --slave=root@localhost:3307
slide-21
SLIDE 21

21

mysqluc – MySQL Utilities Console

  • User defined variables
  • Help features
  • Tab completion (names, parameters, options,

variables)

slide-22
SLIDE 22

22

MySQL Utilities Python Library

  • Major classes for:
  • Database
  • Replication
  • Server
  • Index
  • Table
  • User
  • Also many helpful methods such as
  • connect_servers()
  • setup_common_options()
  • format_tabular_list()
  • find_running_servers()
slide-23
SLIDE 23

23

Library Example

  • Goal: create a utility that makes a copy of a running

server and copies all of the data and users.

  • Steps
  • Setup the parameters
  • Connect to the original server
  • Find all of the databases
  • Find all of the users
  • Make a clone of the original server
  • Copy all of the databases
  • Copy all of the users
slide-24
SLIDE 24

24

Utility options

  • Server to read from: --server
  • Databases: --databases
  • Data directory for new server: --new-data
  • Port for new server: --new-port
  • Server ID for new server: --new-id
slide-25
SLIDE 25

25

Set up options

from optparse import OptionParser parser = OptionParser() parser.add_option("--server", action="store”, dest="server", type="string", default="root@localhost:3306", help="connection information for original server in " "the form: <user>:<password>@<host>:<port>:<socket>") parser.add_option("-d", "--databases", action="store", dest="dbs_to_copy”, type="string", help="comma-separated list of databases " "to include in the copy (omit for all " " databases)”, default=None) . . .

slide-26
SLIDE 26

26

Library Example

  • Parse the connection information

from mysql.utilities.common.options import parse_connection try: conn = parse_connection(opt.server) except: parser.error("Server connection values invalid or" " cannot be parsed.")

  • Connect to original server

from mysql.utilities.common.server import Server server_options = {'conn_info’ : conn, 'role’ : "source"} srv1 = Server(server_options) srv1.connect()

slide-27
SLIDE 27

27

Library Example

  • Find all of the databases

db_list = [ (db[0],None) for db in srv1.get_all_databases() ]

  • Find all of the users

_QUERY = """ SELECT user, host FROM mysql.user WHERE user != 'root’ AND user != '' """ users = srv1.exec_query(_QUERY) user_list = [ user[0] + '@' + user[1] for user in users ]

slide-28
SLIDE 28

28

Library Example

  • Make a clone of the original server

from mysql.utilities.command.serverclone import clone_server res = clone_server(conn, opt.new_data, opt.new_port,

  • pt.new_id, "root", None, False, True)
slide-29
SLIDE 29

29

Library Example

  • Copy all of the databases

# Set destination server connection parameters from mysql.utilities.command import dbcopy dest = { "user" : conn.get("user"), "passwd" : "root", "host" : conn.get("host"), "port" : opt.new_port, "unix_socket" : os.path.join(opt.new_data, "mysql.sock"), } # Set options for copy

  • ptions = {

"quiet" : True, "force" : True, } dbcopy.copy_db(conn, dest, db_list, options)

slide-30
SLIDE 30

30

Library Example

  • Copy all of the users

from mysql.utilities.command.userclone import clone_user

  • ptions = {

"overwrite" : True, "quiet" : True, "globals" : True } for user in user_list: res = clone_user(conn, dest_values, user, (user,), options)

slide-31
SLIDE 31

31

Enhancing the example

  • Consistent copy?

– Locking tables – Consistent snapshot (InnoDB)

  • Copying all users?

– Users not related to the database? – Users with only global privileges?

  • Automatically start replication
  • Killing connections?
slide-32
SLIDE 32

32

Where To Go From Here

You can download MySQL Workbench from: http://www.mysql.com/downloads/workbench/ You can also download the latest development source code tree for the MySQL Workbench Utilities from: http://launchpad/net/mysql-utilities

slide-33
SLIDE 33

33

MySQL High Availability Get it as free ebook: http://oreilly.com/go/ebookrequest Valid this week, mention event “MySQL Replication Update” MySQL Support: www.mysql.com/contact PHP under the hood Thu 10:40am MySQL Technology Update Thu 2:30pm MySQL Binlog API Fri 10:00am Visit MySQL at Oracle booth 701!

slide-34
SLIDE 34

34