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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
