MySQL

MySQL

Тюнинг

Ничего сам не правит, просто отображает рекомендуемые настройки кеширования и прочих радостей. Неплохо развивается, must have
Перловый тюнер

Создание БД

Лучше сразу указывать кодировку:

#!SQL
CREATE DATABASE `my_db` CHARACTER SET utf8 COLLATE utf8_general_ci;

Пользователи

Создание пользователя со всеми правами

#!SQL
GRANT ALL PRIVILEGES ON *.* TO root@'%' identified by "PASSWORD";

Создание пользователя с правами на таблицы определённого префикса

#!SQL

GRANT ALL PRIVILEGES ON `vr\_%`.* TO 'vr'@'localhost';

Позволяет пользователю vr делать что угодно с бд, которые начинаются с vr_

Восстановление пароля root (или любого пользователя БД)

Останавливаем mysql:

#!bash

sudo /etc/mysqld stop

Запускаем обратно, но со спецфлагом (без привилегий):

#!bash

sudo /etc/mysqld --skip-gran-tables&

Логинимся в СУБД от имени root:

#!bash

 mysql -u root

Обновляем поле с паролем для пользователя root(либо любого другого) в системной таблице user. Новый пароль вписываем вместо newpass:

#!sql

UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root';

Обновляем привилегии:

#!sql

FLUSH PRIVILEGES;

Выходим из mysql: exit; Рестарт mysql:

#!bash

/etc/mysqld restart

Работа с запросами

поглядеть все текущие запросы SHOW FULL PROCESSLIST;
убить конкретный запрос по ид KILL $queryID;

всяческие выборки по процесслисту возможны из системных таблиц:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time >20 and COMMAND !='Sleep' order by time\G

General Log

Текущие настройки:

show variables
  where Variable_name in ('version', 'log', 'general_log', 'general_log_file', 'log_output');

Включение:

SET GLOBAL general_log_file='/var/log/mysql/general.log';
SET GLOBAL general_log=ON;

Включать в general log в основном конфиге - плохая идея. Желательно его включать только на время дебага и как можно быстрее отключать (SET GLOBAL general_log=OFF;).

Slow Log

Настройки:

mysql> show variables where Variable_name like ('slow%');

Включение/выключение

SET GLOBAL slow_query_log = OFF;
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 10;

Самые большие таблицы

SELECT table_schema,TABLE_NAME, SUM( data_length + index_length) / 1024 / 1024  "Data Base Size in MB" 
FROM information_schema.TABLES 
GROUP BY table_name,table_schema 
ORDER BY 3;

SELECT table_schema, SUM( data_length + index_length) / 1024 / 1024  "Data Base Size in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema 
ORDER BY 2;

Репликация

Настройки сервера для запуска репликации

server-id = 1
log-bin = /var/lib/mysql/mysql-bin 
replicate-do-db = testdb # базы подлежащие репикации
max_binlog_size = 100M   # размер одного файла бинлогов
binlog_ignore_db = test  # не реплицировать указанные базы
binlog_ignore_db    = mysql
binlog_ignore_db    = performance_schema
binlog_ignore_db    = information_schema
expire_logs_days    = 3  # затирать бинлоги старее 3 дней

Создание ползователя для репликации

grant replication slave, replication client on *.* to replication@"%" identified by "password";

статус слейва:

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Какойнибудь хороший/плохой статус
                  Master_Host: 192.168.0.1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002946
          Read_Master_Log_Pos: 6231609 
               Relay_Log_File: mysqld-relay-bin.001922
                Relay_Log_Pos: 4312353
        Relay_Master_Log_File: mysql-bin.002946
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: exclude.%tmp
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 6231609
              Relay_Log_Space: 6231953
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 3012
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

Особое внимание строкам:
Seconds_Behind_Master: 3012 - на сколько секунд отстал от мастера
Slave_IO_State: - статус, должен быть Waiting for master to send event
Last_Error: - если чтото не так - тут будет ошибка
Last_SQL_Error: - ну или тут, но обычно и там и там одно и тоже.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

должно быть Yes на обоих.

Если чтото не так - в первую очередь пытаемся перезагрузить слейв командами:

STOP SLAVE;
START SLAVE;

если после этого не завелось, стоит проверить логи - скорее всего там какаянить ошибка невозможности применить запрос с мастера. Одно из решений в этом случае - пропустить эту команду. для этого стопаем слейв (в самом деле не обязательно, он и так остановлен раз ошибка), инкримируем и стратуем слейв.

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Еще полезные команды по репликации

Показывает статус мастера (если не запущен ничего не выдаст):

SHOW MASTER STATUS;

Показать список реплик у мастера:

SHOW SLAVE HOSTS;

Список файлов бинарных логов:

SHOW BINARY LOGS;

Подчищаем логи до определенного уровня (до конкретного лога или по дате):

PURGE BINARY LOGS TO 'mysql-bin.000030';
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 30 DAY;

Настройка слейва (перед настройкой необходимо остановить слейв slave stop)

CHANGE MASTER TO 
   MASTER_HOST = '192.168.0.1',
   MASTER_USER = 'replication',
   MASTER_PASSWORD = 'xxxxxxxx',
   MASTER_CONNECT_RETRY = 30,
   MASTER_LOG_FILE = 'mysql-bin.000001',
   MASTER_LOG_POS = 385,
   MASTER_HEARTBEAT_PERIOD = 10
  ;

Для пропуска ошибки Duplicate entry (1062) в конфиг сервера можно добавить директиву и перезагрузить сервис (не рекомендуется, но как временное решение может быть полезно)

[mysqld]
slave-skip-errors=1062

Прочее:

RESET SLAVE;
FLUSH LOGS;
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
  CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 10;

Репликация в MariaDB

Мария в 10 версии умеет мультимастерную репликацию. Есть некоторые ограничения (например 64 мастера максимум), но в целом довольно мощно.
Для переключения между инстансами репликации используем команды

set @@default_master_connection='master1';
CHANGE MASTER TO MASTER_HOST = '1.1.1.1', MASTER_USER = 'replication', MASTER_PASSWORD = 'replication', MASTER_CONNECT_RETRY = 30, MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 120;
set @@default_master_connection='master2';
CHANGE MASTER TO MASTER_HOST = '2.2.2.2', MASTER_USER = 'replication', MASTER_PASSWORD = 'replication', MASTER_CONNECT_RETRY = 30, MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 120;

Естественно, имена могу быть любыми

Для вывода статуса всех слейвов используем команду:

show all slaves status\G

Еще полезности по репликации в марии

RESET SLAVE ['connection_name'] - сбросить все параметры подключения к мастеру
SHOW SLAVE ['connection_name'] STATUS - показать статус конкретного подключения
SHOW ALL SLAVES STATUS - показать статус всех подклчений
START SLAVE ['connection_name'] - запустить указанное подключение
START ALL SLAVES - запустить все подключения
STOP SLAVE ['connection_name'] - остановить указанное подключение
STOP ALL SLAVES - остановить все подключения
Здесь можно почитать подробнее

Бекап

Создание

mysqldump --all-database -pPASSWORD | gzip > dump.sql.gz

Бекапаются все базы и таблицы, включая системные. Лучше не восстанавливать целиком

Восстановление

zcat dump.sql.gz | mysql -pPASSWORD

Нарезка

Для разрезания файла бекапа на базы используем скрипт

#!/usr/bin/perl -w
#
# splitmysqldump - split mysqldump file into per-database dump files.
use strict;
use warnings;
my $dbfile;
my $dbname = q{};
my $header = q{};
while (<>) {

# Beginning of a new database section:
    # close currently open file and start a new one
    if (m/-- Current Database\: \`([\.\-\w]+)\`/) {
    if (defined $dbfile && tell $dbfile != -1) {
        close $dbfile or die "Could not close file!"
    }
    $dbname = $1;
    open $dbfile, ">>", "$1_dump.sql" or die "Could not create file!";
    print $dbfile $header;
    print "Writing file $1_dump.sql ...\n";
    }
    if (defined

$dbfile && tell $dbfile != -1) {
    print $dbfile $_;
    }
    # Catch dump file header in the beginning
    # to be printed to each separate dump file.
    if (!

$dbname) { $header .= $_; }
}
close $dbfile or die "Could not close file!"

Для нарезания базы на таблицы используем скрипт

#!/bin/sh

#SPLIT DUMP FILE INTO INDIVIDUAL TABLE DUMPS

TARGET_DIR="."
DUMP_FILE=$1
TABLE_COUNT=0

if [ $# = 0 ]; then
        echo "Usage: MyDumpSplitter.sh DUMP-FILE-NAME -- Extract all tables as a separate file from dump."
        echo "       MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME -- Extract single table from dump."
        echo "       MyDumpSplitter.sh DUMP-FILE-NAME -S TABLE-NAME-REGEXP -- Extract tables from dump for specified regular expression."
        exit;
elif [ $# = 1 ]; then
        #Loop for each tablename found in provided dumpfile
        for tablename in $(grep "Table structure for table " $1 | awk -F"\`" {'print $2'})
        do
                #Extract table specific dump to tablename.sql
                sed -n "/^-- Table structure for table \`$tablename\`/,/^-- Table structure for table/p" $1 > $TARGET_DIR/$tablename.sql
                TABLE_COUNT=$((TABLE_COUNT+1))
        done;
elif [ $# = 2  ]; then
        for tablename in $(grep -E "Table structure for table \`$2\`" $1| awk -F"\`" {'print $2'})
        do
                echo "Extracting $tablename..."
                #Extract table specific dump to tablename.sql
                sed -n "/^-- Table structure for table \`$tablename\`/,/^-- Table structure for table/p" $1 > $TARGET_DIR/$tablename.sql
                TABLE_COUNT=$((TABLE_COUNT+1))
        done;
elif [ $# = 3  ]; then

        if [ $2 = "-S" ]; then
                for tablename in $(grep -E "Table structure for table \`$3" $1| awk -F"\`" {'print $2'})
                do
                        echo "Extracting $tablename..."
                        #Extract table specific dump to tablename.sql
                        sed -n "/^-- Table structure for table \`$tablename\`/,/^-- Table structure for table/p" $1 > $TARGET_DIR/$tablename.sql
                        TABLE_COUNT=$((TABLE_COUNT+1))
                done;
        else
                echo "Please provide proper parameters.";
        fi
fi

#Summary
echo "$TABLE_COUNT Table extracted from $DUMP_FILE at $TARGET_DIR"

Wiki

configs / db / mysql