在php的共享主机上创建和导入mysql数据库 [英] Create and import mysql database on shared host in php
问题描述
我正在尝试编写如下的php脚本:
I'm trying to write php script that would:
- 连接到mysql
- 创建数据库
- 创建用户和密码
- 将用户添加到数据库
- 将准备好的sql文件导入数据库
问题在于它将在共享主机上(并且我正在尝试使其通用并与其他主机一起使用).我猜数据库和数据库用户将在共享主机上以我的帐户名(用于ftp,登录到控制面板等)为前缀?诸如mylogin _ dbname和mylogin _ dbuser之类的东西.例如,在cpanel中可见-当我添加数据库时,我输入其名称,并在创建数据库后cpanel将其显示为mylogin _ somedb.如何在多个不同的共享主机上使脚本与此脚本一起使用-根据我的主要登录名自动添加前缀?
The thing is that it'll be on shared host (and I'm trying to make it universal and work with different hosts). I guess database and db user will be prefixed with my account name (what I use for ftp, to login to control panel, etc) on shared hosts? Something like mylogin_dbname and mylogin_dbuser. It's visible for example in cpanel - when I add database I enter its name and after it's created cpanel shows it as mylogin_somedb. How do I make my script work with this on multiple different shared hosts - add my prefix automatically depending on my main login?
现在正在使用这样的代码(不知道它是否起作用,这就是我想到的东西):
Now working with such code (don't have a clue if it works, that's just what came to my mind):
<?php
mysql_connect("host", "user", "password"); // Connection to MySQL
$query = "CREATE DATABASE somedb;
USE somedb;
SOURCE path/to/sqlfile.sql;
CREATE USER someuser IDENTIFIED BY PASSWORD 'somepass';
GRANT SELECT,INSERT,UPDATE,DELETE ON somedb.* TO 'someuser'@'host';";
$arr= explode( ';', $query );
foreach( $arr as $command )
{
mysql_query( $command );
}
mysql_close(); // Disconnection from MySQL
?>
推荐答案
KISS原理:只是使用phpMyAdmin?几乎可以肯定已经安装了.如果不是,请安装它.
KISS principle: just use phpMyAdmin? It's almost certainly installed. If it's not, install it.
其强大的导入能力.如果您的数据库太大,则将其gzip.如果仍然很大,请尝试将其分成几部分.我怀疑您是否需要将其作为一项大交易进行转移.你呢?
Its import capability is magnificent. If your database is by any chance too big, gzip it. If it's still to big, try splitting it up in a few pieces. I doubt you need to transfer it as a single big transaction. Do you?
在第一个评论中的解释之后,就到了.这是我做的很简单的脚本.除了不看分隔符:一个查询==一行.
After the explanation in first comment, well, here goes. This is my very simplistic script which does what you want. Except it doesn't take a look at the separators: one query == one line.
<link rel="stylesheet" href="style/contents.css"/>
<?
function timesanitize($v) {
if ($v > 0)
return round($v, 4);
else
return 0;
}
$startmt = microtime();
include_once 'include/db.php';
$f = fopen("db.sql","r");
echo dbGetEngine() . "<br>";
echo "<ul>";
do {
$l = rtrim(fgets($f));
if (strlen($l) == 0)
continue;
if (substr($l, 0, 1) == '#')
continue;
$l = str_replace(
array("\\n"),
array("\n"),
$l);
if (dbGetEngine() == "pgsql")
$l = str_replace(
array("IF NOT EXISTS", "LONGBLOB"),
array("", "TEXT"),
$l);
try {
echo "<li>".nl2br(htmlspecialchars($l));
$mt = microtime();
$db->query($l);
echo "<ul><li>ok - " . timesanitize(microtime() - $mt) . "</ul>";
} catch (PDOException $e) {
echo "<ul><li>".$e->getMessage() . "</ul>";
}
} while (!feof($f));
fclose($f);
echo 'total: ' . timesanitize(microtime() - $startmt);
?>
它还会输出有关每个查询花费了多长时间的少量统计信息.它基于PDO;我相信PDO是在PHP5.1或PHP5.2中引入的.我认为修改它以直接与mysql_*()
函数一起使用应该是微不足道的,如果出于某些原因您更喜欢这样做.
It also outputs a small statistic of how long each query took. It's based around PDO; I believe PDO was introduced in PHP5.1 or PHP5.2. I think it should be trivial to modify it to work directly with mysql_*()
functions, if for some reason you prefer that.
再一次:是的,我知道这很糟糕.但只要它对我有效(tm),甚至对您有效::-)
And once again: yes, I know it sucks. But as long as it Works For Me (tm), and possibly You... :-)
要完成代码,请参见include/db.php
和示例include/config.php
:
To complete the code, here are include/db.php
and a sample include/config.php
:
include/db.php
:
<?
include_once 'include/config.php';
try {
$attribs =
array(
PDO::ATTR_PERSISTENT => $config['db']['persistent'],
PDO::ATTR_ERRMODE => $config['db']['errormode']
);
$db = new PDO(
$config['db']['uri'],
$config['db']['user'],
$config['db']['pass'],
$attribs
);
$db->query("SET NAMES 'utf8'");
$db->query("SET CHARACTER SET 'utf8'");
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
function dbGetEngine() {
global $config;
return substr($config['db']['uri'], 0, strpos($config['db']['uri'], ':'));
}
?>
include/config.php
:
<?
//$config['db']['uri'] = 'sqlite:' . realpath('.') . '/site.db'; // PDO's database access URI
$config['db']['uri'] = 'mysql:host=localhost;dbname=sitedb'; // server should be : 195.78.32.7
//$config['db']['uri'] = 'pgsql:host=localhost;dbname=sitedb';
$config['db']['user'] = 'user_goes_here'; // database username
$config['db']['pass'] = 'pass_goes_here'; // database password
$config['db']['persistent'] = false; // should the connection be persistent
$config['db']['errormode'] = PDO::ERRMODE_EXCEPTION; // PDO's error mode
?>
包括用于SQLite,MySQL和PostgreSQL的示例连接字符串.
Included are sample connection strings for SQLite, MySQL and PostgreSQL.
这篇关于在php的共享主机上创建和导入mysql数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!