Установка 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

Как изменить кодировку MySQL базы данных и её таблиц

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

mysqldump -u USER -h localhost -p BASE | gzip -c > backup_base_`date +%Y-%m-%d`.sql.gz

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

mysql -u root -p
CREATE DATABASE test_db1;
CREATE DATABASE test_db2 CHARACTER SET utf8 COLLATE utf8_general_ci;

Создадим тестовую таблицу в первой базе и посмотрим её кодировку:

USE test_db1;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status like 'users';

Создадим тестовую таблицу во второй базе и посмотрим её кодировку:

USE test_db2;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status;

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

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db1";
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db2";

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

SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "test_db1"
AND table_name = "users"
AND column_name = "firstname";

В моём случае таблица в первой базе была с кодировкой latin1_swedish_ci, так как она является стандартной, а во второй utf8_general_ci так как я её заранее указал.

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

show collation;
show collation like 'utf8%';
show collation like 'latin1%';

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

show databases;

Посмотреть существующие таблицы в базе:

USE test_db1;
show tables;

Теперь сменим кодировку первой базы и её таблицы на utf8 и сразу проверим:

ALTER DATABASE `test_db1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test_db1;
ALTER TABLE `test_db1`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
show table status;

Если нужно изменить кодировку в sql файле, то откроем его в редакторе Notepad++ преобразуем например в UTF-8/без BOM, а также если в начале файла указана кодировка в SET NAMES, изменим её там, после этого можно импортировать файл в базу.

Решение ошибки Table ‘name’ is marked as crashed and last (automatic?) repair failed

Заметил однажды в логах FreeRADIUS ошибку MySQL:

Table ‘./radius/radacct’ is marked as crashed and last (automatic?) repair failed

Как оказалось была повреждена таблица radacct, так как данные там были не особо важны, то помогла очистка всей таблицы.
Очистить можно через phpMyAdmin или SQL запросом:

truncate table TableName

Чуть позднее для эксперимента решил поломать целую базу, взял другую большую таблицу вообще от другого приложения, размером около 8 гигабайт и 80 млн. строк.
Выполнил к ней SQL запрос на очистку старых строк до указанной в запросе даты и перезагрузил в этот момент MySQL, запрос прервался, база осталась цела, выполнил запрос на оптимизацию базы и еще раз перезагрузил MySQL, в итоге получил поврежденную базу и аналогичную ошибку:

#144 — Table ‘name’ is marked as crashed and last (automatic?) repair failed

Чтобы восстановить базу необходимо остановить MySQL сервер (если таблица ничем не используется, то можно не останавливать):

sudo service mysql stop

Перейдем в директорию с базой:

cd /var/lib/mysql/$DATABASE_NAME

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

myisamchk -r -o -f -v $TABLE_NAME

По завершению если останавливали MySQL сервер, то запустим его:

sudo service mysql start

Аналогичным образом на тесте также для ускорения процесса восстановил таблицу скопировав её на другой более мощный сервер, а именно три файла /var/lib/mysql/$DATABASE_NAME/ ($TABLE_NAME.MYD, $TABLE_NAME.MYI, $TABLE_NAME.frm).