shell脚本 、swoole 导出所有数据库

 shell脚本导出所有数据库

#!/bin/bash
#shell 脚本导出所有数据库
#时间
DATE=$(date +%F_%H-%M-%S)
#数据库链接地址
HOST=localhost
#数据库用户
USER=root
#数据库用户密码
PASS=root
#数据库导出备份目录
BACKUP_DIR=/www/shell/sql_shell/v1/backup
#获取所有数据库

#2>/dev/null的意思就是将标准错误stderr删掉。
# 1：> 代表重定向到哪里，例如：echo "123" > /home/123.txt
# 2：/dev/null 代表空设备文件
# 3：2> 表示stderr标准错误
# 4：& 表示 等同于 的意思，2>&1，表示2的输出重定向 等同于 1
# 5：1 表示stdout标准输出，系统默认值是1，所以">/dev/null"等同于 "1>/dev/null"

#egrep -v 过滤掉后面的库
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

#循环导出
for DB in $DB_LIST; do
    BACKUP_DB_DIR=$BACKUP_DIR/${DB}
    #目录不存在则创建
    [ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/null
    /usr/bin/mysqldump --skip-comments -h$HOST -u$USER -p$PASS   ${DB} > ${BACKUP_DB_DIR}/${DB}_${DATE}.sql
    echo '成功导出数据库：'${DB};
    # TABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $DB;show tables;" 2>/dev/null)
done

 使用swoole task任务 导出数据

swoole.php

<?php
require_once 'db.php';
use Swoole\WebSocket;

class WebsocketServer
{
    private $host = "0.0.0.0";

    private $port = "9501";
    /**
     * [private description]
     * @var Swoole\Table
     */
    private $table;

    /**
     * [private description]
     * @var Swoole\WebSocket\Server
     */
    private $server;
    
    // worker_num是用来处理请求逻辑的进程数,task_num是异步任务投递进程
    
    private $config = [
        'worker_num'=>2,
        //task_worker_num  最大数量不超过 swoole_cpu_num*1000
        'task_worker_num' => 8,
        // 1, 使用unix socket通信
        // 2, 使用消息队列通信
        // 3, 使用消息队列通信，并设置为争抢模式
        'task_ipc_mode'   => 3,
    ];
    
    /**
     * [protected description]
     * @var Swoole\Lock
     */
    protected $lock;

    function __construct()
    {
        $this->server = new WebSocket\Server($this->host, $this->port);
        $this->lock = new Swoole\Lock(SWOOLE_MUTEX);
        $this->setConfig();
        $this->onInit();
        $this->tableInit();
        echo swoole_get_local_ip()['ens33'].":".$this->port."\n";
       
    }

    public function open($server, $req)
    {
        $this->table->set(time(), ['fd' => $req->fd]);
        $this->table->set("startTime", ['fd' => time()]);// 当前完成的量
    }
  public function message($server, $frame)
    {
        // 1 解析数据
        $data = json_decode($frame->data, true);
        // var_dump($data);
        // 2. 获取任务量
        $count = $data['total'];
        // echo $count;
        //取余，防止数据量除不尽
        // 3. 任务分块
        $yushu = $count%$this->config['task_worker_num'];
        $pageSize1 = $pageSize = intval($count / $this->config['task_worker_num']);
        // 4. 记录任务
        $this->table->set("task", ['fd' => $count]); // 总任务量
        $this->table->set("task_count", ['fd' => 0]);// 当前完成的量
        
        // 5. 分发任务
        for ($i=0; $i < $this->config['task_worker_num']; $i++) 
        {
            if($i== ($this->config['task_worker_num']- 1))
            {
                $pageSize = $pageSize + $yushu;
            }else{
                $pageSize = $pageSize1;
            }
            $page = ($pageSize * $i);
            //投递任务
            $task_id = $server->task([
                "fid"  => ($this->table->get($data['http_id']))['fd'],
                "data" => $page,
                "avg"  => $pageSize,
                "type" => $data['type'],
            ]);
        }
        $server->push($frame->fd, json_encode(["msg" => "正在处理".date('Y-m-d H:i:s',time())]));
    }

    public function task($server, $task_id, $reactor_id, $data)
    {
          try {
                $db = new Db();
                $mobile = $db->query("select `database` from `database` limit ".$data['data'].','.$data['avg']);
                $date = date('Ymd',time());
                foreach ($mobile as $k => $v) {
                    $sql_name = $v['database'];
                    system("sh /www/v1/sql.sh {$sql_name}");
                }
            }
            catch (\Throwable $e) {
                echo $e;
//记录错误日志
                file_put_contents('/www/error.txt',date('Y-m-d').PHP_EOL.$e->getMessage().PHP_EOL,FILE_APPEND);
            }
        
        // 记录完成的任务
        $this->lock->lock(); // 加锁避免多进程不安全问题
        $task_count = ($this->table->get("task_count"))['fd'];
        $count = $task_count + $data['avg'];
        $this->table->set("task_count", ['fd' => $count]);
        $this->lock->unlock();

        // 通知
        $server->finish($data);
    }
    
    public function onfinish($server, $task_id, $data)
    {
        $task_count = ($this->table->get("task_count"))['fd'];
        $task = ($this->table->get("task"))['fd'];
        $endTime = ($this->table->get("startTime"))['fd'];
        
        if ($task_count == $task && $task_count > 0) {
            $mtime = microtime();
            $time = explode(' ', $mtime);
            $haoshi = round($time[1]+$time[0] - $endTime,5).'s'.PHP_EOL;
            $server->push($data['fid'], json_encode(["msg" => $task_count."个任务，处理完成last".$haoshi]));
            // 清空任务
            $this->table->del("task_count");
            $this->table->del("task");
            $this->table->del("startTime");
        } else {
            $server->push($data['fid'], json_encode(["msg" => $task_count."个任务，处理完成".time()]));
        }

    }

    protected function tableInit()
    {
        $this->table = new Swoole\Table(1 * 1024 * 1024);
        $this->table->column('fd', Swoole\Table::TYPE_INT);
        $this->table->create();
    }

    protected function onInit()
    {
        // [$this, 'open'] 把对象方法转为闭包参数传递
        $this->server->on('open', [$this, 'open']);
        $this->server->on('message', [$this, 'message']);
        $this->server->on('close', [$this, 'close']);
        $this->server->on('task', [$this, 'task']);
        $this->server->on('finish', [$this, 'onfinish']);
    }

    protected function setConfig()
    {
        $msg_key = ftok(__DIR__,'u');
        $this->config['message_queue_key'] = $msg_key;
        $this->server->set($this->config);
    }
    
  

    public function close($server, $fd){}

    public function start()
    {
        $this->server->start();
    }
    
 

}

(new WebSocketServer)->start();

client.php

<?php
class WebsocketClient
{
    private $_host;
    private $_port;
    private $_path;
    private $_origin;
    private $_Socket = null;
    private $_connected = false;
    private $timeout = 01;
    public  function __construct($host='127.0.0.1',$port=9505)
    {
        $this->connect($host, $port, '/');
    }
    public static $instance;
    public static function getInstance()
    {
        if (!(self::$instance instanceof self)) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    public function __destruct()
    {
        $this->disconnect();
    }
    public function sendData($data, $type = 'text', $masked = true)
    {
        if ($this->_connected === false) {
            trigger_error("Not connected", E_USER_WARNING);
            return false;
        }
        if (!is_string($data)) {
            trigger_error("Not a string data was given.", E_USER_WARNING);
            return false;
        }
        if (strlen($data) == 0) {
            return false;
        }
        $res = @fwrite($this->_Socket, $this->_hybi10Encode($data, $type, $masked));
        if ($res === 0 || $res === false) {
            return false;
        }
        $buffer = ' ';
        while ($buffer !== '') {
            $buffer = fread($this->_Socket, 512);
        }
        return true;
    }
    public function connect($host, $port, $path, $origin = false)
    {
        $this->_host = $host;
        $this->_port = $port;
        $this->_path = $path;
        $this->_origin = $origin;
        $key = base64_encode($this->_generateRandomString(16, false, true));
        $header = "GET " . $path . " HTTP/1.1\r\n";
        $header .= "Host: " . $host . ":" . $port . "\r\n";
        $header .= "Upgrade: websocket\r\n";
        $header .= "Connection: Upgrade\r\n";
        //$header.= "Sec-WebSocket-Extensions: permessage-deflate; client_max_window_bits\r\n";
        $header .= "Sec-WebSocket-Key: " . $key . "\r\n";
        if ($origin !== false) {
            $header .= "Sec-WebSocket-Origin: " . $origin . "\r\n";
        }
        $header .= "Sec-WebSocket-Version: 13\r\n\r\n";
        $this->_Socket = @fsockopen($host, $port, $errno, $errstr, $this->timeout);
        if($errno!=0){
            $errstr = iconv('gbk','utf-8',$errstr);
            echo "WebSocket Error:[#{$errno}] {$errstr}";
            exit;
        }
        if($this->_Socket!=false){
            socket_set_timeout($this->_Socket, $this->timeout, 10000);
        }else{
            echo 'WebSocket Error:socket not connected';
            exit;
        }
        //socket_write($this->_Socket, $header);
        $res = @fwrite($this->_Socket, $header);
        if ($res === false) {
            echo "fwrite false \n";
        }
        $response = @fread($this->_Socket, 1500);
        //$response = socket_read($this->_Socket);
        preg_match('#Sec-WebSocket-Accept:\s(.*)$#mU', $response, $matches);
        if ($matches) {
            $keyAccept = trim($matches[1]);
            $expectedResonse = base64_encode(pack('H*', sha1($key . '258EAFA5-E914-47DA-95CA-C5AB0DC85B11')));
            $this->_connected = ($keyAccept === $expectedResonse) ? true : false;
        }
        return $this->_connected;
    }
    public function checkConnection()
    {
        $this->_connected = false;
        // send ping:
        $data = 'ping?';
        @fwrite($this->_Socket, $this->_hybi10Encode($data, 'ping', true));
        $response = @fread($this->_Socket, 300);
        if (empty($response)) {
            return false;
        }
        $response = $this->_hybi10Decode($response);
        if (!is_array($response)) {
            return false;
        }
        if (!isset($response['type']) || $response['type'] !== 'pong') {
            return false;
        }
        $this->_connected = true;
        return true;
    }
    public function disconnect()
    {
        $this->_connected = false;
        is_resource($this->_Socket) && fclose($this->_Socket);
    }
    public function reconnect()
    {
        sleep(10);
        $this->_connected = false;
        fclose($this->_Socket);
        $this->connect($this->_host, $this->_port, $this->_path, $this->_origin);
    }
    private function _generateRandomString($length = 10, $addSpaces = true, $addNumbers = true)
    {
        $characters = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!"ยง$%&/()=[]{}';
        $useChars = array();
        // select some random chars:
        for ($i = 0; $i < $length; $i++) {
            $useChars[] = $characters[mt_rand(0, strlen($characters) - 1)];
        }
        // add spaces and numbers:
        if ($addSpaces === true) {
            array_push($useChars, ' ', ' ', ' ', ' ', ' ', ' ');
        }
        if ($addNumbers === true) {
            array_push($useChars, rand(0, 9), rand(0, 9), rand(0, 9));
        }
        shuffle($useChars);
        $randomString = trim(implode('', $useChars));
        $randomString = substr($randomString, 0, $length);
        return $randomString;
    }
    private function _hybi10Encode($payload, $type = 'text', $masked = true)
    {
        $frameHead = array();
        $frame = '';
        $payloadLength = strlen($payload);
        switch ($type) {
            case 'text':
                // first byte indicates FIN, Text-Frame (10000001):
                $frameHead[0] = 129;
                break;
            case 'close':
                // first byte indicates FIN, Close Frame(10001000):
                $frameHead[0] = 136;
                break;
            case 'ping':
                // first byte indicates FIN, Ping frame (10001001):
                $frameHead[0] = 137;
                break;
            case 'pong':
                // first byte indicates FIN, Pong frame (10001010):
                $frameHead[0] = 138;
                break;
        }
        // set mask and payload length (using 1, 3 or 9 bytes)
        if ($payloadLength > 65535) {
            $payloadLengthBin = str_split(sprintf('%064b', $payloadLength), 8);
            $frameHead[1] = ($masked === true) ? 255 : 127;
            for ($i = 0; $i < 8; $i++) {
                $frameHead[$i + 2] = bindec($payloadLengthBin[$i]);
            }
            // most significant bit MUST be 0 (close connection if frame too big)
            if ($frameHead[2] > 127) {
                $this->close(1004);
                return false;
            }
        } elseif ($payloadLength > 125) {
            $payloadLengthBin = str_split(sprintf('%016b', $payloadLength), 8);
            $frameHead[1] = ($masked === true) ? 254 : 126;
            $frameHead[2] = bindec($payloadLengthBin[0]);
            $frameHead[3] = bindec($payloadLengthBin[1]);
        } else {
            $frameHead[1] = ($masked === true) ? $payloadLength + 128 : $payloadLength;
        }
        // convert frame-head to string:
        foreach (array_keys($frameHead) as $i) {
            $frameHead[$i] = chr($frameHead[$i]);
        }
        if ($masked === true) {
            // generate a random mask:
            $mask = array();
            for ($i = 0; $i < 4; $i++) {
                $mask[$i] = chr(rand(0, 255));
            }
            $frameHead = array_merge($frameHead, $mask);
        }
        $frame = implode('', $frameHead);
        // append payload to frame:
        $framePayload = array();
        for ($i = 0; $i < $payloadLength; $i++) {
            $frame .= ($masked === true) ? $payload[$i] ^ $mask[$i % 4] : $payload[$i];
        }
        return $frame;
    }
    private function _hybi10Decode($data)
    {
        $payloadLength = '';
        $mask = '';
        $unmaskedPayload = '';
        $decodedData = array();
        // estimate frame type:
        $firstByteBinary = sprintf('%08b', ord($data[0]));
        $secondByteBinary = sprintf('%08b', ord($data[1]));
        $opcode = bindec(substr($firstByteBinary, 4, 4));
        $isMasked = ($secondByteBinary[0] == '1') ? true : false;
        $payloadLength = ord($data[1]) & 127;
        switch ($opcode) {
            // text frame:
            case 1:
                $decodedData['type'] = 'text';
                break;
            case 2:
                $decodedData['type'] = 'binary';
                break;
            // connection close frame:
            case 8:
                $decodedData['type'] = 'close';
                break;
            // ping frame:
            case 9:
                $decodedData['type'] = 'ping';
                break;
            // pong frame:
            case 10:
                $decodedData['type'] = 'pong';
                break;
            default:
                return false;
                break;
        }
        if ($payloadLength === 126) {
            $mask = substr($data, 4, 4);
            $payloadOffset = 8;
            $dataLength = bindec(sprintf('%08b', ord($data[2])) . sprintf('%08b', ord($data[3]))) + $payloadOffset;
        } elseif ($payloadLength === 127) {
            $mask = substr($data, 10, 4);
            $payloadOffset = 14;
            $tmp = '';
            for ($i = 0; $i < 8; $i++) {
                $tmp .= sprintf('%08b', ord($data[$i + 2]));
            }
            $dataLength = bindec($tmp) + $payloadOffset;
            unset($tmp);
        } else {
            $mask = substr($data, 2, 4);
            $payloadOffset = 6;
            $dataLength = $payloadLength + $payloadOffset;
        }
        if ($isMasked === true) {
            for ($i = $payloadOffset; $i < $dataLength; $i++) {
                $j = $i - $payloadOffset;
                if (isset($data[$i])) {
                    $unmaskedPayload .= $data[$i] ^ $mask[$j % 4];
                }
            }
            $decodedData['payload'] = $unmaskedPayload;
        } else {
            $payloadOffset = $payloadOffset - 4;
            $decodedData['payload'] = substr($data, $payloadOffset);
        }
        return $decodedData;
    }
}

$client = new \WebSocketClient('127.0.0.1', '9501');
$sendData = ['htt_id'=>time()];
$client->sendData(json_encode($sendData));

db.php

<?php
/**
 * 数据库DAO -->>> 对数据库进行操作的类
 */
class Db
{
    /**
     * 连接数据的地址
     * localhost 数据库连接地址  dbname 数据库名
     * @var string
     */
    CONST DRIVER_CLASS = 'mysql:host=localhost;dbname=table;';
    // dbname=rds_db
    /**
     * 数据库的用户名
     * @var string
     */
    CONST USERNAME = 'root';
    /**
     * 数据库的密码
     * @var string
     */
    CONST PASSWORD = 'root';
    /**
     * 数据库连接出错
     * @var string|array
     */
    private $error = '没有异常';

    /**
     * 连接数据库驱动
     * @var PDO
     */
    private $pdo;

    public function __construct()
    {
        try {
            // 初始化执行数据库类
            $this->pdo = new PDO(self::DRIVER_CLASS, self::USERNAME, self::PASSWORD);
            $this->pdo->query('SET NAMES UTF8');
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException  $e) {
            // throw new \Exception($e->getMessage(), 500);
            return $e->getMessage();
        }
    }

    /**
     * 读操作 -->> 查询
     * @param  string $sql 查询sql
     * @return array       执行结果
     */
    public function query($sql)
    {
        try {
            $result = $this->pdo->query($sql);
            $data = [];
            foreach($result as $key => $value){
                $data[] = $value;
            }
            return $data;
            // return (count($data) <= 1) ? $data[0] : $data ;

        } catch (PDOException  $e) {
            return $e->getMessage();
        }
    }

    public function call($sql, $select_param = null)
    {
        $stmt = $this->pdo->prepare($sql);
        if ($stmt->execute()) {
            if (isset($select_param)) {
                return $this->pdo->query($select_param)->fetchAll();
            }
            return true;
        } else {
            return false;
        }
    }
    /**
     * 写操作 -->> 增删改
     * @param  string $sql 查询sql
     * @return array       执行结果
     */
    public function execute($sql)
    {
        try {
            return $this->pdo->exec($sql);
        } catch (PDOException  $e) {
            // throw new \Exception($e->getMessage(), 500);
            return $e->getMessage();
        }
    }

    //------------------
    //属性get | set 方法
    //------------------

    /**
     * 获取系统错信息
     */
    public function getError()
    {
        return $this->error;
    }

    public function write($data)
    {
        file_put_contents("log.txt", $data."\n", FILE_APPEND);
    }
}
$db = new Db;

sql.sh

#!/bin/bash
#shell 脚本导出所有数据库
#时间
DATE=$(date +%F_%H-%M-%S)
#数据库链接地址
HOST=mysql57.rdsmo7iycysnpc7.rds.hkg.baidubce.com
#数据库用户
USER=hezuo456
#数据库用户密码
PASS=GzxinshentaiServer@cms
#数据库导出备份目录
BACKUP_DIR=/www/wwwcms_test/shell/sql_shell/v1/backupswoole
#数据库
DB=$1
BACKUP_DB_DIR=$BACKUP_DIR/${DB}
#目录不存在则创建
[ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/null
#导出数据库
/usr/bin/mysqldump --skip-comments -h$HOST -u$USER -p$PASS   ${DB} > ${BACKUP_DB_DIR}/${DB}_${DATE}.sql
    

执行命令  先执行swoole.php  把服务开起来

php swoole.php

 再执行 php client.php