Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

import/export very large mysql database in phpmyadmin

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.

like image 732
Aamir Avatar asked Apr 25 '11 12:04

Aamir


1 Answers

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.

like image 178
Mere Development Avatar answered Sep 18 '22 00:09

Mere Development