MySQL for System Administrators

LCA sysadmin miniconf
2021Jan23 @ 12:15
Melbourne

der.hans
CDE
Object Rocket, a rackspace company
https://www.ObjectRocket.com/

Yes, we’re hiring :)

ObjectRocket
https://www.objectrocket.com/careers/

Rackspace Technologies
https://rackspace.jobs/

Upcoming Presentations

List of Some Upcoming and Previous Talks and Publications
- https://www.LuftHans.com/talks

Social Media and Fediverse

FLOX_advocate on Mastodon
- https://floss.social/@FLOX_advocate
LuftHans on PLUME
- https://fediverse.blog/~/LuftHans
LuftHans on Freenode IRC
- usually in #LOPSA, #PLUGaz and #LibreLounge

Default MySQL prompt

Oh the monotony!

Percolating Selzer

MYSQL_PS1

MYSQL_PS1 example

export MYSQL_PS1="(\u@\h:\d)> "

$ grep MYSQL_PS1 ~/.bashrc
export MYSQL_PS1="(\u@${HOSTNAME%.*.*}:\d)> "
$

MySQL shells, viva la difference

Cluster to three

that’s clusterssh hitting 3 boxen in parallel
still needs 2 passwords :(

OpenSSH: the Home Game

Non-automagic authentication

$ mysql -u root -p password

ps auxw
Shell history
~/.mysql_history
Copy and paste of command and output

Interactive authentication

$ mysql -u root -p

Automagic authentication

~/.my.cnf
- check /root/.my.cnf
/etc/mysql/debian.cnf
MYSQL_PWD
- use from a config file

Authorization / Privileges

Grant / Revoke
Read-only: SELECT
Read-write: SELECT, DELETE, INSERT, UPDATE

Stored Procedures

SQL SECURITY

DEFINER

INVOKER

mysqldump

Use for schema dumps

Include mysql.proc to preserve routine creation timestamps

--no-data –routines –skip-add-locks –skip-opt --triggers –all-databases

Percona XtraBackup

Cool tool, still uses a lot of resources

Snapshots

Put MySQL data on snapshotable filesystem

Snapshot still requires short read-lock

mylvmbackup works with LVMv2

Log files

/var/log/mysql/

Error log
Slow query log
General log
- Careful on busy DBen

Querying Data

Use the tools we know

Build your own pipeline

e8d1_dr_who_build_your_own_sonic_screwdriver.400x501.jpg

Use your favorite CLI tools

sed | grep | awk | cut | sort | uniq

Sysadminny examples

find horsey music

$ mysql -e "select album from music" | grep -i horse

show root authorized from other than localhost

$ mysql -B -e "select user, host from mysql.user" | awk '$1 == "root" && $2 !~ /localhost|127.0.0.1|::1/'

as of 8.0.22, was something else before

$ mysql -e "show replica status\G" | grep -E 'Master_Host|Slave_.*_(State|Running)|Seconds_Behind_Master|Last_.*_Err'

libreadline

mysql> pager less

DB stuff

Use InnoDB
- Don’t change internal databases
  - mysql.*
  - information_schema.*
  - performance_schema.*
Use file_per_table
For faster shutdowns
- set global innodb_max_dirty_pages_pct = 0;

Character sets

Always specify character set when creating a DB or table

Or get used to Swedish

Community

MySQL, Percona and MariaDB, Oh my!
Oracle stewardship
Competition
Cooperative competitive

Resources

Site: MySQL documentation
Site: Percona’s MySQL Performance blog
Site: TokuDB blog
Book: High Performance MySQL
Tool: Percona Toolkit
Conf: Percona Live
Conf: SCaLE’s MySQL track

Thank You

Credits

Thanks to the LCA organizers and other volunteers for running a conference for us to enjoy

Thanks to the FLOSS database community in its many forms

Thanks to Brian for the Open SSH Home game image

Thanks to OpenBSD and OpenSSH for that critical tool

Mahlzeit!