Category Archive: MySQL

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

Март 31st, 2016 | BY | Category: MySQL, Скрипты | Нет комментариев »
В прежние времена я часто занимался автоматизацией бэкапирования, т.е. создания резервной копии баз данных, файлов и т.д.. Сейчас таких задач у меня не возникает, но все же решил написать о том, как писать такие скрипты. Перечислим базовые шаги алгоритма скрипта:
  1. Выбрать директорию, где храняться резервные копии
  2. Снять резервную копию базы данных
  3. Сжать файлы с помощью архиватора и присвоить удобное наименование архиву
  4. Добавить скрипт в планировщик и установить время и периодичность запуска
Рассмотрим версии скрипта для Windows и для Linux.

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

Февраль 29th, 2016 | BY | Category: 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 (Утилиты)

Апрель 15th, 2014 | BY | Category: 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)

Ноябрь 23rd, 2013 | BY | Category: MySQL | Нет комментариев »
По умолчанию 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

Июль 28th, 2013 | BY | Category: 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)

Июнь 15th, 2013 | BY | Category: MySQL | Нет комментариев »
Допустим, у вас есть сервер на 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)

Март 5th, 2013 | BY | Category: MySQL | Нет комментариев »
Отключить проверку внешних ключей 
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

Март 5th, 2013 | BY | Category: Java, 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 (Общее)

Февраль 7th, 2013 | BY | Category: 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%'
Naik's blog is proudly powered by WordPress
designed by WPart.org