export tables.csv:
export_tables.shmysql -uroot -p your_password -hlocalhost --batch --raw <<-EOF > tables.csvselect 'public' as table_schema,col.table_name,col.column_name,col.ordinal_position,col.data_type as column_type,case when (group_concat(constraint_type separator ', '))like '%PRIMARY KEY%'then 't' else 'f' end as primary_key,case when col.is_nullable = 'YES' then 't' else 'f' end as is_nullable,tab.table_comment,col.column_commentfrom information_schema.columns coljoin information_schema.tables tabon col.table_schema = tab.table_schemaand col.table_name = tab.table_nameand tab.table_type = 'BASE TABLE'left join information_schema.key_column_usage kcuon col.table_schema = kcu.table_schemaand col.table_name = kcu.table_nameand col.column_name = kcu.column_nameleft join information_schema.table_constraints tcoon kcu.constraint_schema = tco.constraint_schemaand kcu.constraint_name = tco.constraint_nameand kcu.table_name = tco.table_namewhere col.table_schema = 'your_database_name' and col.table_schema not in('information_schema', 'sys','performance_schema', 'mysql')group by 1,2,3,4,5,7,8,9order by col.table_schema,col.table_name,col.ordinal_positionEOF
You need to replace your_database_name & password & host & username
If there are foreign keys in your database schema, you can export it:
export_relations.shmysql -uroot --batch --raw <<-EOF > relations.csvSELECT 'public' AS 'schema',kcu.referenced_table_name AS 'table',kcu.referenced_column_name AS 'column',kcu.ordinal_position AS no,'public' AS relation_table_schema,kcu.table_name AS relation_table,kcu.column_name AS relation_columnFROM information_schema.key_column_usage AS kcuWHERE referenced_column_name IS NOT NULLAND CONSTRAINT_SCHEMA = 'your_database_name'EOF
You need to replace your_database_name & password & host & username
If there are not any foreign keys in your database schema, you don't need to import relations csv file.
You can use `auto draw` feature to detect relationship. It has rule:
users.account_id -> accouts.id
and check the AUTO DRAW checkbox:
result: