Category Archive: 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 -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 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

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

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

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 - кол-во записей в таблице Пример выполнения запроса: Я не буду выгружать таблицы, которые занимают более 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.

Допустим, у вас есть сервер на 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 -u -p

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

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

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'

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

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%'

---