OurSQL Episode 145: Biblical Tools, part 3

This week we finish up talking about the Openark Kit for MySQL. Ear Candy is using both --master-data and --tab with mysqldump, and At the Movies features Robert Hodges of Continuent presenting Scalable MySQL Operation in the Cloud with Continuent Tungsten.

Openark Kit series:
Part 1
Part 2

Openark Kit
oak-purge-master-logs
PURGE BINARY LOGS at the MySQL manual page.
SHOW SLAVE HOSTS command.

oak-repeat-query

oak-security-audit

Sample output of running oak-security-audit:

[root@bugzilla1 scripts]# ./oak-security-audit  -S /var/lib/mysql/mysql.sock --defaults-file=~/.my.cnf
-- Auditing in strict level
-- The following users are assumed as root: root
--
-- Looking for non local 'root' accounts
-- -------------------------------------
-- Found 1 non local 'root' accounts. Recommended actions:
RENAME USER 'root'@'192.168.70.54' TO 'root'@'localhost';
--
-- Looking for anonymous user accounts
-- -----------------------------------
-- Passed
--
-- Looking for accounts accessible from any host
-- ---------------------------------------------
-- Passed
--
-- Looking for accounts with empty passwords
-- -----------------------------------------
-- Passed
--
-- Looking for accounts with identical (non empty) passwords
-- ---------------------------------------------------------
-- Passed
--
-- Looking for (non root) accounts with all privileges
-- ---------------------------------------------------
-- Passed
--
-- Looking for (non-root) accounts with admin privileges
-- -----------------------------------------------------
-- There are 10 non-root accounts with admin privileges
-- admin privileges are: SUPER, SHUTDOWN, RELOAD, PROCESS, CREATE USER, REPLICATION CLIENT, REPLICATION SLAVE. Recommended actions:
GRANT <non-admin-privileges> ON *.* TO 'checksum'@'192.168.70.%';
GRANT <non-admin-privileges> ON *.* TO 'checksum'@'localhost';
GRANT <non-admin-privileges> ON *.* TO 'collectd'@'localhost';
GRANT <non-admin-privileges> ON *.* TO 'munin'@'127.0.0.1';
GRANT <non-admin-privileges> ON *.* TO 'munin'@'localhost';
GRANT <non-admin-privileges> ON *.* TO 'nagiosdaemon'@'192.168.70.20_';
GRANT <non-admin-privileges> ON *.* TO 'nagiosdaemon'@'192.168.74.123';
GRANT <non-admin-privileges> ON *.* TO 'nagiosdaemon'@'192.168.75.27';
GRANT <non-admin-privileges> ON *.* TO 'repl'@'192.168.70.20_';
GRANT <non-admin-privileges> ON *.* TO 'repl'@'192.168.70.80';
--
-- Looking for (non-root) accounts with write privileges on the mysql schema
-- -------------------------------------------------------------------------
-- Passed
--
-- Looking for (non-root) accounts with data definition privileges
-- ---------------------------------------------------------------
-- There are 2 non-root accounts with global data definition privileges.
-- These accounts can drop or alter tables in all schemata, including the mysql database itself
-- data definition privileges are: CREATE, DROP, EVENT, ALTER, INDEX, TRIGGER, CREATE VIEW, ALTER ROUTINE, CREATE ROUTINE. Recommended actions:
GRANT <non-data-definition-privileges> ON *.* TO 'checksum'@'192.168.70.%';
GRANT <non-data-definition-privileges> ON *.* TO 'checksum'@'192.168.74.%';
-- It is further recommended to only grant privileges on specific databases
--
-- Looking for (non-root) accounts with schema data definition privileges
-- ----------------------------------------------------------------------
-- There are 4 non-root accounts with schema data definition privileges
-- These accounts can drop or alter tables in those schemas, or drop the schema itself.
-- data definition privileges are: CREATE, DROP, EVENT, ALTER, INDEX, TRIGGER, CREATE VIEW, ALTER ROUTINE, CREATE ROUTINE. Recommended actions:
GRANT <non-data-definition-privileges> ON "bugs".* TO 'bugs'@'192.168.70.20_';
GRANT <non-data-definition-privileges> ON "bugs".* TO 'bugs'@'192.168.70.21_';
GRANT <non-data-definition-privileges> ON "percona".* TO 'checksum'@'192.168.70.%';
GRANT <non-data-definition-privileges> ON "percona".* TO 'checksum'@'localhost';
--
-- Looking for (non-root) accounts with global data manipulation privileges
-- ------------------------------------------------------------------------
-- Passed
--
-- Checking global sql_mode
-- ------------------------
-- sql_mode does not contain NO_AUTO_CREATE_USER. Recommended actions:
SET GLOBAL sql_mode = 'NO_AUTO_CREATE_USER';
--
-- Checking old_passwords setting
-- ------------------------------
-- Passed
--
-- Checking networking
-- -------------------
-- Networking is enabled. Recommended actions:
-- This is usually fine. If you're only accessing MySQL from the localhost,
-- consider setting --skip-networking and using UNIX socket or named pipes.
--
-- Checking for `test` database existance
-- --------------------------------------
-- Passed

oak-show-limits

Sample of the first 10 lines of the output of oak-show-limits, showing the Mozilla Bugzilla bugs database, showing the auto_increment statistics for the first 10 tables with auto_increment fields:

$ ./oak-show-limits  -S /var/lib/mysql/mysql.sock --defaults-file=~/.my.cnf | head
bugs.attachments.attach_id:     764110/8388607  0.091089
bugs.bug_see_also.id:   6762/8388607    0.000806
bugs.bug_severity.id:   7/32767 0.000214
bugs.bug_status.id:     7/32767 0.000214
bugs.bugs.bug_id:       884282/8388607  0.105415
bugs.bugs_activity.id:  300668082/2147483647    0.140009
bugs.bz_schema.id:      1/2147483647    0.000000
bugs.cf_blocking_191.id:        23/32767        0.000702
bugs.cf_blocking_192.id:        32/32767        0.000977
bugs.cf_blocking_20.id: 23/32767        0.000702

In this example, attach_id is at 9.1% of the max auto_increment value, bug_id is at 10.5% of the max_autoincrement value, and the id field of the bugs_activity table is at 14.0% of the max_autoincrement value.

Here is an example of using the --threshold option:

[root@bugzilla1 scripts]#  ./oak-show-limits  -S /var/lib/mysql/mysql.sock --defaults-file=~/.my.cnf --threshold .1
bugs.bugs.bug_id:       884282/8388607  0.105415
bugs.bugs_activity.id:  300668116/2147483647    0.140010

oak-show-replication-status

original blog post by Shlomi about Online ALTER TABLE.

Ear Candy
We talk about what happens to the CHANGE MASTER TO statement as the output of mysqldump when using the --tab option.
Example:
mysqldump --master-data=1 --single-transaction --tab=/path/to/csv/ <other options> > master_pos.sql
mysqldump options

At the Movies
This week in At the Movies, we present Robert Hodges of Continuent presentingScalable MySQL Operation in the Cloud with Continuent Tungsten at the SkySQL and MariaDB Solutions Day 2013.

Where you can see us
Gerry will be at the July Seattle MySQL User Group meeting on Monday July 1st.

Sheeri will be at the July Boston MySQL User Group meeting on Monday July 8th. David Berube of Berube Consulting will talk about "Increasing MySQL Performance through Aggressive Data Archiving Database". This talk comes straight from its debut at Percona Live back in April, except of course it's free to anyone in the Boston area.

Sheeri will speak about Performance Schema on Monday July 29th, in Tokyo.

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/1250