▼

2019年7月5日 星期五

【MYSQL】記錄一次install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.報錯-版本8.0

#使用percona-toolkit工具內的pt-duplicate-key-checker功能時報錯
[root@rosalie ~]# pt-duplicate-key-checker -uroot -p1234 --socket=/tmp/mysql.sock  --database=tttt
install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.
Compilation failed in require at (eval 20) line 3.
at /usr/bin/pt-duplicate-key-checker line 894

#查看一下/usr/bin/pt-duplicate-key-checker的894行是些什麼
#顯然是跟DBD有關，可能那麼說明DBD::MYSQL未安裝成功。

   894          $dbh = eval { DBI->connect($cxn_string, $user, $pass, $defaults) };
   895    
   896          if ( !$dbh && $EVAL_ERROR ) {
   897             if ( $EVAL_ERROR =~ m/locate DBD\/mysql/i ) {
   898             die "Cannot connect to MySQL because the Perl DBD::mysql module is "
   899                  . "not installed or not found.  Run 'perl -MDBD::mysql' to see "
   900                  . "the directories that Perl searches for DBD::mysql.  If "
   901                  . "DBD::mysql is not installed, try:\n"
   902                  . "  Debian/Ubuntu  apt-get install libdbd-mysql-perl\n"
   903                  . "  RHEL/CentOS    yum install perl-DBD-MySQL\n"

---------------------------------------------------------------
#問題是因為目標機器缺少mysql驅動程序庫：libmysqlclient.so
#這是構建“DBD-mysql”時的一個重要庫。所以要修復它

#查詢一下mysql.so路徑
[root@rosalie ~]# find / -name "mysql.so"
/usr/lib64/perl5/auto/DBD/mysql/mysql.so

#檢查一下mysql.so與libmysqlclient.so.16的依賴關係，顯然無法連接到動態庫
[root@rosalie ~]# ldd /usr/lib64/perl5/auto/DBD/mysql/mysql.so
    linux-vdso.so.1 =>  (0x00007ffcfb9ef000)
    libmysqlclient.so.16 => not found
    libz.so.1 => /lib64/libz.so.1 (0x00007f28b3b3f000)
    libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f28b3908000)
    libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f28b36ef000)
    libm.so.6 => /lib64/libm.so.6 (0x00007f28b346a000)
    libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007f28b31fe000)
    libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007f28b2e19000)
    libc.so.6 => /lib64/libc.so.6 (0x00007f28b2a84000)
    libfreebl3.so => /lib64/libfreebl3.so (0x00007f28b2881000)
    libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f28b263d000)
    libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f28b2355000)
    libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f28b2151000)
    libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f28b1f25000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00007f28b1d20000)
    /lib64/ld-linux-x86-64.so.2 (0x00007f28b3f7c000)
    libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f28b1b15000)
    libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f28b1912000)
    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f28b16f7000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f28b14da000)
    libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f28b12ba000)


 
解決libmysqlclient.so.16 => not found的問題

#下載後拷貝到  /lib64/ 下
[root@rosalie-mysql]#  cp libmysqlclient.so.16 /lib64/

# 拷貝完成後再次查看 依賴關係
[root@rosalie-mysql]#  ldd /usr/lib64/perl5/auto/DBD/mysql/mysql.so
    linux-vdso.so.1 =>  (0x00007fffcf49f000)
    libmysqlclient.so.16 => /lib64/libmysqlclient.so.16 (0x00007f84f0d20000)
    libz.so.1 => /lib64/libz.so.1 (0x00007f84f0b0a000)
    libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f84f08d2000)
    libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f84f06b9000)
    libm.so.6 => /lib64/libm.so.6 (0x00007f84f0435000)
    libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007f84f01c8000)
    libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007f84efde3000)
    libc.so.6 => /lib64/libc.so.6 (0x00007f84efa4f000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f84ef831000)
    libfreebl3.so => /lib64/libfreebl3.so (0x00007f84ef62e000)
    libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f84ef3ea000)
    libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f84ef102000)
    libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f84eeefe000)
    libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f84eecd2000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00007f84eeacd000)
    /lib64/ld-linux-x86-64.so.2 (0x00007f84f125c000)
    libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f84ee8c2000)
    libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f84ee6bf000)
    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f84ee4a4000)
    libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f84ee285000)

#接著再次執行pt-duplicate-key-checker
#出現的錯誤問題變了一點
[root@rosalie-mysql]# pt-duplicate-key-checker -u root -p1234 --database=tttt
DBI connect(';;mysql_read_default_group=client','root',...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at /usr/bin/pt-duplicate-key-checker line 894
mysqli_real_connect(): The server requested authentication method unknown to the client [sha256_password]

#因8.0.11版本起，不再像mysql5.7及以前版本那樣，設定使用者密碼時預設的驗證方式為caching_sha2_password，如果發現升級mysql8.0.11後原有的程式不能連線mysql，可迅速在mysql command line client客戶端用下面的命令設定成mysql5.7及以前版本的密碼驗證方式，同時MYSQL8.0.11下修改密碼的方式與原先也不大一樣，原先的部分修改密碼的命令在mysql8.0.11下不能使用。

#進入MYSQL內查詢使用者帳號及HOST
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| rosalie          | 127.0.0.1 |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| rosalie          | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)

#似乎是密碼加密方法不同，由原本mysql_native_password 改成8.0的 caching_sha2_password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';
Query OK, 0 rows affected (0.00 sec)

#之後再次執行就沒有報錯了
[root@rosalie-mysql]# pt-duplicate-key-checker -u root -p1234 --database=tttt





於 沒有留言:

2019年7月4日 星期四

【MYSQL】percona-toolkit工具-利用pt-duplicate-key-checker找出重複索引與冗餘索引


因實際環境上，可能開發因為需求不斷增加，但卻沒有仔細分析檢測過是否重複，此時可以利用percona-toolkit工具內的pt-duplicate-key-checker功能，來找出重複索引與冗餘索引。

安裝方法可參考↓

pt-duplicate-key-checker的使用方法

pt-duplicate-key-checker [OPTION...] [DSN]
使用說明：檢查重複或多餘(冗餘)的索引和外鍵，並打印出信息。
參數說明：加粗的為常用的。

--all-structs：
結構（B樹，哈希等）的索引，默認情況下是禁止的。因為BTREE索引可能會覆蓋作為FULLTEXT索引的列，其實他們不是真正的重複，因為是不同的索引結構。

--ask-pass 

提示輸入密碼。
--charset
縮寫-A，字符集設置：utf8，gbk，latin1 …
--[no] clustered
主鍵和第二索引一起是重複的，默認開啟。檢測時的非主鍵索引的多列索引的後綴是一個主鍵最左邊的前綴，則作為一個重複鍵。如：
PRIMARY KEY (`a`)
KEY `b` (`b`,`a`)
SELECT ... WHERE b=1 ORDER BY a;
如果按照這個工具去掉b索引，則會出現filesort，他給的意見是留下b（b）單列索引，把order by a去掉，因為他們的結果是一樣的，a是主鍵。
--config 
 讀取該配置文件的列表。
--databases
只檢查該列表中的數據庫。
--defaults-file
縮寫-F，讀取Mysql的配置文件，需要絕對路徑。
--engines
縮寫-e，只檢查該列表中指定的表的存儲引擎。
--tables
縮寫-t，只檢查列表中指定的表。
--help 
顯示幫助
--host
縮寫-h，連接到主機地址。

--ignore-databases
跳過檢查的某些數據庫。
--ignore-engines
跳過檢查的某些存儲引擎。
--ignore-tables
 跳過檢查某些表。
--ignore-order
加了這個參數會報： KEY(a,b)和KEY(b,a)是重複索引，默認關閉。
--key-types
檢查索引的類型：f=foreignkeys, k=keys or fk=both，默認是fk。
--password
縮寫-p，連接mysql時候的密碼。
--pid 
連接的PID
--port
縮寫-P，連接時候的端口。
--socket
縮寫-S，連接時候的套接字。
--[no]sql
打印出sql，如果有重複會答應出刪除重複索引的sql。默認開啟。
--[no]summary
打印出索引的統計信息。默認開啟。
--verbose 
縮寫-v，打印出所有的索引信息，包括重複索引。
--version 
打印版本信息。
#創建一個test_index表，並加入多個索引
mysql >
CREATE TABLE test_index(
    ID INT NOT NULL PRIMARY KEY,
    A INT NOT NULL,
    B INT NOT NULL,
    C INT NOT NULL,
    D INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID),
    KEY B (`B`),
    KEY A (`A`),
    KEY ABC (`A`,`B`,`C`),
    KEY BCD (`B`,`C`,`D`),
    KEY ID_C_D (`ID`,`C`,`D`)
) ENGINE=InnoDB;

#可以看一下表的結構
mysql> DESC test_index;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID    | int(11) | NO   | PRI | NULL    |       |
| A     | int(11) | NO   | MUL | NULL    |       |
| B     | int(11) | NO   | MUL | NULL    |       |
| C     | int(11) | NO   |     | NULL    |       |
| D     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
5 rows in set (0.00 sec)

#開始分析剛創建的那張test_index的索引是否重覆
[root@rosalie-mysql02 mysql]# pt-duplicate-key-checker -u root -p1234 --database=tttt


# A software update is available:
#   * The current version for Percona::Toolkit is 3.0.5


# ########################################################################
# tttt.test_index                                                         
# ########################################################################


# ID_2 is a duplicate of PRIMARY
# Key definitions:
#   KEY `ID_2` (`ID`),
#   PRIMARY KEY (`ID`),
# Column types:
#      `id` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `tttt`.`test_index` DROP INDEX `ID_2`;

##紫色的是工具分析出,可以直接在DB執行
#索引ID_2是多餘的,因為ID原本就是主鍵了,又多設一個INDEX變ID_2

# Uniqueness of ID ignored because PRIMARY is a duplicate constraint
# ID is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `ID` (`ID`),
#   PRIMARY KEY (`ID`),
# Column types:
#      `id` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `tttt`.`test_index` DROP INDEX `ID`;

#索引ID是多餘的,因為ID原本就是主鍵了,又多設一個唯一索引ID這欄索引就再重複

# B is a left-prefix of BCD
# Key definitions:
#   KEY `B` (`B`),
#   KEY `BCD` (`B`,`C`,`D`),
# Column types:
#      `b` int(11) not null
#      `c` int(11) not null
#      `d` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `tttt`.`test_index` DROP INDEX `B`;

#索引B是多餘的,因為已經有一個組合索引，同單左前綴索引效用

# A is a left-prefix of ABC
# Key definitions:
#   KEY `A` (`A`),
#   KEY `ABC` (`A`,`B`,`C`),
# Column types:
#      `a` int(11) not null
#      `b` int(11) not null
#      `c` int(11) not null
# To remove this duplicate index, execute:
ALTER TABLE `tttt`.`test_index` DROP INDEX `A`;

#索引A是多餘的,因為已經有一個組合索引，同單左前綴索引效用


# ########################################################################
# Summary of indexes                                                      
# ########################################################################


# Size Duplicate Indexes   16  #Size重複索引16
# Total Duplicate Indexes  4   #重複索引總數
# Total Indexes            9






#這是在未刪除多餘索引前查看表創建的內容
mysql> show create table tttt.test_index;
| test_index | CREATE TABLE `test_index` (
  `ID` int(11) NOT NULL,
  `A` int(11) NOT NULL,
  `B` int(11) NOT NULL,
  `C` int(11) NOT NULL,
  `D` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `ID_2` (`ID`),
  KEY `B` (`B`),
  KEY `A` (`A`),
  KEY `ABC` (`A`,`B`,`C`),
  KEY `BCD` (`B`,`C`,`D`),
  KEY `ID_C_D` (`ID`,`C`,`D`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
1 row in set (0.00 sec)

#依上面工具分析的結果，將多餘的INDEX刪除後，再重新查看
mysql> show create table tttt.test_index;
| test_index | CREATE TABLE `test_index` (
  `ID` int(11) NOT NULL,
  `A` int(11) NOT NULL,
  `B` int(11) NOT NULL,
  `C` int(11) NOT NULL,
  `D` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `ABC` (`A`,`B`,`C`),
  KEY `BCD` (`B`,`C`,`D`),
  KEY `ID_C_D` (`ID`,`C`,`D`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

於 沒有留言:

2019年7月3日 星期三

【MYSQL】 MySQL系統變量(system variables)

    MySQL系統變量(system variables)

什麼是系統變量　
   系統變量實際上用於控制數據庫的一些行為和方式的參數。例如我們啟動數據庫的時，設定內存大小，使用什麼隔離級別，或日誌文件大小，存放位置...等等很多。當然我們數據庫系統啟動後，有些系統變量(參數)也可以通過動態修改來及時調整數據庫。
   系統變量取值：都有默認值，可以在啟動時或是啟動後修改。
   設置範圍：【全局】或【會話 】 級別，全局級別需要super權限就是影響整個伺服器(更改當下前連進系統的不影響)，會話級別只影響自身會話。
   設置方法：啟動前可以通過配置文件以及啟動選項來修改，啟動後通過SET子句來設置。
   生效週期：全局變量全局可見，但只影響在更改後所連接進伺服器的。當前會話及已登錄的會話不受影響。

   對於有關係到size大小的設置值，可以使用後綴K、M或G分別表示千字節、兆字節或gigabytes，不區分大小寫。
  

查詢測試的版本
mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 8.0.16                       |
| version_comment         | MySQL Community Server - GPL |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.12              |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

修改變量及查詢變量的語法
　更改方式(藍色是變量名稱)查看語法(藍色是變量名稱)
Global全局
set global sort_buffer_size=value;
set @@global.sort_buffer_size=value;
select @@global.sort_buffer_size;
show global variables like ' sort_buffer_size';
Session會話
set session sort_buffer_size=value;
set @@session.sort_buffer_size=value;
set sort_buffer_size=value;
select @@sort_buffer_size;
select @@session.sort_buffer_size;
show session variables like 'sort_buffer_size';
Both both表示既可以是作為全局級別的，也可以作為會話級別的Global跟Session變更語法都可使用，看是針對全局或會話
Varies 是根據數據庫版本而定　　
--查看全部系统变量
show variables;

因變量參數太多，可參考官方說明

Name
System VarVar ScopeDynamic
變量名系統變量級別動態YES.NO

Session 、 Global  、 Both、級別的差別
(範圍分 Global、  Session 、 Both)由上連結可以查看參數的 Var Scope值
1.Session測試查看及變更 use_secondary_engine而官網寫 Var Scope 的var範圍是 Session
--查看該參數的設置
mysql> show variables like "use_secondary_engine%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| use_secondary_engine | ON    |
+----------------------+-------+
1 row in set (0.01 sec)

--修改變量為關(用session會話方式更改)
mysql> set use_secondary_engine=off;
Query OK, 0 rows affected (0.00 sec)

--修改完後查看
mysql> show variables like "use_secondary_engine%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| use_secondary_engine | OFF   |
+----------------------+-------+
1 row in set (0.00 sec)

--用全局方式修改變量會報錯(因為他不是global而是session)
mysql> set global use_secondary_engine=off;
ERROR 1228 (HY000): Variable 'use_secondary_engine' is a SESSION variable and can't be used with SET GLOBAL

--使用另一台機器，在修改後登入，因為是會話級別，所以前者操作，只會影響自身的連線，不會影響別人的連線，設定也會在登出後恢復
mysql> show variables like "use_secondary_engine%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| use_secondary_engine | ON    |
+----------------------+-------+
1 row in set (0.00 sec)



2.Global 測試查看及變更 sha256_password_proxy_users 而官網寫 Var Scope 的var範圍是 Global
--查看該參數的設置
mysql> show variables like "sha256_password_proxy_users%";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| sha256_password_proxy_users | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

--用會話方式修改變量會報錯(因為他不是session而是global)
mysql> set sha256_password_proxy_users=on;
ERROR 1229 (HY000): Variable 'sha256_password_proxy_users' is a GLOBAL variable and should be set with SET GLOBAL

--用全局方式修改OK
mysql> set global sha256_password_proxy_users=on;
Query OK, 0 rows affected (0.00 sec)

--當下查詢變量改更改
mysql> show variables like "sha256_password_proxy_users%";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| sha256_password_proxy_users | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)


--使用另一台機器，測試不管在登入前或登入後的使用者皆都更改了
mysql> show variables like "use_secondary_engine%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| use_secondary_engine | ON    |
+----------------------+-------+
1 row in set (0.00 sec)


3.Both測試查看及變更 binlog_format 而官網寫 Var Scope 的var範圍是 Both
#用會話方式更改沒問題
mysql> set binlog_format ="Mixed";
Query OK, 0 rows affected (0.01 sec)

#查詢也己變更
mysql> show variables like "binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

#但用全局方式查詢顯示全局仍為ROW
mysql> show global variables like "binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

#用全局方式變更
mysql> set global binlog_format ="Mixed";
Query OK, 0 rows affected (0.00 sec)

#用全局方式查詢也己更改
mysql> show global variables like "binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)


總結
官網顯示 Dynamic 動態 分YES或NO 
YES：可以進入DB內更改
NO :只能從配置文件my.cnf更改，重啟後才會生效

可能出現問題

--只能讀不能直接修改，要透過my.cnf修改重啟才可
mysql> set global back_log=152;
ERROR 1238 (HY000): Variable 'back_log' is a read only variable

--應該用global用成session
mysql> set sha256_password_proxy_users=on;
ERROR 1229 (HY000): Variable 'sha256_password_proxy_users' is a GLOBAL variable and should be set with SET GLOBAL

--應該用session用成global
mysql> set global use_secondary_engine=off;
ERROR 1228 (HY000): Variable 'use_secondary_engine' is a SESSION variable and can't be used with SET GLOBAL
於 沒有留言: