Neil's Note

Virtual Columns in InnoDB INFORMATION_SCHEMA 記錄了一些關於 InnoDB 管理的 schema 物件的 metadata, 這些資訊來自於 InnoDB 內部 system tables, 這些資訊沒辦法跟一般 table 一樣, 可以直接查詢, 傳統上的做法應該是解析 SHOW_ENGINE_INNODB_STATUS 的輸出. 不過 INFORMATION_SCHEMA 這張表有提供介面可以讓你使用 SQL 查詢這類型的資料. 這個跟 Virtual Column 有什麼關係?  Virtual Column 其實並不會儲存實體資料在 table 的 clustered index 內! 不過 Virtual Column 的 metadata 會存在 InnoDB 的 INFORMATION_SCHEMA  的 SYS_COLUMNS 內！ 舉例來說 ## 建立一張表 t 有著 a, b, c 三個欄位, c 是 virtual column CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), PRIMARY KEY(a)); ## SELECT * FROM t; +----+------+------+ | a | b | c | +----+------+------+ | 11 | 3 | 14 | +----+------+------+ ## 從 SYS_COLUMNS 中觀察 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE TABLE_ID IN ( SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "t%" ); +----------+------+-------+-------+--------+-----+ | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN | +----------+------+-------+-------+--------+...

最近剛好需要幫公司的資料庫做 replica, 但由於之前的資料庫沒有開啟 binlog, 所以研究了一下該怎麼操作。 如果是剛開始建立服務，建議 binlog 的設定要記得打開，如果沒有打開的話，變成要實作 replica 就需要先將資料備份出來，然後在備份出來的 master 資料還原給 slave，之後再去開啟 master 的 binlog，與建立要給 slave 同步用的帳號。 稍微解釋一下主從式架構，主要是由 master 來執行讀與寫，然後每個操作會記錄在 binlog 中，master 會透過 REPLICATION 這個權限讓 slave 可以去取回 binlog 的操作，藉由非同步的方式同步資料。 預先提醒，如果 slave 是由 master 透過複製虛擬機出來的，如果遇到 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;  these UUIDs must be different for replication to work 這個錯誤，就是代表 MySQL Server 的 UUID 重複，這時候只需要把 slave 的 /var/lib/mysql/auto.cnf 這個檔案刪除，之後執行 service mysql restart 就可以解決問題囉！ Master 1. 以 Ubuntu 20.10 為例，MySQL 設定 replica 的設定檔在 /etc/mysql/mysql.conf.d/mysqld.cnf 在該檔案中加入 binlog_do_db = 指定的資料庫 bind-address = 0.0.0.0 server_id = 1 log_bin = /var/lib/mysql/mysql-bin.log log-slave-updates = 0 expire_logs_days = 10 # 如果開始 sync_binlog = 1 與innodb_flush_log_at_trx_commit # 一致性會是最好，但會影響效能。 sync_binlog = 0 innodb_flush_log_at_trx_commit = 0 ...

sql_mode="" 主要為sql內部特殊規則限制,通常方便工程師會特別設定成"",代表空值不做任何限制,因為不保證工程師都能寫出高品質的SQL語句 sync_binlog=0 innodb_flush_log_at_trx_commit = 0 兩個特別是影響資料庫刷盤的參數,第一個指binlog刷盤,第二個指資料異動的刷盤,都設定為0速度最優,通常只有Master需要改成雙0,雙1的話安全性最高但最慢 max_connections = 5000 連線數上限 connect_timeout=10 連線timeout秒數 open_files_limit = 65535 能同時開啟檔案的上限 max_allowed_packet = 500M 能接受的封包最大值 transaction-isolation = REPEATABLE-READ 資料安全性隔離等級 RR為預設 query_cache_size = 0 query_cache_type = 0 過時的快取設定,兩組都設定為0,才能徹底關閉 expire_logs_days = 7 binlog保留天數,如果有硬碟空間問題,可以嘗試減少,但是相對備份檔案的有效保存期限 slow_query_log=1 long_query_time=1 slow_query_log_file=/var/lib/mysql/slow.log log_throttle_queries_not_using_indexes=1000 min_examined_row_limit=1000 log-slow-admin-statements = TRUE 慢查詢相關記錄設定,基本上有點能力的DBA才能處理優化,目前只需要開啟紀錄即可 innodb_file_per_table = 1 讓innodb的data實體檔案 .ibd文件從ibdata1獨立出來,分散寫入改善整體效能 innodb_buffer_pool_size = 3G *最重要影響資料庫讀取效能沒有之一 innodb引擎的快取層吃多少記憶體的設定,通常試情況設定為實體80%左右佔成

產生一個 mysql user 給 test 資料庫 並且先綁定 user 只能從 localhost 再改為可以從任何地方 # 建立 test 資料庫 CREATE DATABSES test; # 給 user@localhost 有 test 資料庫所有的權限, 並設置密碼 GRANT ALL privileges on test.* to user@'%'identified by 'your_password'; # 刷新權限 flush privileges; # 將 user@localhost 改為 user@% RENAME USER 'user'@'localhost' TO 'user'@'%'; 如果要讓資料庫是可以允許從其他主機連線 可以去 /etc/mysql/conf.d/mysqld.conf 將 bind-address = 127.0.0.1 換成你想要的ip 同時建議設定防火牆 允許 ip 範圍 # ufw allow from 192.168.2.0/24 允許特定 ip # ufw allow from 192.168.2.235 預設規則都是拒絕 # ufw default deny

LOAD DATA LOCAL INFILE  ' 檔案的絕對路徑 ' INTO TABLE 要塞入資料的資料表 CHARACTER SET UTF8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS ;

UPDATE [table1_name] AS t1 INNER JOIN [table2_name] AS t2 ON t1.[column1_name] = t2.[column1_name] SET t1.[column2_name] = t2.[column2_name]; 更新t1.column2_name = tb.column2_name的用法 參考連結  http://www.voidtricks.com/mysql-inner-join-update/

在做專案時很常會碰到要有個功能叫做 "瀏覽過的商品" 在我們系統是使用Log來做 使用Shopper_Id去Log中找出對應的商品ID，然後order by Timestamp desc 因為商品ID在Log中很容易重覆，所以可以使用distinct/group by來處理 但問題來了，明明有使用order by Timestamp desc，但順序卻不是我要的? 這是因為order by的欄位並不在我們的group by/distinct中 該如何解決? 使用order by MAX(Timestamp) desc就可以了 意思大概就是在我們Log中找Timestamp最大的來比對 更詳細的可以看國外高手的文章囉 So what is a workaround for this problem – in other words, how can we be more specific to get what we really want? Well, let’s rephrase the problem – what if we say we want to retrieve each salesperson ID sorted by their respective highest dollar amount value ( and only have each salesperson_id returned just once )? This is different than just saying that we want each distinct salesperson ID sorted by their Amount, because we are being more specific by saying that we want to sort by  their respective highest dollar amount value . Hopefully you see the difference. From: http://www.programmerinterview.com/index.php/database-sql/sql-select-distinct-a...

來源 :  http://emn178.pixnet.net/blog/post/95064604-%E8%A7%A3%E6%B1%BAtable-'.-dbname-tablename'-is-marked-as-crashed-and-sh 當遇到 Table './dbname/tablename' is marked as crashed and should be repaired when using LOCK TABLES 原因： 資料表的相關檔案由於不明原因發生損壞，而無法正常存取。 解決方案： 可以使用以下兩種方式嘗試修復資料表： 使用SQL語句 REPAIR TABLE tablename 使用myisamchk 使用命令列進入資料表所在目錄，例如 cd /var/lib/mysql/dbname 執行 myisamchk -r tablename 執行過程中可能會遇到以下錯誤 myisamchk: error: myisam_sort_buffer_size is too small 這是修復過程中所需記憶體空間超過預設的空間，可以利用--sort_buffer_size參數指派更大的記憶體，例如： myisamchk -r tablename --sort_buffer_size=2G

LOAD DATA LOCAL INFILE 'G:\articles.csv' INTO TABLE database.table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; http://stackoverflow.com/questions/11429827/how-to-import-a-csv-file-into-mysql-workbench

case when reference: http://jax-work-archive.blogspot.tw/2008/06/case-mysql-switch-if-else.html 必須在 SELECT,UPDATE,INSERT,DELETE 中 具有 switch 與 if else 兩種方式可用 #switch 的用法 SELECT CASE a WHEN 100 THEN a WHEN 50 THEN '0' ELSE '3' END FROM table; #if else 的用法 SELECT CASE WHEN a>100 THEN a WHEN a>50 THEN '0' ELSE '3' END FROM table; IFNULL reference: http://www.barryblogs.com/mysql-ifnull-if/ SELECT IFNULL(0, 1) ->0!=null,所以回傳0 SELECT IFNULL(1, 10) => 1!=null,所以回傳1 SELECT IFNULL(NULL, 'YES') =>若第一個參數為null時,回傳第二個參數,回傳YES

原文引用 :  http://stackoverflow.com/questions/12760394/1146-table-phpmyadmin-pma-recent-doesnt-exist 懶得看只好快速解,但我不知道原因..沒甚麼時間google只能記錄下來 sudo vi /etc/phpmyadmin/config.inc.php 約在81行處 將所有的pma_ 改成 pma__ (兩個底線)

http://stackoverflow.com/questions/514943/php-mysql-order-by-two-columns ORDER BY column_A DESC , column_B DESC

有可能是因為mysql進行dns解析導致網路速度變慢 所以在mysql中的my.ini or my.conf的最底下 在mysqld的選項底下新增skip-name-resolve即可解決!