MySQL
Общее
- Базы MySQL храняться в /var/lib/mysql/db
- phpMyAdmin - Работа с MySQL через web интерфейс
Установка из репозиториев Debian
- Устанавливаем MySQL сервер:
# apt-get install mysql-server
- После установки он автоматически запускается. Вручную это можно сделать выполнив:
# /etc/init.d/mysql start
- Можно посмотреть как он запустился в /var/log/syslog. Подсоединяемся к серверу:
# mysql -u root mysql
- Меняем пароль администратора и выходим:
mysql> UPDATE user SET Password=PASSWORD('new_pass') WHERE user='root';
mysql> quit
- Перезагружаем сервер:
# /etc/init.d/mysql reload
- Подсоединяемся к серверу ещё раз, только теперь с паролем:
# mysql -u root -p mysql
Перенос БД (Резервное копирование и восстановление)
Выгрузить БД можно с помощью phpMyAdmin или mysqldump:
# mysqldump -u root -p DBName > mydb.sql
Чтобы выгрузить все базы данных используйте параметр --all-databases вместо DBName.
Если mysqldump не запускается, то нужно указать полный путь до этого файла, который можно узнать командой:
# find / -name mysqldump /usr/local/mysql-3.23.54a-pc-linux-i686/bin/mysqldump
Чтобы загрузить базу, нужно сначала создать пустую, а для надёжности предварительно удалить старую:
# mysql -u root -p mysql> drop database if exists DBName; mysql> create database DBName; mysql> quit
а затем в неё загрузить данные:
# mysql -u root -p DBName < mydb.sql
Если для пользователя root в MySQL нет пароля, то параметр -p не нужен, также можно задать пароль явным образом, только без пробела, например -pPassWord. Далее нужно убедиться, что в новой базе есть тот пользователь, от имени которого происходило обращение к базе. Если такого нет, то простой способ дать все привилегии одному пользователю:
mysql> GRANT ALL PRIVILEGES ON dbName.* TO UserName@localhost IDENTIFIED BY 'UserPassword';
Перенос пользователей с правами
Для автоматизированного переноса пользователей можно использовать специальную утилиту pt-show-grants из набора утилит Percona Toolkit. Утилита pt-show-grants работает как с Percona Server, так и с MariaDB или MySQL, любой версии.
Чтобы вручную создать в точности такого же пользователя как на старом месте с соответствующими правами, выгружаем базу mysql и выбираем все строки, касающиеся этого пользователя:
# mysqldump -u root -p mysql > mysqldb.sql
В файле mysqldb.sql останется приблизительно следующее:
INSERT INTO db VALUES ('%','DBName','UserName','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO host VALUES ('localhost','UserName','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('localhost', 'UserName', '','N','N','N','N','N','N','N','N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0);
При переходе с одной версии MySQL на другую нужно учитывать, что количество параметров скорее всего будет разным, поэтому команду INSERT придётся подредактировать. Также может изменится алгоритм шифрования паролей. Сколько именно параметров можно узнать выполнив:
mysql> SHOW COLUMNS FROM db;
Можно указать только интересующие поля, а остальные примут значение по умолчанию:
INSERT INTO `user` (`Host`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`) VALUES ('localhost','stacc','Y','Y','Y','Y');
Загружаем базу командой:
# mysql -u root -p mysql < mysqldb.sql
Команды
Команды ОС
| mysql -u root -p12345 postfix < DB.sql | пакетное выполнение SQL выражений для базы данных postfix |
| /usr/bin/mysqlcheck -a DBName или |
Проверка целостности БД |
| mysqldump -u UserName -p DBName > mydb.sql | Выгрузка (дамп) базы MySQL |
| mysqldump --no-data -u UserName -p DBName > schema.sql | Выгрузка только структуры базы MySQL |
| gzip > mydb.sql.gz | Выгрузка со сжатием |
| mysql -u UserName -p DBName < mydb.sql | Загрузка БД |
gunzip < mydb.sql.gz | mysql -u UserName -p DBName |
Распаковка и загрузка |
Команды MySQL
| SHOW DATABASES; | показать все имеющиеся базы данных |
| SHOW TABLES; | показать таблицы в текущей БД |
| SHOW COLUMNS FROM user; | показать поля таблицы user |
| CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci; | создать базу данных db_name, установить кодировку таблиц utf-8 и способ описания порядка букв для сортировок (collations) выбираем general_ci |
| USE mysql; | перейти в БД mysql |
| SELECT host,user FROM user; | показать поля host, user таблицы user |
| DROP TABLE transport; | удалить таблицу transport |
| DELETE FROM user WHERE user='acct' AND host='%'; | удалить конкретную запись |
| UPDATE db SET Delete_priv='Y' WHERE user='acct'; | изменить значение поля в записи |
| FLUSH PRIVILEGES; | применить внесённые изменения |
| ALTER TABLE mailbox ADD (uid int(10) unsigned DEFAULT '107' NOT NULL); | добавляем в таблицу mailbox обязательное поле uid со значением по умолчанию = 107 |
| ALTER TABLE mailbox DROP uid; | удаляем поле uid из таблицы mailbox |
| GRANT ALL PRIVILEGES ON DBName.* TO UserName@localhost IDENTIFIED BY '12345'; | дать все права на базу DBName пользователю UserName, подключающемуся с localhost с паролем 12345 (создаёт пользователя, если его нет)
Если получаем ошибку: ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement то выполняем команду: FLUSH PRIVILEGES; |
| USE mysql; SELECT user,host,db FROM db; |
показать какие пользователи, откуда и к каким базам могут подключаться |
| CHECK TABLE TableName; | проверить целостность таблицы TableName |
| REPAIR TABLE TableName; | исправить целостность таблицы TableName |
- Внести запись в базу данных из bash можно таким образом (определив, конечно, переменные):
<syntaxhighlight lang=bash>
echo "INSERT INTO table_name (\`in\`,\`out\`) VALUES ('$delta_in', '$delta_out');" > $tmp_dir/temp.sql
`mysql -u$db_user -p$db_pass $db_name < $tmp_dir/temp.sql`
</syntaxhighlight>
Как сбросить пароль root в MySQL
Алгоритм:
- Останавливаем сервер
- Запускаем его в режиме без проверки привилегий
- Заходим рутом не указывая пароль
- Меняем пароль root
- Выходим
- Перезапускаем сервер
Команды:
# /etc/init.d/mysqld stop
# mysqld_safe --skip-grant-tables &
# mysql -u root mysql
mysql> update user set Password=PASSWORD('новый_пароль') where User='root';
mysql> quit
# /etc/init.d/mysqld restart
Как сменить префикс в таблицах MySQL
см спец статью и скрипт MySQL_tables_rename
Как переконвертировать таблицы из MyISAM в InnoDB
- Выясняем какие таблицы имеют кодировку MyISAM
- Переконвертируем эти таблицы в InnoDB:
Как узнать размер БД и таблиц
- Из операционной системы:
- Из MySQL. Нужно зайти пользователем root:
и выполнить команду: <syntaxhighlight lang=sql>SELECT table_schema AS "Имя БД", SUM(data_length + index_length) AS "Размер в байтах" FROM information_schema.TABLES GROUP BY table_schema;</syntaxhighlight>
Чтобы отобразить размер в мегабайтах вторую струку нужно заменить на: <syntaxhighlight lang=sql>ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Размер в Мб"</syntaxhighlight>
Чтобы узнать размер всех таблиц всех баз данных: <syntaxhighlight lang=sql> SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC; </syntaxhighlight>
Отладка
По умолчанию MySQL не журналирует выполняемые на нём команды. Нам может это понадобиться для отладки взаимодействующих с ним приложений. У меня возникла такая необходимость при настройке Courier. Включить журналирование можно в файле /etc/init.d/mysql, закоментировав оригинальный запуск программы в секции start и добавив параметр --log:
# /usr/bin/mysqld_safe > /dev/null 2>&1 & /usr/bin/mysqld_safe --log &
Работа над ошибками
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.12' (111)
Причина: Запросы не доходят до сервера MySQL. Причин может быть множество.
Решение: в /etc/mysql/my.cnf изменить параметр bind-address со значения по умолчанию 127.0.0.1 на ip адрес интерфейса, например, внутренней сети:
bind-address = 10.0.0.12
Ссылки
- Документация на MySQL на русском
- Официальная документация на английском. В имени HTML файла указана команда по которой даётся справка, что удобно для быстрого поиска описания нужной команды.
