Copy MySQL schema from remote host to local host

The secret is to use a program called mysqldump which comes with your installation of mysqld.

user@localhost the_directory>mysqldump --no-data -u root remote_db_name -h > temp-db-schema.mysql

user@localhost the_directory>mysql

mysql>create database dbname;
mysql> use dbname;
mysql> source temp-db-schema.mysql;

user@localhost the_directory>rm temp-db-schema.mysql

note: to clear all the data out of the tables and only leave the schema use the following command

mysql -u root --password=ENTER_PASS -Nse 'show tables' ENTER_DB | while read table; do mysql -u root --password=ENTER_PASS -e "truncate table $table" ENTER_DB; done

comments powered by Disqus