无码视频app,三级理论视频,日韩无码精品电影,日本内射视频在线观看,一级片黄色电影免费,91精品国产综合久久蜜芽的特点,久久久人人少妇,三级成人网

公眾號關(guān)注“杰哥的IT之旅”，

選擇“星標(biāo)”，重磅干貨，第一時間送達(dá)！

MySQL 8正式版8.0.11已發(fā)布，官方表示MySQL8要比MySQL 5.7快2倍，還帶來了大量的改進(jìn)和更快的性能！

Mysql8.0安裝 (YUM方式)

首先刪除系統(tǒng)默認(rèn)或之前可能安裝的其他版本的mysql

[root@DB-node01?~]#?for?i?in?$(rpm?-qa|grep?mysql);do?rpm?-e?$i?--nodeps;done
[root@DB-node01?~]#?rm?-rf?/var/lib/mysql?&&?rm?-rf?/etc/my.cnf

安裝Mysql8.0 的yum資源庫

mysql80-community-release-el7-1.noarch.rpm????
?
[root@DB-node01?~]#?yum?localinstall?https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

安裝Mysql8.0

[root@DB-node01?~]#?yum?install?mysql-community-server
???
#啟動MySQL服務(wù)器和MySQL的自動啟動
[root@DB-node01?~]#?systemctl?start?mysqld
[root@DB-node01?~]#?systemctl?enable?mysqld

使用默認(rèn)密碼初次登錄后, 必須要重置密碼

查看默認(rèn)密碼,?如下默認(rèn)密碼為"e53xDalx.*dE"
[root@DB-node01?~]#?grep?'temporary?password'?/var/log/mysqld.log
2019-03-06T01:53:19.897262Z?5?[Note]?[MY-010454]?[Server]?A?temporary?password?is?generated?for?root@localhost:?e53xDalx.*dE
??
[root@DB-node01?~]#?mysql?-pe53xDalx.*dE
............
mysql>?select?version();
ERROR?1820?(HY000):?You?must?reset?your?password?using?ALTER?USER?statement?before?executing?this?statement.

報錯提示必須要重置初始密碼, 下面開始重置mysql登錄密碼（注意要切換到mysql數(shù)據(jù)庫，使用use mysql）

mysql>?use?mysql;
ERROR?1820?(HY000):?You?must?reset?your?password?using?ALTER?USER?statement?before?executing?this?statement.
???
mysql>?ALTER?USER?'root'@'localhost'?IDENTIFIED?BY?'123456';
ERROR?1819?(HY000):?Your?password?does?not?satisfy?the?current?policy?requirements

這個其實與validate_password_policy的值有關(guān), mysql8.0更改了validate_password_policy相關(guān)的配置名稱, 這跟Mysql5.7有點不一樣了.

mysql>?set?global?validate_password.policy=0;
Query?OK,?0?rows?affected?(0.00?sec)
???
mysql>?set?global?validate_password.length=1;
Query?OK,?0?rows?affected?(0.00?sec)

接著再修改密碼

mysql>?ALTER?USER?'root'@'localhost'?IDENTIFIED?BY?'123456';
Query?OK,?0?rows?affected?(0.05?sec)
???
mysql>?flush?privileges;
Query?OK,?0?rows?affected?(0.03?sec)

退出, 重新使用新密碼登錄mysql

[root@DB-node01?~]#?mysql?-p123456
...........
mysql>?select?version();
+-----------+
|?version()?|
+-----------+
|?8.0.15????|
+-----------+
1?row?in?set?(0.00?sec)

查看服務(wù)端口

mysql>?show?global?variables?like?'port';
+---------------+-------+
|?Variable_name?|?Value?|
+---------------+-------+
|?port??????????|?3306??|
+---------------+-------+
1?row?in?set?(0.01?sec)

查看mysql連接的授權(quán)信息

mysql>?select?host,user,password?from?mysql.user;
ERROR?1054?(42S22):?Unknown?column?'password'?in?'field?list'

上面這是mysql5.6及以下版本的查看命令, mysql5.7之后的數(shù)據(jù)庫里mysql.user表里已經(jīng)沒有password這個字段了，password字段改成了authentication_string。

mysql>?select?host,user,authentication_string?from?mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
|?host??????|?user?????????????|?authentication_string??????????????????????????????????????????????????|
+-----------+------------------+------------------------------------------------------------------------+
|?localhost?|?mysql.infoschema?|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.session????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.sys????????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?root?????????????|?$A$005${7J0=4Dc7Jym8eI/FU4jimKWFvkD9XmoAkF1ca5.Un0bc6zgmPtU.0??????????|
+-----------+------------------+------------------------------------------------------------------------+
4?rows?in?set?(0.00?sec)

mysql8.0修改用戶密碼命令

mysql>?use?mysql;
mysql>?ALTER?USER?'root'@'localhost'?IDENTIFIED?BY?'123456';
mysql>?flush?privileges;

Mysql8.0安裝 (二進(jìn)制方式)

首先刪除系統(tǒng)默認(rèn)或之前可能安裝的其他版本的mysql

[root@mysql8-node?~]#?for?i?in?$(rpm?-qa|grep?mysql);do?rpm?-e?$i?--nodeps;done
[root@mysql8-node?~]#?rm?-rf?/var/lib/mysql?&&?rm?-rf?/etc/my.cnf

安裝需要的軟件包

[root@mysql8-node?~]#?yum?-y?install?libaio
[root@mysql8-node?~]#?yum?-y?install?net-tools

下載并安裝Mysql8.0.12

[root@mysql8-node?~]#?groupadd?mysql
[root@mysql8-node?~]#?useradd?-g?mysql?mysql
?
[root@mysql8-node?~]#?cd?/usr/local/src/
[root@mysql-node?src]#?ll
-rw-r--r--?1?root?root?620389228?Aug?22??2018?mysql8.0.12_bin_centos7.tar.gz
[root@mysql-node?src]#?tar?-zvxf?mysql8.0.12_bin_centos7.tar.gz
[root@mysql-node?src]#?mv?mysql?/usr/local/
[root@mysql-node?src]#?chown?-R?mysql.mysql?/usr/local/mysql
?
[root@mysql-node?src]#?vim?/home/mysql/.bash_profile
export?PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
[root@mysql-node?src]#?source?/home/mysql/.bash_profile
[root@mysql-node?src]#?echo?"PATH=$PATH:/usr/local/mysql/bin"?>>?/etc/profile
[root@mysql-node?src]#?source?/etc/profile

創(chuàng)建數(shù)據(jù)目錄

[root@mysql-node?src]#?mkdir?-p?/data/mysql/{data,log,binlog,conf,tmp}?????????
[root@mysql-node?src]#?chown?-R?mysql.mysql?/data/mysql

配置mysql

[root@mysql-node?src]#?su?-?mysql
[mysql@mysql-node?~]$?vim?/data/mysql/conf/my.cnf
[mysqld]
lower_case_table_names??????????=?1
user????????????????????????????=?mysql
server_id???????????????????????=?1
port????????????????????????????=?3306
?
default-time-zone?=?'+08:00'
enforce_gtid_consistency????????=?ON
gtid_mode???????????????????????=?ON
binlog_checksum?????????????????=?none
default_authentication_plugin???=?mysql_native_password
datadir?????????????????????????=?/data/mysql/data
pid-file????????????????????????=?/data/mysql/tmp/mysqld.pid
socket??????????????????????????=?/data/mysql/tmp/mysqld.sock
tmpdir??????????????????????????=?/data/mysql/tmp/
skip-name-resolve???????????????=?ON
open_files_limit????????????????=?65535
table_open_cache????????????????=?2000
?
#################innodb########################
innodb_data_home_dir????????????=?/data/mysql/data
innodb_data_file_path???????????=?ibdata1:512M;ibdata2:512M:autoextend
innodb_buffer_pool_size?=?12000M
innodb_flush_log_at_trx_commit?=?1
innodb_io_capacity?=?600
innodb_lock_wait_timeout?=?120
innodb_log_buffer_size?=?8M
innodb_log_file_size?=?200M
innodb_log_files_in_group?=?3
innodb_max_dirty_pages_pct?=?85
innodb_read_io_threads?=?8
innodb_write_io_threads?=?8
innodb_thread_concurrency?=?32
innodb_file_per_table
innodb_rollback_on_timeout
?
innodb_undo_directory???????????=?/data/mysql/data
innodb_log_group_home_dir???????=?/data/mysql/data
?
###################session###########################
join_buffer_size?=?8M
key_buffer_size?=?256M
bulk_insert_buffer_size?=?8M
max_heap_table_size?=?96M
tmp_table_size?=?96M
read_buffer_size?=?8M
sort_buffer_size?=?2M
max_allowed_packet?=?64M
read_rnd_buffer_size?=?32M
?
############log?set###################
log-error???????????????????????=?/data/mysql/log/mysqld.err
log-bin?????????????????????????=?/data/mysql/binlog/binlog
log_bin_index???????????????????=?/data/mysql/binlog/binlog.index
max_binlog_size?????????????????=?500M
slow_query_log_file?????????????=?/data/mysql/log/slow.log
slow_query_log??????????????????=?1
long_query_time?????????????????=?10
log_queries_not_using_indexes???=?ON
log_throttle_queries_not_using_indexes??=?10
log_slow_admin_statements???????=?ON
log_output??????????????????????=?FILE,TABLE
master_info_file????????????????=?/data/mysql/binlog/master.info

初始化 (稍等一會兒, 可以到/data/mysql/log/mysqld.err日子里查看初始化過程, 看看有沒有error信息)

[mysql@mysql-node?~]$?mysqld?--defaults-file=/data/mysql/conf/my.cnf??--initialize-insecure??--user=mysql?

啟動mysqld

[mysql@mysql-node?~]$?mysqld_safe?--defaults-file=/data/mysql/conf/my.cnf?&?
[mysql@mysql-node?~]$?lsof?-i:3306
COMMAND???PID??USER???FD???TYPE???DEVICE?SIZE/OFF?NODE?NAME
mysqld??24743?mysql???23u??IPv6?23132988??????0t0??TCP?*:mysql?(LISTEN)

登錄mysql, 重置密碼

本地首次使用sock文件登錄mysql是不需要密碼的
[mysql@mysql-node?~]#?mysql?-S?/data/mysql/tmp/mysqld.sock
.............
mysql>?ALTER?USER?'root'@'localhost'?IDENTIFIED?BY?'123456';
Query?OK,?0?rows?affected?(0.07?sec)
?
mysql>?flush?privileges;
Query?OK,?0?rows?affected?(0.03?sec)
?
mysql>?select?host,user,authentication_string?from?mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
|?host??????|?user?????????????|?authentication_string??????????????????????????????????????????????????|
+-----------+------------------+------------------------------------------------------------------------+
|?localhost?|?mysql.infoschema?|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.session????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.sys????????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?root?????????????|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9??????????????????????????????|
+-----------+------------------+------------------------------------------------------------------------+
4?rows?in?set?(0.00?sec)

退出, 此時密碼重置后, 就不能使用sock文件無密碼登錄了

[root@mysql-node?~]#?mysql?-S?/data/mysql/tmp/mysqld.sock
ERROR?1045?(28000):?Access?denied?for?user?'root'@'localhost'?(using?password:?NO)
?
[root@mysql-node?~]#?mysql?-p123456
mysql:?[Warning]?Using?a?password?on?the?command?line?interface?can?be?insecure.
ERROR?2002?(HY000):?Can't?connect?to?local?MySQL?server?through?socket?'/tmp/mysql.sock'?(2)

做sock文件的軟鏈接

[root@mysql-node?~]#?ln?-s?/data/mysql/tmp/mysqld.sock?/tmp/mysql.sock

登錄

[root@mysql-node?~]#?mysql?-p123456
或者
[root@mysql-node?~]#?mysql?-uroot?-S?/data/mysql/tmp/mysqld.sock?-p123456
.............
mysql>?select?version();
+-----------+
|?version()?|
+-----------+
|?8.0.12????|
+-----------+
1?row?in?set?(0.00?sec)
?
#授予用戶權(quán)限.?必須先要創(chuàng)建用戶,?才能授權(quán)!!
(創(chuàng)建用戶時要帶@并指定地址,?則grant授權(quán)時的地址就是這個@后面指定的!,?否則grant授權(quán)就會報錯!)
mysql>?create?user?'kevin'@'%'?identified?by?'123456';
Query?OK,?0?rows?affected?(0.11?sec)
?
mysql>?grant?all?privileges?on?*.*?to?'kevin'@'%'?with?grant?option;?
Query?OK,?0?rows?affected?(0.21?sec)
?
mysql>?select?host,user,authentication_string?from?mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
|?host??????|?user?????????????|?authentication_string??????????????????????????????????????????????????|
+-----------+------------------+------------------------------------------------------------------------+
|?%?????????|?kevin????????????|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9??????????????????????????????|
|?localhost?|?mysql.infoschema?|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.session????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.sys????????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?root?????????????|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9??????????????????????????????|
+-----------+------------------+------------------------------------------------------------------------+
5?rows?in?set?(0.00?sec)
?
mysql>?update?mysql.user?set?host='172.16.60.%'?where?user="kevin";
Query?OK,?1?row?affected?(0.16?sec)
Rows?matched:?1??Changed:?1??Warnings:?0
?
mysql>?flush?privileges;
Query?OK,?0?rows?affected?(0.05?sec)
?
mysql>?select?host,user,authentication_string?from?mysql.user;
+-------------+------------------+------------------------------------------------------------------------+
|?host????????|?user?????????????|?authentication_string??????????????????????????????????????????????????|
+-------------+------------------+------------------------------------------------------------------------+
|?172.16.60.%?|?kevin????????????|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9??????????????????????????????|
|?localhost???|?mysql.infoschema?|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost???|?mysql.session????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost???|?mysql.sys????????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost???|?root?????????????|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9??????????????????????????????|
+-------------+------------------+------------------------------------------------------------------------+
5?rows?in?set?(0.00?sec)
?
mysql>??create?user?'bobo'@'172.16.60.%'?identified?by?'123456';??????
Query?OK,?0?rows?affected?(0.09?sec)
?
mysql>?grant?all?privileges?on?*.*?to?'bobo'@'172.16.60.%';?????
Query?OK,?0?rows?affected?(0.17?sec)
?
mysql>?flush?privileges;
Query?OK,?0?rows?affected?(0.04?sec)
?
mysql>?select?host,user,authentication_string?from?mysql.user;
+-------------+------------------+------------------------------------------------------------------------+
|?host????????|?user?????????????|?authentication_string??????????????????????????????????????????????????|
+-------------+------------------+------------------------------------------------------------------------+
|?172.16.60.%?|?bobo?????????????|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9??????????????????????????????|
|?172.16.60.%?|?kevin????????????|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9??????????????????????????????|
|?localhost???|?mysql.infoschema?|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost???|?mysql.session????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost???|?mysql.sys????????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost???|?root?????????????|?*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9??????????????????????????????|
+-------------+------------------+------------------------------------------------------------------------+
6?rows?in?set?(0.00?sec)
?
mysql>?show?grants?for?kevin@'172.16.60.%';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|?Grants?for[email protected].%????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|?GRANT?SELECT,?INSERT,?UPDATE,?DELETE,?CREATE,?DROP,?RELOAD,?SHUTDOWN,?PROCESS,?FILE,?REFERENCES,?INDEX,?ALTER,?SHOW?DATABASES,?SUPER,?CREATE?TEMPORARY?TABLES,?LOCK?TABLES,?EXECUTE,?REPLICATION?SLAVE,?REPLICATION?CLIENT,?CREATE?VIEW,?SHOW?VIEW,?CREATE?ROUTINE,?ALTER?ROUTINE,?CREATE?USER,?EVENT,?TRIGGER,?CREATE?TABLESPACE,?CREATE?ROLE,?DROP?ROLE?ON?*.*?TO?`kevin`@`172.16.60.%`?WITH?GRANT?OPTION?|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1?row?in?set?(0.00?sec)

MySQL單機(jī)多實例安裝配置

通過上面二進(jìn)制部署可知, 已經(jīng)起來一個3306端口的MySQL實例, 現(xiàn)在需要再起來兩個實例, 分別為3307, 3308. 操作如下:

創(chuàng)建實例的數(shù)據(jù)目錄

[root@mysql-node?~]#?mkdir?-p?/data/mysql3307/{data,log,binlog,conf,tmp}??????
[root@mysql-node?~]#?mkdir?-p?/data/mysql3308/{data,log,binlog,conf,tmp}?????
[root@mysql-node?~]#?chown?-R?mysql.mysql?/data/mysql3307
[root@mysql-node?~]#?chown?-R?mysql.mysql?/data/mysql3308

配置mysql

[root@mysql-node?~]#?cp?-r?/data/mysql/conf/my.cnf?/data/mysql3307/conf/
[root@mysql-node?~]#?cp?-r?/data/mysql/conf/my.cnf?/data/mysql3308/conf/
[root@mysql-node?~]#?sed?-i?'s#/data/mysql/#/data/mysql3307/#g'?/data/mysql3307/conf/my.cnf
[root@mysql-node?~]#?sed?-i?'s#/data/mysql/#/data/mysql3308/#g'?/data/mysql3308/conf/my.cnf
[root@mysql-node?~]#?sed?-i?'s/3306/3307/g'?/data/mysql3307/conf/my.cnf
[root@mysql-node?~]#?sed?-i?'s/3306/3308/g'?/data/mysql3308/conf/my.cnf??
[root@mysql-node?~]#?chown?-R?mysql.mysql?/data/mysql*

進(jìn)行初始化兩個實例

[root@mysql-node?~]#?mysqld?--defaults-file=/data/mysql3307/conf/my.cnf??--initialize-insecure??--user=mysql?
[root@mysql-node?~]#?mysqld?--defaults-file=/data/mysql3308/conf/my.cnf??--initialize-insecure??--user=mysql

接著啟動mysqld

[root@mysql-node?~]#?mysqld_safe?--defaults-file=/data/mysql3307/conf/my.cnf?&
[root@mysql-node?~]#?mysqld_safe?--defaults-file=/data/mysql3308/conf/my.cnf?&

查看啟動是否成功

[root@mysql-node?~]#?ps?-ef|grep?mysql
mysql????23996?????1??0?14:37??????????00:00:00?/bin/sh?/usr/local/mysql/bin/mysqld_safe?--defaults-file=/data/mysql/conf/my.cnf
mysql????24743?23996??0?14:38??????????00:00:17?/usr/local/mysql/bin/mysqld?--defaults-file=/data/mysql/conf/my.cnf?--basedir=/usr/local/mysql?--datadir=/data/mysql/data?--plugin-dir=/usr/local/mysql/lib/plugin?--log-error=/data/mysql/log/mysqld.err?--open-files-limit=65535?--pid-file=/data/mysql/tmp/mysqld.pid?--socket=/data/mysql/tmp/mysqld.sock?--port=3306
root?????30473?23727??0?15:33?pts/0????00:00:00?/bin/sh?/usr/local/mysql/bin/mysqld_safe?--defaults-file=/data/mysql3307/conf/my.cnf
mysql????31191?30473?17?15:33?pts/0????00:00:02?/usr/local/mysql/bin/mysqld?--defaults-file=/data/mysql3307/conf/my.cnf?--basedir=/usr/local/mysql?--datadir=/data/mysql3307/data?--plugin-dir=/usr/local/mysql/lib/plugin?--user=mysql?--log-error=/data/mysql3307/log/mysqld.err?--open-files-limit=65535?--pid-file=/data/mysql3307/tmp/mysqld.pid?--socket=/data/mysql3307/tmp/mysqld.sock?--port=3307
root?????31254?23727??0?15:33?pts/0????00:00:00?/bin/sh?/usr/local/mysql/bin/mysqld_safe?--defaults-file=/data/mysql3308/conf/my.cnf
mysql????31977?31254?39?15:33?pts/0????00:00:02?/usr/local/mysql/bin/mysqld?--defaults-file=/data/mysql3308/conf/my.cnf?--basedir=/usr/local/mysql?--datadir=/data/mysql3308/data?--plugin-dir=/usr/local/mysql/lib/plugin?--user=mysql?--log-error=/data/mysql3308/log/mysqld.err?--open-files-limit=65535?--pid-file=/data/mysql3308/tmp/mysqld.pid?--socket=/data/mysql3308/tmp/mysqld.sock?--port=3308
root?????32044?23727??0?15:34?pts/0????00:00:00?grep?--color=auto?mysql
?
[root@mysql-node?~]#?lsof?-i:3307
COMMAND???PID??USER???FD???TYPE???DEVICE?SIZE/OFF?NODE?NAME
mysqld??31191?mysql???22u??IPv6?23144844??????0t0??TCP?*:opsession-prxy?(LISTEN)
[root@mysql-node?~]#?lsof?-i:3308
COMMAND???PID??USER???FD???TYPE???DEVICE?SIZE/OFF?NODE?NAME
mysqld??31977?mysql???22u??IPv6?23145727??????0t0??TCP?*:tns-server?(LISTEN)
[root@mysql-node?~]#?lsof?-i:3306
COMMAND???PID??USER???FD???TYPE???DEVICE?SIZE/OFF?NODE?NAME
mysqld??24743?mysql???23u??IPv6?23132988??????0t0??TCP?*:mysql?(LISTEN)

登錄3307端口實例, 并設(shè)置密碼

[root@mysql-node?~]#?mysql?-S?/data/mysql3307/tmp/mysqld.sock
............
mysql>?ALTER?USER?'root'@'localhost'?IDENTIFIED?BY?'123456';
Query?OK,?0?rows?affected?(0.11?sec)
?
mysql>?flush?privileges;
Query?OK,?0?rows?affected?(0.11?sec)

退出, 使用新密碼登錄

[root@mysql-node?~]#?mysql?-uroot?-S?/data/mysql3307/tmp/mysqld.sock?-p123456???
.............
mysql>

同理, 登錄3308端口實例, 并設(shè)置密碼

[root@mysql-node?~]#?mysql?-S?/data/mysql3308/tmp/mysqld.sock
...........
mysql>?ALTER?USER?'root'@'localhost'?IDENTIFIED?BY?'123456';
Query?OK,?0?rows?affected?(0.13?sec)
?
mysql>?flush?privileges;
Query?OK,?0?rows?affected?(0.03?sec)

退出, 使用新密碼登錄

[root@mysql-node?~]#?mysql?-uroot?-S?/data/mysql3308/tmp/mysqld.sock?-p123456
....................
mysql>

3306, 3307, 3308三個端口實例的啟動命令分別為:

mysqld_safe?--defaults-file=/data/mysql/conf/my.cnf?&
mysqld_safe?--defaults-file=/data/mysql3307/conf/my.cnf?&
mysqld_safe?--defaults-file=/data/mysql3308/conf/my.cnf?&

登錄命令分別為:

mysql?-uroot?-S?/data/mysql/tmp/mysqld.sock?-p123456
mysql?-uroot?-S?/data/mysql3307/tmp/mysqld.sock?-p123456
mysql?-uroot?-S?/data/mysql3308/tmp/mysqld.sock?-p123456

不過為了解決大家平時重復(fù)安裝的問題，特意將多實例安裝方法編輯成腳本了，有需要的讀者可以在本公眾號后臺直接回復(fù) MySQL8 獲取多實例安裝腳本。

Mysql8.0使用過程中踩過的一些坑

1)創(chuàng)建用戶和授權(quán) 在mysql8.0創(chuàng)建用戶和授權(quán)和之前不太一樣了，其實嚴(yán)格上來講，也不能說是不一樣, 只能說是更嚴(yán)格, mysql8.0需要先創(chuàng)建用戶(創(chuàng)建用戶時要帶@并指定地址, 則grant授權(quán)時的地址就是這個@后面指定的!, 否則grant授權(quán)就會報錯!)和設(shè)置密碼,然后才能授權(quán)。

mysql>?create?user?'kevin'@'%'?identified?by?'123456';
Query?OK,?0?rows?affected?(0.04?sec)
?
mysql>?grant?all?privileges?on?*.*?to?'kevin'@'%'?with?grant?option;???
Query?OK,?0?rows?affected?(0.04?sec)
?
mysql>?create?user?'bobo'@'%'?identified?by?'123456';????
Query?OK,?0?rows?affected?(0.06?sec)
?
mysql>?grant?all?privileges?on?*.*?to?'bobo'@'%'?with?grant?option;
Query?OK,?0?rows?affected?(0.03?sec)
?
mysql>?flush?privileges;
Query?OK,?0?rows?affected?(0.04?sec)
?
mysql>?select?host,user,authentication_string?from?mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
|?host??????|?user?????????????|?authentication_string??????????????????????????????????????????????????|
+-----------+------------------+------------------------------------------------------------------------+
|?%?????????|?bobo?????????????|?$A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1??|
|?%?????????|?kevin????????????|?$A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85???????|
|?localhost?|?mysql.infoschema?|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.session????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.sys????????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?root?????????????|?$A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2???????|
+-----------+------------------+------------------------------------------------------------------------+

如果還是用Mysql5.7及之前版本的直接授權(quán)的方法, 會有報錯:

mysql>?grant?all?privileges?on?*.*?to?'shibo'@'%'?identified?by?'123456';
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?'123456''?at?line?1

2)Mysql8.0默認(rèn)是不能使用root賬號進(jìn)行遠(yuǎn)程登錄的! root賬號只能本地登錄!

mysql>?select?host,user,authentication_string?from?mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
|?host??????|?user?????????????|?authentication_string??????????????????????????????????????????????????|
+-----------+------------------+------------------------------------------------------------------------+
|?%?????????|?bobo?????????????|?$A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1?|
|?%?????????|?kevin????????????|?$A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85?|
|?localhost?|?mysql.infoschema?|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.session????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.sys????????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?root?????????????|?$A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2?|
+-----------+------------------+------------------------------------------------------------------------+
6?rows?in?set?(0.00?sec)

如果想要遠(yuǎn)程登錄, 則需要進(jìn)行update更新下root賬號的權(quán)限

mysql>?update?mysql.user?set?host='%'?where?user="root";
Query?OK,?1?row?affected?(0.10?sec)
Rows?matched:?1??Changed:?1??Warnings:?0
?
mysql>?flush?privileges;
Query?OK,?0?rows?affected?(0.14?sec)
?
mysql>?select?host,user,authentication_string?from?mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
|?host??????|?user?????????????|?authentication_string??????????????????????????????????????????????????|
+-----------+------------------+------------------------------------------------------------------------+
|?%?????????|?bobo?????????????|?$A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1??|
|?%?????????|?kevin????????????|?$A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85???????|
|?%?????????|?root?????????????|?$A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2???????|
|?localhost?|?mysql.infoschema?|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.session????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
|?localhost?|?mysql.sys????????|?$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED?|
+-----------+------------------+------------------------------------------------------------------------+
6?rows?in?set?(0.00?sec)

這樣就能在遠(yuǎn)程使用root賬號登錄該mysql8.0的數(shù)據(jù)庫了

修改root賬號權(quán)限, 允許root賬號遠(yuǎn)程登錄后, 用navicat進(jìn)行mysql的遠(yuǎn)程連接時，出現(xiàn)了彈窗報錯：出現(xiàn)這個原因是mysql8 之前的版本中加密規(guī)則是mysql_native_password, 而在mysql8之后,加密規(guī)則是caching_sha2_password, 解決問題方法有兩種:

一種是升級navicat驅(qū)動;

一種是把mysql用戶登錄密碼加密規(guī)則還原成mysql_native_password; 這里選擇第二種方法來解決:

#修改加密規(guī)則
mysql>?ALTER?USER?'root'@'%'?IDENTIFIED?BY?'123456'?PASSWORD?EXPIRE?NEVER;???????
Query?OK,?0?rows?affected?(0.16?sec)
?
#更新一下用戶的密碼
mysql>?ALTER?USER?'root'@'%'?IDENTIFIED?WITH?mysql_native_password?BY?'123456';
Query?OK,?0?rows?affected?(0.08?sec)
?
#刷新權(quán)限
mysql>?FLUSH?PRIVILEGES;
Query?OK,?0?rows?affected?(0.03?sec)

這樣問題就解決了。

1、使用sqlyog鏈接時會出現(xiàn)2058的異常，此時我們需要修改mysql，命令行登錄mysql（與修改密碼中登錄相同，使用修改后的密碼），然后執(zhí)行下面的命令：mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; 其中password為自己修改的密碼。然后SQLyog中重新連接，則可連接成功，OK。
2、如果報錯：ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'則使用下面命令：mysql > ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

sqlyog鏈接時出現(xiàn)2058異常

修改默認(rèn)編碼方式 mysql8.0默認(rèn)編碼方式為utf8mb4，因此使用時不需要修改，可使用如下命令查看：

mysql?>?SHOW?VARIABLES?WHERE?Variable_name?LIKE?'character_set_%'?OR?Variable_name?LIKE?'collation%';

如果需要修改其他編碼方式，比如需要修改為utf8mb4，可以使用如下方式：

修改mysql配置文件my.cnf, 找到后請在以下三部分里添加如下內(nèi)容：
[client]
default-character-set?=?utf8mb4
[mysql]
default-character-set?=?utf8mb4
[mysqld]
character-set-client-handshake?=?FALSE
character-set-server?=?utf8mb4
collation-server?=?utf8mb4_unicode_ci
init_connect='SET?NAMES?utf8mb4'

然后重啟mysqld服務(wù)即可, 其中:

character_set_client??(客戶端來源數(shù)據(jù)使用的字符集)
character_set_connection?????(連接層字符集)
character_set_database???(當(dāng)前選中數(shù)據(jù)庫的默認(rèn)字符集)
character_set_results?(查詢結(jié)果字符集)
character_set_server?(默認(rèn)的內(nèi)部操作字符集)

數(shù)據(jù)庫連接參數(shù)中:

characterEncoding=utf8 會被自動識別為utf8mb4，也可以不加這個參數(shù)，會自動檢測。
而autoReconnect=true?是必須加上的。

6)部分參數(shù)配置查詢命令

#查詢mysql最大連接數(shù)設(shè)置
mysql>?show?global?variables?like?'max_conn%';
mysql>?SELECT?@@MAX_CONNECTIONS?AS?'Max?Connections';
?
#?查看最大鏈接數(shù)
mysql>?show?global?status?like?'Max_used_connections';
?
#?查看慢查詢?nèi)罩臼欠耖_啟以及日志位置
mysql>?show?variables?like?'slow_query%';
?
#?查看慢查詢?nèi)罩境瑫r記錄時間
mysql>?show?variables?like?'long_query_time';
?
#?查看鏈接創(chuàng)建以及現(xiàn)在正在鏈接數(shù)
mysql>?show?status?like?'Threads%';
?
#?查看數(shù)據(jù)庫當(dāng)前鏈接
mysql>?show?processlist;

#?查看數(shù)據(jù)庫配置
mysql>?show?variables?like?'%quer%';?

作者：散盡浮華?
出處：https://www.cnblogs.com/kevingrace/p/10482469.html

如果您覺得這篇文章對您有點用的話，麻煩您為本文來個四連：轉(zhuǎn)發(fā)分享、點贊、點在看、留言，因為這將是我寫作與分享更多優(yōu)質(zhì)文章的最強動力！


本公眾號全部博文已整理成一個目錄，請在公眾號后臺回復(fù)「m」獲取！
推薦閱讀：
1、MySQL | MySQL 數(shù)據(jù)庫系統(tǒng)（一）
2、MySQL | MySQL 數(shù)據(jù)庫系統(tǒng)（二）- SQL語句的基本操作
3、MySQL | MySQL 數(shù)據(jù)庫系統(tǒng)（三）- 數(shù)據(jù)庫的用戶授權(quán)
4、MySQL | MySQL 數(shù)據(jù)庫系統(tǒng)（四）- 數(shù)據(jù)庫的備份與恢復(fù)
5、13000字！最常問的MySQL面試題集合
6、IT運維面試問題總結(jié)-數(shù)據(jù)庫、監(jiān)控、網(wǎng)絡(luò)管理（NoSQL、MongoDB、MySQL、Prometheus、Zabbix）
7、【建議收藏】寫給程序員的 MySQL 面試高頻 100 問！
8、MySQL 常用優(yōu)化指南，及大表優(yōu)化思路都在這了！
9、面試問爛的 MySQL 四種隔離級別，看完吊打面試官！
10、超全面的 MySQL 優(yōu)化面試解析
關(guān)注微信公眾號「杰哥的IT之旅」，后臺回復(fù)「1024」查看更多內(nèi)容，回復(fù)「加群」備注：地區(qū)-職業(yè)方向-昵稱?即可加入讀者交流群。
點個[在看]，是對杰哥最大的支持！

CentOS 下 MySQL 8.0 安裝部署！

選擇“星標(biāo)”，重磅干貨，第一時間送達(dá)！

Mysql8.0安裝 (YUM方式)

Mysql8.0安裝 (二進(jìn)制方式)

MySQL單機(jī)多實例安裝配置

Mysql8.0使用過程中踩過的一些坑