MySQL - система управления базами данных. Представляет собой быстрый многопоточный, многопользовательский надежный SQL-сервер баз данных и подходит как для критических по задачам производственных систем с большой нагрузкой, так и для встраивания в программное обеспечение массового распространения.


Управление

Для управлениями им можно использовать как интерфейс командной строки, так и ряд программ на различных языках. Например, Phpmyadmin или MySQL Administrator (в OS Ubuntu Linux пакет именуется как mysql-admin).

Как узнать размер баз в консоли

Заходим в базу

mysql -u root -p

И отдаём такую команду

1
SELECT table_schema "database_name", sum( data_length + index_length )/1024/1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Как создать базу MySQL

Нужно зайти в базу данных

mysql -u root -p

и создать базу

CREATE DATABASE WIKIADMIN; #название wikiadmin поменяйте на своё, какое понравится

Сбросить пароль root MySQL

Иногда возникает небольшая проблема в виде забытого или утерянного пароль пользователя root в БД MySQL. Это не так фатально, как можно было подумать, т.к. есть возможность восстановить его без сброса других данных. Для начала нам нужно будет остановить сервер MySQL:

/etc/init.d/mysql stop

Далее запускаем сервер БД снова, но уже с опцией –skip-grant-tables, которая отключает проверку привилегий:

mysqld_safe –skip-grant-tables &

Теперь мы можем подключиться к серверу пользователем root без ввода пароля:

mysql -u root

И установить новый пароль. Для этого нужно выбрать необходимую базу:

USE mysql;

Установить новый пароль для пользователя root:

1
UPDATE user SET password=PASSWORD("PASSWORD") WHERE User='root';

И “применить” привилегии и затем выйти:

1
2
FLUSH PRIVILEGES;
quit

Все, теперь можно перезапустить сервер, чтобы он работал в нормальном режиме без опции –skip-grant-tables. Вход для root будет доступен по новому паролю:

1
2
/etc/init.d/mysql stop
/etc/init.d/mysql start

Как проверить базу на ошибки

Запускаем проверку от рута, нужно будет ввести пароль:

mysqlcheck -A –all-databases -u root -p

Ниже приведены возможные опции для mysqlcheck. Какие из них поддерживает ваша версия, можно проверить с помощью команды mysqlcheck –help.

-A, –all-databases

Проверить все базы данных. Аналогична опции –databases, если указать все базы данных.

-1, –all-in-1

Вместо выполнения запросов для каждой таблицы в отдельности выполнить все запросы в одном отдельно для каждой таблицы. Имена таблиц будут представлены в виде списка имен, разделенных запятой.

-a, –analyze

Анализировать данные таблицы.

–auto-repair

Если проверенная таблица повреждена, автоматически восстановить ее. Исправления будут произведены после проверки всех таблиц, если были обнаружены повреждения.

-#, –debug=…

Выводит информацию журнала отладки. Часто используется следующий набор параметров: ’d:t:o,filename’

–character-sets-dir=…

Директория, где находятся установки символов.

-c, –check

Проверить таблицу на наличие ошибок.

-C, –check-only-changed

Проверить только таблицы, измененные со времени последней проверки или некорректно закрытые.

–compress

Использовать сжатие данных в протоколе сервер/клиент.

-?, –help

Вывести данную вспомогательную информацию и выйти из программы.

-B, –databases

Проверить несколько баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных.

–default-character-set=…

Установить набор символов по умолчанию.

-F, –fast

Проверить только базы данных, которые не были закрыты должным образом.

-f, –force

Продолжать даже при получении ошибки SQL.

-e, –extended

При использовании данного параметра совместно с CHECK TABLE можно быть на 100 процентов быть уверенным в целостности таблицы, хотя это и займет много времени. Если же использовать этот параметр с REPAIR TABLE, запустится расширенное восстановление таблицы, которое может потребовать не только длительного времени выполнения, но и привнесет также массу ненужных строк!

-h, –host=…

Подключиться к хосту.

-m, –medium-check

Быстрее, чем –extended-check, но находит только 99,99 процентов всех ошибок. Для большинства случаев этот вариант вполне подходит.

-o, –optimize

Оптимизировать таблицу.

-p, –password[=…]

Используемый пароль при подключении к серверу. Если пароль не указан, у пользователя запрашивается пароль с терминала.

-P, –port=…

Номер порта, используемого для подключения.

-q, –quick

При использовании данной опции совместно с CHECK TABLE предотвращается сканирование строк для корректировки неправильных связей. Это наиболее быстрый метод проверки. Если же использовать этот параметр с REPAIR TABLE, программа попытается восстановить только систему индексов. Это наиболее быстрый метод восстановления таблицы.

-r, –repair

Может исправить почти все, за исключением уникальных ключей, имеющих дубликаты.

-s, –silent

Выводить только сообщения об ошибках.

-S, –socket=…

Файл сокета, используемый для подсоединения.

–tables

Перекрывает опцию –databases (-B).

-u, –user=#

Имя пользователя MySQL, если этот пользователь в данное время не является активным.

-v, –verbose

Вывести информацию о различных этапах.

-V, –version

Вывести информацию о версии и выйти из программы.

Команды mysql

  • Посмотреть список процессов

show processlist;

  • Посмотреть и проверить привилегии пользователя

SHOW GRANTS FOR user;

  • Удалить базу

DROP DATABASE base;

Установка MySQL

Установка в CentOS

yum –enablerepo=epel –enablerepo=rpmforge install mysql-server

Настройка паролей, удаление лишнего

/usr/bin/mysql_secure_installation

Если база данных и сайт расположены на одном сервере, то есть смысл запретить вообще сетевую работу MySQL, оставив для работы только сокеты

nano /etc/my.cnf

Добавляем в [mysqld]

skip-networking

Проверяем

netstat -antpu | grep 3306


Создание пользователя, базы и добавление всех привилегий в консоли

Для начала необходимо зайти в MySQL

$ mysql -u root -p

После того, как появится приглашение уже внутри БД (mysql>), вам нужно создать базу с любым вашим названием. Например, база test

mysql> create database test;

Далее вам нужно создать пользователя и задать ему пароль. Например, пользователь user, пароль pass:

mysql> grant usage on *.* to user@localhost identified by ‘pass’;

Будьте внимательны, пользователь будет работать только локально. Если вам нужно подключаться с удаленного хоста, вместо localhost укажите IP адрес хоста.

А теперь дадим все права пользователю user на базу данных test:

mysql> grant all privileges on test.* to user@localhost;

Символ * (звездочка) указан для того, чтобы пользователь получил доступ ко всем таблицам. Если нужно использовать только отдельную таблицу (например, table1), вместо

test.*

укажите

test.table1

Если вам нужно поменять IP хоста

зайдите в БД mysql

use mysql;

Посмотрите, какой IP уже указан пользователю user

select host, user from user;

И укажите другой IP, например, 10.0.0.2:

update user set host=’10.0.0.2’ where user=’user’ and host=’localhost’;

Примените привилегии:

flush privileges;

mysql local only

Нужно, чтобы мускуль висел только локально. Нет проблем, в

[mysqld]

добавляем

bind-address = localhost

Ошибки

Checking for corrupt, not cleanly closed and upgrade needing tables

Принудительно проверяем таблички

mysqlcheck --check-upgrade --all-databases --auto-repair -u root -p

mysql_upgrade --force -u root -p

Cannot load from mysql.proc. The table is probably corrupted

mysql_upgrade -u root -p

Unknown storage engine ‘InnoDB’

/etc/init.d/mysql stop

cd /var/lib/mysql/

rm -f ib_logfile0 ib_logfile1

/etc/init.d/mysql start

Проверяем:

mysql> show engines;

1
2
3
4
5
6
7
8
9
10
11
12
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’

Решается либо правкой конфига, либо можно задать переменную на нужный момент. В конфиге можно выставить:

[mysqldump]  max_allowed_packet=500M`

Или же задать значение сразу таким образом:

SET SESSION max_allowed_packet=1073741824;

или глобально

SET GLOBAL max_allowed_packet=1073741824;

Unknown/unsupported storage engine: InnoDB

Добавляем с секцию

[mysqld]

это:

ignore-builtin-innodb 

default-storage-engine=myisam

Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

mysql_install_db

Тесты для MySQL

Как посмотреть все запросы в базе данных MySQL

Иногда возникает необходимость проверить запросы в базе данных. Конечно, это можно сделать средствами движка сайта (например, Wordpress), но это не всегда возможно. В таком случае можно включить запросы непосредственно в самой базе. Это можно сделать через конфигурацию, а вот задать и так. Я подскажу, как это сделать с помощью команд, чтобы избежать перезагрузки базы данных в ходе внесения изменений в my.cnf. Итак, зайдите на ваш сервер. Это можно сделать и в PHPMyAdmin, а можно и в консоли:

mysql -u root -p

Отдаём команду следующего вида

set global general_log_file='/tmp/mysql_query.log';

То есть мы указываем куда писать все логи. Дальше включаем логирование

set global general_log = 1;

Учтите, что если ваш сервер достаточно нагружен, то лог будет расти очень быстро. Поэтому, подождав немного или проделав нужны операции, можно выключить логирование:

set global general_log = 0;

И дальше смотрите файл /tmp/mysql_query.log и выискивайте нужное для себя.


Мониторим MySQL

Нужно запустить проверку на тот случай, если один запрос будет слишком долго блокировать таблицу. Для этого в Cron добавим ежеминутную проверку таким скриптиком:

1
2
3
4
5
6
7
8
9
10
11
#!/bin/sh
# cmd und variabel
 CMND=`mysql --user=root --password=123123 -e "SHOW PROCESSLIST;"`
 TIME_Q=`echo $CMND | awk '{print $14 }' | tr -d %`
 ID_Q=`echo $CMND | awk '{print $9 }' | tr -d %`
 DB_Q=`echo $CMND | awk '{print $12 }' | tr -d %`
NEED_Q='100'
# die Abfahrt der Post
if [ "$TIME_Q" -gt "$NEED_Q" ]; then
 echo "ID" $ID_Q, "DB" $DB_Q, "time" $TIME_Q | mail -s "Alarm! Lock too big!" wikiadmin@wikiadmin.net
fi

Здесь нужно указать данные по базе, а так же выставить параметр NEED_Q, в котором указано максимальное время. На почту будет приходить ID, имя базы и время выполнения.

Если нужен тот же скрипт для Nagios, достаточно слегка видоизменить этот и получаем:

1
2
3
4
5
6
7
8
9
10
11
#!/bin/sh
# cmd und variabel
 CMND=`mysql --user=root --password=123123 -e "SHOW PROCESSLIST;"`
 TIME_Q=`echo $CMND | awk '{print $14 }' | tr -d %`
NEED_Q='100'
#
if [ "$TIME_Q" -gt "$NEED_Q" ]; then
echo "Long lock"
exit 2
fi
echo "Ok"

Создание пароля для пользователя root

При первоначальной установке MySQL пароля у административного пользователя нет.

mysqladmin -u root password 'НОВЫЙ_ПАРОЛЬ'

Смена ранее установленного пароля у пользователя root

mysqladmin -u root -p'ТЕКУЩИЙ_ПАРОЛЬ' password 'НОВЫЙ_ПАРОЛЬ'

Как изменить пароль у пользователя с помощью SQL-команды

  • Нужно зайти в MySQL

mysql -u root -p

  • Зайти в базу mysql, содержащую пароли.

use mysql;

  • Меняем пароль у пользователя user на пароль new_password

UPDATE user SET password=PASSWORD("new_password") where User='user';

  • Применяем привилегии:

flush privileges;

  • Выходим:

quit;


См. так же: