CentOS 下 MySQL 8.0 安裝部署，超詳細(xì)！
點(diǎn)擊上方“開(kāi)源Linux”，選擇“設(shè)為星標(biāo)”
回復(fù)“學(xué)習(xí)”獲取獨(dú)家整理的學(xué)習(xí)資料！
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資源庫(kù)
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
???
#啟動(dòng)MySQL服務(wù)器和MySQL的自動(dòng)啟動(dòng)
[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.
報(bào)錯(cuò)提示必須要重置初始密碼, 下面開(kāi)始重置mysql登錄密碼（注意要切換到mysql數(shù)據(jù)庫(kù)，使用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
這個(gè)其實(shí)與validate_password_policy的值有關(guān), mysql8.0更改了validate_password_policy相關(guān)的配置名稱, 這跟Mysql5.7有點(diǎn)不一樣了.
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ù)庫(kù)里mysql.user表里已經(jīng)沒(méi)有password這個(gè)字段了，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
初始化 (稍等一會(huì)兒, 可以到/data/mysql/log/mysqld.err日子里查看初始化過(guò)程, 看看有沒(méi)有error信息)
[mysql@mysql-node?~]$?mysqld?--defaults-file=/data/mysql/conf/my.cnf??--initialize-insecure??--user=mysql?
啟動(dòng)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)
退出, 此時(shí)密碼重置后, 就不能使用sock文件無(wú)密碼登錄了
[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)限.?必須先要?jiǎng)?chuàng)建用戶,?才能授權(quán)!!
(創(chuàng)建用戶時(shí)要帶@并指定地址,?則grant授權(quán)時(shí)的地址就是這個(gè)@后面指定的!,?否則grant授權(quán)就會(huì)報(bào)錯(cuò)!)
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ī)多實(shí)例安裝配置
通過(guò)上面二進(jìn)制部署可知, 已經(jīng)起來(lái)一個(gè)3306端口的MySQL實(shí)例, 現(xiàn)在需要再起來(lái)兩個(gè)實(shí)例, 分別為3307, 3308. 操作如下:
創(chuàng)建實(shí)例的數(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)行初始化兩個(gè)實(shí)例
[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
接著啟動(dòng)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?&
查看啟動(dòng)是否成功
[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í)例, 并設(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í)例, 并設(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三個(gè)端口實(shí)例的啟動(dòng)命令分別為:
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
不過(guò)為了解決大家平時(shí)重復(fù)安裝的問(wèn)題，特意將多實(shí)例安裝方法編輯成腳本了，有需要的讀者可以在本公眾號(hào)后臺(tái)直接回復(fù) MySQL8 獲取多實(shí)例安裝腳本。
Mysql8.0使用過(guò)程中踩過(guò)的一些坑
1)創(chuàng)建用戶和授權(quán) 在mysql8.0創(chuàng)建用戶和授權(quán)和之前不太一樣了，其實(shí)嚴(yán)格上來(lái)講，也不能說(shuō)是不一樣, 只能說(shuō)是更嚴(yán)格, mysql8.0需要先創(chuàng)建用戶(創(chuàng)建用戶時(shí)要帶@并指定地址, 則grant授權(quán)時(shí)的地址就是這個(gè)@后面指定的!, 否則grant授權(quán)就會(huì)報(bào)錯(cuò)!)和設(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)的方法, 會(huì)有報(bào)錯(cuò):
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賬號(hào)進(jìn)行遠(yuǎn)程登錄的! root賬號(hào)只能本地登錄!
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賬號(hào)的權(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賬號(hào)登錄該mysql8.0的數(shù)據(jù)庫(kù)了
修改root賬號(hào)權(quán)限, 允許root賬號(hào)遠(yuǎn)程登錄后, 用navicat進(jìn)行mysql的遠(yuǎn)程連接時(shí)，出現(xiàn)了彈窗報(bào)錯(cuò)： 
出現(xiàn)這個(gè)原因是mysql8 之前的版本中加密規(guī)則是mysql_native_password, 而在mysql8之后,加密規(guī)則是caching_sha2_password, 解決問(wèn)題方法有兩種:一種是升級(jí)navicat驅(qū)動(dòng); 一種是把mysql用戶登錄密碼加密規(guī)則還原成mysql_native_password; 這里選擇第二種方法來(lái)解決:
#修改加密規(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)
這樣問(wèn)題就解決了。
1、使用sqlyog鏈接時(shí)會(huì)出現(xiàn)2058的異常，此時(shí)我們需要修改mysql，命令行登錄mysql（與修改密碼中登錄相同，使用修改后的密碼），然后執(zhí)行下面的命令：mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; 其中password為自己修改的密碼。然后SQLyog中重新連接，則可連接成功，OK。 2、如果報(bào)錯(cuò)：ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'則使用下面命令：mysql > ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; sqlyog鏈接時(shí)出現(xiàn)2058異常 修改默認(rèn)編碼方式 mysql8.0默認(rèn)編碼方式為utf8mb4，因此使用時(shí)不需要修改，可使用如下命令查看：
mysql?>?SHOW?VARIABLES?WHERE?Variable_name?LIKE?'character_set_%'?OR?Variable_name?LIKE?'collation%';
如果需要修改其他編碼方式，比如需要修改為utf8mb4，可以使用如下方式：
修改mysql配置文件my.cnf, 找到后請(qǐng)?jiān)谝韵氯糠掷锾砑尤缦聝?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??(客戶端來(lái)源數(shù)據(jù)使用的字符集)
character_set_connection?????(連接層字符集)
character_set_database???(當(dāng)前選中數(shù)據(jù)庫(kù)的默認(rèn)字符集)
character_set_results?(查詢結(jié)果字符集)
character_set_server?(默認(rèn)的內(nèi)部操作字符集)
數(shù)據(jù)庫(kù)連接參數(shù)中:
characterEncoding=utf8 會(huì)被自動(dòng)識(shí)別為utf8mb4，也可以不加這個(gè)參數(shù)，會(huì)自動(dòng)檢測(cè)。
而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)罩臼欠耖_(kāi)啟以及日志位置
mysql>?show?variables?like?'slow_query%';
?
#?查看慢查詢?nèi)罩境瑫r(shí)記錄時(shí)間
mysql>?show?variables?like?'long_query_time';
?
#?查看鏈接創(chuàng)建以及現(xiàn)在正在鏈接數(shù)
mysql>?show?status?like?'Threads%';
?
#?查看數(shù)據(jù)庫(kù)當(dāng)前鏈接
mysql>?show?processlist;
#?查看數(shù)據(jù)庫(kù)配置
mysql>?show?variables?like?'%quer%';?
作者：散盡浮華?
出處：https://www.cnblogs.com/kevingrace/p/10482469.html
關(guān)注「開(kāi)源Linux」加星標(biāo)，提升IT技能
評(píng)論
圖片
表情