MAIN PAGE

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                    |
+------------------------------+----------------------+

• query_cache_size - Размер кэша

+-------------------------+-----------+
| 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     |
+-------------------------+-----------+

• Qcache_free_memory - Свободно памяти для кэширования
• Qcache_lowmem_prunes - Удалено кэша из за нехватки памяти (стремиться к 0)
• Qcache_queries_in_cache - Запросов в кэше
• Qcache_not_cached - не кэширующихся запросов
• Qcache_hits - Успешных обращений к кэшу

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 

• –default-character-set=charset_name # Указываем кодировку подключения. По умолчанию utf-8 или в более ранних latin1
• character-sets-dir=/usr/local/mysql/share/mysql/charsets/
• default-character-set=cp1251

Table is marked as crashed and should be repaired

• Без параметров - только проверка базы на ошибки.
• -A - Все базы данных
• -r - чинить
• -o - оптимизировать #не до конца понятен способ оптимизации

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`;

• UNIQUE и PRIMARY KEY также является индексами по умолчанию.

Возможный ошибки:

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

• log=/var/log/mysql-queries.log - лог работы
• log-error=/var/log/mysql-errors.log - лог ошибок
• log-slow-queries=/var/log/mysql-slow-queries.log - лог медленных запросов

Репликация БД (Мастер-слэйв)
Статья о дополнительной настройке
Информация о движках 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-дерево принимает на вход несколько колонок. Тем при селекте по этим столбцам скорость несколько возврастает.

1. Индекс по левой колонке\индексу. (col1, col2) подходит для col1 AND col2, col1 но не подходит для col2
2. Не подходит для OR. Только для AND

добавление
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; 

таблиц