Category Archive: MySQL

Бэкапирование

В прежние времена я часто занимался автоматизацией бэкапирования, т.е. создания резервной копии баз данных, файлов и т.д.. Сейчас таких задач у меня не возникает, но все же решил написать о том, как писать такие скрипты.
Перечислим базовые шаги алгоритма скрипта:

  1. Выбрать директорию, где храняться резервные копии
  2. Снять резервную копию базы данных
  3. Сжать файлы с помощью архиватора и присвоить удобное наименование архиву
  4. Добавить скрипт в планировщик и установить время и периодичность запуска

Рассмотрим версии скрипта для Windows и для Linux.

Нахождение медианы в массиве (MySQL)

Нахождение медианы в массиве для MySQL я решил способом ниже. Кто знает более лучшие варианты скидывайте :). Но, желательно, без использования стандартных функций СУБД для нахождения медианы.

set @a := 0;
set @cnt := (select count(*) from t_table);
 
select 
    (sum(case when num = ceil(@cnt / 2) then c_value else null end)
   + sum(case when num = ceil((@cnt + 1) / 2) then c_value else null end)) / 2
from(
    select (@a := @a + 1) num,c_value from t_table order by c_value
)w

Шпаргалка по MySQL (Утилиты)

Запуск MySQL-клиента:

mysql -uroot -p

Запуск MySQL-клиента c указанием хоста:

mysql -h192.168.0.2 -uroot -p

Запуск MySQL-клиента c указанием хоста и порта:

mysql -h192.168.0.2 -P3307 -uroot -p

Создание резервной копии:

mysqldump -uroot -p test > test.sql

Создание резервной копии c указанием максимального размера пакета:

mysqldump -uroot -p --max_allowed_packet=128M test > test.sql

Восстановление резервной копии:

mysql -uroot -p < test.sql test

Смена пароля:

mysqladmin -uroot -p password mynewpass

Перенос директории с данными MySQL (на примере RedHat)

По умолчанию MySQL хранит файлы базы данных на том же диске, где он установлен, и часто требует перенос этих файлов на другой диск большей вместимости.

Допустим, у нас имеется RedHat с установленным MySQL
1) Проверим настройки MySQL, вызовем команду:

cat /etc/my.cnf

Получим примерно следующие данные:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet = 500M
default-storage-engine=innodb
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

В файле конфигурации параметр datadir указывает на место хранения файлов базы данных.
В нашем случае он указывает на /var/lib/mysql

2) Для проведения переноса данных, первым делом, нужно остановить MySQL.

service mysqld stop

3) Определимся с целевой директорией, куда будет осуществлен перенос.
Допустим, мы хотим перенести директорию mysql в директорию /600g

У меня фантазии не хватило - я назвал диск по его размеру.
Пример из жизни: на одном сервере диск в 600 гигабайт я назвал /600g, 
на другом диск в 3 терабайта - назвал /3t

Создадим директорию:

mkdir /600g

4) Теперь скопируем саму папку с содержимым с помощью команды:

cp -R -p /var/lib/mysql /600g

Где cp — стандартная команда для копирования.
Стоит обратить внимание на ее параметры:
-R — говорит о том, что нужно копировать директорию рекурсивно (включая поддиректории и файлы в поддиректориях)
-p — копирование с сохранением всех прав доступа и разрешений
/var/lib/mysql — здесь указывается переносимая директория
/600g — место, куда будет перенесена директория

После переноса директории mysql в /600g, мы получим следующий путь:

/600g/mysql

5) Осталось изменить конфигурацию:

nano /etc/my.cnf

Замените строки, указав на новый путь размещения директории:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

на следующие:

datadir=/600g/mysql
socket=/600g/mysql/mysql.sock

6) К сожалению, это не все.
Нужно еще заменить пути в файле демона: /etc/init.d/mysqld

7) Запускаем MySQL

service mysqld start

8) После переноса появиться еще одна проблема, и пока не знаю как ее исправить.
Если у вас на этом же сервере есть клиент MySQL и вы могли его раньше запускать следующим образом:

mysql -uroot -p

То сейчас нужно еще будет явно указывать расположение файла сокета:

mysql -uroot -p --socket=/600g/mysql/mysql.sock

Скорее всего, где то в настройках еще нужно переписать путь.
Как в следующий раз появиться возможность произвести подобный перенос, разберусь с этой проблемной. А экспериментировать с боевым сервером не комильфо.

Создание урезанной копии базы данных MySQL

Очень часто встречается ситуация, когда нужно иметь копию реальной базы данных на своем компьютере. Например, для тестирования программы на реальных данных, для поиска ошибок или просто иметь более-менее заполненные данными таблицы и т.д. Если в базе хранятся файлы, и они интенсивно добавляются в базу, то размер такой базы может быть очень большим. Чтобы сделать копию такой базы нужно время, свободное место, также затруднительно скачать такую базу удаленно через интернет. Как выход можно сделать урезанную копию — выгружать только таблицы, в которых не храняться файлы. При условии, конечно, что на данные таблиц не ссылаются другие таблицы.

Классический резервную копию делают следующей командой:

mysqldump -uroot -p your_database > dump.sql

Где -p — опция позволяет вводить пароль в скрытом виде,
your_database — название выгружаемой в дамп базы данных.

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

mysqldump -uroot -p your_database table1 table2 table3 > dump.sql

В данном случае, из базы данных your_database будут выгружены таблицы (со структурой и данными): table1, table2 и table3. Остальные таблицы не будет выгружены, даже структура.

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

SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'your_database'

Где information_schema.tables — системная таблица, содержащая информацию о таблицах,
your_database — название вашей базы данных

Полученные данные имеют вид стобца, нам же нужны список названий с пробелом в качестве разделителя.
Для этого выполните следующий запрос:

SELECT TABLE_NAME INTO OUTFILE 'c:\\tables.txt' 
LINES TERMINATED BY ' '
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'your_database'

Данный запрос позволяет выгрузить данные в файл c:\tables.txt. Из-за экранирования вместо разделителя пути «\» нужно использовать «\\» (Для linux-систем разделителем является слэш «/», который не экранируется, т.е. путь может быть выглядеть следующим образом /tmp/tables.txt. )
Причем, если файл c:\tables.txt уже существует выйдет ошибка «Error: File ‘c:\tables.txt’ already exists». Нужно удалить этот файл или переименовать название файла для выгрузки и выполнить запрос заново.
Строка LINES TERMINATED BY ‘ ‘ говорит о том, что разделитель строк теперь пробел. Это позволяет значения из столбца записать в виде строки.

Теперь полученный список названий таблиц через пробел можно подставить в запрос:

mysqldump -uroot -p your_database таблицы_через_пробел > dump.sql

Но нам нужны ведь не все таблицы, поэтому вы можете из запроса удалить ненужные таблицы.

Теперь о том, как найти таблицы, которые будем игнорировать. Отсеивать будем по размеру и наименованию таблиц.
Для начала выведем список таблиц, отсортированный по размеру таблиц, используем всё ту же таблицу information_schema.tables.

SELECT
    table_name,    
    ROUND(data_length/1024/1024,2) AS total_size_mb,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema='your_database'
ORDER BY total_size_mb desc

Где table_name — название таблицы,
total_size_mb — размер таблицы в мегабайтах,
table_rows — кол-во записей в таблице
Пример выполнения запроса:
sizes
Я не буду выгружать таблицы, которые занимают более 10 мегабайт и в названии которых присутствует слово «ATTACH». В таблицах ATTACH храняться файлы, поэтому они занимают много места.
Следующий запрос позволяет получить список таблиц, которые не будем выгружать.

SELECT
    table_name,    
    ROUND(data_length/1024/1024,2) AS total_size_mb,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema='your_database' AND ROUND(data_length/1024/1024,2) > 10 AND TABLE_NAME LIKE '%attach%'
ORDER BY total_size_mb desc

Так как, скорее всего, список исключаемых таблиц будет невелик. Можно исключить их вручную, так будет даже нагляднее. Итоговый запрос с учетом исключения ненужных таблиц будет выглядеть следующим образом:

SELECT TABLE_NAME INTO OUTFILE 'c:\\tables.txt' 
LINES TERMINATED BY ' '
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME NOT IN ('ignore_table_1','ignore_table_2','ignore_table_3')

Данный запрос выведет названия таблиц через пробел в файл, но не будет исключенных таблиц: ignore_table_1, ignore_table_2 и ignore_table_3.

Остается подставить полученный список таблиц в команду резервного копирования:

mysqldump -uroot -p your_database таблицы_через_пробел > dump.sql

В данным случае, это позволило получить урезанную резервную копию на 7 гигабайт меньше оригинальной.

p.s. Есть один минус — при восстановлении резервной копии нужно создать таблицы, которые мы проигнорировали. В моем проекте это не проблема, т.к. используется ORM(Object-relational mapping), который сам восстановит недостающие таблицы.
Также будьте внимательны при выполнении запросов в различных sql-клиентах. Например, Squirrel SQL Client по умолчанию выведет в файл первый 100 названий таблиц, поэтому нужно снять флаг Limit rows.

Установка клиента MySQL 5.5 на CentOS 5.5 (RHEL)

Допустим, у вас есть сервер на CentOS 5.5, на котором не установлен клиент MySQL, и вам нужно подключиться или снять бэкап с базы данных MySQL 5.5 на другом сервере, и в добавок ко всему на сервере нет доступа к интернету.
Для начала нужно закачать на сервер установочный пакет клиента MySQL: MySQL-client-5.5.29-1.rhel5.x86_64.rpm
Затем выполните команду:

sudo rpm -ivh MySQL-client-5.5.29-1.rhel5.x86_64.rpm

Ключ -i означает установку пакета (install).
Флаги v и h не являются обязательными, но нужны для включения вывода информации о ходе процесса установки пакета.
Если у вас уже имеется старая версия пакета, вам нужно выполнить другую команду:

sudo rpm -Uvh MySQL-client-5.5.29-1.rhel5.x86_64.rpm

Ключ -U означает обновление пакета (update).
Последную команду можно использовать не только для обновления пакета, но и для установки. Другими словами, при наличии старой версии пакета, он будет обновлен, а при его отсутствии, пакет будет будет установлен.

Если процесс установки прошел без ошибок, можно попробовать подключиться к удаленному MySQL:

mysql -h<host> -u<user> -p<password>

host — адрес удаленного сервера
user — пользователь
password — пароль пользователя

Шпаргалка по MySQL (Foreign Key)

Отключить проверку внешних ключей

SET foreign_key_checks = 0;

Включить проверку внешних ключей

SET foreign_key_checks = 1;

Просмотреть ограничения для таблицы:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA = 'YOUR_DATABASE' AND TABLE_NAME = 'YOUR_TABLE';

Просмотреть ссылочное ограничение двух связанных таблиц (имя ограничения — поле CONSTRAINT_NAME):

SELECT CONSTRAINT_SCHEMA,CONSTRAINT_NAME,TABLE_NAME,REFERENCED_TABLE_NAME 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA = 'YOUR_DATABASE' 
AND TABLE_NAME = 'YOUR_TABLE' AND REFERENCED_TABLE_NAME = 'YOUR_REFERENCED_TABLE'

Возможные проблемы с MySQL

  1. Просто установить MySQL мало, нужно его еще  донастроить. Самое главное — нужно не забыть про настройку max_allowed_packet (максимально разрешенный размер пакета). Например, если max_allowed_packet равен 1 мегабает, то в один прекрасный день вы не сможет записать данные размером более 1 мегабайта.
    Появится ошибка вида: Packet for query is too large (27545659 > 1048576)
    Теперь о том, как увеличить max_allowed_packet.


    Для windows:
    В файле my.ini после строки[mysqld]пропишите следующее значение max_allowed_packet=512M
    Не забудьте перегрузить службу MySQL!


    Для redhat:
    В файле /etc/my.cnf в блок [mysqld] пропишите следующий параметр max_allowed_packet = 512M
    Не забудьте перегрузить сервиc mysqld!

    service mysqld restart

  2. При создании базы данных сразу же указывайте кодировку UTF-8, иначе в linux-системах, будет проблема с юникодными символами:

    CREATE DATABASE your_database DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

  3. Проблема с кодировкой при подключении к базе данных. Допустим, у нас есть строка подключения с указанием кодировки UTF8:
    jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8
    Та же строка из-за экранирования символов в xml документе будет выглядеть так:
    jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF8
    Если вы нечайно возьмете строку экранированную для xml с &amp; ошибки не возникнет, но кодировка не установиться. Вместо кириллицы будут вопросительные знаки. В windows проблема c кодировкой может и не возникнет, но в unix и linux обязательно возникнет.


  4. Может возникнуть проблема с внешними ключами (foreign key). Очень важно следить какой движок у ваших таблиц. У связанных таблиц движок должен быть InnoDB. Если у одной таблицы движок InnoDB, а у другой MyISAM, внешний ключ будет создан, но вы не сможете вставить данные в столбец с Foreign Key. Как это исправить:
    1) Нужно поменять движок у существующих таблиц. Для каждой таблицы выполните запрос:

    ALTER TABLE название_таблицы ENGINE=InnoDB;

    Чтобы вычислить таблицы с движок отличным от InnoDB выполните запрос:

    SELECT    
        TABLE_NAME,
        ENGINE,
        CONCAT(CONCAT('ALTER TABLE ',TABLE_NAME),' ENGINE=InnoDB;')
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_SCHEMA=DATABASE() AND ENGINE <> 'InnoDB'

    Результатом будет: название таблицы, текущий движок таблицы и уже сформированный запрос на изменение движка на InnoDB. Вам остается выбрать запросы для тех таблиц, у которых вы хотите поменять движок.
    2) Как можно проконтролировать создание таблиц именно с движком InnoDB. Во-первых, можно при создании таблицы явно указать движок InnoDB. Во-вторых, можно установить движок InnoDB по умолчанию. Для этого в файлах my.ini (для windows) и my.cnf (для linux) в секции [mysqld] сделайте следующую настройку:

    default-storage-engine=innodb
    

  5. MySQL по умолчанию хранить таблицы (innodb) в одном файле. Причем размер этого файла никогда не уменьшается, т.е. если он достиг одного уровня, и вы удалили некоторые данные, MySQL не отдаст это свободное пространство. Это может быть критично при дефиците свободного места на диске.
    Чтобы решить эту проблему нужно перед созданием базы данных добавить в конфигурационный файл опцию:

    innodb_file_per_table=1
    

    После установки этой опции каждая таблица будет храниться в отдельном файле. Это также может быть полезно при ограничении на размер файла в операционной системе.
    Для начала лучше потренироваться на тестовой базе данных, а потом пробовать на боевой, сняв перед этим резервную копию.

Шпаргалка по MySQL (Общее)

Создание базы данных:

CREATE DATABASE your_database DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

Смена кодировки базы данных

ALTER DATABASE your_database DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

Просмотреть кодировки таблиц

SELECT TABLE_NAME,TABLE_COLLATION 
FROM information_schema.tables WHERE TABLE_SCHEMA = 'your_database'

Дать все привилегии на все базы и таблицы:

GRANT ALL PRIVILEGES ON *.* TO user@'%' IDENTIFIED BY 'your_passw' WITH GRANT OPTION;

Дать все привилегии на таблицы базы your_database:

GRANT ALL PRIVILEGES ON your_database.* TO user@'%' 
IDENTIFIED BY 'your_passw' WITH GRANT OPTION;

Поменять название и тип столбца с VARCHAR(255) на VARCHAR(2000):

ALTER TABLE YOUR_TABLE CHANGE YOUR_COLUMN YOUR_COLUMN_NEW_NAME VARCHAR(2000)

Поменять тип столбца с INT на DOUBLE:

ALTER TABLE YOUR_TABLE CHANGE YOUR_COLUMN YOUR_COLUMN_NEW_NAME DOUBLE

Переименовать таблицу:

ALTER TABLE OLD_TABLE_NAME RENAME NEW_TABLE_NAME

Название текущей базы данных:

SELECT DATABASE()

Информация о таблицах текущей базы данных (название, движок, размер в мегабайтах и кол-во записей):

SELECT
    TABLE_NAME,
    ENGINE,
    ROUND(DATA_LENGTH/1024/1024,2) AS TOTAL_SIZE_MB,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA=DATABASE()
ORDER BY TOTAL_SIZE_MB DESC

Список всех подключений к MySQL:

SHOW PROCESSLIST

Просмотреть значения серверных переменных:

SHOW VARIABLES

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

SHOW VARIABLES LIKE '%character%'