MySQL

Материал из wiki.p3.ru
Версия от 09:00, 14 декабря 2023; Eternity (обсуждение | вклад) (Новая страница: «== Общее == * Базы MySQL храняться в <tt>/var/lib/mysql/db</tt> * [http://phpmyadmin.ru phpMyAdmin] - Работа с MySQL через web интерфейс == Установка из репозиториев Debian == * Устанавливаем MySQL сервер: # apt-get install mysql-server * После установки он автоматически запускается. Вручную это можно сделать...»)
(разн.) ← Предыдущая версия | Текущая версия (разн.) | Следующая версия → (разн.)

Общее

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

Алгоритм:

  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

Шаблон:Командная строка

  • Переконвертируем эти таблицы в 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

Ссылки

Источник — https://wiki.p3.ru/index.php?title=MySQL&oldid=604