1
M_1008
phpMyAdmin doesn´t export big tables
  • 2012/2/28 11:11

  • M_1008

  • Just popping in

  • Posts: 18

  • Since: 2012/2/20


Hello,

I´m trying to import the data base from an obsolete web to a clean instalation (from XOOPS 2.2.3 to 2.5.4 and from CBB 3.04 to 4.06).

The problem is I can´t export the database with phpMyAdmin, if I try to download everything, all I get is a 0 bytes file.

Using a lot of patience, I´ve downloaded each of the 70 tables and everything goes well with the exception of prefix_bb_posts (82.7 MB), prefix_bb_posts_text (192.6 MB)
and prefix_priv_msgs (289.9 MB).

I searched a lot and if I understand well, the problem is a timeout that can be fixed changing $cfg['ExecTimeLimit'] to 0 at config.inc.php but I seem to have no access to this file with my provider.

Any idea?

On the other hand, the web was made back in 2006 by another person and I´ve found that I have all the tables repeated four times with 3 different prefixes, being duplicated even the one that´s in use but with last modification date from 2009.

Could I delete all this old tables without problem?


Thank you and excuse me about my poor english.

2
Peekay
Re: phpMyAdmin doesn´t export big tables
  • 2012/2/28 14:28

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


I must admit I have never had an issue due to timeouts when exporting, although importing a large DB can be a problem.

If you do eventually manage the export, 'Big Dump' will stagger the import to avoid timeouts.

http://www.ozerov.de/bigdump/
A thread is for life. Not just for Christmas.

3
Mamba
Re: phpMyAdmin doesn´t export big tables
  • 2012/2/28 17:33

  • Mamba

  • Moderator

  • Posts: 11409

  • Since: 2004/4/23


Quote:
I must admit I have never had an issue due to timeouts when exporting, although importing a large DB can be a problem.

Same here. Import could be a problem in phpMyAdmin, and sometimes I have to manually copy and paste parts of the file, one after another, so phpMyAdmin can digest them.

For exports, make sure that use the zip option to zip the files. This will make it faster to download.
Support XOOPS => DONATE
Use 2.5.11 | Docs | Modules | Bugs

4
Peekay
Re: phpMyAdmin doesn´t export big tables
  • 2012/2/28 19:30

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


@Mamba

You'll like Big Dump.

...assuming it works as well as it did when I recommended it back in 2005

The zip option sounds like a good idea too.
A thread is for life. Not just for Christmas.

5
Mamba
Re: phpMyAdmin doesn´t export big tables
  • 2012/2/28 19:46

  • Mamba

  • Moderator

  • Posts: 11409

  • Since: 2004/4/23


Also, let's not forget the "Dump" functionality in the "Maintenance" section of XOOPS Admin (XOOPS 2.5.x):

http://yoursite.com/modules/system/admin.php?fct=maintenance
Support XOOPS => DONATE
Use 2.5.11 | Docs | Modules | Bugs

6
M_1008
Re: phpMyAdmin doesn´t export big tables
  • 2012/2/28 22:27

  • M_1008

  • Just popping in

  • Posts: 18

  • Since: 2012/2/20


That functionality will be great in 2.5.4 if I finally get rid of 2.2.3.

I´ll try bigdump if I get errors importing, but I´m still stuck because I can´t export.

I´ve sent a mail to the hosting provider asking for help. I´ll post their answer if it can be helpfull to anyone.

7
Peekay
Re: phpMyAdmin doesn´t export big tables
  • 2012/2/28 22:44

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


You might want to try a third-party app to download the DB, like SQLYog (used to have a free version) or you could grab the MySQL files (.MYI etc.) with an app like SiteVault (not free... but really good as an off-server FTP/DB backup tool).

How big is the DB? (shown at the bottom of the 'size' column in PHPMyAdmin)
A thread is for life. Not just for Christmas.

8
M_1008
Re: phpMyAdmin doesn´t export big tables
  • 2012/2/29 12:29

  • M_1008

  • Just popping in

  • Posts: 18

  • Since: 2012/2/20


With all the duplicates, they are 1.2 GB, but I´m interested about downloading only a half of them.

Thank you for your answer, Peekay, I´ll try those programs.

9
bjuti
Re: phpMyAdmin doesn´t export big tables
  • 2012/2/29 13:43

  • bjuti

  • Just can't stay away

  • Posts: 871

  • Since: 2009/1/7 2



10
Peekay
Re: phpMyAdmin doesn´t export big tables
  • 2012/2/29 14:22

  • Peekay

  • XOOPS is my life!

  • Posts: 2335

  • Since: 2004/11/20


At 1.2 GB it might be better to do this via shell (assuming you have access). To paraphrase the advice given on this site

cd to the directory where you want to store your dump file, e.g.

cd /var/www/vhosts/yourdomain.com/httpdocs/mydirectory

Then tell mysql to dump it out:

Quote:
mysqldump –-add-drop-table -u the_db_username -p the_db_name > mybackup.sql


It will prompt you for your password.

The directory where you’re sending the dump file needs to be writeable by the server. You can then download the file via FTP.

If it remains a problem, you may have to bite the bullet and ask your host to do this.
A thread is for life. Not just for Christmas.

Login

Who's Online

430 user(s) are online (314 user(s) are browsing Support Forums)


Members: 0


Guests: 430


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits