Mysql:大量kill process id

2021-06-292021-06-29 老王 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 可以寫入,不然也是出錯

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

2019-08-282019-09-20 老王 Leave a comment

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

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `t_table`.* TO 't_user'@'%' IDENTIFIED BY "xxxx";
ERROR 1044 (42000): Access denied for user 'root'@'127.0.0.1' to database 't_table'

1 2	mysql> GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `t_table`.* TO 't_user'@'%' IDENTIFIED BY "xxxx"; ERROR 1044 (42000): Access denied for user 'root'@'127.0.0.1' to database 't_table'

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

mysql > show grants;
+—————————————————+
| Grants for root@127.0.0.1 |
+—————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’127.0.0.1’ |
+—————————————————+

mysql > show grants;

+—————————————————+

| Grants for root@127.0.0.1 |

+—————————————————+

| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’127.0.0.1’ |

+—————————————————+

再往下查

mysql> select * from mysql.user where user='root' \G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *xx
      password_expired: N
 password_last_changed: 2019-08-28 02:24:39
     password_lifetime: NULL
        account_locked: N
*************************** 2. row ***************************
                  Host: 127.0.0.1
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *xx
      password_expired: N
 password_last_changed: 2019-08-28 03:50:21
     password_lifetime: NULL
        account_locked: N
2 rows in set (0.00 sec)

mysql> select * from mysql.user where user='root' \G

*************************** 1. row ***************************

Host: localhost

User: root

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: *xx

password_expired: N

password_last_changed: 2019-08-28 02:24:39

password_lifetime: NULL

account_locked: N

*************************** 2. row ***************************

Host: 127.0.0.1

User: root

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: *xx

password_expired: N

password_last_changed: 2019-08-28 03:50:21

password_lifetime: NULL

account_locked: N

2 rows in set (0.00 sec)

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

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

解法：

mysql> UPDATE mysql.user SET Grant_priv = ‘Y’ WHERE user = ‘root’ AND host = ‘127.0.0.1’;
mysql> flush privileges;

1 2	mysql> UPDATE mysql.user SET Grant_priv = ‘Y’ WHERE user = ‘root’ AND host = ‘127.0.0.1’; mysql> flush privileges;

打完收工!

Mysql bin-log 操作

bin-log 檢視內容

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

mysqlbinlog binlog.0000003 | less

1	mysqlbinlog binlog.0000003 \| less

Row 格式

mysqlbinlog --base64-output=DECODE-ROWS -vvv binlog.0000003 | less

1	mysqlbinlog --base64-output=DECODE-ROWS -vvv binlog.0000003 \| less

常用參數

-j 起始 pos
--start-datetime=datetime 起始時間 ex: --start-datetime="2005-12-25 11:25:56"

1 2	-j 起始 pos --start-datetime=datetime 起始時間 ex: --start-datetime="2005-12-25 11:25:56"

其他參數請見官方說明

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

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

mysql> flush logs;

1	mysql> flush logs;

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

mysql> PURGE BINARY LOGS BEFORE '2018-03-31 08:00:00';

1	mysql> PURGE BINARY LOGS BEFORE '2018-03-31 08:00:00';

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

msyql> PURGE BINARY LOGS TO 'mysql-bin.000047';

1	msyql> PURGE BINARY LOGS TO 'mysql-bin.000047';

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

mysql 查詢 user 帳號及權限

2014-01-202014-01-20 老王

查有哪些帳號
SELECT User,Host FROM mysql.user;
查帳號權限
SHOW GRANTS FOR backuper;

查出來的語法是否有覺得很面熟 ?
查出來的結果是可以直接拿去做帳號復原的 !!

Mysql 日期和時間函數不求人

2014-01-062014-01-06 老王

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

show table status from db_name where TO_DAYS(NOW())-TO_DAYS(Update_time) <=30 \G;

1	show table status from db_name where TO_DAYS(NOW())-TO_DAYS(Update_time) <=30 \G;

這裡是一個使用日期函數的例子。下面的查詢選擇了所有記錄，其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排序的問題

2013-12-032013-12-03 老王

當查詢以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 查詢最後執行指令方法

2013-10-292019-08-15 老王

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

Oracle DB 建立 VIEW

2012-04-232019-08-15 老王

以為在目前公司 ERP 不會用到VIEW , 沒想到居然遇到 SQL 指令超過2000字,就已逹程式上限的冏境 ...

所以還是來google 一下 oracle DB 怎麼建view

1.建立

<span id="lnum1" style="color: #606060;"> 1:</span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">VIEW</span> USER_NAME.VIEW_NAME <span style="color: #0000ff;">AS</span>  <span style="color: #008000;">//以下為查詢語法</span>

				1

						<span id="lnum1" style="color: #606060;"> 1:</span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">VIEW</span> USER_NAME.VIEW_NAME <span style="color: #0000ff;">AS</span>  <span style="color: #008000;">//以下為查詢語法</span>

<span id="lnum2" style="color: #606060;"> 2:</span> <span style="color: #0000ff;">SELECT</span> * <span style="color: #0000ff;">FROM</span> USER_NAME.TABLE <span style="color: #0000ff;">WHERE</span> A_TABLE=<span style="color: #006080;">'SOMETHING'</span>;

				1

						<span id="lnum2" style="color: #606060;"> 2:</span> <span style="color: #0000ff;">SELECT</span> * <span style="color: #0000ff;">FROM</span> USER_NAME.TABLE <span style="color: #0000ff;">WHERE</span> A_TABLE=<span style="color: #006080;">'SOMETHING'</span>;

2.修改

<span id="lnum1" style="color: #606060;"> 1:</span> <span style="color: #0000ff;">REPLACE</span> <span style="color: #0000ff;">VIEW</span> USER_NAME.VIEW_NAME;

				1

						<span id="lnum1" style="color: #606060;"> 1:</span> <span style="color: #0000ff;">REPLACE</span> <span style="color: #0000ff;">VIEW</span> USER_NAME.VIEW_NAME;

3.刪除

<span id="lnum1" style="color: #606060;"> 1:</span> <span style="color: #0000ff;">DROP</span> <span style="color: #0000ff;">VIEW</span> USER_NAME.VIEW_NAME;

				1

						<span id="lnum1" style="color: #606060;"> 1:</span> <span style="color: #0000ff;">DROP</span> <span style="color: #0000ff;">VIEW</span> USER_NAME.VIEW_NAME;

4.查詢

<span id="lnum1" style="color: #606060;"> 1:</span> <span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> VIEW_NAME

				1

						<span id="lnum1" style="color: #606060;"> 1:</span> <span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> VIEW_NAME

T-SQL 常用字串函數

2012-01-052012-01-05 老王

substring(欄位名稱,開始字元,取得字元字數)=>取欄位從第幾字元開始取幾位字元

left(欄位名稱,取得字元字數)=>取欄位左邊開始幾位字元

right(欄位名稱,取得字元字數)=>取欄位右邊開始幾位字元

ltrim(欄位名稱)=>去除欄位左邊空白

rtrim(欄位名稱)=>去除欄位右邊空白

replace(欄位名稱,要被變更的字,變更後的字)=>變更特定字元

分類: T-SQL

Mysql:大量kill process id

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

mysql 還原單一table