Page 1 of 1
How to import MySQL dumps with PHP?
Posted: Wed Jan 19, 2005 3:44 pm
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?
Posted: Wed Jan 19, 2005 4:13 pm
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.

My backups are ususally with at least --all-databases / -A options.
Posted: Sun Jan 30, 2005 11:33 am
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 */;
Posted: Sun Jan 30, 2005 11:38 am
by magicrobotmonkey
try
Code: Select all
LOAD DATA INFILE "/Users/gerhard/Sites/quickstart/typo3conf/tt_content.sql" INTO TABLE tt_content;
Posted: Sun Jan 30, 2005 11:44 am
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
Posted: Sun Jan 30, 2005 12:41 pm
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_?) 
Posted: Sun Jan 30, 2005 6:26 pm
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.)