標籤: mysql

Mysql:大量kill process id

老王 Leave a comment

mysql 被搞爛的時候 ex: 下錯 index , alert 錯, 屬性 ...
大量的 process 堆積又不能 stop mysql,我們需要一個大量 kill 的 script
先了解資料該從哪裡來: 

mysql> show create table information_schema.processlist \G;
*************************** 1. row ***************************
       Table: PROCESSLIST
Create Table: CREATE TEMPORARY TABLE `PROCESSLIST` (
  `ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `USER` varchar(32) NOT NULL DEFAULT '',
  `HOST` varchar(64) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` int(7) NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext,
  `TIME_MS` bigint(21) NOT NULL DEFAULT '0',
  `ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

當然你也可直接用show processlist , 不過還要自己拆解欄位,selete table 篩選語法也很直觀
接著我們可以利用T-SQL 來組合出我們要的語法： 

select concat('KILL ',id,';') from information_schema.processlist
where user='root' and time > 200 and info like 'select %' into outfile '/var/lib/mysql-files/kill.txt';
# user =  root
# time  >  200 sec
# info 是 selet 開頭
# 然後寫到 /var/lib/mysql-files/ , 確認無誤後再輸入
  
mysql> source /var/lib/mysql-files/kill.txt；

## 補充知識
/var/lib/mysql-files/ 這個目錄是預設位置,如果你想改,正常會得到以下錯誤

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
執行以下語法查詢目前設定： 

mysql> show global variables like '%secure_file_priv%';
# null 表示未設定

但這個值是維讀不能線上修改,必要的話要去改my.cnf ,然後重啟mysql
最無腦的就是設定,請留意安全性 

secure_file_priv=''

其次是要寫入的目錄必需是mysql 的user 可寫,如我習慣用 mysql:mysql這個user:group 跑 mysql service ,
就必需 mysql 可以寫入,不然也是出錯

Msyql:mysql_config_editor 免密碼登入-加密版

老王 Leave a comment

在批次處理 mysql 作業，為免去人工輸入密碼，通常有三種做法，我還是小嫩嫩的時候都是用第一種…

  1. 直接在bash 填入密碼，簡單好用但是不安全，mysql 還會發警告 
    mysql -uroot -pxxxx
Warning: Using a password on the command line interface can be insecure
  2. 針對所有使用者免必密碼-修改my.cnf 
    vim /etc/my.cnf
[client]
user=“root”
password=“xxxx”

    2–1 針對個別使用者的免密碼-在自己的家目錄建立~/.my.cnf

    vim ~/.my.cnf
[client]
user=“root”
password=“xxxx”
    
最好加個權限控管，因為“明碼”，你知道的！
chmod 600 ~/.my.cnf

接著 打入mysql -uroot 就可操作 mysql 了

以上都是明碼，我也不太喜歡，還好 mysql 出了第三種方法，就是 mysql_config_editor

host:
mysql_config_editor set –login-path=name –host=localhost –user=root –password
Enter password: (input) “xxxx”



Socket:
mysql_config_editor set -G name -S /tmp/mysql.sock -u root -p
Enter password: (input) “xxxx”



-h,–host=name 添加host到登陸文件中
-G，–login-path=name 在登錄文件中為login path添加名字（默認為client）
-p,–password 在登陸文件中添加密碼（該密碼會被mysql_config_editor自動加密）
-u，–user 添加用戶名到登陸文件中
-S,–socket=name 添加sock文件路徑到登陸文件中
-P，–port=name 添加port到登陸文件中

接著你就會發現在家目錄下有個 ~/.mylogin.cnf 加密的二進制檔案，相對安全[不會有人承諾100%安全的..]，你可以用以下指令查看已建置的內容

mysql_config_editor print –all

然後使用以下指令操作mysql

mysql –login-path=name

移除設定

mysql_config_editor remove –login-path=name

更多說明請見 https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html

Percoan 體系注意：
Percona client 5.7 找不到 mysql_config_editor 的解法

apt install libperconaserverclient20-dev
#注意：有可能會連mysql-server 一起update，production 環境要留意

Mysql:5.7 sql_mode 新預設值

老王 Leave a comment

通常遇到升到5.7 ，又沒改default value，會碰到2個問題

  • datetime default value 是 0000–00–00 00:00:00 不給建table
  • group by 在 select 中，必需明確定義(不能是只是‘*’)

都不難解，但取決於是開發階段還是升級，開發階段就改code 符合mysql 吧, 嚴謹總是比較好!
至於升級… 訂定目標吧
無奈解法：改回5.6 的預設值(記得也要修改mysql.cnf)：

Mysql 5.7 預設 sql mode 說明

ONLY_FULL_GROUP_BY

在嚴格模式下，不要讓GROUP BY部分中的查詢指向未選擇的列，否則報錯。

NO_ZERO_DATE

在嚴格模式，不要將’0000–00–00’做為合法日期。你仍然可以用IGNORE選項插入零日期。在非嚴格模式，可以接受該日期，但會生成警告。

NO_ZERO_IN_DATE

在嚴格模式，不接受月或日部分為0的日期，對年不限制。如果使用IGNORE選項，我們為類似的日期插入’0000–00–00’。在非嚴格模式，可以接受該日期，但會生成警告。

ERROR_FOR_DIVISION_BY_ZERO

在嚴格模式，在INSERT或UPDATE過程中，如果被零除(或MOD(X，0))，則產生錯誤(否則為警告)。如果未給出該模式，被零除時MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作結果為NULL。

NO_AUTO_CREATE_USER

在嚴格模式下，防止GRANT自動建立新使用者，除非還指定了密碼。

NO_ENGINE_SUBSTITUTION

如果需要的儲存引擎被禁用或未編譯，可以防止自動替換儲存引擎。

STRICT_TRANS_TABLES

為事務儲存引擎啟用嚴格模式，也可能為非事務儲存引擎啟用嚴格模式，非法資料值被拒絕，下面有詳細說明。

嚴格模式控制MySQL如何處理非法或丟失的輸入值。有幾種原因可以使一個值為非法。例如，資料型別錯誤，不適合列，或超出範圍。當新插入的行不包含某列的沒有顯示定義DEFAULT子句的值，則該值被丟失。

對於事務表，當啟用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式時，如果語句中有非法或丟失值，則會出現錯誤。語句被放棄並滾動。

對於非事務表，如果插入或更新的第1行出現壞值，兩種模式的行為相同。語句被放棄，表保持不變。如果語句插入或修改多行，並且壞值出現在第2或後面的行，結果取決於啟用了哪個嚴格選項：

對於STRICT_ALL_TABLES，MySQL返回錯誤並忽視剩餘的行。但是，在這種情況下，前面的行已經被插入或更新。這說明你可以部分更新，這可能不是你想要的。要避免這點，最好使用單行語句，因為這樣可以不更改表即可以放棄。

對於STRICT_TRANS_TABLES，MySQL將非法值轉換為最接近該列的合法值並插入調整後的值。如果值丟失，MySQL在列中插入隱式 預設值。在任何情況下，MySQL都會生成警告而不是給出錯誤並繼續執行語句。

嚴格模式不允許非法日期，例如’2004–04–31’。它不允許禁止日期使用“零”部分，例如’2004–04–00’或”零”日期。要想禁止，應在嚴格模式基礎上，啟用NO_ZERO_IN_DATE和NO_ZERO_DATE SQL模式。

如果你不使用嚴格模式(即不啟用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式)，對於非法或丟失的值，MySQL將插入調整後的值並給出警告。在嚴格模式，你可以通過INSERT IGNORE或UPDATE IGNORE來實現。

Mysql: ALL PRIVILEGES 帳號依然無開帳號 ( ERROR 1044)

老王 Leave a comment

一直以來用root@localhost 開帳號都沒什麼問題，開了一個[email protected] 給dev 用，就出問題了

檢查一下權限，發現也沒問題

再往下查

眼尖的你有發現嗎 ？
“Grant_priv: N”

查了一下google 大神，原來必需要有 Grant_priv 才能 Grant, 卻不包含在 ALL PRIVILEGES 裡

解法：

打完收工!

mysql 還原單一table

老王

需求：在 mysqldump databases 的備份檔分割出單一 table dump file
mysqldump 時的環境參數至少需有
add-drop-table TRUE
lock-tables TRUE
使用sed 分表

線上環境範例
實測環境: centos6.9 , mysql5.5
分表:

還原:

一行:

2021-09-30
想開車不用自己造輪子-> github

mysql bin-log 操作

老王

Mysql bin-log 操作

bin-log 檢視內容

一般 statement 格式，可能會狂噴，加個less

Row 格式

常用參數

其他參數請見 官方說明

bin-log 造成空間不足的處理方法

動作觸發 : 結束目前的binlog , 產生新的binlog , 檢查過期binlog並刪除

指定刪除某個時間以前的 binary log 檔案


指定刪除某個檔案流水號以前的 binary log 檔案


直接去刪binlog 檔，再把 binlog.index 裡面的記錄刪除，但是官方不建議這麼做

Mysql 日期和時間函數不求人

老王

出處 : http://www.webasp.net/article/25/24538.htm
我的應用 : 查詢表格更新日期小於30天

這裡是一個使用日期函數的例子。下面的查詢選擇了所有記錄，其date_col的值是在最後30天以內：
mysql> SELECT something FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。這些索引值對應於ODBC標準。
mysql> select DAYOFWEEK('1998-02-03');
-> 3
WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5
mysql> select WEEKDAY('1997-11-05');
-> 2
DAYOFMONTH(date)
返回date的月份中日期，在1到31範圍內。
mysql> select DAYOFMONTH('1998-02-03'); Read More

mysql中ip排序的問題

老王

當查詢以IP排序時
               select * from mypc order by ip
出現下面囧境：
192.168.1.1
192.168.1.11
192.168.1.111
192.168.1.2
192.168.1.22

在mysql中有兩個函數，一個INET_NTOA() 一個INET_ATON()
N就是數字(NUMBER) A就是位址(ADDRESS)
如果下指令   SELECT INET_ATON('127.0.0.1');
出現   2130706433
如果下指令   SELECT INET_NTOA(2130706433);
出現   '127.0.0.1'
所以我在查詢時排序使用INET_ATON()就可以把IP使用數字化排序。
指令：
          select * from ipmactable order by inet_aton(ip)
便可正常如下：
192.168.1.1
192.168.1.2
192.168.1.5
192.168.1.22
192.168.1.111

 

來源 : http://hd-inky.blogspot.tw/2010/05/mysqlip.html

mysql 查詢最後執行指令方法

老王

MySQL 有幾種方法可以用, 將以挑喜歡的其中一種, 加入 /etc/mysql/my.cnf(debian), 再 restart mysql 即可:
第一種: 查 Slow query 的 SQL 語法:

  • log_slow_queries = /var/log/mysql/mysql-slow.log
  • long_query_time = 2 (超過 2秒的 SQL 語法記錄起來, 設短一點來記錄除錯也是一種方法 XD)

第二種: 設 MySQL Replication 用 binlog:

  • log_bin = /var/log/mysql/mysql-bin.log (此檔要用 mysqlbinlog 解來看)
  • mysql 會將所有 INSERT/UPDATE/DELETE 語法記於此(但是語法可能跟你想的不同), 這是要寫給 SLAVE 用的 log 檔

第三種: 推薦此方法, 將 MySQL 執行的每行指令全都記錄起來:

  • log = /tmp/mysql.log

restart mysql 後, tail -f /tmp/mysql.log