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