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

mysql> use sys

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+-----------------------------------------------+

| Tables_in_sys                                 |

+-----------------------------------------------+

| host_summary                                  |

| host_summary_by_file_io                       |

| host_summary_by_file_io_type                  |

| host_summary_by_stages                        |

| host_summary_by_statement_latency             |

| host_summary_by_statement_type                |

| innodb_buffer_stats_by_schema                 |

| innodb_buffer_stats_by_table                  |

| innodb_lock_waits                             |

| io_by_thread_by_latency                       |

| io_global_by_file_by_bytes                    |

| io_global_by_file_by_latency                  |

| io_global_by_wait_by_bytes                    |

| io_global_by_wait_by_latency                  |

| latest_file_io                                |

| memory_by_host_by_current_bytes               |

| memory_by_thread_by_current_bytes             |

| memory_by_user_by_current_bytes               |

| memory_global_by_current_bytes                |

| memory_global_total                           |

| metrics                                       |

| processlist                                   |

| ps_check_lost_instrumentation                 |

| schema_auto_increment_columns                 |

| schema_index_statistics                       |

| schema_object_overview                        |

| schema_redundant_indexes                      |

| schema_table_lock_waits                       |

| schema_table_statistics                       |

| schema_table_statistics_with_buffer           |

| schema_tables_with_full_table_scans           |

| schema_unused_indexes                         |

| session                                       |

| session_ssl_status                            |

| statement_analysis                            |

| statements_with_errors_or_warnings            |

| statements_with_full_table_scans              |

| statements_with_runtimes_in_95th_percentile   |

| statements_with_sorting                       |

| statements_with_temp_tables                   |

| sys_config                                    |

| user_summary                                  |

| user_summary_by_file_io                       |

| user_summary_by_file_io_type                  |

| user_summary_by_stages                        |

| user_summary_by_statement_latency             |

| user_summary_by_statement_type                |

| version                                       |

| wait_classes_global_by_avg_latency            |

| wait_classes_global_by_latency                |

| waits_by_host_by_latency                      |

| waits_by_user_by_latency                      |

| waits_global_by_latency                       |

| x$host_summary                                |

| x$host_summary_by_file_io                     |

| x$host_summary_by_file_io_type                |

| x$host_summary_by_stages                      |

【PostgreSQL】記錄一次psql: FATAL: Ident authentication failed for user "XXX" 錯誤訊息

角色已創好，卻無法登入

-bash-4.1$ psql -h localhost -U aaa mydb

psql: FATAL: Ident authentication failed for user "aaa"

修改pg_hba.conf參數

[root@rosalie-mysql01 ~]# vi /var/lib/pgsql/10/data/pg_hba.conf

# "local" is for Unix domain socket connections only

local all all peer改trust

# IPv4 local connections:

host all all 127.0.0.1/32 ident改md5

# IPv6 local connections:

host all all ::1/128 identmd5

# Allow replication connections from localhost, by a user with the

# replication privilege.

修改好參數後重啟,再試登入成功

[root@rosalie-mysql01 ~]# service postgresql-10 restart

[root@rosalie-mysql01 ~]# su - postgres

-bash-4.1$ psql -h localhost -U aaa mydb

Password for user aaa:

psql (10.10)

Type "help" for help.

mydb=> \c

You are now connected to database "mydb" as user "aaa".

mydb=> \du

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------------------+-----------

aaa | Create role, Create DB | {}

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

reporting | Replication | {}

【PostgreSQL】PostgreSQL10.10主從復制步驟

測試用機器資訊如下

主192.168.1.1 CentOS release 6.9 (Final)

從192.168.1.2 CentOS release 6.10 (Final)

紅色是指令

主服務器先創一個資料夾

[root@rosalie-mysql01 opt]# mkdir -p /opt/pgsql/pg_archive

切換使用者並登入，接著建立一個使用者reporting

[root@rosalie-mysql01 pg_archive]# su - postgres

-bash-4.1$ psql

psql (10.10)

Type "help" for help.

創一個使用者

postgres=# CREATE ROLE reporting login replication encrypted password '1234';

CREATE ROLE

離開

postgres=# \q

修改用戶端身份驗證由組態檔案pg_hba.conf的位置,看原本服務是安裝在哪

-bash-4.1$ vi /var/lib/pgsql/10/data/pg_hba.conf

內容很多,但只要加入兩行

-------------------------------------------------------

host       all                     all                 192.168.1.2/32 trust #允許從IP連接到主服務器

host       replication        reporting      192.168.1.2/32 md5 #允許從IP使用reporting用戶來複製

修改參數配置

-bash-4.1$ vi /var/lib/pgsql/10/data/postgresql.conf

-------------------------------------------------------

listen_addresses = '*'

# 監控所有IP

archive_mode = on

# 設置自動歸檔

wal_level = replica

# 預寫日誌模式,增加wal歸檔資訊，包括唯讀伺服器需要的資訊。

archive_command = 'cp %p /opt/pgsql/pg_archive/%f'

# 用此命令進行歸檔logfile segment

max_wal_senders = 3

# 默認是0,設置指最多有幾個複製連接,如幾個從就設幾個,但pg_basebackup會使用到一個

wal_keep_segments = 256

# 默認是0,設置複製保留的最多的xlog數目

wal_sender_timeout = 60s

# 設置複製主機發送數據的超過時間

max_connections = 100

# 這個設置要留意,主庫必需要比從庫大

hot_standby = on

full_page_writes = on

# 開啟全頁寫入pg_rewind前提條件,如果不將整個page寫入wal中,在介質恢復的時候WAL中記錄的數據不足以實現完整的恢復

wal_log_hints = on

# pg_rewind 前提條件

min_wal_size = 512MB

# 最小的wal空間

max_wal_size = 2GB

# max_wal_size = (3 * checkpoint_segments) * 16MB CheckPointSegments得到的值范圍是 max_wal_size 的 1/3 ～ 1/2，最小為1

設置完成後重啟服務

[root@rosalie-mysql01 ~]# service postgresql-10 restart

Stopping postgresql-10 service: [ OK ]

Starting postgresql-10 service: [ OK ]

從伺服器配置

刪除數據庫下的文件,然後從主伺服器同步複製數據至從伺服器

[root@rosalie-mysql02 opt]# service postgresql-10 stop

Stopping postgresql-10 service: [ OK ]

[root@rosalie-mysql02 opt]# rm -rf /var/lib/pgsql/10/data/

[root@rosalie-mysql02 opt]# pg_basebackup -h 192.168.1.1 -U reporting -D /var/lib/pgsql/10/data -X stream -P

Password: (會要求輸入密碼)

24010/24010 kB (100%), 1/1 tablespace

[root@rosalie-mysql02 pg_archive]# chown -R postgres:postgres /var/lib/pgsql/10/data/

pg_basebackup參數說明如下：

選項說明

-h指定連接的數據庫的主機名或IP地址

-U指定連接的用戶名

-F指定了輸出的格式，支持p（原樣輸出）或者t（tar格式輸出）

-X表示備份開始後，啟動另一個流複製連接從主庫接收WAL日誌

-P表示允許在備份過程中實時的打印備份的進度

-R表示會在備份結束後自動生成recovery.conf文件

-D指定備份寫入的數據目錄,需要與數據庫配置的數據庫目錄一致,初次備份之前從庫的數據目錄需要手動清空

-l表示指定一個備份的標識

修改recovery.conf參數

[root@rosalie-mysql02 opt]# vi /var/lib/pgsql/10/data/recovery.conf

standby_mode = on 

 # 表示此節點為從服務器

primary_conninfo = 'host=192.168.1.1 port=5432 user=reporting password=1234 sslmode=disable sslcompression=1 target_session_attrs=any' 

# 主服服務的信息以及連接的用戶

restore_command = '/usr/bin/rsync -a %f /opt/pgsql/pg_archive/%p'

#表示日誌文件名 %p表示目標路徑和文件名

archive_cleanup_command = 'pg_archivecleanup /opt/pgsql/pg_archive %r'

recovery_target_timeline = 'latest'

重啟從服務器

[root@rosalie-mysql02 pgsql]# service postgresql-10 restart

Stopping postgresql-10 service: [ OK ]

Starting postgresql-10 service: [ OK ]

接著主伺服器查詢

[root@rosalie-mysql01 ~]# ps -ef |grep postgres

postgres 9188 1 0 11:12 ? 00:00:00 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data

postgres 9190 9188 0 11:12 ? 00:00:00 postgres: logger process

postgres 9192 9188 0 11:12 ? 00:00:00 postgres: checkpointer process

postgres 9193 9188 0 11:12 ? 00:00:00 postgres: writer process

postgres 9194 9188 0 11:12 ? 00:00:00 postgres: wal writer process

postgres 9195 9188 0 11:12 ? 00:00:00 postgres: autovacuum launcher process

postgres 9197 9188 0 11:12 ? 00:00:00 postgres: stats collector process

postgres 9198 9188 0 11:12 ? 00:00:00 postgres: bgworker: logical replication launcher

postgres 9204 9188 0 11:12 ? 00:00:00 postgres: wal sender process reporting 192.168.1.2(48262) streaming 0/B000098

root 9206 7033 0 11:12 pts/0 00:00:00 grep postgres

或登入postgres用指令查詢

[root@rosalie-mysql01 ~]# su - postgres

-bash-4.1$ psql

psql (10.10)

Type "help" for help.

postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

pid | state | client_addr | sync_priority | sync_state

------+-----------+---------------+---------------+------------

9204 | streaming | 192.168.1.2 | 0 | async

(1 row)

從伺服器查詢

[root@rosalie-mysql02 data]# su - postgres

-bash-4.1$ psql

psql (10.10)

Type "help" for help.

postgres=# select * from pg_stat_replication;

pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | back

end_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_prior

ity | sync_state

------+----------+-----------+------------------+---------------+-----------------+-------------+-------------------------------+-----

---------+-----------+-----------+-----------+-----------+------------+-----------------+---------------+-----------------+-----------

----+------------

9204 | 16384 | reporting | walreceiver | 192.168.1.2 | | 48262 | 2019-10-30 11:12:41.816703+08 |

| streaming | 0/B0000D0 | 0/B0000D0 | 0/B0000D0 | 0/B0000D0 | 00:00:00.000136 | 00:00:00.0003 | 00:00:00.000426 |

0 | async

(1 row)

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

t

(1 row)

【PostgreSQL】一般常用指令

切換使用者 su - postgres

[root@rosalie-mysql02 ~]# su - postgres

-bash-4.1$

連線至資料庫 psql

-bash-4.1$ psql

psql (10.10)

Type "help" for help.

postgres=#

查詢所有資料庫 \l

postgres=# \l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

(4 rows)

查詢select用法功能 \help 名稱

postgres=# \help select

Command: SELECT

Description: retrieve rows from a table or view

Syntax:

[ WITH [ RECURSIVE ] with_query [, ...] ]

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]

[ * | expression [ [ AS ] output_name ] [, ...] ]

[ FROM from_item [, ...] ]

[ WHERE condition ]

[ GROUP BY grouping_element [, ...] ]

[ HAVING condition [, ...] ]

[ WINDOW window_name AS ( window_definition ) [, ...] ]

[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]

[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

[ LIMIT { count | ALL } ]

[ OFFSET start [ ROW | ROWS ] ]

[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

where from_item can be one of:

[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]

[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]

with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

[ LATERAL ] function_name ( [ argument [, ...] ] )

[ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

[ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )

[ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )

[ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )

[ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and grouping_element can be one of:

( )

expression

( expression [, ...] )

ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )

CUBE ( { expression | ( expression [, ...] ) } [, ...] )

GROUPING SETS ( grouping_element [, ...] )

and with_query is:

with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

離開資料庫 \q

postgres=# \q

-bash-4.1$

【PostgreSQL】Linux 上安装 PostgreSQL步驟

至官網https://www.postgresql.org/  點選Downloads

選擇自己適合的作業系統版本

• Red Hat family Linux (including CentOS/Fedora/Scientific/Oracle variants)

進入後1.2.3部份選取自己要的版本--1.2.3選擇好後，選項4.5.6.7會自動產生

1. Select version: 1211109.69.59.4
   ●10
   
   
2. Select platform: 
   ●RedHat Enterprise，CentOS，Scientific或Oracle版本6
   
   
3. Select architecture: 
   ●x86_64
   
   
4. Install the repository RPM:
   ●yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm
   
   
   直接COPY安裝指令在LINUX上執行

[root@rosalie-mysql02 ~]# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Loaded plugins: fastestmirror

Setting up Install Process

pgdg-redhat-repo-latest.noarch.rpm | 5.8 kB 00:00

Examining /var/tmp/yum-root-XHUpda/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-5.noarch

Marking /var/tmp/yum-root-XHUpda/pgdg-redhat-repo-latest.noarch.rpm to be installed

Determining fastest mirrors

epel/metalink | 5.0 kB 00:00

* base: mirror01.idc.hinet.net

* epel: fedora.cs.nctu.edu.tw

* extras: mirror01.idc.hinet.net

* updates: mirror01.idc.hinet.net

base | 3.7 kB 00:00

dockerrepo | 2.9 kB 00:00

epel | 5.3 kB 00:00

epel/primary_db | 6.1 MB 00:00

extras | 3.4 kB 00:00

updates | 3.4 kB 00:00

updates/primary_db | 6.6 MB 00:00

Resolving Dependencies

--> Running transaction check

---> Package pgdg-redhat-repo.noarch 0:42.0-5 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================

Package Arch Version Repository Size

=======================================================================================================================================

Installing:

pgdg-redhat-repo noarch 42.0-5 /pgdg-redhat-repo-latest.noarch 7.5 k

Transaction Summary

=======================================================================================================================================

Install 1 Package(s)

Total size: 7.5 k

Installed size: 7.5 k

Is this ok [y/N]: y

Downloading Packages:

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Warning: RPMDB altered outside of yum.

Installing : pgdg-redhat-repo-42.0-5.noarch 1/1

Verifying : pgdg-redhat-repo-42.0-5.noarch 1/1

Installed:

pgdg-redhat-repo.noarch 0:42.0-5

Complete!

5. Install the client packages: (安裝客戶端軟件包)

        ●yum install postgresql10

[root@rosalie-mysql02 ~]# yum install postgresql10

Loaded plugins: fastestmirror

Setting up Install Process

Loading mirror speeds from cached hostfile

* base: mirror01.idc.hinet.net

* epel: fedora.cs.nctu.edu.tw

* extras: mirror01.idc.hinet.net

* updates: mirror01.idc.hinet.net

pgdg10 | 3.7 kB 00:00

pgdg10/primary_db | 256 kB 00:00

pgdg11 | 3.6 kB 00:00

pgdg11/primary_db | 214 kB 00:00

pgdg12 | 3.6 kB 00:00

pgdg12/primary_db | 106 kB 00:00

pgdg94 | 3.7 kB 00:00

pgdg94/primary_db | 272 kB 00:00

pgdg95 | 3.7 kB 00:00

pgdg95/primary_db | 289 kB 00:00

pgdg96 | 3.7 kB 00:00

pgdg96/primary_db | 283 kB 00:00

Resolving Dependencies

--> Running transaction check

---> Package postgresql10.x86_64 0:10.10-1PGDG.rhel6 will be installed

--> Processing Dependency: postgresql10-libs(x86-64) = 10.10-1PGDG.rhel6 for package: postgresql10-10.10-1PGDG.rhel6.x86_64

--> Processing Dependency: libicu for package: postgresql10-10.10-1PGDG.rhel6.x86_64

--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql10-10.10-1PGDG.rhel6.x86_64

--> Running transaction check

---> Package libicu.x86_64 0:4.2.1-14.el6 will be installed

---> Package postgresql10-libs.x86_64 0:10.10-1PGDG.rhel6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================

Package Arch Version Repository Size

=======================================================================================================================================

Installing:

postgresql10 x86_64 10.10-1PGDG.rhel6 pgdg10 1.6 M

Installing for dependencies:

libicu x86_64 4.2.1-14.el6 base 4.9 M

postgresql10-libs x86_64 10.10-1PGDG.rhel6 pgdg10 327 k

Transaction Summary

=======================================================================================================================================

Install 3 Package(s)

Total download size: 6.9 M

Installed size: 28 M

Is this ok [y/N]: y

Downloading Packages:

(1/3): libicu-4.2.1-14.el6.x86_64.rpm | 4.9 MB 00:00

(2/3): postgresql10-10.10-1PGDG.rhel6.x86_64.rpm | 1.6 MB 00:02

(3/3): postgresql10-libs-10.10-1PGDG.rhel6.x86_64.rpm | 327 kB 00:00

---------------------------------------------------------------------------------------------------------------------------------------

Total 1.4 MB/s | 6.9 MB 00:05

warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Importing GPG key 0x442DF0F8:

Userid : PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>

Package: pgdg-redhat-repo-42.0-5.noarch (@/pgdg-redhat-repo-latest.noarch)

From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Is this ok [y/N]: y

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Installing : postgresql10-libs-10.10-1PGDG.rhel6.x86_64 1/3

Installing : libicu-4.2.1-14.el6.x86_64 2/3

Installing : postgresql10-10.10-1PGDG.rhel6.x86_64 3/3

Verifying : libicu-4.2.1-14.el6.x86_64 1/3

Verifying : postgresql10-10.10-1PGDG.rhel6.x86_64 2/3

Verifying : postgresql10-libs-10.10-1PGDG.rhel6.x86_64 3/3

Installed:

postgresql10.x86_64 0:10.10-1PGDG.rhel6

Dependency Installed:

libicu.x86_64 0:4.2.1-14.el6 postgresql10-libs.x86_64 0:10.10-1PGDG.rhel6

Complete!

6. Optionally install the server packages: (安裝服務器軟件包)

        ●yum install postgresql10-server

[root@rosalie-mysql02 ~]# yum install postgresql10-server

Loaded plugins: fastestmirror

Setting up Install Process

Loading mirror speeds from cached hostfile

* base: mirror01.idc.hinet.net

* epel: fedora.cs.nctu.edu.tw

* extras: mirror01.idc.hinet.net

* updates: mirror01.idc.hinet.net

Resolving Dependencies

--> Running transaction check

---> Package postgresql10-server.x86_64 0:10.10-1PGDG.rhel6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================

Package Arch Version Repository Size

=======================================================================================================================================

Installing:

postgresql10-server x86_64 10.10-1PGDG.rhel6 pgdg10 5.1 M

Transaction Summary

=======================================================================================================================================

Install 1 Package(s)

Total download size: 5.1 M

Installed size: 18 M

Is this ok [y/N]: y

Downloading Packages:

postgresql10-server-10.10-1PGDG.rhel6.x86_64.rpm | 5.1 MB 00:03

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Installing : postgresql10-server-10.10-1PGDG.rhel6.x86_64 1/1

Verifying : postgresql10-server-10.10-1PGDG.rhel6.x86_64 1/1

Installed:

postgresql10-server.x86_64 0:10.10-1PGDG.rhel6

Complete!

7. Optionally initialize the database and enable automatic start:

        ●service postgresql-10 initdb

        ●chkconfig postgresql-10 on

        ●service postgresql-10 start

[root@rosalie-mysql02 ~]# service postgresql-10 initdb

Initializing database: [ OK ]

[root@rosalie-mysql02 ~]# chkconfig postgresql-10 on

[root@rosalie-mysql02 ~]# service postgresql-10 start

Starting postgresql-10 service: [ OK ]

[postgres@rosalie-mysql02 ~]$ service postgresql-10 status
postgresql-10 (pid  15039) is running...

安裝完成

【MYSQL】記錄一次Table 'performance_schema.session_variables' doesn't exist錯誤訊息

mysql> show variables like "%plugin%";

ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist

mysql> show variables like '%show_compatibility_56%';

ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist

第1種解決辦法,如果在不重啟的情形下可用

show_compatibility_56此標誌控制引擎在各種MySQL版本上設置和讀取變量（會話和全局）的查詢時的行為方式。

mysql> set @@global.show_compatibility_56=ON;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%show_compatibility_56%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| show_compatibility_56 | ON    |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql> show variables like "%audit%";

+-------------------------------+-----------------------+

| Variable_name                 | Value                 |

+-------------------------------+-----------------------+

| server_audit_events           |                       |

| server_audit_excl_users       |                       |

| server_audit_file_path        | server_audit.log      |

| server_audit_file_rotate_now  | OFF                   |

| server_audit_file_rotate_size | 1000000               |

| server_audit_file_rotations   | 9                     |

| server_audit_incl_users       |                       |

| server_audit_loc_info         |                       |

| server_audit_logging          | OFF                   |

| server_audit_mode             | 1                     |

| server_audit_output_type      | file                  |

| server_audit_query_log_limit  | 1024                  |

| server_audit_syslog_facility  | LOG_USER              |

| server_audit_syslog_ident     | mysql-server_auditing |

| server_audit_syslog_info      |                       |

| server_audit_syslog_priority  | LOG_INFO              |

+-------------------------------+-----------------------+

16 rows in set (0.00 sec)

另一種解決辦法需重啟DB

mysql> mysql_upgrade -u root -p --force

接著重啟

systemctl restart mysqld

【MYSQL】記錄一次DBI connect(';;mysql_read_default_group=client','root',...) failed: Can't connect to local MySQL server through socket...報錯-版本5.7

[root@rosalie-mysql01 ~]# pt-duplicate-key-checker -u root -p1234 --database=tttt

DBI connect(';;mysql_read_default_group=client','root',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /usr/local/bin/pt-duplicate-key-checker line 894

顯示預設的sock路徑有誤，重新指定正確路徑即可，加入socket=路徑

加入紫色字段即可

[root@rosalie-mysql01 ~]# pt-duplicate-key-checker -u root -p1234 --database=tttt --socket=/tmp/mysql.sock

# ########################################################################

# 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`;

# 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`;

# 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`;

# 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`;

# ########################################################################

# Summary of indexes

# ########################################################################

# Size Duplicate Indexes 16

# Total Duplicate Indexes 4

# Total Indexes 8