This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
сервис_mysql [2017/07/17 09:11] val [Начиная версии 5.7] |
сервис_mysql [2021/03/12 10:34] val |
||
---|---|---|---|
Line 3: | Line 3: | ||
===== Установка ===== | ===== Установка ===== | ||
- | ==== Ubuntu ==== | + | ==== Debian/Ubuntu ==== |
* на все вопросы о пароле пользователя root нажимаем "Enter" | * на все вопросы о пароле пользователя root нажимаем "Enter" | ||
+ | * ставится 3-4 минуты | ||
<code> | <code> | ||
- | root@server:~# apt install mysql-server | + | debian10# apt install default-mysql-server |
+ | |||
+ | server# apt install mysql-server | ||
</code> | </code> | ||
==== FreeBSD ==== | ==== FreeBSD ==== | ||
<code> | <code> | ||
- | freebsd10# pkg install mysql56-server | + | freebsd11# pkg install mysql56-server |
- | [server:~] # service mysql-server rcvar | + | [server:~] # sysrc mysql_enable=yes |
[server:~] # service mysql-server start | [server:~] # service mysql-server start | ||
Line 31: | Line 34: | ||
shell> cd C:\xampp\mysql\bin\ | shell> cd C:\xampp\mysql\bin\ | ||
</code> | </code> | ||
- | ===== Смена пароля пользователя root ===== | + | ===== Настройка кодировки UTF-8 ===== |
- | ==== Начиная версии 5.7 ==== | + | <code> |
+ | # cat /etc/mysql/conf.d/utf8.cnf | ||
+ | </code><code> | ||
+ | [mysqld] | ||
+ | collation_server=utf8_general_ci | ||
+ | character_set_server=utf8 | ||
+ | init_connect='SET collation_connection = utf8_general_ci' | ||
+ | init_connect='SET NAMES utf8' | ||
+ | skip-character-set-client-handshake | ||
+ | </code> | ||
+ | ===== Управление параметрами сервера ===== | ||
+ | |||
+ | <code> | ||
+ | # cat /etc/mysql/conf.d/my-custom-settings.cnf | ||
+ | </code><code> | ||
+ | [mysqld] | ||
+ | sql_mode="" | ||
+ | innodb_strict_mode=OFF | ||
+ | </code><code> | ||
+ | # service mysql restart | ||
+ | </code> | ||
+ | |||
+ | ===== Смена пароля пользователя root ===== | ||
+ | |||
+ | * [[https://mariadb.com/kb/en/authentication-plugin-unix-socket/|Authentication Plugin - Unix Socket]] | ||
* [[https://www.rosehosting.com/blog/how-to-reset-your-mariadb-root-password/|How to reset your MariaDB root password?]] | * [[https://www.rosehosting.com/blog/how-to-reset-your-mariadb-root-password/|How to reset your MariaDB root password?]] | ||
+ | ==== MariaDB ==== | ||
+ | |||
+ | <code> | ||
+ | MariaDB [(none)]> ALTER USER root@localhost IDENTIFIED VIA mysql_native_password; | ||
+ | |||
+ | MariaDB [(none)]> SET PASSWORD = PASSWORD('ewsgh3sd'); | ||
+ | |||
+ | # service mysql restart | ||
+ | </code> | ||
+ | |||
+ | ==== MySQL, начиная c версии 5.7 ==== | ||
+ | |||
* [[https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/|Change user password in MySQL 5.7 with “plugin: auth_socket”]] | * [[https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/|Change user password in MySQL 5.7 with “plugin: auth_socket”]] | ||
Line 41: | Line 80: | ||
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678'; | ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678'; | ||
</code> | </code> | ||
- | ==== До версии 5.7 ==== | + | ==== MySQL, до версии 5.7 ==== |
<code> | <code> | ||
# mysqladmin --user=root password somepassword | # mysqladmin --user=root password somepassword | ||
Line 61: | Line 100: | ||
==== Из командной строки ==== | ==== Из командной строки ==== | ||
<code> | <code> | ||
- | # mysql billing -u billing --password=XXX -B --skip-column-names -e "select login, pass from accounts where uid=YYY" | + | # mysql billing -u billing --password=XXX -B --skip-column-names -e "select login, pass from accounts where uid=YYY" | tr '\t' ';' |
</code> | </code> | ||
===== Управление базами данных и пользователями ===== | ===== Управление базами данных и пользователями ===== | ||
Line 113: | Line 152: | ||
</code> | </code> | ||
+ | ===== Отладка ===== | ||
+ | |||
+ | ==== Протоколирование всех запросов ==== | ||
+ | <code> | ||
+ | # cat /etc/mysql/mariadb.conf.d/50-server.cnf | ||
+ | </code><code> | ||
+ | ... | ||
+ | [mysqld] | ||
+ | ... | ||
+ | general-log | ||
+ | general-log-file=/var/log/mysql/q.log | ||
+ | log-output=file | ||
+ | ... | ||
+ | </code> | ||
+ | |||
+ | ===== adminer ===== | ||
+ | |||
+ | * [[https://www.adminer.org/]] | ||
+ | |||
+ | <code> | ||
+ | # apt install adminer | ||
+ | |||
+ | # cat /etc/apache2/sites-available/000-default.conf | ||
+ | </code><code> | ||
+ | ... | ||
+ | Alias /adminer /usr/share/adminer/adminer/ | ||
+ | ... | ||
+ | </code> | ||
+ | |||
+ | ===== phpmyadmin ===== | ||
+ | |||
+ | * Выбрать apache2, все остальное по умолчанию | ||
+ | |||
+ | <code> | ||
+ | # apt install phpmyadmin | ||
+ | |||
+ | # ls -l /etc/apache2/conf-available/phpmyadmin.conf | ||
+ | |||
+ | # less /etc/phpmyadmin/apache.conf | ||
+ | </code><code> | ||
+ | http://server.corpX.un/phpmyadmin | ||
+ | </code> | ||
===== Управление сбоями ===== | ===== Управление сбоями ===== | ||
- | * Можно в консоли сервера запустить mysqlcheck -r, он автоматически починит проблемные таблицы во всех базах. | ||
* [[http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html|SHOW PROCESSLIST Syntax]] | * [[http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html|SHOW PROCESSLIST Syntax]] | ||
+ | |||
+ | <code> | ||
+ | 1. Настраиваем mysql на работу в режиме чтения | ||
+ | |||
+ | # nano /etc/my.cnf | ||
+ | ... | ||
+ | innodb_force_recovery=4 | ||
+ | ... | ||
+ | |||
+ | 2. Запускаем из командной строки | ||
+ | |||
+ | /usr/libexec/mysqld --skip-grant & | ||
+ | |||
+ | 3. Бекапим все базы данных | ||
+ | |||
+ | mysqldump --all-databases --add-drop-table > dumpfile.sql | ||
+ | |||
+ | 4. Останавливаем сервер | ||
+ | |||
+ | killall mysqld | ||
+ | |||
+ | 5. Убираем настройку работы в режиме чтения | ||
+ | |||
+ | nano /etc/my.cnf | ||
+ | ... | ||
+ | #innodb_force_recovery=4 | ||
+ | ... | ||
+ | |||
+ | 6. Удаляем/перемещаем каталог со старыми базами данных | ||
+ | |||
+ | mv /var/lib/mysql /var/lib/mysql_ | ||
+ | |||
+ | 7. Создаем новый каталог для баз данных с нужными правами | ||
+ | |||
+ | mkdir /var/lib/mysql/ | ||
+ | chown mysql:mysql /var/lib/mysql/ | ||
+ | |||
+ | 8. Создаем системные базы данных в новом каталоге | ||
+ | |||
+ | /usr/libexec/mysqld --initialize --user=mysql | ||
+ | |||
+ | 9. Запускаем сервис | ||
+ | |||
+ | /etc/init.d/mysqld start | ||
+ | 10. Восстанавливаем остальные базы данных из бекапа | ||
+ | |||
+ | mysql < dumpfile.sql | ||
+ | </code> |