Импорт и экспорт 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 ПОЛЬЗОВАТЕЛЬ -h localhost -p БАЗА | 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).

Мониторинг размера MySQL базы или таблицы в Zabbix

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

SELECT table_schema "databases name", sum(data_length + index_length)/1024/1024  "DВ size in MB" FROM information_schema.TABLES GROUP BY table_schema;

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

SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "zabbix";

Либо в байтах увидеть размер одой базы с именем zabbix:

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

Чтобы увидеть размер конкретной таблицы базы, например zabbix.history:

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

Примеры предыдущих запросов мы и используем для мониторинга. Создадим в директории скриптов Zabbix скрипт, например с именем zabbix_database_size.sh и содержимым:

mysql -u root -pPASSWORD -h 127.0.0.1 -e "SELECT SUM( data_length + index_length ) AS 'size' FROM information_schema.TABLES WHERE table_schema = 'zabbix' LIMIT 1;" -s -N

В Zabbix узлу сети или в новом шаблоне создадим элемент данных в котором укажем:

Имя: Zabbix Database Size
Тип: Внешняя проверка
Ключ: zabbix_database_size.sh
Тип информации: Числовой (целое положительное)
Единица измерения: b

Соответственно создадим для него график.

Все.

Скрипт отправки СМС через Goip4 шлюз

Приведу пример скрипта написанного на PHP, для отправки СМС сообщений через Goip4 шлюз.
Скрипт получает данные из базы SQL запросом и поочередно отправляет СМС на каждый номер, а также заносит запись об отправке в специальную таблицу sms.

#!/usr/bin/php

<?php

include("config/config.inc.php");
include("config/connect.inc.php");


$Result = mysqli_query($db,
"
SELECT
    abon.first_name,
    abon.second_name,
    abon.id as id,
    abon.depozit as balance,
    abon.id,
    abon.otkl,
    abon.mobile,
    abon.tarif,
    new_tarif.cost_day,
    new_tarif.cost as cost_month,
    new_tarif.dayORmonth as period
FROM
    abon, new_tarif
WHERE
    abon.tarif = new_tarif.id AND abon.state=1 AND abon.killed='' AND abon.tarif=114
"
);


while($Row = mysqli_fetch_array($Result))
{
$user = $Row[id];
$balance =$Row[balance];
$phone = $Row[mobile];

$phone_length=strlen($phone);

if($phone_length==10)
{
$mess_ = "Текст сообщения. На вашем счету $balance";
$mess =  rawurlencode($mess_);

print("$phone           $mess_\n");

//test phone
//$phone = "0670000000";
$r = file_get_contents("http://192.168.22.10/goip/en/dosend.php?USERNAME=ЛОГИН&PASSWORD=ПАРОЛЬ&smsprovider=3&goipname=lifecell&smsnum=$phone&method=2&Memo=$mess");
mysqli_query($db, "INSERT INTO sms VALUES(NULL, '$user', '$phone', NOW(), '$mess_', '$balance')");
//print($r);
//exit();

}
}
?>

Содержимое config.inc.php:

<?php
$mysql_host="localhost";
$mysql_user="ЛОГИН";
$mysql_password="ПАРОЛЬ";
$mysql_base="БАЗА";
?>

Содержимое connect.inc.php:

<?php
$db=mysqli_connect($mysql_host, $mysql_user, $mysql_password, $mysql_base);
mysqli_query($db, "SET NAMES 'utf8'");
?>

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

Стандартно параметр max_connections в MySQL имеет значение 151 и разрешается на одно соединение больше, то есть может быть 152 соединения.
В случае если соединений будет больше, то будет ошибка «too many connections».
На примере использую MySQL 5.5.54.

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

mysql -u root -p

Посмотрим список текущих соединений:

show processlist;
SHOW FULL PROCESSLIST\G
show status where `variable_name` = 'Threads_connected';

Посмотрим текущее значение max_connections:

show variables like "max_connections";

Если необходимо увеличить его без перезапуска MySQL (будет действовать до перезапуска MySQL):

set global max_connections = 300;

Чтобы после перезапуска MySQL значение max_connections сохранилось, в конфигурационном файле /etc/mysql/my.cnf, в секции [mysqld] укажем его:

max_connections = 300

Решение ошибки в phpMyAdmin «Данная таблица не содержит уникального столбца»

Нужно было недавно изменить некоторые данные в MySQL базе данных, решил воспользоваться phpMyAdmin и столкнулся с сообщением:

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

На английском сообщение выглядит так:

This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.

Можно решить это выполнив указанный ниже sql запрос, который добавит к таблице уникальный столбец по которому можно определять номера строк:

ALTER TABLE таблица add newid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Но чтобы не менять структуру таблицы, я просто изменил нужные данные sql запросом. Например:

UPDATE таблица SET serv='131' WHERE user='767' AND date='2016-02-04' AND serv='64';

Вот и все. Указанное выше сообщение указывает что изменять данные через phpMyAdmin нельзя, но их можно изменить написав sql запрос.