Welcome to WebmasterWorld Guest from 54.159.202.12

Forum Moderators: open

Message Too Old, No Replies

MySQL - export/import

export records from multiple tables

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 2, 2006
posts:2155
votes: 3


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
5:17 pm on June 9, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


The 1062 is most likely because it's not executing a drop table before creating and doing an insert. (?)
7:06 pm on June 9, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 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.
3:29 pm on June 10, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 2, 2006
posts:2155
votes: 3


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.
4:38 pm on June 10, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Well, if you just export the entire DB, there should be a box to "add drop" on export of the .sql file.
1:43 am on June 12, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 2, 2006
posts:2155
votes: 3


if you just export the entire DB


Can you do that for selected tables - add drop?

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

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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)
1:11 pm on June 20, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 2, 2006
posts:2155
votes: 3


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.