How to pull out schema of db from MySQL/phpMyAdmin?
Solution 1
Not sure exactly what you want. You can try one of these methods:
1) Use phpMyAdmin's export feature to export the database. PMA allows you to omit the data so get all CREATE TABLE
statements.
2) You can do the same using mysqldump. This command should export CREATE DATABASE/CREATE TABLE queries:
mysqldump -hlocalhost -uroot -proot --all-databases --no-data > create-database-and-tables.sql
3) You can pull information from mySQL schema tables. Most mySQL clients (phpMyAdmin, HeidiSQL etc) allow you to export result of queries as CSV. Some useful queries:
/*
* DATABASE, TABLE, TYPE
*/
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql')
ORDER BY TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
/*
* DATABASE, TABLE, COLUMN, TYPE
*/
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE /* ETC */
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql')
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
Solution 2
You can use mysqldump
from the console, e.g.
mysqldump -h localhost -u user -p -d -r dump.sql db_name
The -d
switch makes mysqldump
to dump the schema only, -r
directs output to a given file. You can also use -X
to write dump in XML format.
Solution 3
In the PhpMyAdmin's Export tab for the database, you want:
Check the box : Custom - display all possible options.
Under Format-specific options: section, choose Structure only
arun nair
Updated on August 07, 2020Comments
-
arun nair over 3 years
is there some way of pulling out the schema of your mysql databases?
I have lots of db and tables inside these databases, and I use phpmyadmin with WAMP in my local machine.
I need to pull out the schema, or atleast the database name, table name, columns and their attributes (e.g.
INT(2)
) and export it as csv/ excel/ whatever format which finally can be edited in excel.i use:
server:
Apache/2.2.21 (Win64) PHP/5.3.8 MySQL client version: mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $ PHP extension: mysqli Documentation
mysql:
Server: localhost (localhost via TCP/IP) Server version: 5.5.16-log Protocol version: 10 User: root@localhost MySQL charset: UTF-8 Unicode (utf8)
thanks!