Table does not show tables relations after importing database

I imported a MySQL dump file into my MySQL server using following command.

mysql> create database database_name;
mysql> use database_name;

In Linux command prompt,

$ mysql -u user_name -p database_name < /tmp/test1.dmp

But, while viewing the database using phyMyAdmin the newly created database_name does not show table relations. It shows only the tables, but not relations.

Is there any set up required before importing the database in mys开发者_Python百科ql database?

How to extract the relations between tables?

I just went through the exact same problem.

Is there any set up required before importing the database in mysql database?

Not exactly, but it seems LAMP Server installed on Ubuntu or any Linux Distribution uses MyISAM storage engine by default while creating tables. On InnoDB supports Foreign Key relation. ^{[more info]}

To change the storage engine afterwards. [Source answer]

You have to add the line default-storage-engine = InnoDB under the [mysqld] section of your mysql config file (my.cnf or my.ini depending on your operation system) and restart the mysqld service. I don't believe you can change this through phpMyAdmin.

On ubuntu, my.cnf is located inside /etc/mysql/.

Or, you can use mysql command

mysql> SET storage_engine=InnoDb;

After this, all the tables and database you create after this, will use InnoDB as their default storage engine, thus eliminating the issue afterwards.

How to extract the relations between tables?

After you change default engine of your database. You also have to change the default engine of your tables, because they haven't been changed yet. Use the syntax below to change the storage engine

ALTER TABLE <table_name> ENGINE = innodb

Using phpMyAdmin

• Go to the operations tab after selecting a table
• Go to the table options [See below]
• You will see an option to change the storage engine of the table

  

• Change the storage engine to InnoDb and hit Go

After this, export the database using phpMyadmin or dump your database using mysqldump. it will show the relations.