# mariadb docker run -p6666:3306 -e MYSQL_ROOT_PASSWORD=some -d -P mariadb
https://dev.mysql.com/doc/refman/5.7/en/glossary.html
https://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/glossary.html
mysqld --help #check syntax my.cnf
Обнаружение нагрузки → просмотр текущих задач
mysql> show full processlist\G;
Обнаружение нагрузки → Мониторинг долгих запросов
vi /etc/my.cnf
log_slow_queries = /var/log/mysql/queries-slow.log long_query_time = 1
Проверка базы данных
http://phpclub.ru/mysql/doc/using-mysqlcheck.html
MyISAM – для полнотекстового поиска
InnoDB – для всего остального, так как менее подвержена повреждениям, имеет транзакции…
+------------------------------+----------------------+ | Variable_name | Value | +------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_stmt_cache_size | 32768 | | have_query_cache | YES | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_stmt_cache_size | 18446744073709547520 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 314572800 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | table_definition_cache | 400 | | table_open_cache | 4 | | thread_cache_size | 0 | +------------------------------+----------------------+
+-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 212366800 | | Qcache_hits | 4830446 | | Qcache_inserts | 18678151 | | Qcache_lowmem_prunes | 14419894 | | Qcache_not_cached | 880499 | | Qcache_queries_in_cache | 8968 | | Qcache_total_blocks | 18084 | +-------------------------+-----------+
SET GLOBAL max_connections = 150;
Эквивалентно:
SET character_set_client = charset_name; SET character_set_results = charset_name; SET character_set_connection = charset_name;
Можно указать binary в качестве кодировки, чтобы перекодирования не производилось.
set @@global.query_cache_size=300*1024*1024
var_dump(mysql_client_encoding()); $result = mysql_query("show variables like '%character%';", $this->lid); while($row = mysql_fetch_row($result)){var_dump($row);}
Показать все базы
SHOW DATABASES;
Удалить базу u1234_one
DROP DATABASE `u1234_one`;
Удалить колонку в таблице
ALTER TABLE hhru DROP COLUMN email;
Добавить колонку в таблицу
ALTER TABLE calls ADD calldate datetime NOT NULL default '0000-00-00 00:00:00';
Модифицировать колонку
ALTER TABLE mytable MODIFY COLUMN mycolumn VARCHAR(100)
Показать все таблицы
SHOW TABLES;
Показать всех пользователей
SELECT * FROM mysql.user \G;
Показать права для текущего пользователя
SHOW GRANTS;
Показать версию MySQL
SELECT VERSION();
Изменил привилегии для пользователей?! Тогда!:
FLUSH PRIVILEGES; # применит твои изменения
Изменить кодировку в таблице
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8;
TRUNCATE TABLE yourTable; echo show tables | mysql -uuser -ppass | grep -i "cach" | awk '{print "TRUNCATE TABLE " $1";"}' | mysql -uuser -ppass #очищаем все таблицы в имене которых "cach"
CREATE TABLE recipes_new LIKE production.recipes; INSERT recipes_new SELECT * FROM production.recipes;
SELECT * FROM table LIMIT 10
SELECT id FROM result ORDER BY ID DESC LIMIT 1; SELECT id FROM result ORDER BY ID ASC LIMIT 1;
INSERT INTO emails (email, uri, fio) VALUES ("some@inbox.ru", "test", "Constantin Conovaloff");
INSERT INTO Table ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 )
SHOW FULL COLUMNS FROM your_table;
SHOW CREATE TABLE t\G
create table emails ( id int auto_increment not null, email varchar(255) not null, uri varchar(255), senddone int not null DEFAULT 0, primary key (id), unique (email));
create table times ( calldate DATETIME NOT NULL default '0000-00-00 00:00:00', curdate TIMESTAMP NOT NULL default CURRENT_TIMESTAMP);
SELECT q.id AS question_id, q.name AS question_name, q.answer AS answer_text, w.name AS question_author FROM questions AS q LEFT JOIN workers AS w ON w.id = q.author; В данном случае, q.uthor получается из таблицы questions, а далее идет запрос к таблице workers
user=u; pass=p; host=localhost; database=${user};mysqldump -u${user} -p${pass} -h ${host} --add-drop-table --no-data ${database} | grep ^DROP | mysql -u${user} -p${pass} -h ${host} ${database}
SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
WHERE date_create >= DATE_FORMAT('$date', '%Y-%m') and date_create <= DATE_FORMAT('$fdate', '%Y-%m')
/etc/init.d/mysql stop mysqld_safe --skip-grant-tables & mysql -u root mysql update user set Password=PASSWORD('ThisMyPassword') where User='root'; flush privileges; /etc/init.d/mysql restart
Дампим одну базу
mysqldump -uroot -h82.82.82.82 -p database > database.sql
Дампим все базы данных в один файл
mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql
Дампим выборочное несколько баз в один файл
mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql
Дампим все базы данных по отдельности
for db_name in `echo 'SHOW DATABASES' | mysql -BN -uroot -pQWERTY`; do echo $db_name; mysqldump -aeQ -f --add-locks --add-drop-table -uroot -pQWERTY $db_name | gzip > $db_name.gz; done
Table is marked as crashed and should be repaired
mysqlcheck -uUSER -pPASSWORD --repair --extended user_base
mysqlcheck -uUSER -pPASSWORD --repair --extended -A
SQL запросом:
USE user_base REPAIR TABLE TABLE;
После mysql_connect:
mysql_query('SET NAMES cp1251');
Или для всего сервера в my.cnf:
default-character-set=cp1251 character-set-server=cp1251 collation-server=cp1251_general_ci init-connect= SET NAMES cp1251; skip-character-set-client-handshake
Импорт с указанием кодировки: mysql --default-character-set=utf8
--extended-insert=false
INSERT INTO result( worker, questid, answerid, ticketid, dateAnswer) VALUE( 43, 10, 11, (SELECT #В инсерте создаем отдельный SELECT запрос @ticketID := r.ticketid + 1 #В селекте задаем переменную ticketID для дальнейшего использования. + инкрементация полученного значения FROM result AS r #FROM result выдаст ошибку, так как к result уже есть обращение и следует использовать AS some ORDER BY ID DESC LIMIT 1), (SELECT @nowTime := NOW()) #Создаем переменную @nowTime для одинакового значения во всем инсерте и меньших количествах вызовов функции. ), (43, #Множественный инсерт 10, 11, @ticketID, #Использование рание созданных переменных @nowTime), (43, 10, 11, @ticketID, @nowTime);
ALTER TABLE `TEST` ADD INDEX (`CF_ID`);
Пример:
mysql> explain select * from TEST where CF_ID=8; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | TEST | ALL | NULL | NULL | NULL | NULL | 30 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.01 sec) mysql> ALTER TABLE `TEST` ADD INDEX (`CF_ID`); mysql> explain select * from TEST where CF_ID=8; +----+-------------+-------+------+---------------+-------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------+---------+-------+------+-------+ | 1 | SIMPLE | TEST | ref | CF_ID | CF_ID | 4 | const | 1 | | +----+-------------+-------+------+---------------+-------+---------+-------+------+-------+ 1 row in set (7.91 sec)
Убрать индексы:
ALTER TABLE `TEST` DROP INDEX `CF_ID`;
Возможный ошибки:
ERROR 1170 (42000): BLOB/TEXT column 'callid' used in key specification without a key length Сообщает, что BLOB или TEXT не могут быть индексами, так как имеют рандомную длину. В этом случае следует использовать ADD FULLTEXT INDEX Если колонка имеет большой текст, то можно хранить и делать уникальным md5 колонки. Если колонка имеет все значения похожими (к примеру 1383312588.9068, телефон или т.д.) то колонку стоит сделать varchar(255)
закомментируем в /etc/my.cnf строку
sort_buffer_size = 512K
или увеличиваем значение до 2мб:
sort_buffer_size = 2M
Репликация БД (Мастер-слэйв)
Статья о дополнительной настройке
Информация о движках MySQL
Скрипт по проверки MySQL и оптимизации 1
Скрипт по проверки MySQL и оптимизации 2 - mysqltuner.pl
Распространенные сообщения об ошибках
Системные переменные (оффСайт eng)
ссылки:
http://ruseller.com/lessons.php?rub=28&id=1189 - простые примеры создания процедуры
Сам код хранятся в таблице proc базы mysql Информация о именах хранится в information_schema.ROUTINES
thx http://baron.su/archives/135-Hranimye_procedury_i_mysqldump.html
Для тех, кто обладает памятью, лучшей, чем у меня: экспорт хранимых процедур и функций включается параметром --routines, например: mysqldump --routines --extended-insert berserk > outputfile.sql
Если же база уже перенесена, и не хватает лишь переноса хранимых функций и процедур, можно воспользоваться командой: mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt berserk > outputfile.sql
Все тоже самое, но без экспорта триггеров. mysqldump --skip-triggers --routines --no-create-info --no-data --no-create-db --skip-opt berserk > outputfile.sql
Название базы (berserk) выделено курсивом, вероятно, вам потребуется его заменить. Импорт в базу на новом сервере можно осуществить так: mysql newberserk < outputfile.sql
Импортируем test.sql
-- MySQL dump 10.13 Distrib 5.1.70, for debian-linux-gnu (x86_64) -- ------------------------------------------------------ -- Server version 5.1.70-beget-log /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`constaj0_dle`@`localhost`*/ /*!50003 PROCEDURE `testProc`() BEGIN SELECT * FROM dle_users LIMIT 1; END */;;
Где constaj0_dle заменяется на корректную базу данных.
Проверяем что процедура загружена:
mysql> select * from information_schema.ROUTINES \G;
Вызываем процедуры
call constaj0_dle.testProc;
Удаляем процедуру
DROP PROCEDURE constaj0_dle.testProc;
при импорте дампа - В строке, может содержаться более 1 мегабайта. В этом случе, необходимо увеличить значение max_allowed_packet при работе сайта - mysql_query('set SESSION wait_timeout = 600'); SET SESSION wait_timeout = 3600 #секунты SET SESSION max_allowed_packet = 16777216 #байты (16 мегабайт)
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; CREATE USER 'myuser'@'%' IDENTIFIED BY PASSWORD '*HASH';
Смотрим права доступа для пользователей
select * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;
Даем права execute для процедуры или функции # еще не тестил
GRANT execute ON PROCEDURE proc TO username; GRANT execute ON FUNCTION func TO username;
Даем права на все, для процедуры:
grant all on procedure routine.res to user;
Даем этим пользователя права к базам
GRANT ALL ON mydb.* TO 'myuser'@'%'; GRANT ALL ON mydb TO 'myuser'@'%'; GRANT CREATE ON mydb TO 'myuser'@'%'; FLUSH PRIVILEGES;
http://dev.mysql.com/doc/refman/5.1/en/grant.html
http://stackoverflow.com/questions/16287559/mysql-adding-user-for-remote-access
CREATE USER 'main'@'%' ; GRANT ALL PRIVILEGES ON *.* TO 'main'@'%'; FLUSH PRIVILEGES;
"SET NAMES cp1251" "SET CHARACTER SET cp1251" "SET NAMES utf8" "SET CHARACTER SET utf8" mysql_query("SET CHARACTER SET cp1251"); mysql_query("SET NAMES cp1251"); mysql_query("SET CHARACTER SET utf8"); mysql_query("SET NAMES utf8"); --default-character-set=utf8 --default-character-set=cp1251
Создает еще одну колонку отсортированное в бинарное дерево с указателями на место обращения к основной колонки.
добавление ALTER TABLE table_name ADD INDEX (`column_name`);
удаление ALTER TABLE table_name DROP INDEX column_name
Тоже что и обычный, но B-дерево принимает на вход несколько колонок. Тем при селекте по этим столбцам скорость несколько возврастает.
добавление ALTER TABLE table_name ADD INDEX anyIndexName (`column_name`, `col2`);
удаление ALTER TABLE table_name DROP INDEX anyIndexName
Подходит только для полнотекстового поиска. В остальном не увеличивает скорость. Кажется не работает с LIKE
SET SESSION FOREIGN_KEY_CHECKS = 0; #при возникновение ошибок FOREIGN_KEY
Подзапрос в одной таблице SELECT login FROM overloads WHERE id IN (select id from overloads WHERE server = "nostromo"); SELECT * FROM overloads WHERE id IN (select id from overloads WHERE server = "nostromo") AND login LIKE "%cav%";
SET @n:=0; UPDATE table1 AS scr1 SET scr1.rate = (@n:=@n+1) WHERE scr1.id_tour = '29' AND scr.b_id IN (SELECT b_id FROM table_users AS tt) ORDER BY scr1.money DESC
баз данных
SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
таблиц
Discussion
Запись абсолютно всех запросов к mysql.
Процедура
Так же известны под именем Routines, подпрограммы
Процедура относится сразу ко всем базам данных?
Ограничивается ее использование только пользователем который ее вызывает?
Тригеры
Вьюхи
Бэкапятся
Ничего дополнительного. Поподают в mysqldump без аргументов
Если нужно бэкапить только их, то: экспорт:
импорт:
Присутствие
на уровне базы в виде подобия таблицы. Выглядят как обычные таблицы.
Просмотр
Присутствуют и неотличимы в show tables, но сразу палятся в show FULL tables;
Посмотреть на вьюху можно через
Есть процедуры (PROCEDURE) а есть функции (FUNCTION) Процедуры вызываем через
функцию вызываем через
функции назначаются на определенные таблицы, которые указаны в information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'table_name'
фитчи
Online DDL – как минимум, умеет проставлять индексы в таблицы не блокируя ее. Вероятно, много чего еще.
Посмотреть размер
Баз данных
таблиц (поменять database_name):
установка 5.6
https://dev.mysql.com/downloads/mysql/5.6.html#downloads
Быстрый импорт
Если при импорте, CPU не нагружается, то можно посмотреть в iotop и если наибольшую нагрузку создает [jbd2/sda3-8] вместе с mysql, то можно отключить коммит изменений после каждого импорта и некоторые чеки:
групировка по дням
http://sqlize.com/0fZI6J0Oi5 – jsfiddle для mysql
USE INDEX
После FROM:
Но вместо этого, можно указывать в запросе “не использовать” кэш
Новая фитча
скрыть вывод на время дебага
Использвоание переменных в запросе
Join
Есть несколько видов Джойнов:
Другим языком:
профилирование
https://dev.mysql.com/doc/refman/5.5/en/show-profile.html
https://www.percona.com/blog/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/ – когда нужно использовать внешние ключи
включение логирования всех запросов
Nested Sets
Materialized Path (Path Enumeration)
Adjacency List
Closure Table
Последовательность выполнения
rename database
Получить последний push_token по created_at, при группировке по user.id
Прочитать про sysbench и его возможность бенчмарка базы данных