PHP利用XML备份MySQL数据库实例

"") header("Location:listtable.php");?>
 

$con=mysql_connect('localhost','root','xswlily');
$query="select * from $table ";
//数据库查询
$result=mysql_db_query("embed",$query,$con);
$filestr="<"."?xml version="1.0" encoding="GB2312"?".">";
$filestr.="<".$table."s>";
while ($row=mysql_fetch_array($result))
//列出所有的记录
{$filestr.="<".$table.">";
$fields=mysql_list_fields("embed",$table,$con);
$j=0;
//$num_fields=mysql_field_name($fields,$j);
//echo $num_fields;
while ($j$num_fields=mysql_field_name($fields,$j);
$filestr.="<".$num_fields.">";
$filestr.=$row[$j];
$filestr.="";
$j++;}
$filestr.="";
}
$filestr.="";
echo $filestr;
//以下是文件操作代码
$filename=$table.".xml";
$fp=fopen("$filename","w");
fwrite($fp,$filestr);
fclose($fp);
Echo "数据表".$table."已经备份成功！";?>

CREATE PROCEDURE和CREATE FUNCTION

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    [characteristic ...] routine_body

    proc_parameter:

    [ IN | OUT | INOUT ] param_name type

    func_parameter:

    param_name type

type:

    Any valid MySQL data type

characteristic:

    LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

routine_body:

    Valid SQL procedure statement or statements

Create procedure sp_Name ([proc_parameter ])
            routine_body

 Create functionsp_Name ([func_parameter ])
           Returns type
    Routine_body

mysql> delimiter //
mysql> create procedure g
    -> begin
    -> select version() i
    -> end
    -> //
Query OK, 0 rows affected

mysql> call getversion(@a
    -> //
Query OK, 0 rows affected

mysql> select @a;
    -> //
+---------------------+
| @a                  |
+---------------------+
| 5.0.45-community-nt |
+---------------------+
1 row in set (0.05 sec)

Drop table if exists user; 
    Create table user( 
        Id int unsigned not null auto_increment, 
        Name varchar(20) not null, 
        Pwd char(32) not null, 
        Primary key(Id) 
    ); 

Delimiter // 
    Create procedure insertuser(in username varchar(20),in userpwd varchar(32)) 
    Begin 
        Insert into welefen.user(Name,Pwd) values (username,md5(userpwd)); 
    End 
    // 

Delimiter // 
    Create procedure validateuser(in username varchar(20),out param1) 
    Begin  
        Select Pwd into param1 from welefen.user where Name=username; 
    End 
    // 

Delimiter // 
    Create procedure modifyPwd(in username varchar(20),in userpwd varchar(32)) 
    Begin 
        Update welefen.user set Pwd=md5(userpwd) where Name=username; 
    End 
    // 

<?php 
     
     if (!mysql_connect("localhost","root","welefen")){  
         echo "连接数据库失败"; 
    } 
     if (!mysql_select_db("welefen")){ 
         echo "选择数据库表失败<br>"; 
    } 
     
    $insert_user=array("welefen","welefen");//这里的welefen分别为用户名、密码 
     if (mysql_query("call insertuser('$insert_user[0]','$insert_user[1]')")){ 
         echo "添加用户$insert_user[0]成功<br>"; 
    }else { 
         echo "添加用户$insert_user[0]失败<br>"; 
    } 
     
    $validate_user=array("welefen","welefen");//这里的welefen分别为用户名、密码 
    mysql_query("call validateuser('$validate_user[0]',@a)"); 
    $Pwd=mysql_query("select @a"); 
    $result=mysql_fetch_array($Pwd); 
     if ($result[0]==md5($validate_user[1])){ 
         echo "用户$validate_user[0]验证正确<br>"; 
    }else { 
         echo "用户$validate_user[0]验证错误<br>"; 
    } 
     
    $modify_Pwd=array("welefen","weilefeng"); //welefen为用户名weilefeng为新密码 
     if (mysql_query("call modifyPwd('$modify_Pwd[0]','$modify_Pwd[1]')")){ 
         echo "用户$modigy_Pwd[0]的密码修改成功<br>"; 
    }else { 
         echo "用户$modigy_Pwd[0]的密码修改失败<br>"; 
    } 
     
    $delete_user=array("welefen");           //welefen为用户名 
     if (mysql_query("call deleteuser('$delete_user[0]')")){ 
         echo "用户$delete_user[0]删除成功<br>"; 
    }else { 
         echo "用户$delete_user[0]删除失败<br>"; 
    } 
    ?>  

<?php
header ( 'http-equiv="Content-Type" content="text/html; charset=utf-8"' );
$DB_host = "localhost"; //数据库主机
$DB_user = "root"; //数据库用户
$DB_psw = "root3306"; //数据库密码
$DB_datebase = "gk_yue39_com"; //数据库名
$DB_charset = "utf8"; //数据库字符集
$dbprefix="yue392_com_";
$new_dbprefix="yue39_com_";
$db = new mysqli ( $DB_host, $DB_user, $DB_psw ); //实例化对象

//检查连接
if (mysqli_connect_errno ()) {
 printf ( "Connect failed: %sn", mysqli_connect_error () );
 exit ();
}

$db->select_db ( $DB_datebase ); //选择操作数据库

$db->set_charset ( $DB_charset ); //设置数据库字符集

//执行一个查询
$sql = 'show tables';
$result = $db->query ( $sql );

echo $result->num_rows . ' 行结果  ' . $result->field_count . ' 列内容<br/>';

//$result->data_seek('5');//从结果集中第5条开始取结果

echo '<table border="1" cellspacing="0" cellpadding="0" align="center" width="90%">';

//循环输出字段名
//$result->field_seek(2);//从字段集中第2条开始取结果
while ( true == ($field = $result->fetch_field ()) ) {
 echo '<th>' . $result->current_field . '_' . $field->name . '(' . $field->length . ')</th>';
}

//循环输出查询结果
while ( true == ($row = $result->fetch_assoc ()) ) {
 echo '<tr>';
 foreach ( $row as $col ) {
$sql="rename table `".$col."` to `".str_replace ( $dbprefix, $new_dbprefix, $col)."`";
    if($db->query ( $sql )){
  echo '<td align="center">' . $sql. '</td><td><font color="blue"> success</font></td>';
  }else{
  echo '<td align="center">' . $sql. '</td><td><font color="red"> failed</font></td>';
  } 
  }
 echo '</tr>';
}

echo '</table>';
$result->free ();//释放结果集
$db->close (); //关闭连接
?>

function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
 
$time_start = microtime_float();
for ( $i=0; $i&lt;30000; $i++){ 
$dbh=new mysqli("XXX.XXX.XXX.XXX", "wubx", "wubxwubx", "userdb", 3308);
$dbh-&gt;close();
}
$time_end= microtime_float();
$time_use= ($time_end - $time_start)/30000;
print "$time_usen";
#php tconn.php
0.00090954260031382

net.ipv4.tcp_max_syn_backlog = 819200
net.core.netdev_max_backlog = 400000
net.core.somaxconn = 4096
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=0
#sysctl -p

public static function stringFromColumnIndex($pColumnIndex = 0)
    {
        //  Using a lookup cache adds a slight memory overhead, but boosts speed
        //  caching using a static within the method is faster than a class static,
        //      though it's additional memory overhead
        static $_indexCache = array();
 
        if (!isset($_indexCache[$pColumnIndex])) {
            // Determine column string
            if ($pColumnIndex < 26) {
                $_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
            } elseif ($pColumnIndex < 702) {
                $_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) .
                                              chr(65 + $pColumnIndex % 26);
            } else {
                $_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) .
                                              chr(65 + ((($pColumnIndex - 26) % 676) / 26)) .
                                              chr(65 + $pColumnIndex % 26);
            }
        }
        return $_indexCache[$pColumnIndex];
    }

PHPExcel_Cell::stringFromColumnIndex($i); // 从o开始

PHPExcel_Cell::columnIndexFromString(‘AA’);