CODEWITHSUNDEEP
mysqldatabaseexport
Leon Claassen
Leon Claassen

Reputation: 183

MySQL Database Export (100 Rows from each table in a database)

I'm looking for a way to export +/- 100 rows from each table in a database into a MySQL script.

The reason for this is I have setup a mock Web server on my laptop, and want to replicate the structure and some data from our server. The databases on the server have been poorly setup with no indexes at all.

Once I have exported the +/- 100 Rows I want to recreate a mock environment on my laptop so I can work on improving this.

Any help will be greatly appreciated, Thanks.

Upvotes: 4

Views: 5237

Answers (3)

Khan Sharukh
Khan Sharukh

Reputation: 1201

Export

Please try the following for exporting within the machine you are logged in

mysqldump -u root -p --opt --lock-tables=false --where="1 limit 1000" database_name > db_backup.sql

Exporting from SSH try below

mysqldump -h SERVER_IP -u root -p --opt --lock-tables=false --where="1 limit 1000" database_name > db_ssh_backup.sql

Import

Please try the following for importing within the machine you are logged in

mysqldump -u root -p --opt --lock-tables=false --where="1 limit 1000" database_name < db_backup.sql

Importing via SSH try below

mysqldump -h SERVER_IP -u root -p --opt --lock-tables=false --where="1 limit 1000" database_name < db_ssh_backup.sql

Note

Export / Import from server may throw Unknown table 'COLUMN_STATISTICS' in information_schema (1109) error

Use the below flag to solve this issue

--column-statistics=0

Upvotes: 4

Amit Garg
Amit Garg

Reputation: 580

mysqldump -u {username} -p {db_name} --where="true limit 100" > db_name.sql

Upvotes: 1

SIDU
SIDU

Reputation: 2278

I doubt there are any tools available to export each table of 100 rows of a whole database at one time.

However you can export 100 rows of each table one by one:

goto http://topnew.net/sidu to download that little tool.

And export your table in the following way:

1 - click on your table name
2 - click export
3 - in where section, input: 1=1 limit 100

It will export max 100 rows for that table

And you need export tables one by one

Upvotes: -2

Related Questions