January 17Jan 17 安裝資料庫MariaDB 11.8與配置，資料庫最佳化 2 - 3G RAM-------------------------------- 安裝MariaDB與配置 -------------------------------- 下載GPG Key curl -sSL https://mariadb.org/mariadb_release_signing_key.asc | gpg --dearmor > /usr/share/keyrings/mariadb.gpg 加入MariaDB 11.8的源 echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/mariadb.gpg] https://mirror-cdn.xtom.com/mariadb/repo/11.8/debian $(lsb_release -sc) main" > /etc/apt/sources.list.d/mariadb.list 更新系統 apt update 安裝 MariaDB 11.8.x 最新穩定版 apt install mariadb-server mariadb-client Configuring mariadb-server Feedback plugin collects basic anonymous statistical information that can be used by the developers to improve MariaDB. This is an easy way to help with MariaDB development. Collected statistics can be viewed at http://mariadb.org/feedback-plugin Enable the Feedback plugin and submit anonymous usage information? <Yes> /<No> 選擇 <No> 瀏覽版本，已安裝 11.8.5-MariaDB mariadb -V ------------------- 安全設置 ------------------- mariadb-secure-installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! 注意：建議所有 MariaDB 運行此腳本的所有部分生產使用中的伺服器！ 請仔細閱讀每個步驟！ In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here. 為了登入 MariaDB 並確保其安全，我們需要 root 使用者目前的密碼。如果你剛剛安裝了MariaDB，並且尚未設定root密碼，則只需在此處按Enter 鍵即可。 Enter current password for root (enter for none): 輸入 root 的目前密碼 (不輸入)： 按 Enter OK, successfully used password, moving on... 好的，密碼已成功使用，繼續... Setting the root password or using the unix_socket ensures that nobody can log into the MariaDB root user without the proper authorisation. 設定root密碼或使用 unix_socket 可確保任何人在未經適當授權的情況下都無法登入MariaDB root使用者。 You already have your root account protected, so you can safely answer 'n'. 你的root帳戶已受到保護，因此你可以安全地回答 'n'。 Switch to unix_socket authentication [Y/n] 切換到 unix_socket 身份驗證 [Y/n] 按 n 若為單機伺服器，建議按 Y，使用 unix_socket 驗證（較安全） Change the root password? [Y/n] 更改root密碼？ [Y/n] 按 y New password: 輸入你自定義root的密碼 Re-enter new password: 再次輸入你自定義root的密碼 By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. 預設情況下，MariaDB安裝有一個匿名用戶，允許任何人登入MariaDB，而無需為其建立用戶帳戶。這僅用於測試，並使安裝過程更加順利。你應該在進入生產環境之前刪除它們。 Remove anonymous users? [Y/n] 刪除匿名用戶？ [Y/n] 按 y Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. 通常，只允許root從 'localhost' 連線。這可確保其他人無法從網路猜測root密碼。 Disallow root login remotely? [Y/n] 禁止遠端root登入？ [Y/n] 要按 y By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. 預設情況下，MariaDB附帶一個名為 'test' 的資料庫，任何人都可以存取。這也僅用於測試，應在進入生產環境之前將其刪除。 Remove test database and access to it? [Y/n] 刪除測試資料庫並存取它？ 按 y Reloading the privilege tables will ensure that all changes made so far will take effect immediately. 重新載入權限表將確保迄今為止所做的所有變更都會立即生效。 Reload privilege tables now? [Y/n] 現在重新載入權限表？ [Y/n] 按 y All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! 重啟資料庫 systemctl restart mariadb -------------- UTF-8 -------------- vi /etc/mysql/mariadb.conf.d/50-client.cnf 在 [client] 底下，加入這一行 default-character-set = utf8mb4 # # This group is read by the client library # Use it for options that affect all clients, but not the server # [client] # Example of client certificate usage #ssl-cert = /etc/mysql/client-cert.pem #ssl-key = /etc/mysql/client-key.pem # # Allow only TLS encrypted connections #ssl-verify-server-cert = on default-character-set = utf8mb4 # This group is *never* read by mysql client library, though this # /etc/mysql/mariadb.cnf.d/client.cnf file is not read by Oracle MySQL # client anyway. # If you use the same .cnf file for MySQL and MariaDB, # use it for MariaDB-only client options [client-mariadb] ESC儲存並離開 :wq --------------------------------------------------------------------------------- 資料庫最佳化 / 數據庫優化 2 - 3 GB --------------------------------------------------------------------------------- 修改 vi /etc/mysql/mariadb.conf.d/50-server.cnf # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # this is read by the standalone daemon and embedded servers [server] # this is only for the mariadbd daemon [mariadbd] # # * Basic Settings # port = 3306 user = mariadb pid-file = /run/mysqld/mysqld.pid basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp # Broken reverse DNS slows down connections considerably and name resolve is # safe to skip if there are no "host by domain name" access grants #skip-name-resolve # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #預設僅監聽 localhost，如需遠端連線請自行調整 bind-address = 127.0.0.1 # # * Fine Tuning # #MyISAM index buffer，多數 InnoDB 環境影響有限 key_buffer_size = 32M #大型資料庫匯入，需求可提高 max_allowed_packet = 1G table_open_cache = 192 sort_buffer_size = 768K net_buffer_length = 8K read_buffer_size = 768K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M thread_cache_size = 96 tmp_table_size = 128M max_heap_table_size = 128M explicit_defaults_for_timestamp = true #skip-networking max_connections = 100 max_connect_errors = 100 open_files_limit = 65535 #若不使用主從複製或 PITR，可考慮關閉 binlog log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 default_storage_engine = InnoDB # # * Logging and Replication # expire_logs_days = 10 # # * Character sets # # MariaDB default is now utf8 4-byte character set. # No Debian specific default is required. # # * InnoDB # innodb_file_per_table = 1 innodb_buffer_pool_size = 2G #若調整此值導致無法啟動，請確認 ib_logfile 已重新建立 innodb_log_file_size = 256M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick #大型資料庫匯入，需求可提高 max_allowed_packet = 1G [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # Most important is to give InnoDB 80 % of the system RAM for buffer use: # https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size #innodb_buffer_pool_size = 8G # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadbd] # This group is only read by MariaDB-11.8 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-11.8] ESC儲存並離開 :wq ------------------------------------- 重啟 & 重開機 ------------------------------------- 重啟 systemctl restart mariadb 開機啟動mariadb systemctl enable mariadb 啟動mariadb systemctl start mariadb 瀏覽狀態 systemctl status mariadb 重開機 reboot 重開機，確認MariaDB可以自動啟動，顯示 Active: active (running) systemctl status mariadb Edited January 18Jan 18 by Jack
Create an account or sign in to comment