MySQL - export/import - Databases forum at WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

MySQL - export/import

export records from multiple tables

smallcompany

5:13 pm on Jun 8, 2011 (gmt 0)

I was moving wordpress based blog from one server to another. Among all things I wanted to move the stats from one of my plugins which were in mysql.

In phpmyadmin I went to export on a database level, selected tables and exported them into one file.

On import I always received a complaint 1062.

I ended up going into each table separately and selecting export from there which worked fine.

I'll probably be doing more than once and I wonder if it's possible to select more than one table and export records only like when you do it for each table separately.

Thanks

rocknbil

5:17 pm on Jun 9, 2011 (gmt 0)

The 1062 is most likely because it's not executing a drop table before creating and doing an insert. (?)

Demaestro

7:06 pm on Jun 9, 2011 (gmt 0)

Something to watch out for when doing exports from PHPMYADMIN is to set the "Maximal length of created query" to something really really high. I have reached the default limit a couple times and it has messed up the export.

smallcompany

3:29 pm on Jun 10, 2011 (gmt 0)

Thanks to both.

In regards of the DROP, I tried to find the setting in phpmyadmin when exporting whole tables, but it did not work or I did not know how to do it.

I'll check that length next time when I use it.

The whole scenario is moving WP blogs and I will have maybe couple of more as I'm switching the hosting provider.

rocknbil

4:38 pm on Jun 10, 2011 (gmt 0)

Well, if you just export the entire DB, there should be a box to "add drop" on export of the .sql file.

smallcompany

1:43 am on Jun 12, 2011 (gmt 0)

if you just export the entire DB

Can you do that for selected tables - add drop?

Thanks

rocknbil

4:56 pm on Jun 13, 2011 (gmt 0)

I don't know. The question is why you'd need to do it on selected tables. If you're moving a database, it should be easy - export entire database in an .sql dump, create new DB on new site, import new .sql file, done. If you're getting errors, I'd solve the problem, not make more work by doing individual tables.

At any rate, you can edit an .sql file like any other text file and add your own commands. It's really just a batch file. An example, for one table,


DROP TABLE IF EXISTS `your-table-name`; 
CREATE TABLE IF NOT EXISTS `your-table-name` ( 
 `id` int(10) unsigned NOT NULL auto_increment, 
 `target` varchar(100) NOT NULL default '', 
 `field_1` varchar(100) NOT NULL', 
 `field_2` tinyint(2) NOT NULL default 0 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

(followed by insert statements)

smallcompany

1:11 pm on Jun 20, 2011 (gmt 0)

OK. Thanks.

The question is why you'd need to do it on selected tables.

I knew there was more than one way of transferring WP blog onto another server (same domain).

I opted for export from within WP, and installed plugins manually.

The only leftover was data from statistics plugin. It was few more tables within existing WP database.
That was what I ended up exporting each table record separately, and wondered why MySQL would complain when I tried to import (and overwrite) whole tables.