Настройка mysql,percona и информация по командам

Опубликовано в Автор: human

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

CREATE DATABASE `db.example.com` CHARACTER SET utf8 COLLATE utf8_general_ci;

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='username';

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON `db.example.com` . * TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON `db.example.com` . * TO 'username'@'%';

Обновим права

flush privileges;

Set max_allowed_packet to ~32 mb

SET GLOBAL max_allowed_packet=32777216;

Как слить данных для тестировщиков – например 100 записей

mysqldump -u root -pPassword -h 192.168.0.1 --where="true limit 100" databasename > danamasename.sql

tar -xzOf your_db_dump.sql.tar.gz | mysql -u USERNAME -pPASSWORD your_database

Дать все права root

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'PASSWORD';

GRANT ALL PRIVILEGES ON . TO root@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

Как импортировать все таблицы sql в базу

for a in `ls -1 *.sql`; do mysql -u root -pПароль mysql  'имя_базы_данных' < $a; done

Выполнить Mysql запрос из bash

#!/bin/bash
mysql -u root -pSeCrEt << EOF
use mysql;
show tables;
EOF

Как сменить пароль mysql

Свой пароль можно поменять через:

SET PASSWORD = PASSWORD('пароль')

Пароль определенного пользователя можно поменять через:

SET PASSWORD FOR логин@localhost = PASSWORD('пароль');
     SET PASSWORD FOR логин@"%" = PASSWORD('пароль');

тоже самое делают:

 UPDATE mysql.user SET Password=PASSWORD('пароль') WHERE User='логин' AND Host='localhost';
  FLUSH PRIVILEGES;

или

GRANT USAGE ON БД.* TO логин@localhost IDENTIFIED BY 'пароль';

или mysqladmin -u логин password пароль

Инструкция по сбросу пароль root  Mysql.

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

 /etc/init.d/mysql stop

Запускаем в режиме без проверки прав доступа:
/usr/sbin/mysqld –skip-grant-tables &

Соединяемся как root без пароля:

mysql

Выбираем активную БД:

use mysql;

Обновляем пароль:
update user set Password=PASSWORD(‘новый пароль’) WHERE User=’root’;

flush privileges;

Завершаем работу mysqld:

"Ctrl+c"

Запускаем MySQL

 /etc/init.d/mysql start

Бэкап данных с помощью p

ercona xtrabackup

innobackupex  --defaults-file=/etc/mysql/my.cnf --user=root  --password=password  --no-timestamp  --databases "bi.etagi.com" --rsync /root/backupdb/

innobackupex --apply-log --redo-only --defaults-file=/etc/mysql/my.cnf --password=password --no-timestamp  --throttle=40 --rsync /root/backupdb/

 

Архивация данных и восстановление одной таблицы из бд

Ниже представленный скрипт позволяет из архива полного дампа вытащить одну таблицу.

#!/bin/sh
 
DB=$1
TABLE=$2
PATH=/some/path/backup
 
if [ -f $PATH/$DB.sql.gz ]; then
    /bin/gunzip -c $PATH/$DB.sql.gz | /usr/bin/awk '/CREATE TABLE `'$TABLE'`/,/UNLOCK TABLES/' > /tmp/$DB.$TABLE.sql
    else
    echo 'FILE not found'
fi

Небольшое пояснение по скрипту. В качестве параметров он принимает имя базы данных и имя таблицы, которую необходимо восстановить.

Имя архива состоит из имени базы с расширением sql.gz

Пример выполнения скрипта

extract_table.sh database_name table_name

Из архива базы database_name извлечется таблица table_name

После этого, если необходимо, можно восстановить данную таблицу в базу.

 mysql -uusername -ppassword database_name < /tmp/database_name.table_name.sql

Убить большое количество запросов в базу

Как убить большое количество запрсов в базу данных? Всё просто. Например запросы, которые длятся больше 10 секунды и с пользователем user_remote

Смотрим количество процессов(необязательно);

#!/bin/bash
for i in `/usr/bin/mysql -e "show full processlist" |grep "$1" |grep "$2"|grep "$3"| awk '{print $1}'`;
do
KILL_STR="kill $i";
    /usr/bin/mysql -e "$KILL_STR";
    echo $KILL_STR;
done;
echo "DONE";

Выполняем запрос из файла.

. /tmp/kill_list.txt

Mysql slow log включить

nano /etc/mysql/my.cnf

Добавим строку

log-slow-slave-statements = 1

stop slave;
set global log_warnings=1;
SHOW VARIABLES LIKE '%log%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 10;
SET GLOBAL LOG_QUERIES_NOT_USING_INDEXES = ON;
SET GLOBAL slow_query_log_file='/var/mysql_m/log/slowlog.log';
start slave;

Проверить значения переменных можно следующим образом:

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
mysql> SHOW SESSION VARIABLES LIKE 'long_query_time';

Mysql очистка bilog

Просто установим количество дней для хранения бинлогов и очистим их.

SET GLOBAL expire_logs_days = 10;
flush logs;

Или удалим binlog до определенной даты

PURGE BINARY LOGS BEFORE '2017-07-20 07:00:00';

Включаем slave compressed protocol

show global variables like 'slave_compressed_protocol';
stop slave;
set global slave_compressed_protocol=1;
start slave;
show global variables like 'slave_compressed_protocol';

Бэкап данных со slave с жатием в gz

mysqldump -u root -pPassword -R –master-data=2 –max-allowed-packet=1024M -B dbname | gzip > dbname.sql.gz

Пропуск ошибок репликации

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

Mysql тюнинг репликации

Устанавливаем параметры количества потоков репликации. Ставим в зависимости от реплицируемых баз данных – по 1 на базу, если у вас 10 баз – ставьте 10.

stop slave;
 set global master_info_repository='table';
 set global relay_log_info_repository='table';
 set global slave_parallel_workers=5;
 start slave;

Смотрим

select * from mysql.slave_worker_info\G

Избавляемся от лагов mysql

1) sync_relay_log = 0
2) sync_relay_log_info = 0
3) sync_master_info = 0
Смотрим значения

SHOW VARIABLES LIKE 'sync_relay_log';
SHOW VARIABLES LIKE 'sync_relay_log_info';
SHOW VARIABLES LIKE 'sync_master_info';

Все делаем через

 set global sync_relay_log=0;

 set global sync_relay_log_info=0;

 set global sync_master_info=0;

Переключение slave->master в случае отказа master

В случае отказа основного (master) сервера СУБД, необходимо вручную или автоматически скриптом переключить кластер на другой master-сервер СУБД. Для этого обычно slave-сервер, хранящий последние реплицированные данные, переводят в режим основного.
Общая схема этой процедуры такова:
Закрываем доступ клиентов к веб-приложению
Если используется двухуровневая конфигурация (фронтэнд nginx – бэкэнд apache и т.п.), рекомендуется на фронтэнде отключить доступ к бэкэнду (веб-приложению) и отдавать при обращении клиентов к кластеру информационную страницу о регламентных работах.

Останавливаем на всех slave-серверах поток получения обновлений бинарного лога с основного (master) сервера:

STOP SLAVE IO_THREAD;
SHOW PROCESSLIST;

Ждем, пока от потока выполнения команд slave (SQL_THREAD) не появится сообщение “Has read all relay log; waiting for the slave I/O thread to update it”, говорящее о том, что slave-сервер выполнил все команды из relay-лога в своей базе. Сразу останавливать slave командой STOP SLAVE не рекомендуется, т.к. не все SQL-команды могут быть выполнены из relay-лога (по причине отставания и т.п.), а при переключении slave на новый мастер, relay-лог будет очищен и, возможно, потеряется часть “непроигранных” данных.
Подготовка нового master-сервера
Убеждаемся, что на slave-сервере, который мы хотим сделать master-сервером, бинарный лог ведется и не логируются запросы из master:

SHOW VARIABLES LIKE 'log_bin';
log_bin       | ON
SHOW VARIABLES LIKE 'log_slave_updates';
log_slave_updates | OFF

Полностью останавливаем slave – потоки чтения бинарного лога и выполнения SQL-команд:

STOP SLAVE;
RESET MASTER;

Команда RESET MASTER необходима для очистки бинарного лога нового master, иначе, если в бинарном логе будут записи (устаревшие и т.п.), они проиграются на подключаемых к нему slave-серверах. Такое возможно, если сервер был master с включенным бинарным логом, потом стал slave и перестал использовать бинарный лог, потом снова переводится в режим master.

Итак, новый master подготовлен, у него очищен бинарный лог, и он готов обрабатывать запросы.
Переключение slave-серверов на новый master-сервер
На всех slave-серверах выполняем:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='#new_master_host_name#';
START SLAVE;

Настройка репликации mysql через скрипт

Добавим содержимое в скрипт

nano /root/CreateMySQLSlave.sh

#!/bin/bash
MYSQL_MASTER_HOST=10.1.2.20
MYSQL_SLAVE_HOST=10.1.2.30
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_MASTER_CONN="-h${MYSQL_MASTER_HOST} ${MYSQL_CONN}"
MYSQL_SLAVE_CONN="-h${MYSQL_SLAVE_HOST} ${MYSQL_CONN}"
MYSQLDUMP_OPTIONS="--master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
CREATE_REPL_USER="GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"
mysql ${MYSQL_MASTER_CONN} -AN -e"${CREATE_REPL_USER}"
RELOAD_FILE=/root/MySQLData.sql
echo "STOP SLAVE;" > ${RELOAD_FILE}
echo "CHANGE MASTER TO master_host='${MYSQL_MASTER_HOST}'," >> ${RELOAD_FILE}
echo "master_port=3306," >> ${RELOAD_FILE}
echo "master_user='repluser'," >> ${RELOAD_FILE}
echo "master_password='replpass'," >> ${RELOAD_FILE}
echo "master_log_file='dummy-file'," >> ${RELOAD_FILE}
echo "master_log_pos=1;" >> ${RELOAD_FILE}
mysqldump ${MYSQL_MASTER_CONN} ${MYSQLDUMP_OPTIONS} >> ${RELOAD_FILE}
echo "START SLAVE;" >> ${RELOAD_FILE}
mysql ${MYSQL_SLAVE_CONN} -A < ${RELOAD_FILE}

Делаем его исполняемым и запускаем

chmod +x /root/CreateMySQLSlave.sh

/root/CreateMySQLSlave.sh

Естественно указываем свои ип адреса мастера и раба.

head -29 /root/MySQLData.sql | tail -1

Обязательно настраиваем бинарные логи на мастере.
Добавим в /etc/my.cnf на мастере.
[mysqld]
log-bin=mysql-bin
Рестартим mysql.
не забываем установим server-id на мастере и слейве – 1 и 2.
На мастере /etc/my.cnf

[mysqld]
server-id=1

На слейве /etc/my.cnf

[mysqld]
server-id=2

P.S. Как изменить формат mysql лога bin.

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

Mysql работаем с бинлогами

Например:

mysqlbinlog -s -d db_name -r out.sql mysql-bin.000038

В данном случае будет обработан файл mysql-bin.000038 (из текущей директории), вывод оправится в out.sql, будут выведены только команды, относящиеся к изменению базы с именем db_name. Параметром -s мы запретили вывод дополнительной служебной информации.
Более полезный пример:

 mysqlbinlog -s -d db_name -u user_name --start-datetime="2009-01-23 21:10:00" -t mysql-bin.000001 > out.sql

Mysql error ib_logfile0 is of different size

Что делать, если во время перезапуска Mysql показывает подобную ошибку:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
100118 20:52:52 [ERROR] Plugin 'InnoDB' init function returned error.
100118 20:52:52 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
100118 20:52:52 [ERROR] Unknown/unsupported table type: InnoDB
100118 20:52:52 [ERROR] Aborting

Цифры могут быть другими

Это значит, что с момента последней перезагрузки в файле my.cnf был изменен такой параметр:

innodb_log_file_size  = 256M

Как видно из ошибки, фактический размер ib_logfile файлов составляет 5Мб, хотя в настройках указан размер в 256Мб. Существует два варианта решения этой проблемы.
1. Изменить размер лог файлов на правильный
В нашем случае необходимо изменить настройки в my.cnf на:

innodb_log_file_size  = 5M

После этого повторно запустить Mysql.
2. Обновить размер файлов
Для того, чтобы Mysql заработал с новой настройкой, необходимо сначала остановить Mysql:

/etc/init.d/mysql stop

После этого удалить все текущие бинлоги:

rm /var/lib/mysql/ib_logfile*

Нужно удалить оба файла: ib_logfile0 и ib_logfile1

И снова запустить Mysql, он сам пересоздаст эти файлы:

/etc/init.d/mysql start

Восстановление таблиц mysql

 mysqlrepair -u root -pПароль -A --auto-repair --sort_buffer_size=2G

Скрипт дампа и импорта базы и произвольных таблиц

#!/bin/bash
DBNAME="test.itc-life.ru"
DB_USER="root"
DB_PASSWORD="123123"
REMOTE_HOST="10.1.1.1"
OUTPUT_FILE_STRUCTURE="dumpfile"
echo "Дамплю базу по 100 записей"
/usr/bin/mysqldump -u ${DB_USER} -p${DB_PASSWORD} -h 10.1.1.1 --where="true limit 100" --all-databases >  ${dumpfile}.sql
echo "Импортирую в базу"
/usr/bin/mysql -u ${DB_USER} --p${DB_PASSWORD} < $dumpfile.sql
echo "Переходим к дампу таблиц"
for TABLE in `cat tables.list`
do
/usr/bin/mysqldump -u ${DB_USER} --p${DB_PASSWORD} -h 10.1.1.1 --where="true limit 100" `${DBNAME}` ${TABLE} >  ${TABLE}.sql
echo "Закончилcя dump ${DBNAME} таблицы ${TABLE}"
done
echo "Начинаю импорт файлов"
for TABLE in `cat tables.list`
do
/usr/bin/mysql -u ${DB_USER} --p${DB_PASSWORD} `${DBNAME}` < ${TABLE}.sql
echo "importing sql ${TABLE} done"
done
exit

Fix “mysql server has gone away”

SET GLOBAL max_allowed_packet=64*1024*1024;
Опубликовано в Mysql

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

 

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.