mysql commands

dumps.sh

# dump as per normal
# column statistics should be off by default
mysqldump -p -u <user> -h <host> --all-databases --comments --dump-date --column-statistics=0 > dump.sql

# replace rdsadmin user with your master user
sed -i -e 's/DEFINER=`rdsadmin`@`localhost`/DEFINER=`<masteruser>`@`%`/g' dump.sql

# one liner if you need to pipe to a remote host without a dump file as output
# this method wont work if password input is required
mysqldump -p -u <user> -h <host>  \
  | sed -i -e 's/DEFINER=`rdsadmin`@`localhost`/DEFINER=`<masteruser>`@`%`/g' \
  | mysql -p -u <user> -h <host-2>
  
# mysqlpump has more features
# dump users and their privileges
mysqlpump --set-gtid-purged=OFF --exclude-databases=% --users --include-users=x,y,z
# dump triggers
mysqlpump --set-gtid-purged=OFF --skip-dump-rows --include-triggers=% --exclude-routines=% <DB>
# skip data, dump only schema
mysqlpump --skip-dump-rows <database>

mysql

# set up login config profiles
# when prompted for password, put quotes around password to escape special characters
mysql_config_editor set \
  --login-path=<profile name> \
  --host=db-host \
  --user=user \
  --password
  
mysql --login-path=<profile name>

# see processlist
# can wrap it in a while loop to 'watch' it
SELECT info FROM information_schema.processlist 
WHERE DB = 'foo'
HAVING info IS NOT NULL;

# see recent deadlock
SHOW engine innodb status\G;

# lock waits
SHOW engine innodb mutex\G;

# list table sizes
SELECT
  TABLE_SCHEMA AS `Database`,
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;