OurSQL Episode 151: Tooling Around, Part 1

This week we start talking about the Python MySQL Utilities. Ear Candy is a pitfall when importing a mysqldump export and At the Movies is "Deploying MySQL in AWS and OpenStack" by Mark Riddoch of SkySQL.

Part 2 of MySQL Utilities
Part 3 of MySQL Utilities
Part 4 of MySQL Utilities
Part 5 of MySQL Utilities

MySQL Utilities
MySQL Utilities documentation
Download MySQL Utilities

Sample connection string:
username:password@host:port

mysqlauditadmin

mysqlauditgrep

mysqldbcompare

Unified difftype example:
options: --skip-row-count --skip-data-check --run-all-tests information_schema
# server1 on localhost: ... connected.
# server2 on remote.mozilla.com: ... connected.
# Checking databases information_schema on server1 and information_schema on server2
#
#                                                   Defn    Row     Data  
# Type      Object Name                             Diff    Count   Check 
# -------------------------------------------------------------------------
# TABLE     CHARACTER_SETS                          pass    SKIP    SKIP   
# TABLE     CLIENT_STATISTICS                       pass    SKIP    SKIP   

# TABLE     PLUGINS                                 pass    SKIP    SKIP   
# TABLE     PROCESSLIST                             FAIL    SKIP    SKIP   
#
# Object definitions differ. (--changes-for=server1)
#

--- `information_schema`.`PROCESSLIST`
+++ `information_schema`.`PROCESSLIST`
@@ -1,6 +1,6 @@
CREATE TEMPORARY TABLE `PROCESSLIST` (
   `ID` bigint(4) NOT NULL DEFAULT '0',
-  `USER` varchar(16) NOT NULL DEFAULT '',
+  `USER` varchar(128) NOT NULL DEFAULT '',
   `HOST` varchar(64) NOT NULL DEFAULT '',
   `DB` varchar(64) DEFAULT NULL,
   `COMMAND` varchar(16) NOT NULL DEFAULT '',

# TABLE     PROFILING                               pass    SKIP    SKIP   
# TABLE     REFERENTIAL_CONSTRAINTS                 pass    SKIP    SKIP   
...
# TABLE     VIEWS                                   pass    SKIP    SKIP   
# TABLE     XTRADB_ADMIN_COMMAND                    pass    SKIP    SKIP  

# Database consistency check failed.
#
# ...done

SQL difftype:

options: --skip-row-count --skip-data-check --run-all-tests information_schema --difftype=sql
# server1 on localhost: ... connected.
# server2 on db1.mktdb.services.phx1.mozilla.com: ... connected.
# Checking databases information_schema on server1 and information_schema on server2
#
#                                                   Defn    Row     Data  
# Type      Object Name                             Diff    Count   Check 
# -------------------------------------------------------------------------
# TABLE     CHARACTER_SETS                          pass    SKIP    SKIP   
# TABLE     CLIENT_STATISTICS                       pass    SKIP    SKIP   
...
# TABLE     PLUGINS                                 pass    SKIP    SKIP   
# TABLE     PROCESSLIST                             FAIL    SKIP    SKIP   
#
# Transformation for --changes-for=server1:
#

ALTER TABLE `information_schema`.`PROCESSLIST`
  CHANGE COLUMN USER USER varchar(128) NOT NULL,
MAX_ROWS=31775;

# TABLE     PROFILING                               pass    SKIP    SKIP   
...
# TABLE     VIEWS                                   pass    SKIP    SKIP   
# TABLE     XTRADB_ADMIN_COMMAND                    pass    SKIP    SKIP  

# Database consistency check failed.
#
# ...done

mysqldiff

mysqldiff example
options: --difftype=sql information_schema:information_schema --force
# server1 on localhost: ... connected.
# server2 on db1.mktdb.services.phx1.mozilla.com: ... connected.
# Comparing `information_schema` to `information_schema`           [PASS]
# Comparing `information_schema`.`CHARACTER_SETS` to `information_schema`.`CHARACTER_SETS`   [PASS]
# Comparing `information_schema`.`CLIENT_STATISTICS` to `information_schema`.`CLIENT_STATISTICS`   [PASS]
# Comparing `information_schema`.`PLUGINS` to `information_schema`.`PLUGINS`   [PASS]
# Comparing `information_schema`.`PROCESSLIST` to `information_schema`.`PROCESSLIST`   [FAIL]
# Transformation for --changes-for=server1:
#
ALTER TABLE `information_schema`.`PROCESSLIST`
  CHANGE COLUMN USER USER varchar(128) NOT NULL,
MAX_ROWS=31775;
# Comparing `information_schema`.`PROFILING` to `information_schema`.`PROFILING`   [PASS]
# Comparing `information_schema`.`REFERENTIAL_CONSTRAINTS` to `information_schema`.`REFERENTIAL_CONSTRAINTS`   [PASS]
# Comparing `information_schema`.`USER_PRIVILEGES` to `information_schema`.`USER_PRIVILEGES`   [PASS]
# Comparing `information_schema`.`USER_STATISTICS` to `information_schema`.`USER_STATISTICS`   [FAIL]
# Transformation for --changes-for=server1:
#
ALTER TABLE `information_schema`.`USER_STATISTICS`
  CHANGE COLUMN USER USER varchar(128) NOT NULL,
MAX_ROWS=30448;
# Comparing `information_schema`.`VIEWS` to `information_schema`.`VIEWS`   [PASS]
# Comparing `information_schema`.`XTRADB_ADMIN_COMMAND` to `information_schema`.`XTRADB_ADMIN_COMMAND`   [PASS]
Compare failed. One or more differences found.

mysqldiskusage

[scabral@bugzilla1 ~]$  mysqldiskusage --server=root:password@localhost:/var/lib/mysql/mysql.sock
# Source on localhost: ... connected.
# Database totals:
+---------------------+------------------+
| db_name             |           total  |
+---------------------+------------------+
| bugs                | 148,361,416,727  |
| mysql               | 7,396,699        |
| percona             | 238,778,705      |
| performance_schema  | 154,041          |
+---------------------+------------------+

Total database disk usage = 148,607,746,172 bytes or 138.40 GB

--quiet example:

[scabral@bugzilla1 ~]$  mysqldiskusage --server=root:password@localhost:/var/lib/mysql/mysql.sock --quiet
# Source on localhost: ... connected.
+---------------------+------------------+
| db_name             |           total  |
+---------------------+------------------+
| bugs                | 148,363,540,227  |
| mysql               | 7,396,699        |
| percona             | 238,778,705      |
| performance_schema  | 154,041          |
+---------------------+------------------+

--all example:

[root@bugzilla1.db.phx1 ~]# mysqldiskusage --server=root:password@localhost:/var/lib/mysql/mysql.sock  --all
# Source on localhost: ... connected.
# Database totals:
+---------------------+------------------+
| db_name             |           total  |
+---------------------+------------------+
| bugs                | 148,362,478,335  |
| mysql               | 7,396,699        |
| percona             | 238,778,705      |
| performance_schema  | 154,041          |
+---------------------+------------------+
Total database disk usage = 148,608,807,780 bytes or 138.40 GB
# Log information.
# The general_log is turned off on the server.
+-----------------+--------------+
| log_name        |        size  |
+-----------------+--------------+
| mysql-slow.log  |     108,567  |
| mysql.err       | 748,861,887  |
+-----------------+--------------+
Total size of logs = 748,970,454 bytes or 714.27 MB
# Binary log information:
Current binary log file = bugzilla1-bin.000068
+-----------------------+-------------+
| log_file              | size        |
+-----------------------+-------------+
| bugzilla1-bin.000045  | 1073741940  |
| bugzilla1-bin.000046  | 1073747089  |
...
| bugzilla1-bin.000067  | 1073742486  |
| bugzilla1-bin.000068  | 675211533   |
| bugzilla1-bin.index   | 864         |
+-----------------------+-------------+
Total size of binary logs = 25,377,525,189 bytes or 23.63 GB
# Relay log information:
Current relay log file = bugzilla1-relay-bin.000205
+-----------------------------+------------+
| log_file                    | size       |
+-----------------------------+------------+
| bugzilla1-relay-bin.000204  | 345        |
| bugzilla1-relay-bin.000205  | 347145270  |
| bugzilla1-relay-bin.index   | 84         |
+-----------------------------+------------+
Total size of relay logs = 347,145,699 bytes or 331.06 MB
# InnoDB tablespace information:
+--------------+----------------+
| innodb_file  |          size  |
+--------------+----------------+
| ib_logfile0  |   314,572,800  |
| ib_logfile1  |   314,572,800  |
| ibdata1      | 4,582,277,120  |
+--------------+----------------+
Total size of InnoDB files = 5,211,422,720 bytes or 4.85 GB
#...done.

Example showing only the bugs and mysql databases, in quiet output with the general, slow query and error logs:

[scabral@bugzilla1.db.phx1 ~]$ mysqldiskusage --server=root:password@localhost:/var/lib/mysql/mysql.sock -q bugs mysql --log
# Source on localhost: ... connected.
+----------+------------------+
| db_name  |           total  |
+----------+------------------+
| bugs     | 148,363,553,355  |
| mysql    | 7,396,699        |
+----------+------------------+
# The general_log is turned off on the server.
+-----------------+--------------+
| log_name        |        size  |
+-----------------+--------------+
| mysql-slow.log  |     108,567  |
| mysql.err       | 748,884,627  |
+-----------------+--------------+

Ear Candy
We talk about time zones and mysqldump. Episode 46 when we talked about times and time zones.

At the Movies
This week in At the Movies, we present Mark Riddoch of SkySQL talking about Deploying MySQL in AWS and OpenStack. Mark talks about the deployment issues and options and the path SkySQL chose. This video is from the SkySQL and MariaDB Solutions Day 2013 that happened earlier this year.

Where you can see us
Sheeri will be at the September Boston MySQL User Group meeting on Monday September 9th.

We will both be speaking at MySQL Connect in San Francisco from Saturday September 21st - Monday September 23rd.

Sheeri will be at the Nagios World 2013 Conference in St. Paul, Minnesota on Monday, September 30th through Thursday, October 3rd.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1256