2019年12月11日 星期三

【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                      |

2019年10月31日 星期四

【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 | {}

2019年10月30日 星期三

【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)

2019年10月29日 星期二

【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$

2019年10月28日 星期一

【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:

    直接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!

  1. 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!

  1. 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!
  1. 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...

安裝完成

2019年10月22日 星期二

【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

2019年10月21日 星期一

【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

【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 s...