Category: MySQL / Mariadb

提高 MySQL 的 open files limit

對於中高用量的 MySQL 伺服器, 提高 max_connections 是必然的設定, 不然便會出現 too many connections 錯誤。但 max_connections 這個數值不可以任意提高的, 如果設定太高, 會出以類似以下的錯誤: Changed limits: max_open_files: 1024 max_connections: 214 table_cache: 256 例如設定 max_connections 到 1000, 系統出現上面 Warning …

MySQL 管理相關指令

以下是一些常用的 MySQL 管理相關指令匯整, 以下的指令如果以 # 字符開頭, 代表是在 CLI 輸入的指令, 如果是以 “mysql>” 開始, 則表示登入 MySQL 後輸入的指令。登入 MySQL: 建立資料庫: 匯入資料庫: (將 /path/to/backup.sql 匯入到 database_name 資料庫) 匯出資料庫: (將 database_name 資料庫匯出到 /path/to/backup.sql) 刪除資料庫: 新增使用者: …

MySQL 修改使用者密碼

修改 MySQL 密碼可以透過 mysql 指令 (在 Windows 下是 mysql.exe),mysqladmin 指令或 phpMyAdmin 完成,以下是用 mysql 及 mysqladmin 修改 MySQL 使用者密碼的方法。mysql 指令 在指令模式用 root 帳號透過 mysql 登入 MySQL 伺服器: # mysql -u root …

MySQL Log 記錄檔位置

MySQL 和 MariaDB 的 Log 記錄檔預設儲存在資料庫目錄, Redhat 及 Debian 系發行版的資料庫目錄都是在 /var/log/mysql/ 目錄。如果在資料庫沒有找到, 可以查看 /etc/my.cnf 裡面的 log-error 一行, 這裡可以自訂 Log 記錄檔的位置, 例如在 CentOS 7 的 MariaDB 是這樣:知道 Log 記錄檔的位置後, 可以用以下指令檢視 Log …

MySQL 開啟 innodb_file_per_table 及轉換現有資料表

MySQL 會將 InnoDB 的資料表內的資料及索引全部儲存到共享空間, 即是所有 InnoDB 資料表的資料全都放到 ibdata1 檔案內。而 innodb_file_per_table 就可以設定每個資料表, 使用獨立表空間儲存資料, 即是每個資料表有屬於自己的 .ibd 檔案。這樣做的好處是對日後的管理較容易, 在 MySQL 5.6.6 開始, innodb_file_per_table 預設是開啟, 而在 5.6.6 前的版本預設是關閉的, 要查詢目前 MySQL 的 innodb_file_per_table 是否開啟, 可以查看 …

列出所有 MySQL 資料庫大小

當發現 MySQL / MariaDB 伺服器的硬碟使用空間越來越多, 想找出那一個資料庫佔用最多空間時, 以前用 MyISAM 儲存引擎時, 可以直接用 du 指令查看資料庫目錄。但如果是用 InnoDB 的話, 用 du 指令就沒有用了。要找出所有 MySQL 資料庫的空間大小, 可以用 MySQL 的 root 帳號登入 MySQL CLI 或 PHPMyAdmin, 然後執行以下 SQL …

MySQL 完整複製資料表

當需要將 MySQL 資料表完整複製時, 如果資料表是使用 MyISAM 儲存引擎, 可以直接停止 MySQL 伺服器, 再將資料表的 .MYD 及 MYI 檔案複製。但如果資料表是 InnoDB, 或者不想停止 MySQL 運作, 除了用傳統的備份方法外, MySQL 已經內建了複製的功能, 具體做法如下。假設舊資料表是 old_table, 新資料表是 new_table, 可以執行以下 SQL Query:用這種方法複製的資料表, 會將資料表結構, 資料及 …

MySQL 換算 Unix Timestamp 日期時間

在多時寫程式都會用 Unix Timestamp 儲存時間,好處是可以很簡單作日期計算,以及用不同格式顯示日期。以前儲存在 MySQL 的日期時間,會擷取後用 PHP 的 date() 函式顯示,但 MySQL 已經內置了用作換算 Unix Timestamp 的功能,用起來很方便,以下記錄一下用法。UNIX_TIMESTAMP() — 取得目錄 Unix Timestamp:以前習慣在 PHP 用 time() 函式插入 Unix Timestamp 到 MySQL,在 SQL Query 裡面用 …

同時安裝 MySQL 及 MariaDB

MariaDB 是 MySQL 的替代產品, 也是 RHEL 7 及 CentOS 7 的預設資料庫系統。有時需要在 MariaDB 及 MySQL 之間做測試, 或者在不同版本 MySQL 之間做測試, 同時安裝及運行 MariaDB 及 MySQL 會很方便, 以下紀錄一下在主機內同時安裝 MariaDB 及 MySQL 運行, 當然如果有需要, 也可以按此方法安裝 …

MySQL 檢查欄位是否 NULL 或空白

有時需要在 MySQL 資料庫內,檢查欄位的值是否 NULL,這個可以使用 MySQL 內建檢查 NULL 的功能,分別是 “IS NULL” 及 “IS NOT NULL”, 以下是用法:select 資料表 table_name,欄位 col_name 是 NULL 的紀錄: select 資料表 table_name,欄位 col_name 不是 NULL 的記錄: 如果想 select …