Generate MySQL data dump in SQL from PHP

Question

I'm writing a PHP script to generate SQL dumps from my database for version control purposes. It already dumps the data structure by means of running the appropriate SHOW CREATE .... query. Now I want to dump data itself but I'm unsure about the best method. My requirements are:

I need a record per row
Rows must be sorted by primary key
SQL must be valid and exact no matter the data type (integers, strings, binary data...)
Dumps should be identical when data has not changed

I can detect and run mysqldump as external command but that adds an extra system requirement and I need to parse the output in order to remove headers and footers with dump information I don't need (such as server version or dump date). I'd love to keep my script as simple as I can so it can be hold in an standalone file.

What are my alternatives?

Meanwhile, I'm trying out SHOW INDEX and mysql_real_escape_string() — Álvaro González
– Álvaro González, Commented Apr 28, 2010 at 11:56
mysqldump doesn't really add an extra system requirement, it ships with mySQL. — Christian Studer
– Christian Studer, Commented Apr 30, 2010 at 12:01
So mysqldump adds MySQL as system requirement. Most devs around here don't have a local server: they connect to the common server in the LAN. — Álvaro González
– Álvaro González, Commented Apr 30, 2010 at 12:54
If MySQL is running on the common server and your script is also running on the common server, I don't see any problem. — cbednarski
– cbednarski, Commented Apr 30, 2010 at 18:23
Are you sure that versioning data using home made dumps is a good idea? I like the idea of versioning the schema, but data? — newtover
– newtover, Commented Apr 30, 2010 at 20:37

DSD · Accepted Answer · 2010-04-29 11:37:38Z

1

I think the mysqldump output parsing is still the easiest. I think there are really few meta-data you have to exclude, and thus dropping those should be only a few lines of code.

You could also look at the following mysqdump options: --tab, --compact

answered Apr 29, 2010 at 11:37

DSD

262 bronze badges

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

Comments

wesside · Accepted Answer · 2010-04-28 20:54:12Z

0

Could always try the system() function. Hope you are in linux :)

answered Apr 28, 2010 at 20:54

wesside

5,7605 gold badges32 silver badges35 bronze badges

1 Comment

Álvaro González Over a year ago

There are systems outside Linux as well :)

NeuroScr · Accepted Answer · 2010-04-30 11:46:35Z

0

"Select * from tablename order by primary key" you can get the table names from the information_schema database or the "show tables" command. Also can get the primary key from "show indexes from tablename" and then loop and create insert statements strings

answered Apr 30, 2010 at 11:46

NeuroScr

3221 silver badge7 bronze badges

Collectives™ on Stack Overflow

Generate MySQL data dump in SQL from PHP

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related