mysql.txt

--- MYSQL ----------------------------------------------------------------------

mysqld_safe - is the recommended way to start a mysqld server on Unix.
  mysqld_safe adds some safety features such as restarting the server when an
  error occurs and logging runtime information to an error log.

  mysqld_safe reads all options from the [mysqld], [server], and [mysqld_safe]
  sections in option files.

--------------------------------------------------------------------------------
ENABLE LOGS:

// To enable error log add following

[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log
[mysqld]
log_error=/var/log/mysql/mysql_error.log

// To enable general query log add following

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

// To enable Slow Query Log add following

log_slow_queries       = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes


BACKUPS:

innobackupex  --stream=tar ./| lz4 | ssh upload@backup1  \
  "cat - > /ssd0/upload/sql/mysql_backup.tar.lz4"


EXAMPLES:







// Get size of MySQL databases:

mysql> SELECT table_schema "mydb",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;

// Get the size of the specific table in MySQL:

mysql> SELECT
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = ‘db_name’
    AND table_name = ‘table_name’;

// Get the size of all tables, descending order:

mysql> SELECT
     table_schema as `Database`,
     table_name AS `Table`,
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;


// Drop multiple databases;

#!/bin/bash

DB_TO_DROP=`mysql -s -e "show databases;" |grep "somedb_"`
for db in $DB_TO_DROP; do
    echo "DROPPING: $db"
    #mysql -s -e "DROP DATABASE $db;"
done