Создание дампа

mysqldump –all-databases > dump.sql – дамп всех баз
mysqldump –databases db1 db2 db3 > dump.sql – дамп нескольких указанных бд
mysqldump -u USER -pPASSWORD DATABASENAME > dump.sql – дамп
mysqldump -u USER -pPASSWORD DATABASENAME | gzip > /path/to/outputfile.sql.gz – дамп с упаковкой в gzip
mysqldump -u USER -pPASSWORD DATABASENAME | gzip > `date+/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz` – дамп с упаковкой в gzip и датой создания
mysqldump –no-data – u USER -pPASSWORD DATABASENAME > /path/to/file/schema.sql – копируем структуру БД без данных
mysqldump -u USER -pPASSWORD DATABASENAME TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql – если нужно сделать дамп только одной или нескольких таблиц

Пример бекапа в кроне:
0 0 * * * /usr/bin/mysqldump -u root -pPASSWORD DBNAME | gzip > /home/user/backup_mysql/DBNAME_`date “+\%d-\%m-\%Y_\%H:\%M:\%S”`.sql

Восстановление из дампа

mysql -u USER -pPASSWORD DATABASENAME < /path/to/dump.sql – восстановление БД из дампа
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASENAME – восстановление БД из дампа в архиве .gz
mysql -u USER -pPASSWORD < /path/to/dump.sql – восстановление нескольких БД из дампа

Восстановление из дампа в консоли MySQL

create database mydb;
use mydb;
source /path/to/db_backup.dump;

Консоль MySQL

mysql -u root -p – залогиниться в консоль mysql
show databases; – показать все БД
create database mydb; – создать БД mydb
show status;
show processlist;

Создание пользователя

CREATE USER ‘USERNAME’@’localhost’ IDENTIFIED BY ‘USERPASSWORD’;
GRANT ALL PRIVILEGES ON DBNAME . * TO ‘USERNAME’@’localhost’;
FLUSH PRIVILEGES;
SELECT User FROM mysql.user; – посмотреть всех пользователей

Общие

mysqlshow -u USER -pPASSWORD – просмотр списка всех БД
mysqladmin -u USER -pPASSWORD create NEWDATABASE – создать БД NEWDATABASE
mysqlshow -u USER -pPASSWORD DATABASENAME – список всех таблиц БД DATABASENAME

Остальное полезное

du -sh /var/lib/mysql | sort -gr – узнать размер всех БД
Запрос выведет размер указанной БД:

SELECT table_schema AS "DATABASE_NAME", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Размер в Мб" FROM information_schema.TABLES GROUP BY table_schema

mytop – удобный диспетчер задач для MySQL, как htop, в нем можно убивать процессы в MySQL

В крон добавить 2 задания:

20 1 * * * /usr/bin/mysqlcheck -u root --auto-repair --check --all-databases >/dev/null 2>&1
40 1 * * * /usr/bin/mysqlcheck -u root --auto-repair --optimize --all-databases >/dev/null 2>&1

Для таблиц в InnoDB при создании дампа надо добавлять –single-transaction, это гарантирует целостность данных бекапа. Для таблиц MyISAM это не актуально, ибо они не поддерживают транзакционность.
В большинстве случаев лучше использовать движок InnoDB.

MyISAM InnoDB
Полнотекстовый поиск + > 5.6.4
Транзакции +
Блокировка записи Таблицы Строки

MyISAM стоит использовать, если нужен полнотекстовый поиск до версии 5.6. Также этот движок подойдет, когда в таблице очень мало записей и большое количество чтений.

Во всех остальных случаях нужно использовать InnoDB.

Важно под каждую базу на боевом сервере создавать своего пользователя.

Кодировка базы может быть любой, если она UTF8.

В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli.

Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов:

ssh -fNL LOCAL_PORT:localhost:3306 [email protected]_HOST

Пользуйтесь и дополняйте в комментариях 🙂

Поделиться