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

Question

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.

Answer 1

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

Answer 2

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