php 生成逐个表导出的sql shell脚本, mysql数据导入

原创

mzh9112 2021-08-13 01:07:11 博主文章分类：php ©著作权

文章标签 php mysql mysqldump sql bash 文章分类 PHP 后端开发

©著作权归作者所有：来自51CTO博客作者mzh9112的原创作品，请联系作者获取转载授权，否则将追究法律责任

* export.php

<?php

// BEGIN config
$settings = [
    'host' => '172.16.0.224',
    'port' => '3306',
    'name' => '数据库名',
    'username' => 'root',
    'password' => '*****',
    'charset' => 'utf8'
];
// END config

function get_shell_suffix() {
    switch (php_uname('s')) {
        case 'Windows NT':
            $suffix = ".bat";
            break;
        case 'Linux':
            $suffix = ".sh";
            break;
        default:
            $suffix = ".sh";
    }
    return $suffix;
}

try {
    /** @var $pdo \PDO */
    $pdo = new PDO(
        sprintf(
            "mysql:host=%s;dbname=%s;port=%s;charset=%s",
            $settings['host'],
            $settings['name'],
            $settings['port'],
            $settings['charset']
        ),
        $settings['username'],
        $settings['password']
    );
} catch (PDOException $e) {
    echo "Database connection failed";
    exit;
}

/** @var $stmt \PDOStatement */
$stmt = $pdo->prepare("show tables", [
    PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY
]);
$stmt->execute();

$arr = [];
// dbname
$attr = "Tables_in_".$settings['name'];
while (($o = $stmt->fetchObject()) !== false) {
    array_push($arr, $o->{$attr});
}
$stmt->closeCursor();

// var_dump($arr);
is_dir("out") || mkdir("out");

$shellPath = dirname(__FILE__)."/out/dump".get_shell_suffix();
$out = fopen($shellPath, "w");
$sqlDir = "sql";
is_dir($sqlDir) || mkdir ($sqlDir);

array_walk($arr, function(&$item, $key, $c) use($out, $sqlDir) {
    $line = sprintf("mysqldump -u%s -h%s -p%s --databases %s --table %s > %s/%s.sql\n",
        $c['username'], $c['host'], $c['password'], $c['name'], $item, $sqlDir, $item);
    fwrite($out, $line);
}, $settings);

* run:

php export.php

bash ./out/dump.sh

windows:

.\out\dump.bat

* import.php

<?php
// usage:
// php import.php
// .\out\import.bat

// BEGIN config
$settings = [
    'host' => '127.0.0.1',
    'port' => '3306',
    'name' => '数据库名',
    'username' => 'root',
    'password' => '*****',
    'charset' => 'utf8'
];
// END config
function get_shell_suffix() {
    switch (php_uname('s')) {
        case 'Windows NT':
            $suffix = ".bat";
            break;
        case 'Linux':
            $suffix = ".sh";
            break;
        default:
            $suffix = ".sh";
    }
    return $suffix;
}

function scan_dir($dir, $handler) {
    // Open a known directory, and proceed to read its contents
    if (!is_dir($dir)) {
        return;
    }
    if ($dh = opendir($dir)) {
        while (($file = readdir($dh)) !== false) {
            if ($file !== "." && $file != "..") {
                call_user_func($handler, $file);
            }
        }
        closedir($dh);
    }
}

/**
 * @param $sql string sql
 * @param $settings  array assoc 'username', 'password'
 * @return string
 */
function sql_exec_cmd($sql, $settings) {
    return sprintf("mysql -u%s -p%s -e \"%s\"\n", $settings["username"], $settings['password'], $sql);
}

function sql_exec_cmd_db($sql, $settings) {
    return sprintf("mysql -u%s -p%s --database %s -e \"%s\"\n",
        $settings["username"], $settings['password'],
        $settings["name"],
        $sql);
}

is_dir("out") || mkdir("out");

$shellPath = dirname(__FILE__)."/out/import".get_shell_suffix();
$out = fopen($shellPath, "w");

$createDB = sql_exec_cmd(sprintf("CREATE DATABASE IF NOT EXISTS `%s`", $settings['name']), $settings);
fwrite($out, $createDB);
fwrite($out, sql_exec_cmd(sprintf("USE %s", $settings['name']), $settings));

$pwd = dirname(__FILE__);
$sqlDir = $pwd.DIRECTORY_SEPARATOR.'sql';

scan_dir($sqlDir, function($file) use ($out, $settings, $sqlDir) {
    // echo $file.PHP_EOL;
    $sql = sprintf("source %s", $sqlDir.DIRECTORY_SEPARATOR.$file);
    fwrite($out, sql_exec_cmd_db($sql, $settings));
});

fclose($out);

* run:

.\out\import.bat

修改mysql密码

set password for root@localhost = password('xxxxxxxxxx');

mysql数据库重命名

https://blog.csdn.net/fareast_mzh/article/details/87936959

开发过程中需要debug一个一个的小功能

需要备份、恢复数据，避免重复的手工操作。

可以写2个shell脚本 dump.sh load.sh

* dump.sh

#!/bin/bash
set -v
/opt/lampp/bin/mysqldump -uroot -hlocalhost -ptest1111 --port 3306 --databases zkt_crm --tables 5kcrm_oa_examine_data > 5kcrm_crm_oa_examine_data.sql
/opt/lampp/bin/mysqldump -uroot -hlocalhost -ptest1111 --port 3306 --databases zkt_crm --tables 5kcrm_oa_examine > 5kcrm_crm_oa_examine.sql
/opt/lampp/bin/mysqldump -uroot -hlocalhost -ptest1111 --port 3306 --databases zkt_crm --tables 5kcrm_crm_report > 5kcrm_crm_report.sql
/opt/lampp/bin/mysqldump -uroot -hlocalhost -ptest1111 --port 3306 --databases zkt_crm --tables 5kcrm_oa_finance > 5kcrm_oa_finance.sql
/opt/lampp/bin/mysqldump -uroot -hlocalhost -ptest1111 --port 3306 --databases zkt_crm --tables 5kcrm_admin_examine_record > 5kcrm_admin_examine_record.sql

* load.sh

#!/bin/bash
set -v
/opt/lampp/bin/mysql -uroot -hlocalhost -ptest1111 --port 3306 --database zkt_crm < 5kcrm_crm_oa_examine_data.sql
/opt/lampp/bin/mysql -uroot -hlocalhost -ptest1111 --port 3306 --database zkt_crm < 5kcrm_crm_oa_examine.sql
/opt/lampp/bin/mysql -uroot -hlocalhost -ptest1111 --port 3306 --database zkt_crm < 5kcrm_crm_report.sql
/opt/lampp/bin/mysql -uroot -hlocalhost -ptest1111 --port 3306 --database zkt_crm < 5kcrm_admin_examine_record.sql
/opt/lampp/bin/mysql -uroot -hlocalhost -ptest1111 --port 3306 --database zkt_crm < 5kcrm_oa_finance.sql