【MYSQL】mysql8.0的錯誤記錄

問題1 :安裝完成後執行任意指令出現 :
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

解決方法：alter user user() identified by "你的密碼";

mysql> alter user user() identified by "1234";

Query OK, 0 rows affected (0.01 sec)

問題2 :新增人員給予密碼無法執行出現問題 :
mysql> grant all privileges on *.* to 'rosalie'@'localhost' identified by '1234';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '1234'' at line 1

Mysql 8.0以後的版本不適用以上指令創建，需分開指令執行

解決方法：

mysql> create user 'rosalie'@'localhost' identified by '1234';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'rosalie'@'localhost';

Query OK, 0 rows affected (0.01 sec)

【MYSQL】安裝MYSQL8.0.16

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>

【MYSQL】DB內某TABLE資料導出成csv檔

方法一：mysql內執行

進入DB內，執行SQL導出成CSV檔。

此案例是進入DB:testcsv  需要導出的table：newjbo (只需改紅色部份)
/tmp/newjbo.csv是要存放的路徑

執行SQL

mysql> use  testcsv

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> SELECT * INTO OUTFILE '/tmp/newjbo.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM newjbo;

Query OK, 46 rows affected (0.02 sec)

可能會出現的問題

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

secure-file-priv參數是用來限制LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE()傳到哪個指定目錄的。

• ure_file_priv的值為null ，表示限制mysql 不允許導入|導出
• 當secure_file_priv的值為/tmp/ ，表示限制mysql 的導入|導出只能發生在/tmp/目錄下
• 當secure_file_priv的值沒有具體值時，表示不對mysql 的導入|導出做限制

解決：如何查看secure-file-priv參數的值：

mysql> show global variables like '%secure%';

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

| Variable_name            | Value |

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

| require_secure_transport | OFF   |

| secure_auth              | ON    |

| secure_file_priv         | NULL  |

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

3 rows in set (0.07 sec)

此開關默認為NULL，即不允許導入導出。

故修改my.cnf設定重啟後即可導出，在[mysqld]內加入，路徑沒限制就可打"/" 若有限制就看自己要放在哪寫入完整路徑。

[mysqld]
secure_file_priv ="/"

加入後重啟DB，再次進入MYSQL內重新執行SQL，即可導出CSV檔

方法二：LINUX下執行
在LINUX下直接導出
只需修改紅色部份，第一個是語法，第二個是要存放的路徑

mysql -uroot -p --default-character-set=gbk -e "select * from testcsv.newjob" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > testcsv.newjob.csv

【MYSQL】測試一次binlog 刪除或異動資料復原

測試一次binlog 刪除或異動資料復原

#建立數據庫binlogtest

mysql> create database binlogtest;

Query OK, 1 row affected (0.02 sec)

#使用數據庫binlogtest

mysql> use binlogtest;

Database changed

#顯示目前binlog最新的位置，也是目前創建DB的位置

mysql> SHOW BINARY LOGS;

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

| Log_name         | File_size  |

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

| mysql-bin.000020 |  344636396 |

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

6 rows in set (0.03 sec)

#新增test1表

mysql> CREATE TABLE test1(id INT(10) NOT NULL,name varchar(20),age INT(10));

Query OK, 0 rows affected (0.15 sec)

#插入幾筆數據

mysql> insert into test1 (id,name,age) values (1,'user1',18),(2,'user2',19),(3,'user3',20);

Query OK, 3 rows affected (0.03 sec)

Records: 3  Duplicates: 0  Warnings: 0

#檢查一下數據

mysql> select * from test1;

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

| id | name  | age  |

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

|  1 | user1 |   18 |

|  2 | user2 |   19 |

|  3 | user3 |   20 |

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

3 rows in set (0.00 sec)

#準備測試----誤下錯指令

mysql> update test1 set age = 20;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

#數據已變動

mysql> select * from test1;

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

| id | name  | age  |

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

|  1 | user1 |   20 |

|  2 | user2 |   20 |

|  3 | user3 |   20 |

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

3 rows in set (0.00 sec)

#若當下又有使用者插入資料

mysql> insert into test1 values(4,'user4',21),(5,'user5',22),(6,'user6',23);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;

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

| id | name  | age  |

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

|  1 | user1 |   20 |

|  2 | user2 |   20 |

|  3 | user3 |   20 |

|  4 | user4 |   21 |

|  5 | user5 |   22 |

|  6 | user6 |   23 |

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

6 rows in set (0.00 sec)

復原方法：

#再開一個視窗在LINUX下先把binlog文件導出，只有binlogtest這個DB

mysqlbinlog mysql-bin.000020 --database=binlogtest > binlogtest.sql

#接著編輯把錯誤的指令刪除update test1 set age = 20（用FIND搜尋字串）

vi binlogtest.sql

如下字串把下面字段全部刪除後存檔

# at 344637142

#190510 10:40:49 server id 1  end_log_pos 344637253 CRC32 0xad712e55    Query   thread_id=1590  exec_time=0     error_code=0

SET TIMESTAMP=1557456049/*!*/;

update test1 set age = 20

/*!*/;

#接著切換到剛MYSQL視窗把DB內把binlogtest的DB刪除

mysql> drop database binlogtest;

Query OK, 1 row affected (0.06 sec)

#然後在LINUX下把binlogtest.sql就剛匯出的binlog 導入

因為剛在使用binlog匯出時，只有針對binlogtest這個DB匯出，所以匯入時不用再指定DB

mysql -uroot -p  < binlogtest.sql

#接著進入查看，數據都回來了

mysql> use binlogtest

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

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

| test1                |

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

1 row in set (0.00 sec)

mysql> select * from test1;

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

| id | name  | age  |

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

|  1 | user1 |   18 |

|  2 | user2 |   19 |

|  3 | user3 |   20 |

|  4 | user4 |   21 |

|  5 | user5 |   22 |

|  6 | user6 |   23 |

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

6 rows in set (0.00 sec)

同樣的，如果特定某個語句，應該都可以用這種方式復原，例如讓DROP database ...等

【MYSQL】查看binlog內容的方法

一般如果直接cat 或tail 會出現亂碼

[root@rosalie-mysql01 mysql]# cat mysql-bin.000015 |more

þbinSf¹\

--More--

test_table24iem8rUUx0aVBOYz0iLCJtYWMiOiIyM2Y3ODQwNzEzZGYwMjU5OTkwMTlmOGJiYjBlNjdkZmE1NWQ3YzVjYmZjYjk2ODliMWJhYmZkMTI0ZTkzODE5In0=GN6TmpVek5qTTBNMkUxTkE9PSIsInZhbHVlIjoiUUJQZmJocVwvMTB5VEl6YkVIcm9HcWVGb1wvM25IQnJiRmxnUGdFd1BQUUw1RmNqczVOZkhhV1RSR0F0eGpldnROIiwibWFj

IjoiN2FiODdmYWU1ZWQ1NmIxY2VjOTUwYjA4NmNkYjYyNTY5NmYzYzg4ZDI2YTY5NmQzN2YzNjRkMTM3YzViNjNkZSJ9

TmpVek5qTTBNMkUxTkE9PSIsInZhbHVlIjoiWjA1cjB3YVE4eXpndGdyODJhTjgxUzJqR01tcUZ4T1J3TWxMRytRZ1hsQT0iLCJtYWMiOiI1NDNhNTY3ODEzZjVmYzNiMzRiNTZ

方式一 ： 先導出成txt檔，然後即可直接查看

mysqlbinlog mysql-bin.000015 > /tmp/binlog-dump.txt

方式二：選取要查詢的某時段（如果不確定是哪一個檔案用*）

mysqlbinlog --start-datetime="2006-01-01 00:00:00" --stop-datetime="2019-05-08 00:00:00" /var/lib/mysql/mysql-bin.* > /tmp/mysql_restore.txt

方式三：直接查看不再導出

mysqlbinlog mysql-bin.000015 -vv |more

案例一：如果需要復原到某一個時間點（例如刪除了某筆資料，還原到刪除前 )，針對單一DB

查到刪除前位置是808，所以要復原 rookery這個DB，需先把BINLOG單一匯出，再將SQL導入到刪除前

 ，因為BINLOG是記錄所有DB，若不單一導出，這樣會連同其他的DB也一起導出 ，再匯入時就會造成資料重覆

mysqlbinlog –stop-position=808 /usr/local/mysql/data/mysql-bin.000015 --database=rookery > /tmp/4abc3.txt

案例二：取開啟到結束的時間 ，指定DB 

mysqlbinlog --start-datetime="2018-11-02 12:00:00" --stop-datetime="2018-11-02 12:10:00"　--database=goods　/usr/local/mysql/data/mysql-bin.000015 > /tmp/aaa.txt

案例三：查到DROP字段往後取200行

mysqlbinlog -vv mysql-bin.000015 | grep -A 200 "drop"

【LINUX】SHELL編程的awk指令用法

範例文件1 名稱:awktest 正常檢視文件內容如下

[root@rosalie ~]# cat awktest

name tel

tina 0911111111

nono 0922222222

ken 0933333333

piggy 09444444

讀取第一欄內容：awk '{print $1}' 文件名

讀取第二欄內容：awk '{print $2}' 文件名

[root@rosalie ~]# awk '{print $1}' awktest

name

tina

nono

ken

piggy

去掉表頭讀取第二欄內容：awk 'NR!=1{print $2}' 文件名

[root@rosalie ~]# awk 'NR!=1{print $2}' awktest

0911111111

0922222222

0933333333

09444444

只列出含有091111111的列：awk '$0~/0911111111/{print}' 文件名

[root@rosalie ~]# awk '$0 ~ /0911111111/{print}' awktest

tina 0911111111

列出不含ken的列但含表頭：awk '$1 !~/ken/' 文件名

[root@rosalie ~]# awk '$1 !~ /ken/' awktest

name tel

tina 0911111111

nono 0922222222

piggy 09444444

列出不含ken的列，且不含表頭：awk '$1 !~/ken/' 文件名｜grep -v "name"

[root@rosalie ~]# awk  '$1 !~ /ken/' awktest |grep -v "name"

tina 0911111111

nono 0922222222

piggy 09444444

​
範例文件2  名稱:passwd 文件內容如下

[root@rosalie ~]# cat /etc/passwd

nobody:x:99:99:Nobody:/:/sbin/nologin

vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin

saslauth:x:499:76:Saslauthd user:/var/empty/saslauth:/sbin/nologin

postfix:x:89:89::/var/spool/postfix:/sbin/nologin

sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin

ntp:x:38:38::/etc/ntp:/sbin/nologin

tcpdump:x:72:72::/:/sbin/nologin

mysql:x:500:500::/home/mysql:/bin/bash

rosalie:x:501:500::/home/rosalie:/bin/bash

取帳號欄位，以-F":"為區隔""內的符號可變，例如空白或是其他符號，此範例以:來分隔，
意思是取:之後的第一欄(第一種作法)：awk -F":" '{print $1}'

[root@rosalie ~]# awk -F":" '{print $1}' /etc/passwd

nobody

vcsa

saslauth

postfix

sshd

ntp

tcpdump

mysql

rosalie

以":"為區隔取第一欄f1(第二種作法)：cut -d ":" -f1 

[root@rosalie ~]# cut -d ":" -f1 /etc/passwd

nobody

vcsa

saslauth

postfix

sshd

ntp

tcpdump

mysql

rosalie

在取得的欄位上填加字串"NAME "： awk -F ":" '{print "NAME " $1}' 

 [root@rosalie ~]# awk -F ":" '{print "NAME " $1}' /etc/passwd

NAME nobody

NAME vcsa

NAME saslauth

NAME postfix

NAME sshd

NAME ntp

NAME tcpdump

NAME mysql

NAME rosalie

取第及四及三欄：awk -F ":" '{print $4,$3}'  

[root@rosalie ~]# awk -F ":" '{print $4,$3}' /etc/passwd

99 99

69 69

76 499

89 89

74 74

38 38

72 72

500 500

500 501

取最後一欄：awk -F":" '{print $(NF)}'

[root@rosalie ~]# awk -F ":" '{print $(NF)}' /etc/passwd

/sbin/nologin

/sbin/nologin

/sbin/nologin

/sbin/nologin

/sbin/nologin

/sbin/nologin

/sbin/nologin

/bin/bash

/bin/bash

【LINUX】SHELL編程的sed說明及範例

SED

選項與參數：

-n：打印。在一般sed的用法中，所有來自STDIN的數據一般都會被列出到終端上。但如果加上-n參數後，則只有經過sed特殊處理的那一行(或者動作)才會被列出來。

-e：直接在命令列模式上進行sed 的動作編輯；

-f：直接將sed的動作寫在一個文件內，-f 則可以運行filename內的sed 動作

-r：sed 的動作支持的是延伸型正規表示法的語法。(默認是基礎正規表示法語法)

-i：直接修改讀取的文件內容，而不是輸出到終端。

某文件sed.txt內容如下，共三行

my name is rosalie

hello

hihi

參數範例

[root@rosalie shell]# sed -n "/rosalie/p" sed.txt

my name is rosalie

說明：-n打印要搭配p打印文件sed.txt內的，有rosalie的行

[root@rosalie shell]# sed -n "2p" sed.txt

hello

說明：打印，1p第一行、2p即第二行以此類推

[root@rosalie shell]# sed -n "1,3p" sed.txt

my name is rosalie

hello

hihi

說明：打印，1到3行,即1,3p

功能： s：取代，可以直接進行取代的工作哩！通常這個s的動作可以搭配正規表示法！例如s/old/new/g就是啦！

i：插入，i的後面可以接字串，而這些字串會在新的一行出現(目前的上一行)

a：新增，a的後面可以接字串，而這些字串會在新的一行出現(目前的下一行)

c：取代，c的後面可以接字串，這些字串可以取代n1,n2之間的行！

d：刪除，因為是刪除啊，所以d後面通常不接任何咚咚

p：列印，亦即將某個選擇的數據印出。通常p會與參數sed -n 一起運行～

範例：

[root@rosalie shell]# echo "my name is rosalie" | sed 's/^/& /g'

 my name is rosalie

說明：s/舊/新/g 取代 /^空白 /&添加  後面空一格,指加入空字串-(指在在my name is rosalie前加一個空格)

[root@rosalie shell]# echo "my name is rosalie" | sed '/rosalie/i######'

#######

my name is rosalie

說明：/i在目前的上一行插入-(指在字串rosalie的上一行插入#######)

[root@rosalie shell]# echo "my name is rosalie" | sed '/rosalie/a@@@@@@'

my name is rosalie

@@@@@@@

說明：/a在目前的下一行插入-(指在字串rosalie的上一行插入@@@@@@@)

其他範例:(需求是把文件內192字段全部更改為162)

文件test.txt內容如下：

[root@rosalie ~]# cat test.txt
192.168.1.1

192.168.1.2

192.168.2.22

若LINUX下用VI編輯修改的話 

[root@rosalie ~]# vi test.txt

192.168.1.1

192.168.1.2

192.168.2.22

:%s/168/162/          (按下ENTER)

[root@rosalie-mysql01 ~]# cat test.txt  (168都會變成162)

sed命令修改替換

sed -i 's/168/162/g' test.txt

也可以達到修改的效果

其他範例：

文件內容排序不一，想所有的字串以空格分開一行一行呈現

[root@rosalie shell]# cat sed.txt

my name is rosalie

hello

hihi  khkgs ojiowjoi kgjksss

aaghks kgjhks kkkll

gggg

sgsgs

hgsk

[root@rosalie-mysql01 shell]# cat sed.txt | sed "s/ /\n/g" |grep -v "^$"|sort

aaghks

gggg

hello

hgsk

hihi

is

kgjhks

kgjksss

khkgs

kkkll

my

name

ojiowjoi

rosalie

sgsgs

說明：例如s取代功能"s/ /\n/g" 黃色是搜尋到的字串即空白，取代\n紅色表示這是指換行的意思，grep -v "^$"是避免文件內還有空白符號，故篩選掉空白，sort是用來排序的(在sort後加 -nr則反排)

[root@rosalie shell]# cat sed.txt | sed "s/ /\n/g" |grep -v "^$"| sort | sed -n '1p;$p'

aaghks

sgsgs

說明：打印第一及$表示最後一列