學習筆記: mysql8

#使用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的問題

下載 libmysqlclient.so.16， http://files.directadmin.com/services/debian_5.0_64/libmysqlclient.so.16

#下載後拷貝到  /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

1. 去官網下載安裝包，下載鏈接：點擊打開鏈接

https://dev.mysql.com/downloads/mysql/

用wget 下載

[root@rosalie-mysql02]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.16-linux-glibc2.12-x86_64.tar

2. 解壓縮

[root@rosalie-mysql02]# tar xvf mysql-8.0.16-linux-glibc2.12-x86_64.tar

mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

mysql-router-8.0.16-linux-glibc2.12-x86_64.tar.xz

mysql-test-8.0.16-linux-glibc2.12-x86_64.tar.xz

再解tar.xz的壓縮

[root@rosalie-mysql02]# tar -xJf mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

3. 移動壓縮包到usr/local目錄下,並重命名文件

[root@rosalie-mysql02]# mv mysql-8.0.16-linux-glibc2.12-x86_64 /usr/local/

4. 創建mysql用戶組和mysql用戶

[root@rosalie-mysql02]# groupadd mysql

[root@rosalie-mysql02]# useradd -g mysql mysql

5. 進行初始化成功後，記得把密碼記住

[root@rosalie-mysql02 bin]# /usr/local/mysql/bin/mysqld --initialize --user=mysql

2019-05-14T09:08:25.271808Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.16) initializing of server in progress as process 18517

2019-05-14T09:08:27.891836Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: me>*_PnB*65o

2019-05-14T09:08:28.655548Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.16) initializing of server has completed

6. 接著啟動MYSQL

[root@rosalie-mysql02 bin]# service mysql start

. SUCCESS! 

##登入成功

[root@rosalie-mysql02 bin]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 15

Server version: 8.0.16

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

學習筆記

2019年7月5日星期五

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

2019年5月14日星期二

【MYSQL】mysql8.0的錯誤記錄

【MYSQL】安裝MYSQL8.0.16

【MYSQL】MYSQL的SYS表說明（版本8.0）

2019年7月5日 星期五

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

2019年5月14日 星期二

【MYSQL】mysql8.0的錯誤記錄

【MYSQL】安裝MYSQL8.0.16

【MYSQL】MYSQL的SYS表說明（版本8.0）

2019年7月5日星期五

2019年5月14日星期二