mysqldump partial database

Question

I recently decided to switch the company through which i get my hosting, so to move my old db into my new db, i have been trying to run this:

mysqldump --host=ipaddress --user=username --password=password db_name table_name | mysql -u username -ppassword -h new_url new_db_name

and this seemed to be working fine.. but because my database is so freaking massive, i would get time out errors in the middle of my tables. So i was wondering if there was any easy way to do a mysqldump on just part of my table.

I would assume the work flow will look something like this:

create temp_table
move rows from old_table where id>2,500,000 into temp_table
some how dump the temp table into the new db's table (which has the same name as old_table)

but i'm not exactly sure how to do those steps.

Not sure if it will solve your problem, but check out the `--quick` option at http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html. Says it's useful for large tables. — Matt Fenwick, Jan 18 '12 at 17:56

Shiplu Mokaddim · Accepted Answer · 2012-01-19T11:58:30.133

6

Add this --where="id>2500000" at the end of mysqldump command. ^{MySQL 5.1 Reference Manual}

In your case the mysqldump command would look like

mysqldump --host=ipaddress \
    --user=username \
    --password=password \
    db_name table_name \
    --where="id>2500000

If you dump twice. The second dump will contain table creation info. But next time you want to add the new rows only. So for second dump add --no-create-info option in mysqldump command line.

edited Jan 19 '12 at 11:58

answered Jan 18 '12 at 18:13

Shiplu Mokaddim

56,364
17
141
187

as it turns out, this command actually replaced my table with the rows where "id>2500000". so what i ended up doing was moving them all to a new table, and merging the two tables with [this post](http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables) – BananaNeil Jan 19 '12 at 07:35
@BananaNeil It can happen if both your dumps contain To dump only the data second time add `--no-create-info` option on the mysql command line. – Shiplu Mokaddim Jan 19 '12 at 11:53

score 4 · Answer 2 · answered Dec 10 '12 at 12:35

I've developed a tool for this job. It's called mysqlsuperdump and can be found here:

https://github.com/hgfischer/mysqlsuperdump

With it you can speciffy the full "WHERE" clause for each table, so it's possible to specify different rules for each table.

You can also replace the values of each column by each table in the dump. This is useful, for example, when you want to export a database dump to use in development environment.

mysqldump partial database

2 Answers2

Linked