{"id":191,"date":"2024-06-15T19:33:57","date_gmt":"2024-06-15T11:33:57","guid":{"rendered":"http:\/\/zhaoyanqi.cn\/?p=191"},"modified":"2024-06-19T22:13:10","modified_gmt":"2024-06-19T14:13:10","slug":"%e5%ae%9e%e9%aa%8c%ef%bc%9a%e7%94%a8shell%e5%92%8cmysql%e7%bc%96%e5%86%99%e4%b8%80%e4%b8%aa%e7%94%a8%e6%88%b7%e8%ae%a4%e8%af%81%e7%a8%8b%e5%ba%8f","status":"publish","type":"post","link":"https:\/\/zhaoyanqi.cn\/?p=191","title":{"rendered":"\u5b9e\u9a8c\uff1a\u7528shell\u548cmysql\u7f16\u5199\u4e00\u4e2a\u7528\u6237\u8ba4\u8bc1\u7a0b\u5e8f"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\" id=\"88210852\">\u5b9e\u9a8c\u73af\u5883<\/h3>\n\n\n\n<p id=\"ufc98930c\">\u64cd\u4f5c\u7cfb\u7edf\uff1aCentos 7.9<\/p>\n\n\n\n<p id=\"ude5c3c3c\">Mysql\u7248\u672c\uff1a8.0.30<\/p>\n\n\n\n<p id=\"u4ed6c656\">Mysql\u8d26\u53f7\uff1aroot<\/p>\n\n\n\n<p id=\"u1cee5f66\">Mysql\u5bc6\u7801\uff1a1qaz@WSX<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"4133a666\">\u521b\u5efa MySQL \u6570\u636e\u5e93\u548c\u8868<\/h3>\n\n\n\n<p id=\"ua8898f1d\">\u5728 MySQL \u4e2d\u521b\u5efa\u4e00\u4e2a\u6570\u636e\u5e93\u548c\u7528\u6237\u8868\uff1a<\/p>\n\n\n\n<pre id=\"f151b44c\" class=\"wp-block-code\"><code>CREATE DATABASE user_management;\n\nUSE user_management;\n\nCREATE TABLE users (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    username VARCHAR(50) UNIQUE NOT NULL,\n    password VARCHAR(255) NOT NULL\n);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"98a82006\">Shell \u811a\u672c<\/h3>\n\n\n\n<pre id=\"2b8b4e39\" class=\"wp-block-code\"><code>#!\/bin\/bash\n\nMYSQL_USER=\"root\"\nMYSQL_PASS=\"1qaz@WSX\"\nMYSQL_DB=\"user_management\"\n\nfunction register_user() {\n    read -p \"\u8bf7\u8f93\u5165\u7528\u6237\u540d: \" reg_username\n    read -sp \"\u8bf7\u8f93\u5165\u5bc6\u7801: \" reg_password\n    echo\n\n    # \u68c0\u67e5\u7528\u6237\u540d\u662f\u5426\u5df2\u5b58\u5728\n    user_exists=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -D $MYSQL_DB -se \"SELECT COUNT(*) FROM users WHERE username='$reg_username';\")\n    if &#91; \"$user_exists\" -ne 0 ]; then\n        echo \"\u7528\u6237\u540d\u5df2\u5b58\u5728\u3002\u8bf7\u9009\u62e9\u5176\u4ed6\u7528\u6237\u540d\u3002\"\n        return 1\n    fi\n\n    # \u5c06\u65b0\u7528\u6237\u63d2\u5165\u5230\u6570\u636e\u5e93\u4e2d\n    mysql -u $MYSQL_USER -p$MYSQL_PASS -D $MYSQL_DB -e \"INSERT INTO users (username, password) VALUES ('$reg_username', '$reg_password');\"\n    if &#91; $? -eq 0 ]; then\n        echo \"\u7528\u6237\u6ce8\u518c\u6210\u529f\uff01\"\n    else\n        echo \"\u7528\u6237\u6ce8\u518c\u5931\u8d25\u3002\"\n    fi\n}\n\nfunction authenticate_user() {\n    read -sp \"\u8bf7\u8f93\u5165\u5bc6\u7801: \" inp_password\n    echo\n\n    # \u4ece\u6570\u636e\u5e93\u4e2d\u83b7\u53d6\u5bc6\u7801\n    stored_password=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -D $MYSQL_DB -se \"SELECT password FROM users WHERE username='$inp_username';\")\n    if &#91; -z \"$stored_password\" ]; then\n        echo \"\u8ba4\u8bc1\u5931\u8d25\uff1a\u7528\u6237\u540d\u4e0d\u5b58\u5728\u3002\"\n        return 1\n    fi\n\n    if &#91; \"$stored_password\" = \"$inp_password\" ]; then\n        echo \"\u767b\u5f55\u6210\u529f\uff01\u6b22\u8fce\u8fdb\u5165\u4e3b\u9875\u3002\"\n        return 0\n    else\n        echo \"\u8ba4\u8bc1\u5931\u8d25\uff1a\u5bc6\u7801\u9519\u8bef\u3002\"\n        return 1\n    fi\n}\n\nfunction lock_test() {\n    for line in `cat lock.txt`\n    do\n        lock_user=$(echo $line | cut -d: -f1)\n        lock_time=$(echo $line | cut -d: -f2)\n        if &#91; \"$inp_username\" = \"$lock_user\" ]; then\n            # \u5224\u65ad\u9501\u5b9a\u7684\u65f6\u95f4\uff0c\u5982\u679c\u8d85\u8fc760\u79d2\uff0c\u89e3\u9501\uff0c\u5426\u5219\u7ee7\u7eed\u9501\u5b9a\n            current_time=$(date '+%s')\n            period=$((current_time - lock_time))\n            if &#91; $period -le 60 ]; then\n                # \u5904\u4e8e\u9501\u5b9a\u5468\u671f\u5185\n                echo \"\u7528\u6237\u5904\u4e8e\u9501\u5b9a\u72b6\u6001\u3002\u3002\u3002\"\n                return 1\n            else\n                # \u5e94\u8be5\u89e3\u9501\uff0c\u7ee7\u7eed\u8fdb\u884c\u540e\u7eed\u8ba4\u8bc1\u73af\u8282\n                sed -ri \"\/^\\b($inp_username)\\b\/d\" lock.txt\n                lock_user_info&#91;$inp_username]=0\n                return 0\n            fi\n        fi\n    done\n}\n \ndeclare -A lock_user_info\n\nwhile true\ndo\n    echo \"1. \u6ce8\u518c\"\n    echo \"2. \u767b\u5f55\"\n    echo \"3. \u9000\u51fa\"\n    read -p \"\u8bf7\u9009\u62e9\u4e00\u4e2a\u9009\u9879: \" option\n\n    case $option in\n        1)\n            register_user\n            ;;\n        2)\n            read -p \"\u8bf7\u8f93\u5165\u7528\u6237\u540d: \" inp_username\n            lock_test\n            if &#91; $? -ne 0 ]; then\n                echo \"===============================\"\n                continue\n            fi\n            authenticate_user\n            if &#91; $? -eq 0 ]; then\n                break\n            fi\n            let lock_user_info&#91;$inp_username]++\n            ;;\n        3)\n            echo \"\u518d\u89c1\uff01\"\n            exit 0\n            ;;\n        *)\n            echo \"\u65e0\u6548\u9009\u9879\u3002\u8bf7\u91cd\u8bd5\u3002\"\n            ;;\n    esac\n\n    declare -A | grep lock_user_info\n    for k in ${!lock_user_info&#91;@]};\n    do \n        username=$k \n        grep -q \"^\\b$username\\b\" lock.txt\n        if &#91; $? -eq 0 ]; then\n            continue\n        fi\n        error_times=${lock_user_info&#91;$k]}\n        if &#91; $error_times -ge 3 ]; then\n            echo \"\u5c1d\u8bd5\u6b21\u6570\u8fc7\u591a\uff0c\u9501\u5b9a\u7528\u6237\uff01\"\n            echo \"$username:$(date '+%s')\" &gt;&gt; lock.txt \n        fi\n    done\ndone<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"sNaKD\">\u8be6\u7ec6\u8bf4\u660e<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u7528\u6237\u6ce8\u518c\u529f\u80fd (<\/strong><code><strong>register_user<\/strong><\/code><strong> \u51fd\u6570)<\/strong>\uff1a<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u63d0\u793a\u7528\u6237\u8f93\u5165\u7528\u6237\u540d\u548c\u5bc6\u7801\u3002<\/li>\n\n\n\n<li>\u68c0\u67e5\u7528\u6237\u540d\u662f\u5426\u5df2\u5b58\u5728\u3002<\/li>\n\n\n\n<li>\u5982\u679c\u7528\u6237\u540d\u4e0d\u5b58\u5728\uff0c\u5219\u5c06\u7528\u6237\u540d\u548c\u5bc6\u7801\u63d2\u5165\u5230 MySQL \u6570\u636e\u5e93\u7684 <code>users<\/code> \u8868\u4e2d\u3002<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-27.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"879\" height=\"636\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-27.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-192\"  sizes=\"auto, (max-width: 879px) 100vw, 879px\" \/><\/div><\/figure>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>\u7528\u6237\u8ba4\u8bc1\u529f\u80fd (<\/strong><code><strong>authenticate_user<\/strong><\/code><strong> \u51fd\u6570)<\/strong>\uff1a<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u63d0\u793a\u7528\u6237\u8f93\u5165\u7528\u6237\u540d\u548c\u5bc6\u7801\u3002<\/li>\n\n\n\n<li>\u4ece\u6570\u636e\u5e93\u4e2d\u83b7\u53d6\u8be5\u7528\u6237\u540d\u7684\u5bc6\u7801\u3002<\/li>\n\n\n\n<li>\u6bd4\u8f83\u8f93\u5165\u7684\u5bc6\u7801\u548c\u6570\u636e\u5e93\u4e2d\u5b58\u50a8\u7684\u5bc6\u7801\uff0c\u5982\u679c\u5339\u914d\u5219\u8ba4\u8bc1\u6210\u529f\uff0c\u5426\u5219\u8ba4\u8bc1\u5931\u8d25\u3002<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-28.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"846\" height=\"238\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-28.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-193\"  sizes=\"auto, (max-width: 846px) 100vw, 846px\" \/><\/div><\/figure>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li><strong>\u9501\u5b9a\u6d4b\u8bd5\u529f\u80fd (<\/strong><code><strong>lock_test<\/strong><\/code><strong> \u51fd\u6570)<\/strong>\uff1a<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u68c0\u67e5\u7528\u6237\u662f\u5426\u5728\u9501\u5b9a\u72b6\u6001\u3002<\/li>\n\n\n\n<li>\u5982\u679c\u7528\u6237\u5728 <code>lock.txt<\/code> \u6587\u4ef6\u4e2d\u88ab\u9501\u5b9a\uff0c\u5e76\u4e14\u9501\u5b9a\u65f6\u95f4\u672a\u8d85\u8fc7 60 \u79d2\uff0c\u5219\u63d0\u793a\u7528\u6237\u88ab\u9501\u5b9a\u3002<\/li>\n\n\n\n<li>\u5982\u679c\u9501\u5b9a\u65f6\u95f4\u8d85\u8fc7 60 \u79d2\uff0c\u5219\u4ece <code>lock.txt<\/code> \u6587\u4ef6\u4e2d\u5220\u9664\u8be5\u7528\u6237\uff0c\u5e76\u5141\u8bb8\u7ee7\u7eed\u8fdb\u884c\u767b\u5f55\u3002<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-29.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"472\" height=\"363\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-29.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-194\"  sizes=\"auto, (max-width: 472px) 100vw, 472px\" \/><\/div><\/figure>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li><strong>\u4e3b\u5faa\u73af<\/strong>\uff1a<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u63d0\u4f9b\u4e00\u4e2a\u7b80\u5355\u7684\u83dc\u5355\uff0c\u7528\u6237\u53ef\u4ee5\u9009\u62e9\u6ce8\u518c\u3001\u767b\u5f55\u6216\u9000\u51fa\u3002<\/li>\n\n\n\n<li>\u767b\u5f55\u529f\u80fd\u96c6\u6210\u4e86\u9501\u5b9a\u529f\u80fd\uff0c\u5982\u679c\u7528\u6237\u5c1d\u8bd5\u6b21\u6570\u8fc7\u591a\uff0c\u5219\u9501\u5b9a\u7528\u6237 60 \u79d2\u3002<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u5b9e\u9a8c\u73af\u5883 \u64cd\u4f5c\u7cfb\u7edf\uff1aCentos 7.9 Mysql\u7248\u672c\uff1a8.0.30 Mysql\u8d26\u53f7\uff1aroot Mysql\u5bc6 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,8],"tags":[10],"class_list":["post-191","post","type-post","status-publish","format-standard","hentry","category-mysql_basic_management","category-mysql","tag-mysql_lab"],"_links":{"self":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/191","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=191"}],"version-history":[{"count":1,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/191\/revisions"}],"predecessor-version":[{"id":195,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/191\/revisions\/195"}],"wp:attachment":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=191"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=191"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=191"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}