There are multiple ways this can be done. I will mention two here. Method 1 In MySQL, run these statements:
/*This will create table2 with the schema of table1*/
CREATE TABLE `table2` LIKE `table1`
/*This will copy the data from table1 to table2*/
INSERT INTO `table2` SELECT * FROM `table1`
Method 2 On the command line, run these commands:
# Create a dump from MySQL.
# The -d flag will only copy the structure/schema of the tables. If you want
# to copy the data along with the schema then dont use -d flag
# In here, two MySQL commands are piped so the output from mysqldump is sent
# to mysql command
mysqldump -d -u username -pPassword db_name | mysql -u username -pPassword db_new_name
If you do:
Mysql will give you table information which will contain field names, their types, etc. What if you want just the field names and nothing else? You can run this query and do just that:
WHERE table_name = 'tablename'
AND table_schema = 'dbname';
Here’s how you would import data into MySQL using a query
If the CSV file was created in Windows, then replace '\n' with '\r\n'
LOAD DATA LOCAL INFILE '/path/to/csv_file.csv' INTO TABLE `table_name`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "\""
LINES TERMINATED BY '\n'
(field1, field2, field3);
Run a query from command line
# Use the -e flag
shell>mysql -e "select * from table"
Tell MySQL to output the results of a query in HTML or XML
# On command line you would write the following to
# tell MySQL to output the results in HTML format
shell>mysql -e "select * from table" --html
# This is for XML
shell> mysql -e "select * from table" --xml
# This is for batched results
shell> mysql -e "select * from table" --batch