Huge Database

Hi All,
First if all congrats for HeidiSQl comunity it's great.
Now the problem
I have a huge database that I want to export in sql (millions registers).
Is there a way to make multiple files, say a new file every 10.000/100.000 or whatever registers ?
Another option would be to export every row with it's own insert/replace into ... that way I could split the file externally and would work.
My last sql export was 800mb text file !!! and growing every day.
An other would be selective export, in the sense that I could export only register with id > last_backup.
Any idea, would be welcome.
Bye.
Lotiara.

Where's the problem with huge files? You never edit those in some editor manually, do you?

No, there is no such a splitting option. Also, the extended INSERT syntax is always used, no option to deactivate it. Single INSERTs perform veeeeeery slow when you import these again. HeidiSQL does not support slowness :)

You should see if your underlying tables can be trimmed at some point. It's a good discipline to have a cron job deleting old rows from tables which are pure logging tables.

The export feature has actually been simplified along the years. (I remember that old versions used to have several linked controls that were combined into a single "Output" combo and there was even a preview pane with sample SQL.)

The root problem is that the more options you add the more difficult to use it becomes. And if you browse this forum you'll find that some users can't figure out how to use the feature with current design!

Some time ago I was faced to a very specific need (create a SQL dump for version control purposes) and I suggested creating export profiles so you're able to fine tune the export but you aren't forced to do it every time. But the idea wasn't very successful :)

Hi ansgar,

I like this philosophy "HeidiSQL does not support slowness :)"
No registers can be deleted from the table, it's an historian
Database.
If you know any other solution, please share.
Thank you for answering.

kalvaro I agree things must be simple.
But simplicity is not always the best solution, anyway I am very happy with Heidi it's a wonderfull tool and a nice name too.

I will take a look at phpdump or any other scriptor even some bash script, the server is running in a centos 6 box.
Bye.

"the extended INSERT syntax is always used, no option to deactivate it."

Well, people have been asking for this for years and you choose to ignore it.

I would recommend the free MySQL Workbench to anybody who finds they need both options, as many of us do.

So basically you want HeidiSQL to look this way:



... and keep growing up forever as Anse adds every single new feature he's asked to. Alright, but I'm pretty sure you'll eventually come back and complaint because HeidiSQL is sloppy and complicate.

"So basically you want HeidiSQL to look this way:"

You are being very childish.

I pointed out that numerous times users have brought up the need for an option to not have extended inserts.

Each time you have responded with a childish comment, as again.

Tell us all, what happens when using a 350mb dump file and there is a connection problem? Yep, start again and pray.

Thus, you should consider the possibility of not just an option to not use extended inserts, but also to resume.

Hey, there is nothing childish here. This is weighing up the pros and cons of introducing some feature. Calling an opensource developer childish will decrease the chance he's doing anything for you.

I never heard good arguments for single INSERTs. There is not even an issue report in the tracker about that.

Apparently, the mere fact of expressing an opinion that contradicts yours is unacceptable.

Don't worry. Since it's obvious that your only intention is to rant and troll, this second answer will be the last one you'll ever get from me.

Keep cool guys.
The problem is very simple i have a database with millions registers, the dump file is actually 2.4gb yes gb.
If i had an insert for every register the file would be greater, but I could split it with a decent editor and use several small files.
If there was a possibility to have saveral files with say 100.000 resigsters the restore would be easy with a simple bat to handle all files. BUT.
BUT with a single 2.4 gb file, I've tried to restore the database in an other computer and heidi failed to restore. it simply can handle taht file, I had to kill it.
Bye.
It's not a childish question, neiter childish answer. Heidisql is a very nice tool and for me has a very limited backup system.
Batabase are bigger and bigger and a plain text backup is a very good idea for me.
Kalvaro between extreme simplicity and extreme complexity, there are several options.
Thank you, and please,be constructive.
Bye

IMO, heidisql is a multipurpose client with an emphasis on interactive usage, with a necessary balance between divergent features which of course means fringe cases will sometimes not be handled all that well. making all the these various features configurable in response to people´s wishes would indeed result in an options screen as depicted above by kalvaro; i see nothing childish in extrapolating this to its logical conclusion.

heidisql is not an ideal backup client; i suggest that you use the mysqldump and mysql command line clients for that (espec. mysqldump --skip-extended-insert, which should do what you want).