MySQL

Материал из СисадминВики (SysadminWiki.ru)
Перейти к: навигация, поиск

Общее

  • Базы 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

или
mysqlcheck -S /opt/zimbra/db/mysql.sock -uroot -p 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 TABLES LIKE '%user%'; показать таблицы в названиях которых есть 'user'
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
RENAME TABLE `old_name` TO `new_name`; переименовать таблицу
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'db_name' AND table_name LIKE 'quotes__%'; создать команду для удаления из БД 'db_name' всех таблиц с префиксом 'quotes__'. Эту команду нужно будет потом скопировать и запустить отдельно.
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 можно таким образом (определив, конечно, переменные): 

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`

Как сбросить пароль root в MySQL

Алгоритм:

  1. Останавливаем сервер
  2. Запускаем его в режиме без проверки привилегий
  3. Заходим рутом не указывая пароль
  4. Меняем пароль root
  5. Выходим
  6. Перезапускаем сервер

Команды: 

# /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
mysql> SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema'); +------------+-----------------+--------+ | DbName | TableName | Engine | +------------+-----------------+--------+ | my_db_name | searchindex | MyISAM | | my_db_name | sw__searchindex | MyISAM | +------------+-----------------+--------+
  • Переконвертируем эти таблицы в InnoDB:
ALTER TABLE my_db_name.searchindex ENGINE = InnoDB; ALTER TABLE my_db_name.sw__searchindex ENGINE = InnoDB;

Как узнать размер БД и таблиц

  • Из операционной системы пользователем с административными правами (root):
du -h /var/lib/mysql
  • Из MySQL. Нужно зайти пользователем root:
mysql -u root -p

и выполнить команду: 

SELECT table_schema AS "Имя БД",
SUM(data_length + index_length) AS "Размер в байтах"
FROM information_schema.TABLES
GROUP BY table_schema;

Чтобы отобразить размер в мегабайтах вторую струку нужно заменить на: 

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Размер в Мб"

Чтобы узнать размер всех таблиц всех баз данных: 

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;

Отладка

По умолчанию 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

Ссылки

Источник — «https://sysadminwiki.ru/w/index.php?title=MySQL&oldid=3222»