MySQL 5.7 : Сброс пароля root

mkdir /var/run/mysqld && chown mysql:mysql /var/run/mysqld

mysqld_safe --skip-grant-tables &

mysql> use mysql
mysql> update user set authentication_string=PASSWORD("passw0rd") where User='root';

MySQL : ошибка репликации : Last_SQL_Errno: 1396

Настроено по документации ruhighload.com репликация mariadb 10.3
На исходном сервере запущено в docker, на slave - на localhost

https://ruhighload.com/%D0%9A%D0%B0%D0%BA+%D0%BD%D0%B0%D1%81%D1%82%D1%80%D0%BE%D0%B8%D1%82%D1%8C+mysql+master-slave+%D1%80%D0%B5%D0%BF%D0%BB%D0%B8%D0%BA%D0%B0%D1%86%D0%B8%D1%8E%3F

Репликация у клиента остановилась с ошибкой:
Last_SQL_Errno: 1396
Last_SQL_Error: Error 'Operation ALTER USER failed for 'username'@'172.17.0.1'' on query. Default database: ''. Query: 'ALTER USER 'username'@'172.17.0.1' IDENTIFIED BY 'passwd''

Решение: прописать на slave в [mysqld]
slave-skip-errors = 1396

Причина: нет такого пользователя на slave, данный запрос вручную также не проходил
Пользователь репликации не может управлять пользователями БД

mariadb в docker запустить

docker run --hostname 'mariadb-10.3' --name 'mariadb-10.3' -v '/var/lib/mariadb-10.3:/var/lib/mysql' -v '/etc/ispmysql/mariadb-10.3:/etc/mysql/conf.d' -p '127.0.0.1:3310:3306' --restart=always -d mariadb

Посчитать объем баз данных MySQL

Посчитать объем баз

 

SELECT table_schema "Data Base Name",

sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;
 
Размеры всех таблиц в БД

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;

Студент 20 с небольшим лет попал под автобус, и — насмерть. Очнулся на том свете. В кресле напротив увидел кого-то, похожего на человека, но светящегося изнутри инфракрасным светом. -Где я? — Ты в аду, сынок. Но не бойся заранее, здесь на самом деле намного лучше, чем описывают там, на земле. Вот ты в жизни курил? Студент опускает глаза: — Да, курил. — Ну значит, по понедельникам для тебя будет праздник. Это у нас день курения. Выбор — огромный, от трубки и махорки до дорогих сигар и кальяна. Курим весь понедельник, до поздней ночи. И пофигу астма и рак — ты ведь уже умер! А выпить ты любил? Студент опускает голову: — Не без этого... — У-у! Тогда готовься к праздникам по вторникам! Этот день мы пьем весь, с самого утра, от пива и легких вин до самых крепких напитков: самогона и спирта. И плевать на печень: ты ведь уже умер! А как насчет наркотиков? Студент краснеет: — Да, бывало! — Что ж, повеселишься по средам! У нас это день наркотиков, от самых легких, до героина и тяжелой синтетики. Хочешь — колись, хочешь — кури, и не бойся ни ломки, ни передоза, ни ментов — ты ведь уже умер! А с женщинами? Студент оживляется: — А как же! — Тогда оторвешься по четвергам! Это у нас день повального ceкcа. Дело в том, что женщин в аду в 10 раз больше, чем мужчин, и у тебя будет супервыбор! А о сифилисе там всяком даже не вспоминай — ты ведь уже умер! А не был ли ты голубым? Студент вскидывается: -Нет, что вы! — М-да-ссс... Вот пятницы тогда станут для тебя настоящим адом! anekdotov.net

mariadb 5.5 : centos 7 : Unknown collation: 'utf8mb4_unicode_520_ci

Не удалось восстановить базу данных из резервной копии. Процесс завершился с ошибкой: 'ERROR 1273 (HY000) at line 104: Unknown collation: 'utf8mb4_unicode_520_ci' '

sed -i 's/utf8mb4_unicode_520_ci/utf8_general_ci/' a0129493_wor00.sql
sed -i 's/utf8mb4/utf8/' a0129493_wor00.sql
sed -i 's/utf8mb4_unicode_ci/utf8_unicode_ci/' a0129493_wor00.sql

MySQL : ошибка The used table type doesn't support FULLTEXT indexes при импорте дампа

FULLTEXT indexes в InnoDB поддерживаются начиная с версии MySQL 5.6
Варианты:
- Обновить MySQL до версии 5.6
- Исправить дамп базы указав в нем engine = MyISAM

MySQL server has gone away при импорте базы данных

[mysqld]
max_allowed_packet = 256M

MySQL создать пользователя и выдать доступ к базе данных

Как создать пользователя в MySQL сервере и выдать права пользователю на базу данных вручную


MariaDB [(none)]> CREATE USER 'username'@'localhost' IDENTIFIED BY 'pas$w0rd';

MariaDB [(none)]> GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost';

MariaDB [(none)]> FLUSH PRIVILEGES;

MariaDB [(none)]> GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'localhost';

MySQL 5.6 centos 6

yum install https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
vi /etc/yum.repos.d/mysql-community.repo

оставляем нужный репозиторий, 5.7 отключаем yum update

может потребоваться

rpm -e --nodeps compat-mysql51-5.1.54-1.el6.remi.x86_64

mysql_upgrade

mysql reset password сброс пароля mysql

service mariadb stop

mysqld_safe --skip-grant-tables &

MariaDB [mysql]> update mysql.user set password=password('9kWNxHUc3x') where user='root';
MariaDB [mysql]> flush privileges;

pkill mysqld

service mariadb start

Для 5.7 надо использовать alter user https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

mysql 5.6 (5.7) debian

https://dev.mysql.com/downloads/repo/apt/
https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

wget https://dev.mysql.com/get/mysql-apt-config_0.8.1-1_all.deb
dpkg -i mysql-apt-config_0.8.1-1_all.deb

отвечаем какую версию хотим ставить

apt-get update
apt-get install mysql-server

Для других пакетных менеджеров (yum) аналогично — см https://dev.mysql.com/downloads/repo/yum/

Простой однострочник заливки sql sqldump в mysql

ls *.sql | while read dbname; do mysql $(basename $dbname .sql) < $dbname ; done

Массово конвертируем все таблицы в InnoDB

Бэкапим базы
service mariadb stop
cp -Rp /var/lib/mysql /var/lib/mysql.bak
service mariadb start

Делаем список alter table который все поконвертирует
mysql -e 'show tables in blog;' | tail -n +2 | xargs -I '{}' echo "alter table {} engine=InnoDB;" > alter-table.sql

Конвертируем
mysql blog < alter-table.sql

Лечим таблицы MySQL

Легкий случай
mysqlcheck -Ar

Тяжелый случай (при остановленном MySQL)
find /var/lib/mysql -name '*.MYI' |xargs myisamchk -o -r

mariadb лимит на файлы поднять centos 7 systemd

mkdir /etc/systemd/system/mariadb.service.d/

printf '[Service]\nLimitNOFILE = infinity\nLimitMEMLOCK = infinity\n' >> /etc/systemd/system/mariadb.service.d/limits.conf

systemctl daemon-reload
systemctl restart mariadb

Timeouts в MySQL

Добавляем в /etc/my.cnf чтобы завершались залипшие запросы

connect_timeout=600
wait_timeout=600
interactive_timeout=600

wordpress поменять адрес url сайта

WordPress хранит в базе адрес сайта в таблице wp_options и следовательно надо чтобы там было правильно прописано иначе будет кривой редирект, прямо адрес прописан с протоколом

update wp_options set option_value='http://example.com' where option_name='home';
update wp_options set option_value='http://example.com' where option_name='siteurl';

Также может потребоваться правка в wp-config

define('WP_HOME','http://example.com');
define('WP_SITEURL','http://example.com');

Настройка MySQL 5.7 на Ubuntu 16.04 под Bitrix

В MySQL 5.7 по-умолчанию включен innodb_file_per_table=1 и с ним вы нормальных «попугаев» по базе данных не получите

Поэтому первым делом делаем дамп базы, выключаем это в /etc/mysql/my.cnf и заливаем обратно

Ниже приведен конфиг на котором только что поставленный битрикс на core i5 выдает под 100 попугаев

root@ubuntu:~# cat /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
sql_mode = ""
performance-schema = false
table_open_cache = 1024
thread_cache_size = 4
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 128M
max_heap_table_size = 128M
#innodb_file_per_table = 0
innodb_buffer_pool_size = 1024M
innodb_flush_log_at_trx_commit = 2
#innodb_buffer_pool_instances = 2
innodb_flush_method = O_DIRECT
transaction-isolation = READ-COMMITTED
innodb-strict-mode = OFF

Больше там практически ничего не надо крутить что бы ни говорили различные тюнеры — приведет только к потери производительности, можно только увеличивать innodb_buffer_pool_size если база большая и памяти свободной много

Также требуется увеличить лимиты на число открытых файлов (иначе table_open_cache может просто не отрабатывать)

mkdir /etc/systemd/system/mysql.service.d/
printf '[Service]\nLimitNOFILE = infinity\nLimitMEMLOCK = infinity\n' >> /etc/systemd/system/mysql.service.d/limits.conf

И перезапустить mysql
systemctl daemon-reload
systemctl restart mysql

Также в рекомендую менять только
innodb_buffer_pool_size — для InnoDB
key_buffer_size — для MyISAM
Чтобы база входила в оперативную память, но при этом значение это должно быть меньше чем всего ram на сервере
Настройки всего остального в MySQL часто приносят прямо противоположный результат

Обновить MySQL 5.1 до 5.5 в CentOS 6 не трогая PHP из REMI

Используйте пакет compat-mysql51 https://blog.remirepo.net/pages/English-FAQ#mysql


rpm -e --nodeps mysql-libs

yum install http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

yum --enablerepo=remi install compat-mysql51 mysql mysql-server

service mysqld restart
mysql_upgrade

Разрешить root логин в PhpMyAdmin в Ubuntu 16.04 в MySQL 5.7

Разрешить

update mysql.user set plugin='mysql_native_password' where user='root';
flush privileges;

Запретить

update mysql.user set plugin='auth_socket' where user='root';
flush privileges;

Поговаривают, что при этом авторизоваться после этого не удается и надо сбрасывать пароль, гугл предлагает просто переставить сразу пароль таким образом

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

См также https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html