How to import MySQL dumps with PHP?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Uhlhorn
Forum Newbie
Posts: 5
Joined: Wed Jan 19, 2005 3:21 pm
Location: Hamburg

How to import MySQL dumps with PHP?

Post by Uhlhorn »

Hi,

I write the whole database in single files, table by table with this command in a 'for' loop.

Code: Select all

system(sprintf("mysqldump --opt -h $host -u $dbuser -p$dbpw $db $table > %s/$table.sql",$bkdir));
So, I get a lot of files containing tables. I copy this files from the server to my laptop. On my laptop I want read them back into a MySQL database. The variables are: $file, $db_host, $db_user, $db_password, $db.
I've read the MySQL documentation since 2 days, but I didn't found any instruction to read back the table.
What is the right instruction to import the and perhaps replace an existing table?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Doesn't LOAD DATA INFILE work for you?

If you --add-drop-table and perhaps some more options, you will be able to import them on the laptop without the need to drop them before.
Are you exporting a set of certain tables?

Never worked with exporting and loops before, so hence asking. :wink: My backups are ususally with at least --all-databases / -A options.
Uhlhorn
Forum Newbie
Posts: 5
Joined: Wed Jan 19, 2005 3:21 pm
Location: Hamburg

Post by Uhlhorn »

Hi JAM,

Thank You for Your answer. But it didn't work for me. (I'm a beginner!)

I've tried

Code: Select all

mysql> LOAD DATA INFILE "/Users/gerhard/Sites/quickstart/typo3conf/tt_content.sql" INTO TABLE test6.tt_content.sql;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '.sql' at line 1
and got the error, that the table does not exist. I want to import the dumped table. And MySQL should create the table and fill it with content.

In Typo3 I can import this file from the 'install' dialog. It's no problem. But I don't want to import 63 files manually.

This is the dumped file I want to import (shorted):

Code: Select all

-- MySQL dump 9.11
--
-- Host: db336.1und1.de    Database: db105030711
-- ------------------------------------------------------
-- Server version	4.0.23-standard-log

--
-- Table structure for table `tt_content`
--

DROP TABLE IF EXISTS tt_content;
CREATE TABLE tt_content (
  uid int(11) unsigned NOT NULL auto_increment,
  pid int(11) unsigned NOT NULL default '0',
  tstamp int(11) unsigned NOT NULL default '0',
  hidden tinyint(4) unsigned NOT NULL default '0',
  sorting int(11) unsigned NOT NULL default '0',
  CType varchar(30) NOT NULL default '',
...
  tx_xinittwocolumn_type int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (uid),
  KEY parent (pid)
) TYPE=MyISAM;

--
-- Dumping data for table `tt_content`
--


/*!40000 ALTER TABLE tt_content DISABLE KEYS */;
LOCK TABLES tt_content WRITE;
INSERT INTO tt_content VALUES (1,1,1087982660,0,1000000000,'text','Mal sehen, wie es geht',...;
UNLOCK TABLES;
/*!40000 ALTER TABLE tt_content ENABLE KEYS */;
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

try

Code: Select all

LOAD DATA INFILE "/Users/gerhard/Sites/quickstart/typo3conf/tt_content.sql" INTO TABLE tt_content;
Uhlhorn
Forum Newbie
Posts: 5
Joined: Wed Jan 19, 2005 3:21 pm
Location: Hamburg

Post by Uhlhorn »

Hi magicrobotmonkey,

thank You for the fast reply.

Code: Select all

ERROR 1146: Table 'test6.tt_content' doesn't exist
This is the problem. The db is empty. So 'tt_content' doesn't exists.

Gerhard
Uhlhorn
Forum Newbie
Posts: 5
Joined: Wed Jan 19, 2005 3:21 pm
Location: Hamburg

Post by Uhlhorn »

Hurray! I've found the soulution!

Code: Select all

Gerhards-Powerbook-Hamburg:~ gerhard$ mysql -h 127.0.0.1 -u root -p???????? -b test6 < /Users/gerhard/Sites/quickstart/typo3conf/tt_content.sql
Where '????????' is the actual password. This will import the shown file.

(Why makes this forum a '-you' instead of letter_u_?) :(
Uhlhorn
Forum Newbie
Posts: 5
Joined: Wed Jan 19, 2005 3:21 pm
Location: Hamburg

Post by Uhlhorn »

Is there an other way to do this:

Code: Select all

$command = "mysql -h %s -u %s -p%s -b %s < %s";
$command2 = printf($command,$dbhost,$dbuser,$dbpassword,$db,$dumpdatei);
$success = system($command2);
$eingelesen will result in false. So, the command system() will not work under Mac OS X. Can I do this directly from PHP without using a system command?

('-you' is wrong, should be the user option of MySQL. The board changes it by it self.)
Post Reply