MySQL
Содержание
Даты
Разница дат:
|
1 |
select (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(block_date))/(3600*24) as days from vgroups limit 1; |
Первое число текущего месяца:
|
1 |
DATE_SUB(now(),INTERVAL DAYOFMONTH(now())-1 DAY) AS 'month_create' |
Последнее число текущего месяца:
|
1 |
select date_sub(date_add(DATE_SUB(now(),INTERVAL DAYOFMONTH(now())-1 DAY),interval 1 month),interval 1 day) as lastdaymonth |
Первое число следующего месяца:
|
1 |
date_add(DATE_SUB(now(),INTERVAL DAYOFMONTH(now())-1 DAY),interval 1 month) AS 'month_pay' |
Последний день месяца следующего месяца:
|
1 |
date_add(date_add(DATE_SUB(now(),INTERVAL DAYOFMONTH(now())-1 DAY),interval 2 month),interval -1 day) AS 'month_free' |
Работа с пользователями:
Создадим пользователя test с localhost и паролем ‘password’.
|
1 |
create user 'test'@'localhost' identified by 'password'; |
Пользователь создан. Для проверки посмотрим список пользователей:
|
1 |
select user,host,password from mysql.user; |
Создадим базу данных testdb.
|
1 |
create database testdb; |
Дадим пользователю test полные права на базу testdb
|
1 |
grant all on testdb.* to 'test'@'localhost' |
Смена пароля:
|
1 2 |
UPDATE user SET password=PASSWORD("ыеыпвп") where User='sукафукаr' and Host="5363456"; FLUSH PRIVILEGES; |
Удалим пользователя ‘test’@’localhost’
|
1 |
drop user 'test'@'localhost' |
Работа с IP адресами MySQL:
Оптимально хранить ip-адреса не как строки (varchar), а как числа. Для этого существует две функции mySQL — INET_ATON и INET_NTOA, первая преобразует 4 байтную последовательность ip-адреса в число, вторая преобразует обратно.
|
1 2 3 |
SELECT INET_NTOA(ip) FROM `ip_s` WHERE `ip_s`.ip > INET_ATON('255.255.0.0') |
Работа с бекапами:
Простой бекап
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql
Бекап с процедурами:
mysqldump —routines —extended-insert berserk > outputfile.sql
Бекап со структурой без данных
mysqldump —no-data — u USER -pPASSWORD DATABASE > /path/to/file/schema.sql
Следующая команда выгрузит дамп базы данных с триггреми, процедурами, таблицами НО! без данных в таблицах.
|
1 |
mysqldump --routines --no-data --no-create-db --skip-opt billing > onlytriggers.sql |
Если нужно сделать дамп только одной или нескольких таблиц
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql
Бекап в архив
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz
Создание бекапа с указанием его даты
mysqldump -u USER -pPASSWORD DATABASE | gzip > date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz
Заливаем бекап в базу данных
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql
Из архива в базу
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
или так
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
Восстановление одной БД:
mysql -u root -p —one-database db_to_restore < fulldump.sql
Создаём новую базу данных
mysqladmin -u USER -pPASSWORD create NEWDATABASE
Работа с процедурами:
Посмотреть список всех процедур:
|
1 |
SHOW PROCEDURE STATUS; |
Посмотреть код конкретной процедуры:
|
1 |
SHOW create PROCEDURE PORT_CONDITION_1; |
Только body:
|
1 |
select body from mysql.proc where name='PORT_CONDITION_1'; |
Вставить процедуру в базу:
|
1 2 3 4 5 6 7 8 |
delimiter // create PROCEDURE `PORT_CONDITION_4`(IN in_port int, OUT out_state int) BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND SET out_state = 0; SELECT IF(`vgroups`.`blocked`, 2, 1) INTO out_state FROM `vgroups`,`ports` WHERE `vgroups`.`vg_id` = `ports`.`vg_id` AND `ports`.`port_id`= in_port; END // delimiter ; |
Список процессов:
SHOW FULL PROCESSLIST;
Убить запрос:
kill query <id>;
Определение размера таблиц:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT count(*) TABLES, table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SHOW DATABASES; - список баз данных SHOW TABLES [FROM db_name]; - список таблиц в базе SHOW COLUMNS FROM таблица [FROM db_name]; - список столбцов в таблице SHOW CREATE TABLE table_name; - показать структуру таблицы в формате "CREATE TABLE" SHOW INDEX FROM tbl_name; - список индексов SHOW GRANTS FOR user [FROM db_name]; - привилегии для пользователя. SHOW VARIABLES; - значения системных переменных SHOW [FULL] PROCESSLIST; - статистика по mysqld процессам SHOW STATUS; - общая статистика SHOW TABLE STATUS [FROM db_name]; - статистика по всем таблицам в базе |
Работа с триггерами
Показать триггеры определенной таблицы:
|
1 |
show triggers like "%weekends%"; |