MySQL help?

[trekker]

I have an existing database DB1. I would like to create another database DB2 with the exact same tables as DB1. Then I want to load the same data from DB1 to DB2. Anyone can teach me how to achieve that? I'm using MySQL running on RedHat 7.0

Thanks in advance!

[Salmon]

You can dump the entire database to a file using mysqldump on the command line:

mysqldump DB1 > DB1.sql

and then create and identical database using that file:

mysql DB2 < DB1.sql

Assuming that DB2 has already been created using mysqladmin.

You can also just do it with one simple command by piping the output of mysqldump directly into mysql:

mysqldump DB1 | mysql DB2

[trekker]

Thanks.

Here's what I did:
mysql> create database DB2;
then:
mysqldump DB1 > DB1.sql
mysql DB2 < DB1.sql

but I got this error message:
ERROR 1064 at line 8260: You have an error in your SQL syntax near 'mid(mid)) TYPE=MyISAM' at line 18

What does this mean and how do I get it to work?

I've check DB2 and it tallys with DB1 until the first few tables. The rest weren't created.

Any ideas?

[Salmon]

I got this error message:
ERROR 1064 at line 8260: You have an error in your SQL syntax near 'mid(mid)) TYPE=MyISAM' at line 18

Look like trouble with the MID function (takes 3 arguments for one thing).

What does the full statement that is giving errors look like?


I've check DB2 and it tallys with DB1 until the first few tables. The rest weren't created.

Querys stop being made after the an error is encountered so the rest of the script wasn't finished. Prior to running the script again, you'll have to drop the tables that were created or you'll get additional errors (there are other ways to circumvent this problem, but droping the tables is the easiest way in this situation).

[trekker]

Here it is: (from DB1.sql)
CREATE TABLE message (
mid int(11) NOT NULL auto_increment,
iid int(11)default NULL,
parentmid int(11) NOT NULL default '0',
uid int(11) NOT NULL default '0',
name varchar(255)NOT NULL default '',
description text,
creationdate datetime default NULL,
updatedate datetime default NULL,
updateby int(11) default NULL,
status varchar(20) NOT NULL default 'OK',
priority varchar(10) default NULL,
email varchar(60) default NULL,
authorname varchar(60) default NULL,
authormail varchar(60) default NULL,
PRIMARY KEY (mid),
KEY uid(uid),
KEY mid(mid)
) TYPE=MyISAM;

Arrgh!! Don't see what is wrong with it....

Thanks for the help!

[Salmon]

My guess is that MySQL is trying to interpret

KEY mid(mid)

as the MySQL function MID()

If this is the case, I would recommend simply altering the column name.

[trekker]

*whew* That helped!!

I didn't wanted to change the col name so I took a gamble and tried changing "KEY mid(mid)" to "KEY mid (mid)". It worked!!

Thanks for the tip Salmon!