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

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.

Answer 2

In the PhpMyAdmin's Export tab for the database, you want:

1. Check the box : Custom - display all possible options.
   

2. Under Format-specific options: section, choose Structure only
   

Answer 3

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.

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