學習筆記

2019年3月14日星期四

【MYSQL】工具percona-toolkit-3.0.4-RPM和TAR安裝

RPM安裝
版本查詢下載 https://www.percona.com/downloads/percona-toolkit/

# wget https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/redhat/6/x86_64/percona-toolkit-3.0.4-r2e44c3a-el6-x86_64-bundle.tar

本範例是下載percona-toolkit-3.0.4，解壓後出現兩個檔

# tar -xvf percona-toolkit-3.0.4-r2e44c3a-el6-x86_64-bundle.tar
percona-toolkit-3.0.4-1.el6.x86_64.rpm
percona-toolkit-debuginfo-3.0.4-1.el6.x86_64.rpm

執行rpm安裝主程式出現錯誤訊息

# rpm -ivh percona-toolkit-3.0.4-1.el6.x86_64.rpm
warning: percona-toolkit-3.0.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
perl(Time::HiRes) is needed by percona-toolkit-3.0.4-1.el6.x86_64
perl(IO::Socket::SSL) is needed by percona-toolkit-3.0.4-1.el6.x86_64
perl(Term::ReadKey) is needed by percona-toolkit-3.0.4-1.el6.x86_64

解決方式安裝依賴包
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum install perl-TermReadKey.x86_64

另外也有可能缺少的↓
yum install perl-DBI
yum install perl-DBD-MySQL

安裝好缺少的依賴後，再次安裝主程式

# rpm -ivh percona-toolkit-3.0.4-1.el6.x86_64.rpm
warning: percona-toolkit-3.0.4-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]

1:percona-toolkit ########################################### [100%]

然後再下指令pt-query-digest --help只要有出現文字，即表示安裝完成！

# pt-query-digest --help

pt-query-digest analyzes MySQL queries from slow, general, and binary log files.

It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data

from tcpdump. By default, queries are grouped by fingerprint and reported in

descending order of query time (i.e. the slowest queries first). If no C<FILES>

are given, the tool reads C<STDIN>. The optional C<DSN> is used for certain

options like L<"--since"> and L<"--until">. For more details, please use the

--help option, or try 'perldoc /usr/bin/pt-query-digest' for complete

documentation.

Usage: pt-query-digest [OPTIONS] [FILES] [DSN]

Options:

--ask-pass Prompt for a password when connecting to MySQL

--attribute-aliases=a List of attribute|alias,etc (default db|Schema)

--attribute-value-limit=i A sanity limit for attribute values (default 0)

--charset=s -A Default character set

--config=A Read this comma-separated list of config files;

if specified, this must be the first option on

the command line

--[no]continue-on-error Continue parsing even if there is an error (

default yes)

--[no]create-history-table Create the --history table if it does not exist (

default yes)

--[no]create-review-table Create the --review table if it does not exist (

default yes)

--daemonize Fork to the background and detach from the shell

--database=s -D Connect to this database

--defaults-file=s -F Only read mysql options from the given file

--embedded-attributes=a Two Perl regex patterns to capture pseudo-

attributes embedded in queries

--expected-range=a Explain items when there are more or fewer than

expected (default 5,10)

--explain=d Run EXPLAIN for the sample query with this DSN

and print results

--filter=s Discard events for which this Perl code doesn't

return true

--group-by=A Which attribute of the events to group by (

default fingerprint)

--help Show help and exit

--history=d Save metrics for each query class in the given

table. pt-query-digest saves query metrics (

query time, lock time, etc.) to this table so

you can see how query classes change over time

--host=s -h Connect to host

--ignore-attributes=a Do not aggregate these attributes (default arg,

cmd, insert_id, ip, port, Thread_id, timestamp,

exptime, flags, key, res, val, server_id,

offset, end_log_pos, Xid)

--inherit-attributes=a If missing, inherit these attributes from the

last event that had them (default db,ts)

--interval=f How frequently to poll the processlist, in

seconds (default .1)

--iterations=i How many times to iterate through the collect-

and-report cycle (default 1)

--limit=A Limit output to the given percentage or count (

default 95%:20)

--log=s Print all output to this file when daemonized

--order-by=A Sort events by this attribute and aggregate

function (default Query_time:sum)

--outliers=a Report outliers by attribute:percentile:count (

default Query_time:1:10)

--output=s How to format and print the query analysis

results (default report)

--password=s -p Password to use when connecting

--pid=s Create the given PID file

--port=i -P Port number to use for connection

--preserve-embedded-numbers Preserve numbers in database/table names when

fingerprinting queries

--processlist=d Poll this DSN's processlist for queries, with --

interval sleep between

--progress=a Print progress reports to STDERR (default time,

30)

--read-timeout=m Wait this long for an event from the input; 0 to

wait forever (default 0). Optional suffix s=

seconds, m=minutes, h=hours, d=days; if no

suffix, s is used.

--[no]report Print query analysis reports for each --group-by

attribute (default yes)

--report-all Report all queries, even ones that have been

reviewed

--report-format=A Print these sections of the query analysis

report (default rusage,date,hostname,files,

header,profile,query_report,prepared)

--report-histogram=s Chart the distribution of this attribute's

values (default Query_time)

--resume=s If specified, the tool writes the last file

offset, if there is one, to the given filename

--review=d Save query classes for later review, and don't

report already reviewed classes

--run-time=m How long to run for each --iterations. Optional

suffix s=seconds, m=minutes, h=hours, d=days; if

no suffix, s is used.

--run-time-mode=s Set what the value of --run-time operates on (

default clock)

--sample=i Filter out all but the first N occurrences of

each query

--set-vars=A Set the MySQL variables in this comma-separated

list of variable=value pairs

--show-all=H Show all values for these attributes

--since=s Parse only queries newer than this value (parse

queries since this date)

--slave-password=s Sets the password to be used to connect to the

slaves

--slave-user=s Sets the user to be used to connect to the slaves

--socket=s -S Socket file to use for connection

--timeline Show a timeline of events

--type=A The type of input to parse (default slowlog)

--until=s Parse only queries older than this value (parse

queries until this date)

--user=s -u User for login if not current user

--variations=A Report the number of variations in these

attributes' values

--version Show version and exit

--[no]version-check Check for the latest version of Percona Toolkit,

MySQL, and other programs (default yes)

--[no]vertical-format Output a trailing "\G" in the reported SQL

queries (default yes)

--watch-server=s This option tells pt-query-digest which server

IP address and port (like "10.0.0.1:3306") to

watch when parsing tcpdump (for --type tcpdump);

all other servers are ignored

Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time

Rules:

This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.

DSN syntax is key=value[,key=value...] Allowable DSN keys:

KEY COPY MEANING

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

A yes Default character set

D yes Default database to use when connecting to MySQL

F yes Only read default options from the given file

P yes Port number to use for connection

S yes Socket file to use for connection

h yes Connect to host

p yes Password to use when connecting

t no The --review or --history table

u yes User for login if not current user

If the DSN is a bareword, the word is treated as the 'h' key.

Options and values after processing arguments:

--ask-pass FALSE

--attribute-aliases db|Schema

--attribute-value-limit 0

--charset (No value)

--config /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-query-digest.conf,/root/.percona-toolkit.conf,/root/.pt-query-digest.conf

--continue-on-error TRUE

--create-history-table TRUE

--create-review-table TRUE

--daemonize FALSE

--database (No value)

--defaults-file (No value)

--embedded-attributes (No value)

--expected-range 5,10

--explain (No value)

--filter (No value)

--group-by fingerprint

--help TRUE

--history (No value)

--host (No value)

--ignore-attributes arg,cmd,insert_id,ip,port,Thread_id,timestamp,exptime,flags,key,res,val,server_id,offset,end_log_pos,Xid

--inherit-attributes db,ts

--interval .1

--iterations 1

--limit 95%:20

--log (No value)

--order-by Query_time:sum

--outliers Query_time:1:10

--output report

--password (No value)

--pid (No value)

--port (No value)

--preserve-embedded-numbers FALSE

--processlist (No value)

--progress time,30

--read-timeout 0

--report TRUE

--report-all FALSE

--report-format rusage,date,hostname,files,header,profile,query_report,prepared

--report-histogram Query_time

--resume (No value)

--review (No value)

--run-time (No value)

--run-time-mode clock

--sample (No value)

--set-vars

--show-all

--since (No value)

--slave-password (No value)

--slave-user (No value)

--socket (No value)

--timeline FALSE

--type slowlog

--until (No value)

--user (No value)

--variations

--version FALSE

--version-check TRUE

--vertical-format TRUE

--watch-server (No value)

TAR安裝

解壓

# tar xvf percona-toolkit-3.0.4.tar

進去資料夾內

# cd percona-toolkit-3.0.4
# perl Makefile.PL

可能問題一：

Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1.

BEGIN failed--compilation aborted at Makefile.PL line 1.

解決方式安裝依賴包

# type perl
perl is hashed (/usr/bin/perl)

# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

Loaded plugins: fastestmirror

Setting up Install Process

Loading mirror speeds from cached hostfile

* base: free.nchc.org.tw

* extras: free.nchc.org.tw

* updates: free.nchc.org.tw

base | 3.7 kB 00:00

extras | 3.4 kB 00:00

updates | 3.4 kB 00:00

Package perl-ExtUtils-MakeMaker-6.55-144.el6.x86_64 already installed and latest version

Resolving Dependencies

--> Running transaction check

---> Package perl-ExtUtils-CBuilder.x86_64 1:0.27-144.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

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

Package Arch Version Repository Size

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

Installing:

perl-ExtUtils-CBuilder x86_64 1:0.27-144.el6 base 49 k

Transaction Summary

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

Install 1 Package(s)

Total download size: 49 k

Installed size: 59 k

Is this ok [y/N]: y

Downloading Packages:

perl-ExtUtils-CBuilder-0.27-144.el6.x86_64.rpm | 49 kB 00:00

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Installing : 1:perl-ExtUtils-CBuilder-0.27-144.el6.x86_64 1/1

Verifying : 1:perl-ExtUtils-CBuilder-0.27-144.el6.x86_64 1/1

Installed:

perl-ExtUtils-CBuilder.x86_64 1:0.27-144.el6

Complete!

可能問題二：

Checking if your kit is complete...

Warning: the following files are missing in your kit:

README

Please inform the author.

Writing Makefile for percona-toolkit

解決方式

# yum install perl-DBD-MySQL
# perl Makefile.PL
Writing Makefile for percona-toolkit

# make test

PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t

Files=0, Tests=0, 0 wallclock secs ( 0.01 usr + 0.00 sys = 0.01 CPU)

Result: NOTESTS

# make install

Appending installation info to /usr/lib64/perl5/perllocal.pod

然後再下指令pt-query-digest --help只要有出現文字，即表示安裝完成！

# pt-query-digest --help

pt-query-digest analyzes MySQL queries from slow, general, and binary log files.

It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data

--type slowlog

--until (No value)

--user (No value)

--variations

--version FALSE

--version-check TRUE

--vertical-format TRUE

--watch-server (No value)

如果執行pt-query-digest --help 報錯

Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/pt-query-digest line 3225.

BEGIN failed--compilation aborted at /usr/local/bin/pt-query-digest line 3225.

解決方式

# yum install perl-Time-HiRes

安裝完成後再輸入pt-query-digest --help指令
# pt-query-digest --help

pt-query-digest analyzes MySQL queries from slow, general, and binary log files.

It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data

--type slowlog

--until (No value)

--user (No value)

--variations

--version FALSE

--version-check TRUE

--vertical-format TRUE

--watch-server (No value)

只要出現訊息就表示成功

2019年3月13日星期三

【MYSQL】xtrabackup單庫/單表復原

原存在DB如下，測試目前只需要復原testmaster這個DB

testmaster內的table如下

在 table-test表內，有兩筆資料

2019年3月12日星期二

【MYSQL】報錯排解記錄

問題1.Unknown storage engine 'FEDERATED'報錯

[rosalie@test]# mysqldump -uroot -p   --all-databases > all_db_backup.sql

Enter password: 

mysqldump: Got error: 1286: Unknown storage engine 'FEDERATED' when using LOCK TABLES

[rosalie@test]# mysql -uroot -p  < all_db.sql

ERROR 1286 (42000) at line 902: Unknown storage engine 'FEDERATED'

解決方式

修改my.cnf 增加 federated 字樣後，後再重啟DB，重啟後再次操作即可！

問題2.mysqlslap: Error when connecting to server: 1040 Too many connections 報錯

連結至MYSQL出現 Too many connections 表示連接到 MySQL 的連線數超出了 MySQL 的連線數上限, 預設上限是 100。

解決方式

修改 /etc/my.cnf 設定檔

開啟檔案 /etc/my.cnf, 在 [mysqld] 段落下加入這行, 設定連線數上限到 500, 參數內容類似這樣:

[mysqld]

max_connections = 500

問題3.Starting MySQL. ERROR! The server quit without updating PID file報錯

[rosalie@~# vi /etc/my.cnf

增加binlog參數

log_bin = /mysql/data/mysql-bin

加入後重啟DB

[root@ data]# service mysql restart

Shutting down MySQL.. SUCCESS! 

Starting MySQL. ERROR! The server quit without updating PID file (/mysql/data/rosalie.pid).

解決方式

**排除權限(資料夾權限)問題、與mysql進程(ps aux | grep mysql)問題

修改my.cnf 增加server-id

server-id=1

[root@ data]# service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL... SUCCESS!

問題4.2019-05-06 11:35:31 11729 [ERROR] Slave SQL: Error 'SELECT command denied to user 'root'@'192.168.0.0' for table 'table_name'' on query. Default database: 'test'. Query: 'UPDATE users SET remark = 'gefegsd:[invalid_number];' WHERE loginname = 'ggggg'', Error_code: 1142

2019-05-06 11:35:31 11729 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000511' position 921455474

2019-05-06 11:35:31 11729 [ERROR] Slave SQL: Error 'SELECT command denied to user 'root'@'192.168.0.0' for table 'table_name'' on query. Default database: 'test'. Query: 'UPDATE users SET remark = 'gefegsd:[invalid_number];' WHERE loginname = 'ggggg'', Error_code: 1142

2019-05-06 11:35:31 11729 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000511' position 921455474

解決方式

#原因是因為查詢更新的表，在test的資料庫中，並沒有table_name這個表
#停止slave 再重啟即可

mysql> stop slave;

mysql> start slave;

問題5.ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'catalog.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

#查詢欄位ID及組合姓名，以部門ID來分組

mysql> select id,group_concat(name) from users group by department_id;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_crm_catalog.crm_users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解決方式

#查看當前sql_mode參數

mysql> SELECT @@GLOBAL.sql_mode;

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

| @@GLOBAL.sql_mode                                                                                                         |

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

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION                                                                                                             |

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

1 row in set (0.00 sec)

#排除ONLY_FULL_GROUP_BY參數mysql5.7默認開啟only_full_group_by

mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Query OK, 0 rows affected (0.00 sec)

mysql> select id,group_concat(name) from users group by department_id;                        

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

| id | group_concat(name)                                                                                                 |

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

| 23 | Yuna,Sonia,Fedrick,Vicky,June,Ben,Mandy,Evaine,Miya                                               |

|  2 | Jack,Zhi,Admin,PS,Lawrence,Ivan,Sze Mun,Joshua,Vincent                                          |

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

2 rows in set (0.03 sec)

問題6. mysql_install_db初使化失敗

[root@rosalie-mysql01 data]# mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3307

2019-05-22 16:47:56 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize

2019-05-22 16:47:59 [WARNING] The bootstrap log isn't empty:

2019-05-22 16:47:59 [WARNING] 2019-05-22T08:47:56.568382Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead

解決方式

# mysql_install_db 這方法僅適用於 MySQL 5.7.7之前

[root@rosalie-mysql01 data]# /usr/local/mysql/bin/mysqld  --initialize  --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql_3307

問題7. 設置全局變量報錯 ERROR 1238 (HY000): Variable 'xxx' is a read only variable

mysql> set global sha256_password_private_key_path="private_key.peme";

ERROR 1238 (HY000): Variable 'sha256_password_private_key_path' is a read only variable

解決方式

#因該參數為只讀參數，需要在配置文件中更改該參數，之後重啟數據庫

vi /etc/my.cnf

修改參數後存檔

重啟DB

問題8. 啟動MYSQL報錯 Starting MySQL.. ERROR! The server quit without updating PID file (/data/mysql/rosalie-mysql02.pid).

進入MYSQL資料夾內查看ERROR.LOG

2019-08-28T06:38:23.655853Z 0 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use

2019-08-28T06:38:23.655858Z 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?

2019-08-28T06:38:23.655864Z 0 [ERROR] Aborting

[root@rosalie-mysql02 data]# ps -aux | grep mysql

Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ

root 21821 0.0 0.0 11432 1548 pts/2 S 14:25 0:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/rosalie-mysql02.pid

mysql 21989 0.1 9.6 1867128 391416 pts/2 Sl 14:25 0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/mysql --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=rosalie-mysql02.err --pid-file=/data/mysql/rosalie-mysql02.pid --socket=/tmp/mysql.sock --port=3306

root 23293 0.0 0.0 103320 884 pts/2 S+ 14:39 0:00 grep mysql

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

Starting MySQL. SUCCESS!

【MYSQL】mysqldump 備份指令說明與範例

備份指令 :mysqldump

DB→資料庫-TEST、BOOK

TABLE→TEST資料內的資料表-TEST1、TEST2
BOOK資料內的資料表-TBOOK1、TBOOK2、TBOOK3

備份單一資料庫(DB:TEST)

指令：mysqldump -uroot -p --databases database_name > backup_name.sql

範例# mysqldump -uroot -p --databases TEST > TEST.sql

備份單一資料表(DB:TEST內的TEST1)

【MYSQL】權限說明及創建

授予權限指令：
grant 權限(參考表1) on (權限範圍如*.*或dbname.*) to '用戶'@'IP' identified by '密碼';
此指令在給予帳號時就會自動創建帳號 ,等同於先create 再 grant意思相同

mysql> grant select,update on *.* to 'root'@'localhost' identified by '1234';

【LINUX】壓縮、解壓縮指令

副檔名	解壓	壓縮(檔案→紅色壓縮後)	查看檔案內容
*.tar	tar -xvf 檔案	tar -cvf test.tar 要壓的資料夾
*.gz	gzip -d 檔案或 gunzip 檔案	gzip 檔案	zcat 檔案.gz
*.tar.gz	tar -xzf 檔案	tar -czf test.tar.gz 要壓的資料夾
*.tgz(同上)	tar -xzf 檔案	tar -czf test.tar.gz 要壓的資料夾
*.bz2	bzip2 -d 或bunzip2 檔案	bzip2 -z 檔案	bzcat 檔案.bz2
*.tar.bz2	tar -xjf 檔案	tar -cjf test.tar.bz2 要壓的資料夾
*.Z	uncompress 檔案或 compress -d 檔案	需另外安裝 compress 要壓的資料夾
*.tar.Z	tar -xZf 檔案	tar -cZf test.tar.Z 要壓的資料夾需另外安裝
*.rar	unrar e 檔案	rar a test.rar 要壓的資料夾
*.zip	unzip 檔案	zip -r 檔案名.zip 要壓的資料夾	less 檔案.zip

*.tar.xz

tar jxvf 檔案

tar Jcvf 檔案名.tar.xz 要壓的資料夾

常用的參數說明：

-c: 建立壓縮檔案

-f:指定建立的檔案名稱

-v:顯示建立的過程"

-x:解壓(解打包)即解開打包

-r:向壓縮歸檔檔案末尾追加檔案

-u:更新原壓縮包中的檔案

-t:檢視內容

-z:具gzip的屬性

-p:使用原來的檔案屬性

例:

tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
解一個mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz的壓縮檔，解完移至/usr/local/下

訂閱：文章 (Atom)

2019年3月14日 星期四

2019年3月13日 星期三

2019年3月12日 星期二

2019年3月14日星期四

2019年3月13日星期三

2019年3月12日星期二