8

i have a db in phpmyadmin having 3000000 records. i want to export this to another pc. now when i export this only 200000 entries exported into .sql file and that is also not imported on the other pc.

Community
  • 1
  • 1
Aamir
  • 273
  • 2
  • 5
  • 10
  • Try using this tool: http://www.ozerov.de/bigdump.php – Danpe Apr 25 '11 at 12:48
  • Why using phpMyAdmin ? Its better to use mysqldump – Riba Apr 25 '11 at 13:13
  • what is mysqldump? actually my db is in phpmyadmin :( – Aamir Apr 25 '11 at 15:26
  • phpMyAdmin is only an web interface to access your MySQL database. mysqldump is a command used to dump your database. – Riba Apr 25 '11 at 20:01
  • how u use that? and where to get that? – Aamir Apr 26 '11 at 00:09
  • You have to use it directly on your server. Connect via SSH to the box, and use it from the command line. Something like : `mysqldump -u -p > dump.sql` You will be prompted for the database password. Then you can gzip it and move it with SCP to your other server. – Riba Apr 26 '11 at 02:21
  • u r not getting what i m asking. i am asking which SPECIFIC SOFTWARE i may download? these "mysqldump -u -p > dump.sql" will be used with tht software i guess. right? – Aamir Apr 26 '11 at 09:41
  • YOU HAVE NOTHING TO DOWNLOAD !!!!! mysqldump is installed by default on your server ... – Riba Apr 26 '11 at 15:44
  • Did you know what is SSH ? and a terminal ? – Riba Apr 26 '11 at 15:47

1 Answers1

7

Answering this for anyone else who lands here. If you can only use phpMyAdmin because you do not have SSH access to the MySQL service or do not know how to use command line tools, then this might help. However as the comment above suggest, exporting a database of this size would be far easer with mysqldump.

phpMyAdmin (I'm using v3.5.6) allows tables to be exported individually like so:

  1. Select the database to view/edit. A list of all the tables should appear.
  2. In the list of tables, tick the checkbox to the left of the table you want to export.
  3. At the bottom of the tables list, select 'Export' from the 'With Selected:' select box.
  4. The export screen appears. Often a 'Quick' export will work, and save it as 'SQL' format.

phpMyAdmin also allows records to be exported individially or in groups, so you could for example, export just 10 records from a table with 100,000.

  1. Follow steps 1 to 3 above.
  2. Instead of 'Quick' select 'Custom'.
  3. Under 'Rows' select 'Dump some row(s)'
  4. Then enter the number of rows you want to export, and the starting row number.
  5. Under 'Data dump options' at the bottom of the export screen, check that 'Maximal length of created query' is large enough.
  6. Note: If you are exporting large BLOB data for example, this figure might have to be raised.

Hope that helps someone new to phpMyAdmin or DBA via the web.

Mere Development
  • 2,439
  • 5
  • 32
  • 63