Командная строка MySQL

Небольшая памятка по работе с сервером MySQL из командной строки.

Получить информацию об установленной версии MySQL

mysql -V

Для подключения к mysql в консоли наберите команду

mysql -h you_sql_server -u user_name -p

• h — хост c MySQL. Если подключаемся с локальной машины, параметр можно опустить
• u — имя пользователя MySQL (root или другой пользователь MySQL)
• p — пароль, который будет предложено ввести после нажатия enter

Приглашение командной строки изменится, это значит, сервер MySQL ждёт от вас команд.

mysql>

Запросы должны оканчиваться точкой с запятой. Длинные запросы удобно разбивать enter-ом для перехода на новую строку, а после полного написания запроса поставить точку с запятой и выполнить его.

Для отключения от MySQL нужно написать exit или (в unix-системах) нажать комбинацию клавиш ctrl+с.

Для вывода всех баз данных на сервере используйте команду show databases.

SHOW DATABASES;

Выберите нужную базу данных командой use.

USE db_name;

Теперь можно вводить запросы.

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

mysql -u user_name -h host_name db_name -p

Полезные команды MySQL

Показать все таблицы выбранной базы данных в текущей БД.

SHOW TABLES;

Показать все таблицы базы данных db_name.

SHOW TABLES FROM db_name;

Показать список столбцов в таблице table_name в текущей БД

SHOW COLUMNS FROM table_name;

Показать список столбцов в таблице table_name из БД db_name

SHOW COLUMNS FROM table_name FROM db_name;

Вывести структуру нужной таблицы

DESCRIBE table_name;

Показать структуру таблицы, будет выведен sql-запрос на её создание через «CREATE TABLE».

SHOW CREATE TABLE table_name;

Вывести значения системных переменных.

SHOW VARIABLES;

Показать список выполняющихся в настоящий момент запросов.

SHOW PROCESSLIST;

Общая статистика MySQL.

SHOW STATUS;

Статистика по всем таблицам в базе db_name.

SHOW TABLE STATUS FROM db_name;

Что бы выполнять запросы к MySQL из консоли, не всегда требуется предварительно подключаться к mysql. Параметр -e позволяет исполнить команду, вывести результат на экран, после чего отключиться от сервера MySQL. Например, можно вывести список таблиц базы данных.

mysql -uroot -e 'SHOW TABLES' db_name && echo done

Управление базами данных

Создание базы данных из консоли сервера

$ mysqladmin -u root -p create db_name

Удаление базы данных из консоли сервера

mysqladmin -u root -p drop db_name

Создание базы данных db_name из консоли MySQL

CREATE DATABASE db_name COLLATE utf8_general_ci;

Удаление базы данных db_name из консоли MySQL

DELETE DATABASE db_name;

Замена в поле одной подстроки на другую

UPDATE table SET field=replace(field,'original string','new string');

Работа с пользователями

Вывести список пользователей

SELECT User,Host FROM mysql.user;

SHOW GRANTS FOR user_name FROM db_name;

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

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

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

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

Чтобы разрешрешить пользователю подключаться вообще со всех хостов, придётся создать для него две учётные записи.

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

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX
ON db_name.* TO 'user'@'localhost';

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

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'localhost';

А затем установить нужные с помощью GRANT, как было показано выше.

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

REVOKEALLON db_name.* FROM 'user'@'localhost';

Сделать из пользователя суперпользователя и дать полный доступ ко всем БД на сервере

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

DROP USER user@localhost;

FLUSH PRIVILEGES;

Изменить пароль пользователя в консоли MySQL

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('new_password');
FLUSH PRIVILEGES;

UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='user_name';
FLUSH PRIVILEGES;
service mysqld restart;

$ mysqladmin -uuser_name password'password'

Исправление и оптимизация баз данных в MySQL

Чтобы исправить ошибки в поврежденной таблице, в консоли MySQL выполните:

> REPAIR TABLE table_name;

Если же в БД много поврежденных таблиц можно воспользоваться командой mysqlcheck.

Проверить db_name на ошибки.

$ mysqlcheck -p db_name

Восстановление и оптимизация всех БД

$ mysqlcheck -Aor -p

• p – использовать пароль
• -A, —all-databases – проверять все базы данных
• -o, —optimize – оптимизировать
• -r, —repair – восстанавливать повреждённые
• —auto-repair – автоматическое восстановление

Бэкап MySQL из командной строки

Экспорт базы MySQL

$ mysqldump -u username -p db_name > dump.sql

Дамп нескольких баз

$ mysqldump -u username -p -B db_name1 db_name2 > dump.sql

Дамп всех баз на сервере

$ mysqldump -u username -p -A > dump.sql

Дамп только структуры базы, без данных

$ mysqldump -u username -p --no-data db_name > database.sql

Дамп структуры одной таблицы mysql, без данных:

$ mysqldump -u username -p -h host db_name table_name --no-data > /path/dump.sql

Развернуть базу данных MySQL из дампа

$ mysql -u username -p db_name < dump_to_restore.sql

Клонирование таблиц

Скопировать структуру и ключи таблицы, без копирования данных.

CREATE TABLE NEW_TableName LIKE OLD_TableName

Клонировать таблицы базы данных со всеми данными.

CREATE TABLE NEW_TableNameSELECT *FROM OLD_TableName

CREATE TABLE NEW_TableName
LIKE OLD_TableName
INSERT INTO NEW_TableName
SELECT *
FROM OLD_TableName;

На этом всё. Но вы можете поддержать проект. Даже небольшая сумма поможет нам писать больше полезных статей.

Если статья помогла или понравилась, пожалуйста поделитесь ей в соцсетях.

• Как подключиться к базе данных MySQL: инструкция

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

В этом материале мы рассмотрим, как подключиться к MySQL несколькими способами. В качестве тестовой базы можно применять облачные базы данных провайдера Timeweb Cloud. Запуск БД там займет пару минут.

Способы, доступные пользователю

Существует несколько вариантов коннекта, какой именно выбрать, решать вам:

1. Командная строка MySQL. Входит в комплект сервера MySQL.

Во всех перечисленных способах возможно подключение по защищенному каналу благодаря SSL-сертификату.

Сколько подключений к БД доступно

Количество одновременно работающих с базой данных пользователей или приложений ограничено объемом оперативной памяти, выделенной на работу нод конкретного кластера. Все, что требуется от пользователя, это изменить тариф на , масштабировать его под свои требования.

Подключение из разных операционных систем

как подключиться к базе данных MySQL из разных операционных систем. Выглядеть команды будут так:

Подключение через phpMyAdmin

Выводы

Работать с удаленной базой данных можно с криптографической защитой данных или без нее. Это зависит от задачи, которую предстоит решать. Соединение возможно как вручную из операционок Linux и Windows, так и автоматически при наличии в приложении соответствующего кода. Вопрос, как подключиться к локальному серверу MySQL, имеет то же решение. Отличие составляет лишь адрес, который понадобится указать в командах.

День добрый, друзья! 🙂 Для подключения к mysql наберите

mysql -h 127.0.0.1 -u root -p 

• h — хост базы данных, если коннектимся к локальной базе (по ip 127.0.0.1), этот параметр можно опустить
• u — имя пользователя
• p — пароль, его нужно будет ввести после нажатия enter

Например, если нужно законнектиться к локальной быдухе под root`ом и пароль у него не установлен (пустой пароль — это плохо), достаточно написать

mysql -u root

Если на экране появляется приветствие mysql, то всё прошло ok.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 344
Server version: 5.5.28-0ubuntu0.12.04.3 (Ubuntu)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Для выхода из командной строки mysql можно написать exit, в linux достаточно нажать ctrl+C. Теперь можно писать любые mysql-запросы. Чтобы запрос выполнился, в конце обязательно нужно поставит точку с запятой «;». При написании длинных запросов удобно делать enter`ом переход на следующую строку. А после полного написание запроса поставить «;» и нажать «enter».

Для работы с конкретной базой данных нужно её выбрать с помощью оператора use. Показать все базы данных:

show databases;

Результатом будет что то вроде этого:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jeka |
| linky |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
+--------------------+
7 rows in set (0.00 sec)

Выбрать базу данных jeka:

mysql> use jeka
Database changed

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

mysql> show tables;

Просмотреть структуру таблицы (имена и тип полей):

mysql> describe post_estimations;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| post_id | int(10) unsigned | YES | MUL | NULL | |
| user_id | int(10) unsigned | YES | MUL | NULL | |
| estimation | float(2,1) unsigned | NO | | NULL | |
| date | int(10) unsigned | NO | | NULL | |
+------------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Достать информацию о юзере с id=22:

mysql> SELECT * FROM users WHERE id=22;

Бэкап структуры таблицы, а точнее sql-запрос на её создание

show create table table_name;

Если в конце запроса поставить не ; а \G, то результат будет показан другим образом — иногда весьма удобно, если таблица имеет много полей:

select * from update__v4_errors where id = 1102003\G

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

show processlist;

Учтите, что будут отображены еще не выполненные (активные) запросы, поэтому show processlist; актуальная команды только для нагруженных БД, в других случаях маловероятно, что команда что то покажет.

Подробнее о том, как изменять структуру mysql-таблиц (alter table).

Что делать если вижу знаки вопросов вместо текста в MySQL?

Это проблема с кодировкой. Вероятно в my.ini файле mysql-сервера не добавлено:

[mysqld]
init-connect='SET NAMES utf8'

Нужно добавить в .ini файл либо выполнить в консоли команду «SET NAMES utf8», после чего во время текущей сессии отображаться русский текст в mysql-консоли будет без проблем:

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

Как сделать бэкап базы mysql через консоль Linux?

Сделать бэкап базы database в файл dump_name.sql

mysqldump -u [username] -p [password] [database] > [dump_name.sql]

Развернуть базу из файла через командную строку

mysql -u [username] -p [password] [database] < [dump_to_restore.sql]

Сделать дамп структуры одной таблицы mysql (без данных):

mysqldump -u[user] -p[password] -h[host] [database] [table_name] --no-data > /path/dump_name.sql

mysqldump -uroot mydatabase users > users.dump.sql

Развернуть mysql-dump в БД с именем database_name:

mysql -uroot database_name < users.dump.sql

Ещё одна интересная штука. Выполнять из консоли, не требуется предварительно подключаться к mysql (mysql>). Вывести список таблиц базы данных, не подключаясь непосредственно к mysql:

mysql -uroot -e 'show tables' database_name && echo done

mysql> show variables like '%incr%';

В процессе разработки иногда приходится выполнять часть операций вручную через командную строку. Данная статья содержит основные команды для взаимодействия с MySQL.

Подключение к MySQL

Для подключения к локальной БД с пользователем «root» без пароля достаточно прописать:

В примере выше не указывается ни пароль, ни хост. В таком виде она часто используется при разработке на сборках с единственным пользователем по умолчанию. К примеру, на OpenServer’e. Этого пользователя попросту лень менять :), но на продакшене, конечно, следует использовать отдельного пользователя с урезанными правами по отношению к root’у. В таких случаях при подключении необходимо указывать дополнительные параметры.

Подключение без указания конкретной БД:

mysql -h IP_ADDRESS -u USER_NAME -p

Подключение к нужной БД сразу:

mysql -h IP_ADDRESS -u USER_NAME DATABASE_NAME -p

• -h — хост, IP-адрес или домен;
• -u — имя пользователя;
• -p — пароль, вводится после нажатия ENTER;

Если были указаны корректные данные, то приглашение командной строки изменится на «mysql>» или что-то подобное (часто вместо самой MySQL используются её ответвления MariaDB и Percona).

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

Или её синоним:

Работа с базами данных

В исходниках программы старайтесь указывать все операторы баз данных в верхнем регистре, а всё остальное — наименования баз данных, таблиц, столбцов и т.д., в нижнем. Это повышает читабельность запросов в вашем коде. Например:

SELECT id, name FROM customer WHERE status = 100;

Вывод списка имеющихся БД:

Каждая сборка MySQL имеет несколько стандартных баз данных, они хранятся рядом с вашими и выполняют технические функции, хотя с ними тоже можно работать.

• information_schema — хранилище метаданных о вашей базе данных, таких как наименование других баз данных, количестве столбцов, их таких и т.д.
• performance_schema — быстрая версия «information_schema«.
• mysql — еще одно хранилище служебной информации.

Выбор базы данных для работы:

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

Создание новой базы данных с кодировкой «utf8_general_ci» (это не совсем кодировка, это скорее способ сравнения символов):

 new_database utf8_general_ci;

Удаление базы данных:

 new_database;

Работа с пользователями

 mysqluser;

При работе с разными проектами для каждого из них желательно создавать разных пользователей, так мы минимизируем возможные потери от взломов или плохо написанного кода. Создание пользователей состоит из двух этапов. Первый — добавление записи о пользователе в БД. Второй — наделение созданного пользователя полномочиями.

 @ ;

В приведенном примере пользователь будет иметь право подключаться с сервера «localhost». Если требуется дать пользователю право подключаться к базе данных с других хостов (удаленных), то вместо «localhost» необходимо указать символ процента — «%». Чтобы пользователь имел право подключаться и с «localhost» и с удаленных хостов, то необходимо создать две записи с «localhost» и с «%».

Обновление пароля пользователя:

 PASSWORD @ PASSWORD;

Выделение привилегий пользователю:

 PRIVILEGES @;

Пример выше содержит два важных элемента. Первый — список привилегий, в данном случае была использована настройка «ALL PRIVILEGES». Она заменяет список всех привилегий, т.е. «SELECT», «CREATE», «INSERT», «UPDATE», «DELETE», «DROP» и так далее. Второй момент — список баз данных и таблиц на которые выдаются указанные привилегии. В примере выше указаны все базы данных и все таблицы — две звездочки(*.*).

Если вы указываете привилегии не через команду «GRANT» (т.е. вручную), то для вступления изменений в силу необходимо оповестить об этом MySQL вот так:

Вывод привилегий пользователя:

 GRANTS new_user_name;

Сброс всех привилегий пользователя:

 PRIVILEGES @;

 new_user_name@localhost;

Работа с данными

Отображение списка таблиц:

Отображение структуры таблицы:

Операции с данными выглядят и работают также, как и в исходниках программ.

 customer id name age TINYINT
;

 name address client ;

 customer name age ;

 customer age name ;

Экспорт и импорт дампа

Для создания дампа используется отдельная утилита — mysqldump. А импортировать дампы можно через утилиту mysqlimport или основным клиентом — mysql. У всех этих утилит множество настроек с которыми лучше ознакомиться в документации.

Экспорт всей базы данных:

mysqldump h IP_ADDRESS u USER_NAME p DATABASE_NAME dumpsql

Экспорт структуры базы данных (т.е. без данных):

mysqldump h IP_ADDRESS u USER_NAME p --no-data DATABASE_NAME < dump.sql

Экспорт одной таблицы базы данных с данными:

mysqldump h IP_ADDRESS u USER_NAME p DATABASE_NAME dumpsql

Экспорт структуры одной таблицы базы данных (т.е. без данных):

mysqldump h IP_ADDRESS u USER_NAME p DATABASE_NAME --no-data < dump.sql

Импорт базы данных через mysql:

mysql h IP_ADDRESS u USER_NAME p DATABASE_NAME dumpsql

Импорт базы данных через mysql с выводом результатов выполнения запросов:

 pathdumpdumpsql

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

 ;

Дампы на удаленных машинах

Экспорт дампа из удаленной БД на локальную машину:

ssh USER_NAME@HOST_IP mysqldump u DB_USER p DB_NAME dumpsql

Импорт дампа в БД на удаленной машине с локальной машины:

ssh USER_NAME@HOST_IP mysql u DB_USER p DB_NAME dumpsql

Другие команды

Вывод общей информации о базе данных:

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

Полезные ссылки

• dev.mysql.com/doc/refman/8.0/en — официальная документация.
• dev.mysql.com/doc/refman/8.0/en/mysqldump.html — официальная документация по утилите mysqldump;
• itreviewchannel.ru/zagruzka-i-skachivanie-fajlov-po-ssh — загрузка и скачивание файлов по ssh.

• Как создать базу данных и нового пользователя в MySQL: инструкция

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

По умолчанию вам будет доступен пользователь root. Используйте его, чтобы выполнить первоначальную настройку: создать первую БД и других пользователей.

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

Создание базы данных

Подключитесь к MySQL под учётной записью root:

mysql -u root -p

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

Даже если вы ещё ничего не делали, таблица не будет пустой. В ней отобразятся служебные БД. Удалять их не нужно.

создать базу данных MySQL

CREATE DATABASE my_timeweb;

Вместо my_timeweb укажите имя, которое необходимо задать новой БД.

Чтобы убедиться, что новая база появилась на сервере, ещё раз выполните:

В таблице отобразится название БД, которую вы только что создали.

Нельзя, чтобы у двух и более БД были одинаковые имена. Если ещё раз создать базу my_timeweb, то появится сообщение об ошибке с кодом 1007. 

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

CREATE DATABASE IF NOT EXISTS my_timeweb;

В этом случае СУБД проверит условие. Если БД с таким именем не существует, то MySQL создаст её. Если же такая база была создана ранее, то ничего не изменится.

Удаление базы данных

Для удаления выполните:

DROP DATABASE my_timeweb;

Вместо my_timeweb укажите имя БД, которую следует удалить.

У DROP DATABASE также есть расширенный синтаксис, который помогает избежать ошибок:

DROP DATABASE IF EXISTS my_timeweb;

Запрос будет выполнен только при наличии БД с таким именем.

DROP DATABASE удалит БД и связанные с ней данные. Интерфейс командной строки MySQL не предложит вам подтвердить действие. Поэтому применяйте DROP DATABASE с осторожностью.

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

MySQL предлагает удобную систему управления юзерами и их привилегиями. Можно менять права доступа, разрешать и запрещать выполнение действий на сервере. 

MySQL создать пользователя, авторизуйтесь как root и выполните в консоли:

Если нужно указать не конкретный хост, а целый пул, используйте знак %. Например:

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

Удаление учётной записи

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

Выдача привилегий

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

В этой таблице — привилегии, которые часто выдают пользователям. Более подробный список смотрите в

Для выдачи прав нужно выполнить команду GRANT. Например:

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

Отзыв привилегий

Чтобы отозвать привилегии, используйте команду REVOKE. У неё такой же синтаксис, как у GRANT. Но результат будет противоположным.

Если учётная запись не имела никаких прав в этой БД, то ничего не изменится.

Ключевые команды, которые нужно знать

Как создать БД в MySQL:

Как удалить БД:

создать нового пользователя MySQL:

Как удалить пользователя:

Это минимальный набор запросов, который поможет в MySQL создать базу данных и пользователя. Особенности использования этих и других возможностей СУБД подробно описаны в