How to pull out schema of db from MySQL/phpMyAdmin?

Question

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!

Answer 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

Answer 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.