Как решить «ERROR 1062 (23000) at line X: Duplicate entry ?????? for key X»

Импортировал недавно дамп в MySQL и обнаружил следующую ошибку:

ERROR 1062 (23000) at line 14096: Duplicate entry '????????' for key 'name'

Судя по знакам вопроса в ошибке, проблема была скорее в кодировке, посмотрел кодировку дампа через Notepad++, она была — UTF8.

Подключился к mysql, посмотрел кодировку базы:

mysql -u root
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "database_name";

Кодировка базы была latin1, кстати у меня она установилась по умолчанию при создании базы.

Поэтому я удалил базу и создал указав кодировку:

DROP DATABASE database_name;
CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;
exit

После этого выполнил импорт:

mysql database_name < database_name.sql;

Импорт прошел успешно, ошибок не отображалось.

В случае если кодировка базы и дампа совпадает, а вы действительно хотите записать повторяющиеся данные, то можно в дампе, в запросах с этими данными заменить INSERT INTO на INSERT IGNORE INTO, где находятся эти повторяющиеся данные можно понять по номеру строки в ошибке, например у меня «at line 14096».

Если необходимо импортировать дамп поверх существующей базы, то можно изменить INSERT INTO на REPLACE INTO.

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

replace 'INSERT INTO' 'REPLACE INTO' -- database_name.sql

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

Смотрите также мою статьи:
Импорт и экспорт MySQL баз данных
Популярные статьи о MySQL

Решение ошибки с binary-mode при импорте MySQL dump из командной строки

Однажды решил импортировать sql дамп:

mysql -u root database < database.sql.gz

И заметил следующую ошибку:

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''.

Ошибка появляется из-за того что дамп сжат и его необходимо сначала распаковать:

gunzip database.sql.gz

А потом импортировать:

mysql -u root database < database.sql

Смотрите другие мои статьи в категории MySQL

Настройка репликации в MySQL

На тесте выполню настройку репликации в MySQL.

Допустим имеется два сервера с установленной системой Ubuntu Server, на обеих установим MySQL сервер и клиент, если они не установлены:

sudo apt-get install mysql-server mysql-client

Читать далее Настройка репликации в MySQL

Решение ошибки ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Выполнял однажды SQL запрос:

GRANT REPLICATION SLAVE ON TESTDATABASE.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";

И обнаружил следующую ошибку:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Так как привилегии REPLICATION SLAVE являются глобальными и не могут быть назначены конкретной базе данных, то и в запросе их нужно указывать глобально, поэтому SQL запрос должен выглядеть так:

GRANT REPLICATION SLAVE ON *.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";

После этого запрос выполнился успешно:

Query OK, 0 rows affected, 1 warning (0,01 sec)

Восстановление 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;

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

SELECT * FROM tablename LIMIT 25;

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

DESCRIBE tablename;

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

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

SHOW INDEX FROM tablename;

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

SHOW CREATE TABLE tablename;

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

SHOW ENGINES;

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