User Tools
сервис_mysql
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
|
сервис_mysql [2018/04/28 17:06] val [Debian/Ubuntu] |
сервис_mysql [2021/03/12 15:24] val [MariaDB] |
||
|---|---|---|---|
| Line 9: | Line 9: | ||
| <code> | <code> | ||
| - | root@server:~# apt install mysql-server | + | debian10# apt install default-mysql-server |
| + | |||
| + | server# apt install mysql-server | ||
| </code> | </code> | ||
| Line 45: | Line 47: | ||
| </code> | </code> | ||
| + | ===== Управление параметрами сервера ===== | ||
| - | ===== Смена пароля пользователя root ===== | + | <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> | ||
| - | ==== Начиная версии 5.7 ==== | + | ===== Смена пароля пользователя 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('12345678'); | ||
| + | |||
| + | # 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 56: | 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 76: | 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 144: | Line 168: | ||
| ===== adminer ===== | ===== adminer ===== | ||
| - | |||
| - | Попробовать | ||
| * [[https://www.adminer.org/]] | * [[https://www.adminer.org/]] | ||
| Line 151: | Line 173: | ||
| <code> | <code> | ||
| # apt install adminer | # apt install adminer | ||
| + | |||
| + | # cat /etc/apache2/sites-available/000-default.conf | ||
| + | </code><code> | ||
| + | ... | ||
| + | Alias /adminer /usr/share/adminer/adminer/ | ||
| + | ... | ||
| </code> | </code> | ||
| Line 163: | Line 191: | ||
| # less /etc/phpmyadmin/apache.conf | # less /etc/phpmyadmin/apache.conf | ||
| + | </code><code> | ||
| + | http://server.corpX.un/phpmyadmin | ||
| </code> | </code> | ||
| - | |||
| - | * http://server.corpX.un/phpmyadmin | ||
| - | |||
| ===== Управление сбоями ===== | ===== Управление сбоями ===== | ||
| - | * Можно в консоли сервера запустить 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> | ||
сервис_mysql.txt · Last modified: 2021/10/12 10:28 by val
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
