homepage Welcome to WebmasterWorld Guest from 54.161.236.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL - export/import
export records from multiple tables
smallcompany




msg:4323528
 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




msg:4324170
 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




msg:4324216
 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




msg:4324617
 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




msg:4324656
 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




msg:4325139
 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




msg:4325547
 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




msg:4328237
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved