Восстановление MySQL таблиц

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

mysqlcheck -u root -p -A

Где опция -u определяет имя пользователя под которым выполняется подключение к MySQL, -p для запроса пароля, -A для проверки таблиц всех баз данных.

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

mysqlcheck -u root -p -r db_name

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

mysqlcheck -u root -p -r db_name table_name

Опишу возможные опции запуска.

Проверка всех баз данных и их таблиц, кроме INFORMATION_SCHEMA и performace_schema:

--all-databases, -A

Указать перечень баз можно используя опцию:

--databases, -B

Выполнить все одним запросом вместо отдельных для каждой базы:

--all-in-1, -1

Анализировать таблицы:

--analyze, -a

После проверки выполнить ремонт поврежденных таблиц:

--auto-repair

Указание адреса подключения к MySQL серверу (например если их несколько):

--bind-address=ip_address

Подключение к MySQL серверу на указанном узле:

--host=host_name, -h host_name

Директория с установками символов:

--character-sets-dir=dir_name

Проверка таблиц на наличие ошибок (выполняется по умолчанию):

--check, -c

Проверить таблицы которые были изменены с момента последней проверки или которые не были закрыты должным образом:

--check-only-changed, -C

Проверка таблиц на совместимость с текущей версией сервера:

--check-upgrade, -g

Сжатие данных передаваемым между клиентом и сервером если оба это поддерживают:

--compress

Запись и отображение отладочной информации (на тесте стандартный mysqlcheck в Ubuntu 16.04 был скомпилирован без поддержки debug):

--debug=debug_options
--debug-check
--debug-info

Указание набора символов по умолчанию:

--default-character-set=charset_name

Использование только указанного файла параметров:

--defaults-file=file_name

Указанная ниже опция делает проверку более длительной и гарантирует целостность таблиц, при восстановлении тоже займет много времени и произведет много ненужных строк:

--extended, -e

Проверить только таблицы которые не были закрыты должным образом:

--fast, -F

Продолжать даже в случае SQL ошибки:

--force, -f

Быстрее чем extended, находится 99.99% ошибок:

--medium-check, -m

Не читать файлы опций, однако файл .mylogin.cnf будет читаться:

--no-defaults

Смотрите также мою статью — Подключение к MySQL из localhost без ввода пароля.

Оптимизировать таблицы:

--optimize, -o

Указание пароля при подключении:

--password[=password], -p[password]

Номер порта TCP/IP для подключения:

--port=port_num, -P port_num

Отображение имени программы и всех параметров, которые она получает из файлов параметров:

--print-defaults

Указание протокола подключения:

--protocol={TCP|SOCKET|PIPE|MEMORY}

Быстрый метод проверки таблиц, предотвращает сканирование строк для проверки неправильных ссылок:

--quick, -q

Выполнение ремонта:

--repair, -r

Тихий режим, отображаются только ошибки:

--silent, -s

Пропустить указанную базу:

--skip-database=db_name

Указание сокета при подключении:

--socket=path, -S path

Подключение с использованием SSL:

--ssl*

Переопределить -databases или -B, все аргументы имени, следующие за опцией, рассматриваются как имена таблиц:

--tables

Указание перечня протоколов через запятую, которые разрешено использовать при зашифрованных соединениях:

--tls-version=protocol_list

При восстановление получить структуру таблицы из файла .frm:

--use-frm

Имя пользователя MySQL, которое будет использоваться при подключении:

--user=user_name, -u user_name

Подробный режим, отображается больше информации:

--verbose, -v

Отображение версии mysqlcheck и выход:

--version, -V

Смотрите также:
Установка и настройка MySQL сервера на Ubuntu

Просмотр информации о базах данных MySQL

Чтобы посмотреть информацию о базах данных MySQL и их таблицах первым делом подключимся к консоли MySQL, например под пользователем root:

mysql -u root

Или так, если необходимо ввести пароль:

mysql -u root -p

Посмотрим список существующих баз данных:

SHOW DATABASES;

Выбрать базу данных можно так:

USE dbname;

Посмотреть какая в данный момент выбрана база:

SELECT DATABASE();

Чтобы посмотреть какие таблицы содержит выбранная база данных:

SHOW TABLES;

Для просмотра структуры конкретной таблицы выполним команду:

DESCRIBE tablename;

Где Field — это имя столбца, Type — тип данных, Null — определяет, может ли столбец содержать значения NULL, Key — является ли столбец индексным, Default — определяет значение по умолчанию для столбца.

Посмотреть информацию о индексах таблицы можно так:

SHOW INDEX FROM tablename;

Посмотреть информацию о таблице, кодировке, её типе (ENGINES) и т.д. можно командой:

SHOW CREATE TABLE tablename;

Посмотреть какие типы поддерживает сервер можно так:

SHOW ENGINES;

Установка MariaDB в Ubuntu

На тесте установлю MariaDB в Ubuntu 16.04.
В первую очередь посмотрим нужный репозиторий на https://downloads.mariadb.org/mariadb/repositories/
Там же есть и инструкция по их добавлению.

Так как я устанавливаю в Ubuntu 16.04, то команды добавления репозитория будут следующие:

sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirror.ehv.weppel.nl/mariadb/repo/10.2/ubuntu xenial main'
sudo apt-get update

После этого установим MariaDB сервер и клиент:

sudo apt-get install mariadb-server mariadb-client

После установки выполним несколько советов безопасности (удалим базу test, запретим пользователю root удаленный доступ и т.д.):

sudo mysql_secure_installation

Подключится к серверу MariaDB можно так:

sudo mysql -u root -p

Посмотреть статус, остановить, запустить сервер MariaDB можно командами:

sudo systemctl status mariadb
sudo systemctl stop mariadb
sudo systemctl start mariadb

Если понадобится удалить MariaDB, то выполним:

sudo apt-get autoremove mariadb-server mariadb-client

IPTables правила для MySQL

Чтобы открыть внешний доступ к MySQL в IPTables необходимо добавить правила:

iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

Чтобы открыть доступ только конкретной сети, например 10.0.0.0/24:

iptables -A INPUT -s 10.0.0.0/24 -p tcp -m tcp --dport 3306 -j ACCEPT

Чтобы удалить правило укажем ту же команду, заменив -A на -D, например:

iptables -D INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

Посмотреть список правил можно командой:

sudo iptables -nvL

Замечу, для того чтобы открыть внешний доступ, также нужно в конфигурационном файле my.cnf закомментировать строку «bind-address = 127.0.0.1».

Смотри также:
Настройка IPTables
Другие мои статьи о MySQL

Подключение к MySQL из localhost без ввода пароля

Допустим, нужно сделать чтобы пользователь root мог подключатся к MySQL из localhost без ввода пароля.

Для этого, создадим в домашней директории пользователя, от которого выполняется подключение, файл конфигурации MySQL (в текстовом редакторе nano клавиши CTRL+X для выхода, y/n для сохранения или отмены изменений):

sudo nano ~/.my.cnf

В целях безопасности настроим доступ к файлу .my.cnf только root пользователю системы.

sudo chown root:root ~/.my.cnf
sudo chmod 400 ~/.my.cnf

И добавим секцию client, указав в ней логин и пароль пользователя, а также несколько других параметров:

[client]
host=localhost
user=root
password=PASSWORD
socket=/var/run/mysqld/mysqld.sock

Попробуем подключится, сразу должна отобразится консоль MySQL (без запроса пароля):

mysql

Изменение connect_timeout в MySQL

connect_timeout — количество секунд, в течение которых сервер mysql ожидает пакет подключения, прежде чем прервать соединение.

Подключится к MySQL и посмотрим текущее значение:

mysql -u USER -p
show variables like "connect_timeout";
quit;

Значение connect_timeout можно указать в файле /etc/mysql/my.cnf, например:

[mysqld]
connect_timeout=10

В реальном времени можно изменить выполнив SQL запрос (после перезапуска MySQL оно сбросится на стандартное или указанное в файле конфигурации):

SET GLOBAL connect_timeout=10;

Стандартное значение равно 10, минимальное — 2, максимальное 31536000.

Изменение wait_timeout и interactive_timeout в MySQL

wait_timeout — Количество секунд, в течение которых сервер ждет активности в неинтерактивном соединении, прежде чем закрыть его.
В момент соединения значение wait_timeout берется из глобального значения wait_timeout или interactive_timeout в зависимости от типа клиента (как определено опцией CLIENT_INTERACTIVE connect для mysql_real_connect ())

Подключится к MySQL и посмотрим текущее значение:

mysql -u USER -p
show variables like "wait_timeout";
show variables like "interactive_timeout";
quit;

По умолчанию значения wait_timeout и interactive_timeout равняются 28800 секунд = 8 часов.
Минимально можно установить 1, максимум — 31536000, максимум (для Windows) — 2147483.

Можно изменить значение wait_timeout выполнив SQL запрос, например:

set global wait_timeout = 28800;
set global interactive_timeout = 28800;

Чтобы установленное значение не сбросилось, его нужно указать в файле /etc/mysql/my.cnf, в блоке mysqld:

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800

Решение ошибки ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla’

Заметил однажды при импорте sql файла следующую ошибку:

ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla’

Она возникает в связи с тем что новые версии MySQL сервера используют строгий режим и такие параметры как NO_ZERO_DATE не позволяют вносить в базу значения даты как например ‘0000-00-00’.

Подключимся к mysql серверу:

mysql -u root -p

Выполним запрос который отобразит значения sql_mode:

show variables like 'sql_mode';

Скопируем строку с этими значениями и выйдем из mysql:

exit

Откроем файл конфигурации например в текстовом редакторе nano (Ctrl+X для выхода, y/n для сохранения или отмены изменений):

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

У меня в файле не было sql_mode=, по этому в конце файла вставим строку со скопированными ранее значениями убрав из нее NO_ZERO_IN_DATE,NO_ZERO_DATE, в моё случае получилось следующее:

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Перезапустим mysql чтобы применить изменения:

sudo service mysql restart

Все, теперь при импорте данной ошибки не должно быть.

Как создать пользователя MySQL и настроить права доступа

Для создания пользователя первым делом подключимся к консоли MySQL сервера:

mysql

Посмотрим какие есть пользователи:

select * from mysql.user;

Создадим пользователя (там где localhost указывается откуда пользователь может подключатся, можно указать IP-адрес, localhost — с локальной машины где сам MySQL сервер, либо % с любых адресов):

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Если подразумевается подключение не только локально, то необходимо закомментировать строку в my.cnf:

#bind-address = 127.0.0.1

И перезапустить MySQL сервер:

sudo service mysql restart

После этого рекомендую ограничить доступ к MySQL средствами IPTables.
Смотрите также — Настройка IPTables

Чтобы назначить вновь созданному пользователю неограниченные права доступа к конкретной базе данных, выполним следующую команду:

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

Если нужно на все базы:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

Можно указать конкретные права доступа, например только для чтения:

GRANT SELECT ON database_name.* TO 'user'@'localhost';

Если нужно создать новую базу:

CREATE DATABASE database_name;

Чтобы изменения вступили в силу выполним:

FLUSH PRIVILEGES;

Удалить пользователя можно так:

DROP USER 'user'@'localhost';

Импорт и экспорт MySQL баз данных

Ниже приведу примеры импорта и экспорта MySQL баз данных из терминала Linux.

Экспортировать базу данных в файл можно так:

mysqldump -u user -h localhost -p DATABASE_NAME > DATABASE_NAME.sql

Можно экспортировать сразу в архив:

mysqldump -u user -h localhost -p DATABASE_NAME | gzip -c > /dir/DATABASE_NAME_`date +%Y-%m-%d`.sql.gz

Экспортировать несколько баз в один файл и сразу сжать его можно так:

mysqldump -u user -h localhost -p -B DATABASE_NAME1 DATABASE_NAME2 | gzip -c > /dir/DATABASE_NAME_`date +%Y-%m-%d`.sql.gz

Импортировать сжатый файл обратно в базу можно так:

mysql -u user -p DATABASE_NAME < /dir/DATABASE_NAME_`date +%Y-%m-%d`.sql.gz

Не сжатый файл аналогично:

mysql -u user -p DATABASE_NAME < /dir/DATABASE_NAME_`date +%Y-%m-%d`.sql

Ключ -p указывает что необходимо ввести пароль во время соединения с MySQL сервером, для того чтобы не вводить его можно прописать в команде (в целях безопасности это не желательно), например так:

mysql -u user -pPASSWORD DATABASE_NAME < DATABASE_NAME.sql