For web development purposes it is necessery to copy MySQL SQL database schema structure without copying the filled in data. A typical case where a replicate of SQL server structure is needed to be installed on another server is on whether a client is bying a new website and it is planned his website Database Design is similar or same like another already working productive website.
Thanksfully, one doesn't have to script in perl or bash cause mysqldump dump tool has already integrated option for that (–no-data).
Here what mysqldump man page says of –no-data;
–no-data, -d
Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the
CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file).
1. Moving SQL data scheme for all databases in MySQL Server
On host with SQL containing productive data, to dump only the structure of databases / tables and table type, fields rows etc.:
host1# mysqldump -d -h localhost -u root -p'your_password' >sql-all-dbs-tables-empty-structure.sql
Then on the secondary MySQL server, where empty SQL structure (without any filled in info) is needed run:
host2# mysql -u root -p'your_password' < sql-all-dbs-tables-empty-structure.sql
2. Moving SQL data structure for only concrete database
On Linux host1 shell issue;
host1# mysqldump -d -h localhost -u root -p'your_password' database_name>sql-database-empty-structure.sql
On host2 server type;
host2# mysql -u root -p'your_password' < sql-database-empty-structure.sql
3. Moving SQL data structure for few databases
Lets say you have a user (new_user), who has privileges over a number of databases and you want to dump a dump copy of those empty databases;
Same like with one table, just include names of all databases scheme to dump;
host1# mysqldump -d -h localhost -u new_user -p'your_password' database_name atabase_name2 database_whatever >sql-only-some-databases-structure.sql
Then to import on host2 again;
host2# mysql -u new_user -p'your_password' < sql-only-some-databases-structure.sql
4. Dumping and copying only database names from one MySQL to another
Though the case might be rary you might need to dump and copy only list of all databases existing without recreating table database sub-structure. This is doable like so:
On SQL node host1 run;
host1# for i in $(echo "show databases;" | mysql -u root -p|grep -v -E 'Database$' |grep -v information_schema); do echo $i >> structure.txt; done
host2# for i in $(cat structure.txt); do echo "create database $i;" | mysql -u root -p; done
Though I've tested all this and it is safe to use, if you're re-creating SQL database / tables structure make sure you have a working copy of data from SQL.
Well that's it hope this little article helps someone 🙂
