User Tools

Site Tools


sysadmin_misc:mysql

MySQL

  • List views : SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
  • show engine for all tables : SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'dbname';
  • count occurrences for each value: select userid, count(userid) as cuid from sessions group by userid order by cuid desc limit 5;
  • List users : SELECT User,Host FROM mysql.user;
  • list runtime information :
show global status;
show variables;
SHOW PROCESSLIST \G;
  • mysqldumpslow, un tool pour dumper les slow queries en pretty print
  • Get replication information : show slave status\G
  • a small script to skip replication errors upon initialising it :
#!/usr/bin/env bash
set -eu

while true; do
    status=$(mysql --execute="show slave status\G"|grep "Seconds_Behind_Master:"|awk '{print $2}')
    if [ $status == "NULL" ]; then
        mysql --execute="show slave status\G" | grep "Last_SQL_Error:"
        mysql --execute="set global sql_slave_skip_counter=1; start slave;"
    fi
    sleep 0.01
done
  • Préparer le lancement d'une réplication : FLUSH TABLES ; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; system lvcreate -s -L 5G -n snap-20110925 /dev/system/var_lib_mysql; UNLOCK TABLES;
  • Minimum privileges for bareos :
GRANT USAGE ON *.* TO 'bareos'@'localhost' IDENTIFIED BY 'bareos';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'bareos'@'localhost'
sysadmin_misc/mysql.txt · Last modified: 2020/01/27 15:26 by adyxax