?
Solved

MySQL 4 to MySQL 5

Posted on 2013-06-02
2
Medium Priority
?
277 Views
Last Modified: 2013-06-02
I am being forced to convert an application from MySQL 4 to 5.

I thought it would be pretty simple. The problem I've run into is size.

The full database dumped into a sql file is 324MB. The MAXIMUM I am allows to import into the MySQL 5 created database is about 49 MB.

First question, is there a way to increase that? I'm guessing it's a server parameter & that the host (1 & 1 will not allow increasing it.

So I considered breaking it into pieces. There are tw individual tables that are themselves over 49MB. One is 115 MB & ones is 80.

I think I can selectively export the needed data in chunks under 49 MB each, I already did the biggest one; 18 MB in a csv file.

The real question is if I do the 4 or 5 largest tables individually, is there an easy way to export all the others in one "chunk"? There are a total of 64 tables, of course I understand I can do them one by one. I mean the 57 or 58 that I can lump together.

I am using phpmysql to to the exports / imports.

Thanks
0
Comment
Question by:Richard Korts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
SANDY_SK earned 2000 total points
ID: 39214707
Hi rkorts,

49MB is actually too small for it to stop, in fact i have restored dumps which are in GBs in mysql 5

what you can do is check in the my.ini file and change the value of the variable  max_allowed_packet to something like 500 MB like shown below. (This is possible if you have direct access to the mysql server)

max_allowed_packet=500M

when you are changing this value make sure you have stopped the mysql service and then restart it.

also try restoring it using the mysqldump command rather than phpmysql.
0
 

Author Comment

by:Richard Korts
ID: 39214724
To SANDY_SK

Thanks; I found that if I used gzip I could zip the 118 MB (largest table) to 20 MB.

So I'll just load it in a few pieces.

Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question