TTK: Новая страница: «== Общее == * Базы MySQL храняться в `/var/lib/mysql/db` * [http://phpmyadmin.ru phpMyAdmin] - Работа с MySQL через web интерфейс == Установка из репозиториев Debian == * Устанавливаем MySQL сервер: # apt-get install mysql-server * После установки он автоматически запускается. Вручную это можно сделать...»

2023-12-14T09:00:44Z

Новая страница: «== Общее == * Базы MySQL храняться в <tt>/var/lib/mysql/db</tt> * [http://phpmyadmin.ru phpMyAdmin] - Работа с MySQL через web интерфейс == Установка из репозиториев Debian == * Устанавливаем MySQL сервер: # apt-get install mysql-server * После установки он автоматически запускается. Вручную это можно сделать...»

Новая страница

== Общее ==

* Базы MySQL храняться в <tt>/var/lib/mysql/db</tt>
* [http://phpmyadmin.ru phpMyAdmin] - Работа с MySQL через web интерфейс

== Установка из репозиториев Debian ==

* Устанавливаем MySQL сервер:
# apt-get install mysql-server

* После установки он автоматически запускается. Вручную это можно сделать выполнив:
# /etc/init.d/mysql start

* Можно посмотреть как он запустился в /var/log/syslog. Подсоединяемся к серверу:
# mysql -u root mysql

* Меняем пароль администратора и выходим:
<pre>
mysql> UPDATE user SET Password=PASSWORD('new_pass') WHERE user='root';
mysql> quit
</pre>

* Перезагружаем сервер:
# /etc/init.d/mysql reload

* Подсоединяемся к серверу ещё раз, только теперь с паролем:
# mysql -u root -p mysql

== Перенос БД (Резервное копирование и восстановление) ==

Выгрузить БД можно с помощью phpMyAdmin или mysqldump:
# mysqldump -u root -p DBName > mydb.sql

Чтобы выгрузить все базы данных используйте параметр '''--all-databases''' вместо DBName.

Если mysqldump не запускается, то нужно указать полный путь до этого файла, который можно узнать командой:
<pre>
# find / -name mysqldump
/usr/local/mysql-3.23.54a-pc-linux-i686/bin/mysqldump
</pre>

Чтобы загрузить базу, нужно сначала создать пустую, а для надёжности предварительно удалить старую:
<pre>
# mysql -u root -p
mysql> drop database if exists DBName;
mysql> create database DBName;
mysql> quit
</pre>

а затем в неё загрузить данные:
# mysql -u root -p DBName < mydb.sql

Если для пользователя root в MySQL нет пароля, то параметр -p не нужен, также можно задать пароль явным образом, только без пробела, например -pPassWord. Далее нужно убедиться, что в новой базе есть тот пользователь, от имени которого происходило обращение к базе. Если такого нет, то простой способ дать все привилегии одному пользователю:

mysql> GRANT ALL PRIVILEGES ON dbName.* TO UserName@localhost IDENTIFIED BY 'UserPassword';

=== Перенос пользователей с правами ===

Для '''автоматизированного''' переноса пользователей можно использовать специальную утилиту [https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html pt-show-grants] из набора утилит [https://www.percona.com/software/database-tools/percona-toolkit Percona Toolkit]. Утилита pt-show-grants работает как с Percona Server, так и с MariaDB или MySQL, любой версии.

Чтобы '''вручную''' создать в точности такого же пользователя как на старом месте с соответствующими правами, выгружаем базу mysql и выбираем все строки, касающиеся этого пользователя:
# mysqldump -u root -p mysql > mysqldb.sql

В файле <tt>mysqldb.sql</tt> останется приблизительно следующее:
<pre>
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);
</pre>

При переходе с одной версии MySQL на другую нужно учитывать, что количество параметров скорее всего будет разным, поэтому команду INSERT придётся подредактировать. Также может изменится алгоритм шифрования паролей. Сколько именно параметров можно узнать выполнив:
mysql> SHOW COLUMNS FROM db;

Можно указать только интересующие поля, а остальные примут значение по умолчанию:
<pre>
INSERT INTO `user` (`Host`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`) VALUES ('localhost','stacc','Y','Y','Y','Y');
</pre>

Загружаем базу командой:
# mysql -u root -p mysql < mysqldb.sql

== Команды ==

=== Команды ОС ===
{| border=1 style="border-spacing:0;"
|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

|-
| mysqldump -u UserName -p DBName | gzip > mydb.sql.gz || Выгрузка со сжатием

|-
| mysql -u UserName -p DBName < mydb.sql || Загрузка БД

|-
| <code><nowiki>gunzip < mydb.sql.gz | mysql -u UserName -p DBName</nowiki></code> || Распаковка и загрузка

|}

=== Команды MySQL ===

{| border=1 style="border-spacing:0;"
|-
|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
{{Командная строка | shell |<nowiki>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 |
+------------+-----------------+--------+</nowiki>
}}
* Переконвертируем эти таблицы в InnoDB:

{{Командная строка | shell |<nowiki>ALTER TABLE my_db_name.searchindex ENGINE = InnoDB;
ALTER TABLE my_db_name.sw__searchindex ENGINE = InnoDB;</nowiki>
}}

== Как узнать размер БД и таблиц ==
* Из операционной системы:
{{Командная строка | shell |# du -h /var/lib/mysql}}

* Из MySQL. Нужно зайти пользователем root:
{{Командная строка | shell | mysql -u root -p}}

и выполнить команду:
<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. Включить журналирование можно в файле <tt>/etc/init.d/mysql</tt>, закоментировав оригинальный запуск программы в секции start и добавив параметр --log:
<pre>
# /usr/bin/mysqld_safe > /dev/null 2>&1 &
/usr/bin/mysqld_safe --log &
</pre>

== Работа над ошибками ==
<pre>
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.12' (111)
</pre>

'''Причина:''' Запросы не доходят до сервера MySQL. Причин может быть множество.

'''Решение:''' в <tt>/etc/mysql/my.cnf</tt> изменить параметр ''bind-address'' со значения по умолчанию 127.0.0.1 на ip адрес интерфейса, например, внутренней сети:
<pre>
bind-address = 10.0.0.12
</pre>

== Ссылки ==
* [http://www.opennet.ru/docs/RUS/mysqlrus Документация на MySQL на русском]
* [http://dev.mysql.com/doc/refman/5.1/en/show-character-set.html Официальная документация на английском]. В имени HTML файла указана команда по которой даётся справка, что удобно для быстрого поиска описания нужной команды.

[[Категория:Хостинг]]
[[Категория:MySQL]]

MySQL - История изменений