\u5728\u7f8e\u570b\u8cc7\u6599\u4e2d\u5fc3\u90e8\u7f72MySQL\u6642\uff0c\u5b89\u5168\u6027\u81f3\u95dc\u91cd\u8981\u3002\u5be6\u65bd\u4ee5\u4e0b\u5b8c\u6574\u7684\u5b89\u5168\u63aa\u65bd\uff1a<\/p>\n

# \u57f7\u884cMySQL\u5b89\u5168\u5b89\u88dd\u8173\u672c\r\nsudo mysql_secure_installation\r\n\r\n# \u914d\u7f6e\u5bc6\u78bc\u9a57\u8b49\u5916\u639b\u7a0b\u5f0f\r\nmysql -u root -p\r\nSET GLOBAL validate_password.policy=STRONG;\r\nSET GLOBAL validate_password.length=12;\r\nSET GLOBAL validate_password.mixed_case_count=1;\r\nSET GLOBAL validate_password.number_count=1;\r\nSET GLOBAL validate_password.special_char_count=1;<\/code><\/pre>\n
\u9032\u968e\u5b89\u5168\u914d\u7f6e\uff1a<\/p>\n
# \u5efa\u7acb\u5c08\u7528\u7ba1\u7406\u54e1\u4f7f\u7528\u8005\r\nCREATE USER 'admin'@'localhost' IDENTIFIED BY 'StrongPassword123!';\r\nGRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;\r\nFLUSH PRIVILEGES;\r\n\r\n# \u8a2d\u5b9aSSL\u52a0\u5bc6\r\nsudo mysql_ssl_rsa_setup --uid=mysql\r\nsudo chown mysql:mysql \/var\/lib\/mysql\/*.pem<\/code><\/pre>\n
\u6548\u80fd\u6700\u4f73\u5316<\/strong><\/h2>\n
\u5c0d\u65bc8GB\u8a18\u61b6\u9ad4\u7684\u4f3a\u670d\u5668\uff0c\u4f7f\u7528\u4ee5\u4e0b\u7d93\u904e\u7cbe\u5fc3\u8abf\u6574\u7684\u8a2d\u5b9a\u4f86\u6700\u4f73\u5316MySQL\u6548\u80fd\uff1a<\/p>\n
# \u7de8\u8f2fMySQL\u914d\u7f6e\r\nsudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\r\n\r\n[mysqld]\r\n# \u7de9\u885d\u6c60\u8a2d\u5b9a\r\ninnodb_buffer_pool_size = 4G\r\ninnodb_buffer_pool_instances = 4\r\n\r\n# \u65e5\u8a8c\u6a94\u6848\u8a2d\u5b9a\r\ninnodb_log_file_size = 256M\r\ninnodb_log_buffer_size = 16M\r\ninnodb_flush_log_at_trx_commit = 1\r\ninnodb_flush_method = O_DIRECT\r\n\r\n# \u9023\u7dda\u8a2d\u5b9a\r\nmax_connections = 200\r\nthread_cache_size = 32\r\ntable_open_cache = 4000\r\n\r\n# \u67e5\u8a62\u5feb\u53d6\u8a2d\u5b9a\uff08MySQL 5.7\uff09\r\nquery_cache_type = 0\r\nquery_cache_size = 0\r\n\r\n# InnoDB\u8a2d\u5b9a\r\ninnodb_file_per_table = 1\r\ninnodb_read_io_threads = 4\r\ninnodb_write_io_threads = 4<\/code><\/pre>\n
\u81ea\u52d5\u5099\u4efd\u7b56\u7565<\/strong><\/h2>\n
\u5be6\u65bd\u4ee5\u4e0b\u7d9c\u5408\u5099\u4efd\u89e3\u6c7a\u65b9\u6848\uff1a<\/p>\n
#!\/bin\/bash\r\n# \u9032\u968eMySQL\u5099\u4efd\u8173\u672c\r\n\r\n# \u914d\u7f6e\r\nTIMESTAMP=$(date +\"%Y%m%d_%H%M%S\")\r\nBACKUP_DIR=\"\/backup\/mysql\"\r\nMYSQL_USER=\"backup_user\"\r\nMYSQL_PASSWORD=\"your_secure_password\"\r\nRETENTION_DAYS=7\r\nLOG_FILE=\"\/var\/log\/mysql\/backup.log\"\r\n\r\n# \u5efa\u7acb\u5099\u4efd\u76ee\u9304\u7d50\u69cb\r\nmkdir -p $BACKUP_DIR\/{daily,weekly,monthly}\r\n\r\n# \u65e5\u8a8c\u8a18\u9304\u51fd\u5f0f\r\nlog_message() {\r\n    echo \"$(date '+%Y-%m-%d %H:%M:%S') - $1\" >> $LOG_FILE\r\n}\r\n\r\n# \u5099\u4efd\u51fd\u5f0f\r\nperform_backup() {\r\n    local OUTPUT_DIR=$1\r\n    local BACKUP_FILE=\"mysql_backup_${TIMESTAMP}.sql.gz\"\r\n    \r\n    mysqldump --user=$MYSQL_USER --password=$MYSQL_PASSWORD \\\r\n    --all-databases --events --routines --triggers \\\r\n    --single-transaction --quick --lock-tables=false \\\r\n    | gzip > \"${OUTPUT_DIR}\/${BACKUP_FILE}\"\r\n    \r\n    if [ $? -eq 0 ]; then\r\n        log_message \"\u5099\u4efd\u6210\u529f: ${BACKUP_FILE}\"\r\n    else\r\n        log_message \"\u5099\u4efd\u5931\u6557: ${BACKUP_FILE}\"\r\n    fi\r\n}\r\n\r\n# \u57f7\u884c\u6bcf\u65e5\u5099\u4efd\r\nperform_backup \"$BACKUP_DIR\/daily\"\r\n\r\n# \u9031\u65e5\u57f7\u884c\u6bcf\u9031\u5099\u4efd\r\nif [ $(date +%u) -eq 7 ]; then\r\n    perform_backup \"$BACKUP_DIR\/weekly\"\r\nfi\r\n\r\n# \u6bcf\u67081\u65e5\u57f7\u884c\u6bcf\u6708\u5099\u4efd\r\nif [ $(date +%d) -eq 01 ]; then\r\n    perform_backup \"$BACKUP_DIR\/monthly\"\r\nfi\r\n\r\n# \u6e05\u7406\u820a\u5099\u4efd\r\nfind $BACKUP_DIR\/daily -type f -mtime +$RETENTION_DAYS -delete\r\nfind $BACKUP_DIR\/weekly -type f -mtime +30 -delete\r\nfind $BACKUP_DIR\/monthly -type f -mtime +90 -delete\r\n\r\nlog_message \"\u6e05\u7406\u5b8c\u6210\"<\/code><\/pre>\n
\u4f7f\u7528cron\u8a2d\u5b9a\u81ea\u52d5\u57f7\u884c\uff1a<\/p>\n
# \u958b\u555fcrontab\r\ncrontab -e\r\n\r\n# \u65b0\u589e\u4ee5\u4e0b\u689d\u76ee\uff1a\r\n0 2 * * * \/path\/to\/backup_script.sh  # \u6bcf\u5929\u51cc\u66682\u9ede\r\n0 3 * * 0 \/path\/to\/verify_backup.sh  # \u6bcf\u9031\u5099\u4efd\u9a57\u8b49<\/code><\/pre>\n
\u76e3\u63a7\u548c\u7dad\u8b77<\/strong><\/h2>\n
\u7528\u65bc\u8cc7\u6599\u5eab\u5065\u5eb7\u6aa2\u67e5\u7684\u57fa\u672c\u76e3\u63a7\u67e5\u8a62\uff1a<\/p>\n
# \u76e3\u63a7\u7cfb\u7d71\u4f7f\u7528\u60c5\u6cc1\r\nSELECT VARIABLE_VALUE\/1024\/1024 as memory_used_MB \r\nFROM performance_schema.global_status \r\nWHERE VARIABLE_NAME = 'memory_used';\r\n\r\n# \u6aa2\u67e5\u8868\u5927\u5c0f\u548c\u589e\u9577\r\nSELECT \r\n    table_schema as '\u8cc7\u6599\u5eab',\r\n    table_name as '\u8868',\r\n    round(((data_length + index_length) \/ 1024 \/ 1024), 2) as '\u5927\u5c0f (MB)',\r\n    round(index_length \/ 1024 \/ 1024, 2) as '\u7d22\u5f15\u5927\u5c0f (MB)'\r\nFROM information_schema.TABLES\r\nORDER BY (data_length + index_length) DESC\r\nLIMIT 10;\r\n\r\n# \u76e3\u63a7\u6162\u67e5\u8a62\r\nSET GLOBAL slow_query_log = 'ON';\r\nSET GLOBAL long_query_time = 2;\r\nSET GLOBAL slow_query_log_file = '\/var\/log\/mysql\/slow-query.log';<\/code><\/pre>\n
\u6700\u4f73\u5be6\u8e10\u548c\u7dad\u8b77\u8a08\u5283<\/strong><\/h2>\n
\u9075\u5faa\u4ee5\u4e0b\u7dad\u8b77\u8a08\u5283\u4ee5\u7372\u5f97\u6700\u4f73\u6548\u80fd\uff1a<\/p>\n
\n
\u6bcf\u65e5\u4efb\u52d9\uff1a\n
\n
\u76e3\u63a7\u932f\u8aa4\u65e5\u8a8c<\/li>\n
\u6aa2\u67e5\u5099\u4efd\u5b8c\u6210\u72c0\u614b<\/li>\n
\u76e3\u63a7\u78c1\u789f\u7a7a\u9593\u4f7f\u7528\u60c5\u6cc1<\/li>\n<\/ul>\n<\/li>\n
\u6bcf\u9031\u4efb\u52d9\uff1a\n
\n
\u5be9\u67e5\u6162\u67e5\u8a62\u65e5\u8a8c<\/li>\n
\u5206\u6790\u8868\u788e\u7247\u5316\u60c5\u6cc1<\/li>\n
\u9a57\u8b49\u5099\u4efd\u5b8c\u6574\u6027<\/li>\n<\/ul>\n<\/li>\n
\u6bcf\u6708\u4efb\u52d9\uff1a\n
\n
\u5b89\u5168\u7a3d\u6838<\/li>\n
\u6548\u80fd\u6700\u4f73\u5316\u5be9\u67e5<\/li>\n
\u4f7f\u7528\u8005\u6b0a\u9650\u5be9\u67e5<\/li>\n<\/ul>\n<\/li>\n
\u5b63\u5ea6\u4efb\u52d9\uff1a\n
\n
\u4e3b\u8981\u7248\u672c\u5347\u7d1a\u8a55\u4f30<\/li>\n
\u5bb9\u91cf\u898f\u5283\u5be9\u67e5<\/li>\n
\u707d\u96e3\u5fa9\u539f\u6e2c\u8a66<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
\u901a\u904e\u9075\u5faa\u672c\u7d9c\u5408\u6307\u5357\uff0c\u60a8\u5c07\u64c1\u6709\u4e00\u500b\u9069\u5408\u5728\u7f8e\u570b\u8cc7\u6599\u4e2d\u5fc3\u751f\u7522\u74b0\u5883\u4f7f\u7528\u7684\u5f37\u5927\u3001\u5b89\u5168\u548c\u6700\u4f73\u5316\u7684MySQL\u5b89\u88dd\u3002\u5b9a\u671f\u7dad\u8b77\u548c\u76e3\u63a7\u5c07\u78ba\u4fdd\u60a8\u7684\u8cc7\u6599\u5eab\u57fa\u790e\u8a2d\u65bd\u6301\u7e8c\u4fdd\u6301\u6700\u4f73\u6548\u80fd\u548c\u5b89\u5168\u6027\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u5728\u7f8e\u570b\u8cc7\u6599\u4e2d\u5fc3\u8a17\u7ba1\u7684Linux\u4f3a\u670d\u5668\u4e0a\u90e8\u7f72MySQL\u9700\u8981\u8b39\u614e\u7684\u898f\u5283\u548c\u57f7\u884c\u3002\u672c\u7d9c\u5408\u6307\u5357\u5c07\u5e36\u60a8\u5b8c\u6210\u5b89\u88dd\u904e\u7a0b\u3001\u5b89\u5168\u914d […]<\/p>\n
Read More…<\/a><\/p>\n","protected":false},"author":3,"featured_media":21861,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[4815],"tags":[6166,6167,6168,6169,1892],"class_list":["post-21879","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-america-dedicated-server-tc","tag-mysql-installation-tc","tag-us-linux-server-tc","tag-database-configuration-tc","tag-mysql-security-tc","tag-server-hosting-tc"],"acf":[],"yoast_head":"\n