Data Center
Database tricks
What commands would I use from the MySQL command line to recreate a large database table's structure without having to type it all back in through the MySQL Administrator or PHP-MyAdmin GUI?
After making sure that you have the mysql command-line command available on your system, you would start by connecting to your database server using the "mysql -u username -h hostname -p" command. Replace "username" and "hostname" with the name of your database server. Once connected, issue the "show databases;" command to list the databases on the server. Then use the "use databasename;" command to tell the server to work with the "databasename" database. After choosing the database to work with, issue the "show tables;" command to list the tables in the database. Use the "show columns from tablename;" command to list the column names and their associated column types from the table name table. Copy and paste the output to a text file using the system clipboard copy/paste feature. Exit the mysql command interpreter by issuing the "quit" command at the mysql prompt. Use your favorite search-and-replace tools to convert the text file to a command string for mysql of the form "create table newtablename (fieldname1 field1type fieldname2 field2type... )," then return to the mysql command prompt and issue the "create table..." command. This will create a new table in the database called "newtablename."
| Use this form to start a public discussion with other Linux World users on this article. Log In | Register for an account (Why you should) |
Note: Register to have your user name appear; otherwise your comment will show up as "Anonymous."
*Anonymous comments will only appear once they are approved by the moderator.
• Xandros buys Linspire, says ex-CEO
• A Red Hat packed with dynamite
• Deposition challenges Trend Micro patent
• HP throws Tru64 code open
• Firefox 3 vulnerability found
Nerdapalooza Orlando, Florida, July 4-5 2008.
LinuxWorld Conference and Expo San Francisco, August 4-7, 2008.
Linux Plumbers Conference Portland, OR, Sept. 16-19, 2008.
FreedomHEC Santa Monica, November 8-9, 2008.