MySQL: основные команды, бекап, bash скрипт, сброс root пароля

Простой скрипт, который локально на сервере бекапит сайт и данные в домашнюю папку пользователя и хранит копии за последние 7 дней. Можно быстро сохранить сайт изменив пути в скрипте и использовать для переноса сайта на новый хостинг.

В данном примере имя базы данных: site_base, файлы располагаются по адресу /var/www/site-dir.

#!/bin/bash

# Путь к папке с архивами
backup_dir="/home/user/back-up"

#Шаблон для создаваемого архива с датой создания архива
backup_filename=sql-site-`date +%Y-%m-%d`

#Полное имя архива
archive_filename_s=$backup_filename.tar.gz

#Срок хранения архивов
Backup_days=7

echo "Создаем дампы базы"
mysqldump -uroot -hlocalhost -pPASS --routines --triggers --hex-blob --single-transaction --skip-lock-tables  site_base > $backup_dir/$backup_filename.sql

echo "Архивируем SQL и файлы сайта"
tar -C $backup_dir -czf $backup_dir/$archive_filename_s $backup_dir/$backup_filename.sql
tar -czvf $backup_dir/files-backup-`date +%Y-%m-%d`.tar.gz /var/www/site-dir
#Удаляем дамп базы
rm $backup_dir/$backup_filename.sql

echo "Удаляем архивы старше "$Backup_days" дней"
find $backup_dir/sql-site-* -mtime +$Backup_days -exec rm -v {} \;
find $backup_dir/files-backup-* -mtime +$Backup_days -exec rm -v {} \;

Восстанавливаем дамп MySQL базы следующим образом

mysql -uroot -pPASS site_base < dumpname.sql

Либо, можно разархивировать сразу в процессе:

gunzip < dumpfile.sql.gz | mysql -uroot -pPASS site_base

Бонусом опишу несколько полезных команд для работы с пользователями и базами
Посмотреть список пользователей с хостами:

SELECT User,Host FROM mysql.user;

Посмотреть права выбранного пользователя:

SHOW GRANTS FOR root@localhost;

Удалить пользователя:

DROP USER user@localhost;

Создать базу:

CREATE DATABASE dbname COLLATE utf8_general_ci;

Создать пользователя с правами для полного доступа к базе:

GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Лишить пользователя прав доступа к базе:

REVOKE ALL ON dbname.* FROM 'user'@'localhost';

Обновить права:

FLUSH PRIVILEGES;

Восстановление root пароля

service mysqld stop
mysqld_safe --skip-grant-tables &
mysql
mysql> UPDATE mysql.user SET Password=PASSWORD('secret') WHERE User='root';
mysql> FLUSH PRIVILEGES;
service mysqld restart

Перенос каталога с данным (data directory)

service mysqld stop
mkdir -p /path/new/dir
sudo chown -R mysql:mysql /path/new/dir
sudo cp -R -p /var/lib/mysql /path/new/dir
sudo nano /etc/mysql/my.cnf
[mysqld]
datadir=/path/new/dir/mysql
service mysqld start

Статистика mysql

SHOW DATABASES; - список баз данных
SHOW TABLES [FROM db_name]; -  список таблиц в базе 
SHOW COLUMNS FROM таблица [FROM db_name]; - список столбцов в таблице
SHOW CREATE TABLE table_name; - показать структуру таблицы в формате "CREATE TABLE"
SHOW INDEX FROM tbl_name; - список индексов
SHOW GRANTS FOR user [FROM db_name]; - привилегии для пользователя.

SHOW VARIABLES; - значения системных переменных
SHOW [FULL] PROCESSLIST; - статистика по mysqld процессам
SHOW STATUS; - общая статистика
SHOW TABLE STATUS [FROM db_name]; - статистика по всем таблицам в базе

Посмотреть размер базы

SELECT SUM( data_length + index_length ) AS 'size'
FROM information_schema.TABLES
WHERE table_schema = 'db_name'
LIMIT 1;

Удалить mysql полностью с базами и настройками:

sudo systemctl stop mysql
sudo apt purge mysql-*
sudo apt autoremove
sudo rm -Rf /var/lib/mysql/
sudo rm -Rf /etc/mysql/
sudo rm -rf /var/log/mysql
sudo deluser --remove-home mysql
sudo delgroup mysql

Установить mysql на ubutntu:

sudo apt install mysql-server
sudo mysql_secure_installation

Смотрим и меняем способ аутентификации для root с auth_socket на mysql_native_password:

SELECT user,authentication_string,plugin,host FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;
SELECT user,authentication_string,plugin,host FROM mysql.user;