Backup database(s) using query without using mysqldump

Question

I'd like to dump my databases to a file.

Certain website hosts don't allow remote or command line access, so I have to do this using a series of queries.

All of the related questions say "use mysqldump" which is a great tool but I don't have command line access to this database.

I'd like CREATE and INSERT commands to be created at the same time - basically, the same performance as mysqldump. Is SELECT INTO OUTFILE the right road to travel, or is there something else I'm overlooking - or maybe it's not possible?

Are you saying you can't do this? if that's so then I guess you want to replicate what phpMyAdmin does for exporting tables. — Prix
– Prix, Commented Aug 11, 2013 at 1:05
@Prix: your bold link uses mysqldump which the OP wants to avoid. — Basile Starynkevitch
– Basile Starynkevitch, Commented Aug 11, 2013 at 1:07
@BasileStarynkevitch I hope you can read the "can't" part of the bold link, also the follow up link is pretty straightforward as well. — Prix
– Prix, Commented Aug 11, 2013 at 1:09
@Steve To be fairly honest with you I don't think you have to give up on it, its fairly easy to make your own with php or perl, list tables, show how they were created store, then query each table for all the data and pre format it. — Prix
– Prix, Commented Aug 11, 2013 at 1:36

diego · Accepted Answer · 2014-10-06 22:07:26Z

30

Use mysqldump-php a pure-PHP solution to replicate the function of the mysqldump executable for basic to med complexity use cases - I understand you may not have remote CLI and/or mysql direct access, but so long as you can execute via an HTTP request on a httpd on the host this will work:

So you should be able to just run the following purely PHP script straight from a secure-directory in /www/ and have an output file written there and grab it with a wget.

mysqldump-php - Pure PHP mysqldump on GitHub

PHP example:

<?php
require('database_connection.php');
require('mysql-dump.php')
$dumpSettings = array(
    'include-tables' => array('table1', 'table2'),
    'exclude-tables' => array('table3', 'table4'),
    'compress' => CompressMethod::GZIP, /* CompressMethod::[GZIP, BZIP2, NONE] */
    'no-data' => false,            
    'add-drop-table' => false,      
    'single-transaction' => true,   
    'lock-tables' => false,        
    'add-locks' => true,            
    'extended-insert' => true      
);

$dump = new MySQLDump('database','database_user','database_pass','localhost', $dumpSettings);
$dump->start('forum_dump.sql.gz');
    ?>

edited Oct 6, 2014 at 22:07

diego

5794 silver badges6 bronze badges

answered Aug 11, 2013 at 2:38

cerd

2,2511 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ben Over a year ago

Beauty this could be it. I've moved on to a different problem but I'll come back to this in a bit. Will have a good look, cheers.

theremin Over a year ago

missing semicolon in 2nd line - how can I fix this without adding the required 60 characters?

shababhsiddique Over a year ago

very much appreciate this library. I was having a hard time fixing mysqldump permission and pathing issues when moving server. I just changed it up to use this.

James Osguthorpe Sep 22 at 12:44

If you were here right now I would kiss you, sir! I would hug and kiss you! Absolute legend! Someone give this fellow a prize, or a ribbon or something!

bob-the-destroyer · Accepted Answer · 2013-08-11 01:40:33Z

4

With your hands tied by your host, you may have to take a rather extreme approach. Using any scripting option your host provides, you can achieve this with just a little difficulty. You can create a secure web page or strait text dump link known only to you and sufficiently secured to prevent all unauthorized access. The script to build the page/text contents could be written to follow these steps:

For each database you want to back up:

Step 1: Run SHOW TABLES.
Step 2: For each table name returned by the above query, run SHOW CREATE TABLE to get the create statement that you could run on another server to recreate the table and output the results to the web page. You may have to prepend "DROP TABLE X IF EXISTS;" before each create statement generated by the results of these queryies (!not in your query input!).
Step 3: For each table name returned from step 1 again, run a SELECT * query and capture full results. You will need to apply a bulk transformation to this query result before outputing to screen to convert each line into an INSERT INTO tblX statement and output the final transformed results to the web page/text file download.

The final web page/text download would have an output of all create statements with "drop table if exists" safeguards, and insert statements. Save the output to your own machine as a ".sql" file, and execute on any backup host as needed.

I'm sorry you have to go through with this. Note that preserving mysql user accounts that you need is something else entirely.

edited Aug 11, 2013 at 1:40

answered Aug 11, 2013 at 1:35

bob-the-destroyer

3,1092 gold badges25 silver badges30 bronze badges

4 Comments

Ben Over a year ago

You should be sorry, bob. >:( Just kidding. Thanks for your help. It seems that there's something basic I'm forgetting, but can't find what it is. Ah well.

Ben Over a year ago

Yeah, not user accounts, thanks though, I want to dump the whole thing with its keys and create statements.

Ben Over a year ago

Note to future users: although I've accepted this answer as the most likely way to do it, I haven't actually tried it (sorry). Thanks though bob.

bob-the-destroyer Over a year ago

@Steve: no problem. It was meant as a last-resort answer. Hopefully someone else has a better solution for your case.

user266926 · Accepted Answer · 2014-02-05 16:42:20Z

1

Use / Install PhpMySQLAdmin on your web server and click export. Many web hosts already offer you this as a service pre-configured, and it's easy to install if you don't already have it (pure php): http://www.phpmyadmin.net/

This allows you to export your database(s), as well as perform other otherwise tedious database operations very quickly and easily -- and it works for older versions of PHP < 5.3 (unlike the Mysqldump.php offered as another answer here).

I am aware that the question states 'using query' but I believe the point here is that any means necessary is sought when shell access is not available -- that is how I landed on this page, and PhpMyAdmin saved me!

edited Feb 5, 2014 at 16:42

answered Feb 5, 2014 at 16:25

user266926

1212 bronze badges

2 Comments

Alex Over a year ago

Note that you do not always have PHP available and do not necessarily want to install it. Some might also have security concerns if PHP serivce is just running for administration purposes - so when installing be sure to configure it correctly.

MohanBabu Over a year ago

phpmyadmin wont be good option for automated backups. Also, for shared databases, the mysqldump is of no value to be an option to use.

Collectives™ on Stack Overflow

Backup database(s) using query without using mysqldump

3 Answers 3

4 Comments

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related