CODEWITHSUNDEEP
phpsqlmysql
Brickr
Brickr

Reputation: 134

How to back up MySQL table into a dump with PHP?

Is there any way to dump whole database using php but not to CSV or XML because I need it in pure SQL format.

Example of dump I need:

CREATE TABLE 'table_name' etc..;
INSERT INTO 'table_name' (field, field2) VALUES ('1','2');
INSERT INTO 'table_name' (field, field2) VALUES ('1','2');

I don't have usage of exec() or system(). And without manually creating an INSERT statement.. Example:

while($row = mysql_results) {
 $sql_dump = 'INSERT INTO ...';
}

Thank you! :)

Upvotes: 0

Views: 1092

Answers (3)

makriria
makriria

Reputation: 383

Try something like this

$command="mysqldump  --host=localhost --user=USER --password=***** DATABASENAME.table > db.sql";
system($command);

Upvotes: 0

Uday Sawant
Uday Sawant

Reputation: 5798

If you have remote access to database (MySQL port not ssh) you can try to set up synchronization between servers. You can use some other server where you have open or required access. a setup on localhost is enough.
Recent versions of PHPMyAdmin provides a functionality to set synchronization (install it on your local machine). Also SQLYog supports sync functionality.

Upvotes: 0

Ilion
Ilion

Reputation: 6872

You can run a SHOW CREATE TABLE query. This is done from within a mysql client:

+-------+----------------------------------------------------------------+
| Table | Create Table                                                   |
+-------+----------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `AAA` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------+

Data of course can be retrieved through any regular select statement and if you're doing it with a scripting language you can then store the data however you need.

If you're simply in the mysql client and have access to the file system you can run a Select ... Into Outfile statement:

SELECT * INTO OUTFILE 'backup.tab'
ENCLOSED BY '"'
FROM <TableName>;

The above will create a tab separated file.

To load the data back in run:

LOAD DATA INFILE 'backup.tab'
INTO TABLE <TableName>
ENCLOSED BY '"';

Trying to recreate INSERT statements from within MySQL will likely end up with issues over escaping special characters and things like that. Using CSV or Tab separated data files is seen as perfectly standard for exporting and importing data.

Upvotes: 2

Related Questions