Список (показать) таблиц в базе данных MySQL
При управлении серверами баз данных MySQL одной из самых частых задач, которые вы выполняете, является знакомство со средой. Это включает в себя перечисление баз данных, которые находятся на сервере, отображение таблиц баз данных или выборку информации об учетных записях пользователей и их привилегиях.
В этой статье показано, как составить список таблиц в базе данных MySQL или MariaDB через командную строку.
Показать таблицы MySQL
Чтобы получить список таблиц в базе данных MySQL, используйте клиентский инструмент mysql для подключения к серверу MySQL и выполните команду SHOW TABLES.
Получите доступ к серверу MySQL:
mysql -u user -p
Из оболочки MySQL переключитесь на базу данных с помощью инструкции USE:
USE database_name;
Выполните следующую команду, чтобы получить список всех таблиц и представлений в текущей базе данных:
SHOW TABLES;
Вывод будет выглядеть примерно так:
+----------------------------+ | Tables_in_database_name | +----------------------------+ | actions | | permissions | | permissions_roles | | permissions_users | | roles | | roles_users | | settings | | users | +----------------------------+ 8 rows in set (0.00 sec)
Необязательный модификатор FULL покажет тип таблицы в качестве второго выходного столбца.
SHOW FULL TABLES;
Вывод будет выглядеть примерно так:
+----------------------------+------------+ | Tables_in_database_name | Table_type | +----------------------------+------------+ | actions | VIEW | | permissions | BASE TABLE | | permissions_roles | BASE TABLE | | permissions_users | BASE TABLE | | roles | BASE TABLE | | roles_users | BASE TABLE | | settings | BASE TABLE | | users | BASE TABLE | +----------------------------+------------+ 8 rows in set (0.00 sec)
Чтобы получить список таблиц без переключения на базу данных, используйте предложение FROM или IN, за которым следует имя базы данных:
SHOW TABLES FROM database_name;
Предложение LIKE можно использовать для фильтрации выходных данных c командой SHOW TABLES в соответствии с определенным шаблоном.
SHOW TABLES LIKE pattern;
Например, следующий оператор вернет все базы данных, имена которых начинаются с ‘open’:
SHOW TABLES LIKE 'permissions%';
+-------------------------------------------+ | Tables_in_database_name (permissions%) | +-------------------------------------------+ | permissions | | permissions_roles | | permissions_users | +-------------------------------------------+ 3 rows in set (0.00 sec)
Знак процента (%) означает ноль, один или несколько символов.
Показывать таблицы MySQL из командной строки
Чтобы получить информацию о таблицах из оболочки Linux, вы можете использовать либо команду mysql -e, либо команду mysqlshow, которая отображает информацию о базах данных и таблицах.
Это особенно полезно, когда вы хотите работать с базами данных MySQL, используя сценарии оболочки.
Запустите следующую команду на своем терминале, чтобы показать список всех баз данных:
mysql -u user -p -e 'SHOW TABLES FROM database_name;'
Вывод отобразит список всех таблиц:
+----------------------------+ | Tables_in_database_name | +----------------------------+ | actions | | permissions | | permissions_roles | | permissions_users | | roles | | roles_users | | settings | | users | +----------------------------+
Вот пример использования команды mysqlshow:
mysqlshow database_name
Вы можете отфильтровать вывод с помощью команды grep.
Заключение
Чтобы получить информацию о таблицах в базе данных MySQL, используйте команду SHOW TABLES.
Не стесняйтесь оставлять комментарии, если у вас есть какие-либо вопросы.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
29 Янв 2018 23:01:07 | 0 comments
Как посмотреть тип движка у таблиц в MySQL
Очень часто бывает необходимо посмотреть тип движка у таблицы в MySQL.
Как это сделать для 1 таблицы и для всех таблиц в нужной базе данных? Читаем ниже.
Самый простой способ узнать тип движка у таблицы — это вызвать ‘SHOW CREATE TABLE ‘ или ‘SHOW TABLE STATUS FROM LIKE ‘.
mysql> USE mysql; mysql> SHOW CREATE TABLE plugin\G; *************************** 1. row *************************** Table: plugin Create Table: CREATE TABLE `plugin` ( `name` varchar(64) NOT NULL DEFAULT '', `dl` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL plugins' 1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS FROM mysql LIKE 'plugin'\G; *************************** 1. row *************************** Name: plugin Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 4 Avg_row_length: 51 Data_length: 204 Max_data_length: 281474976710655 Index_length: 2048 Data_free: 0 Auto_increment: NULL Create_time: 2018-02-20 00:21:12 Update_time: 2018-02-20 00:21:12 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: MySQL plugins 1 row in set (0.00 sec)
и в строке ENGINE мы видим, что у таблицы plugin тип движка MyISAM.
На самом деле есть способ еще проще — это воспользоваться утилитой mysqlshow.
Посмотрим информацию о таблице plugin из БД mysql:
# mysqlshow -u root -p -i mysql plugin Database: mysql Wildcard: plugin +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------------+ | plugin | MyISAM | 10 | Dynamic | 4 | 51 | 204 | 281474976710655 | 2048 | 0 | | 2017-12-10 23:37:48 | 2017-12-10 23:37:48 | | utf8_general_ci | | | MySQL plugins | +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------------+
Информации довольно много и она достаточно неудобно представлена (много колонок), но можно увидеть поле Engine — это то, что нам нужно.
Если же нужно посмотреть информацию в более удобном виде и узнать не только ENGINE, то можно выполнить такой SQL-запрос:
mysql> SELECT TABLE_NAME,ENGINE,ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mysql' ORDER BY ENGINE asc; +---------------------------+--------+------------+------------+-------------+--------------+ | TABLE_NAME | ENGINE | ROW_FORMAT | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | +---------------------------+--------+------------+------------+-------------+--------------+ | general_log | CSV | Dynamic | 2 | 0 | 0 | | slow_log | CSV | Dynamic | 2 | 0 | 0 | | innodb_index_stats | InnoDB | Compact | 29542 | 5767168 | 0 | | innodb_table_stats | InnoDB | Compact | 3057 | 425984 | 0 | | slave_master_info | InnoDB | Compact | 0 | 16384 | 0 | | slave_relay_log_info | InnoDB | Compact | 0 | 16384 | 0 | | slave_worker_info | InnoDB | Compact | 0 | 16384 | 0 | | db | MyISAM | Fixed | 148 | 65560 | 13312 | | help_topic | MyISAM | Dynamic | 533 | 510552 | 21504 | | proxies_priv | MyISAM | Fixed | 2 | 1386 | 5120 | | time_zone | MyISAM | Fixed | 0 | 0 | 1024 | | event | MyISAM | Dynamic | 0 | 0 | 2048 | | servers | MyISAM | Fixed | 0 | 0 | 1024 | | time_zone_leap_second | MyISAM | Fixed | 0 | 0 | 1024 | | func | MyISAM | Fixed | 3 | 1737 | 2048 | | time_zone_name | MyISAM | Fixed | 0 | 0 | 1024 | | ndb_binlog_index | MyISAM | Dynamic | 0 | 0 | 1024 | | time_zone_transition | MyISAM | Fixed | 0 | 0 | 1024 | | help_category | MyISAM | Dynamic | 40 | 1120 | 3072 | | plugin | MyISAM | Dynamic | 4 | 204 | 2048 | | time_zone_transition_type | MyISAM | Fixed | 0 | 0 | 1024 | | help_keyword | MyISAM | Fixed | 485 | 95545 | 17408 | | proc | MyISAM | Dynamic | 0 | 1200 | 4096 | | user | MyISAM | Dynamic | 160 | 19620 | 6144 | | columns_priv | MyISAM | Fixed | 0 | 0 | 4096 | | help_relation | MyISAM | Fixed | 1090 | 9810 | 20480 | | procs_priv | MyISAM | Fixed | 0 | 0 | 4096 | | tables_priv | MyISAM | Fixed | 7 | 5957 | 8192 | +---------------------------+--------+------------+------------+-------------+--------------+ 28 rows in set (0.00 sec)
Колонка ENGINE — это тип движка у таблицы;
Колонка ROW_FORMAT — это формат строк таблицы;
Колонка TABLE_ROWS — это количества записей в таблице;
Колонка DATA_LENGTH — это размер данных в таблице в байтах;
Колонка INDEX_LENGTH — это размер индекса в таблице в байтах;
На этом все, до скорых встреч. Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.
Проверка исходных таблиц и данных
В этой главе содержится информация о том, как получить дополнительные сведения об объектах исходной базы данных, доступных для семантической модели.
- Просмотр объектов источника
- Предварительный просмотр данных в объектах источника
Просмотр объектов источника
Список исходных таблиц и представлений можно просмотреть в меню «База данных» на левой панели. Щелкните таблицу или представление, чтобы увидеть ее свойства.
На вкладке «Обзор» исходных таблиц и представлений отображается информация о столбцах, например имя столбца, тип данных, уникальность столбца и допустимость неопределенных значений.
Предварительный просмотр данных в объектах источника
Для предварительного просмотра можно открыть первые 25 строк данных в таблицах и представлениях базы данных. Изучение начальных строк позволяет определить принципы моделирования таблиц и представлений баз данных в виде таблиц измерений или в виде таблиц фактов.
- Откройте построитель моделей данных.
- Чтобы открыть таблицу или представление базы данных, выберите нужный объект в меню «База данных» на левой панели.
- Выберите вкладку Данные .
- Изучите первые 25 строк данных таблицы или представления. При необходимости можно регулировать размер столбцов в таблице.
Командная строка MySQL
Небольшая памятка по работе с сервером 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;
Показать список прав пользователя user
SHOW GRANTS FOR user_name FROM db_name;
Создать нового пользователя
CREATE USER 'user'@'host' IDENTIFIED BY'password';
host — здесь имя хоста, доменное имя или ip адрес, с которого пользователь сможет подключаться к серверу, например user@localhost.
Чтобы создаваемый пользователь смог подключаться к серверу MySQL с любого IP адреса или хоста (за исключением localhost), можно использовать символ процента, вот так
CREATE USER 'user'@'%' IDENTIFIED BY 'password';
Чтобы разрешрешить пользователю подключаться вообще со всех хостов, придётся создать для него две учётные записи.
CREATE USER 'user'@'%' IDENTIFIED BY 'password'; CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
Добавить указанные привилегии для таблиц БД db_name пользователю user@localhost
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON db_name.* TO 'user'@'localhost';
Чтобы изменить права пользователю, иногда удобно сначала сбросить все права
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'localhost';
А затем установить нужные с помощью GRANT, как было показано выше.
Добавить все привилегии для таблиц БД db_name пользователю user@localhost
GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'localhost';
Удаление привилегий пользователя user@localhost для БД db_name:
REVOKEALLON db_name.* FROM 'user'@'localhost';
Сделать из пользователя суперпользователя и дать полный доступ ко всем БД на сервере
GRANT ALL ON *.* TO 'user'@'localhost';
Удалить пользователя 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;
Установить пароль для пользователя user в консоли сервера.
$ 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_TableNameCREATE TABLE NEW_TableName LIKE OLD_TableName INSERT INTO NEW_TableName SELECT * FROM OLD_TableName;На этом всё. Но вы можете поддержать проект. Даже небольшая сумма поможет нам писать больше полезных статей.
Если статья помогла или понравилась, пожалуйста поделитесь ей в соцсетях.