CentOS 下 MySQL 8.0 安裝部署！
公眾號關注“杰哥的IT之旅”，
選擇“星標”，重磅干貨，第一時間送達！
MySQL 8正式版8.0.11已發(fā)布，官方表示MySQL8要比MySQL 5.7快2倍，還帶來了大量的改進和更快的性能！
Mysql8.0安裝 (YUM方式)
首先刪除系統(tǒng)默認或之前可能安裝的其他版本的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服務器和MySQL的自動啟動
[root@DB-node01?~]#?systemctl?start?mysqld
[root@DB-node01?~]#?systemctl?enable?mysqld
使用默認密碼初次登錄后, 必須要重置密碼
查看默認密碼,?如下默認密碼為"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的值有關, mysql8.0更改了validate_password_policy相關的配置名稱, 這跟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)
查看服務端口
mysql>?show?global?variables?like?'port';
+---------------+-------+
|?Variable_name?|?Value?|
+---------------+-------+
|?port??????????|?3306??|
+---------------+-------+
1?row?in?set?(0.01?sec)
查看mysql連接的授權信息
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安裝 (二進制方式)
首先刪除系統(tǒng)默認或之前可能安裝的其他版本的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)
?
#授予用戶權限.?必須先要創(chuàng)建用戶,?才能授權!!
(創(chuàng)建用戶時要帶@并指定地址,?則grant授權時的地址就是這個@后面指定的!,?否則grant授權就會報錯!)
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ī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*
進行初始化兩個實例
[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端口實例, 并設置密碼
[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端口實例, 并設置密碼
[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
不過為了解決大家平時重復安裝的問題，特意將多實例安裝方法編輯成腳本了，有需要的讀者可以在本公眾號后臺直接回復 MySQL8 獲取多實例安裝腳本。
Mysql8.0使用過程中踩過的一些坑
1)創(chuàng)建用戶和授權 在mysql8.0創(chuàng)建用戶和授權和之前不太一樣了，其實嚴格上來講，也不能說是不一樣, 只能說是更嚴格, mysql8.0需要先創(chuàng)建用戶(創(chuàng)建用戶時要帶@并指定地址, 則grant授權時的地址就是這個@后面指定的!, 否則grant授權就會報錯!)和設置密碼,然后才能授權。
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及之前版本的直接授權的方法, 會有報錯:
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默認是不能使用root賬號進行遠程登錄的! 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)
如果想要遠程登錄, 則需要進行update更新下root賬號的權限
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)
這樣就能在遠程使用root賬號登錄該mysql8.0的數(shù)據(jù)庫了
修改root賬號權限, 允許root賬號遠程登錄后, 用navicat進行mysql的遠程連接時，出現(xiàn)了彈窗報錯： 
出現(xiàn)這個原因是mysql8 之前的版本中加密規(guī)則是mysql_native_password, 而在mysql8之后,加密規(guī)則是caching_sha2_password, 解決問題方法有兩種:一種是升級navicat驅動; 一種是把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)
?
#刷新權限
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異常 修改默認編碼方式 mysql8.0默認編碼方式為utf8mb4，因此使用時不需要修改，可使用如下命令查看：
mysql?>?SHOW?VARIABLES?WHERE?Variable_name?LIKE?'character_set_%'?OR?Variable_name?LIKE?'collation%';
如果需要修改其他編碼方式，比如需要修改為utf8mb4，可以使用如下方式：
修改mysql配置文件my.cnf, 找到后請在以下三部分里添加如下內容：
[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服務即可, 其中:
character_set_client??(客戶端來源數(shù)據(jù)使用的字符集)
character_set_connection?????(連接層字符集)
character_set_database???(當前選中數(shù)據(jù)庫的默認字符集)
character_set_results?(查詢結果字符集)
character_set_server?(默認的內部操作字符集)
數(shù)據(jù)庫連接參數(shù)中:
characterEncoding=utf8 會被自動識別為utf8mb4，也可以不加這個參數(shù)，會自動檢測。
而autoReconnect=true?是必須加上的。
6)部分參數(shù)配置查詢命令
#查詢mysql最大連接數(shù)設置
mysql>?show?global?variables?like?'max_conn%';
mysql>?SELECT?@@MAX_CONNECTIONS?AS?'Max?Connections';
?
#?查看最大鏈接數(shù)
mysql>?show?global?status?like?'Max_used_connections';
?
#?查看慢查詢日志是否開啟以及日志位置
mysql>?show?variables?like?'slow_query%';
?
#?查看慢查詢日志超時記錄時間
mysql>?show?variables?like?'long_query_time';
?
#?查看鏈接創(chuàng)建以及現(xiàn)在正在鏈接數(shù)
mysql>?show?status?like?'Threads%';
?
#?查看數(shù)據(jù)庫當前鏈接
mysql>?show?processlist;
#?查看數(shù)據(jù)庫配置
mysql>?show?variables?like?'%quer%';?
作者：散盡浮華?
出處：https://www.cnblogs.com/kevingrace/p/10482469.html
本公眾號全部博文已整理成一個目錄，請在公眾號后臺回復「
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ù)庫的用戶授權
4、MySQL | MySQL 數(shù)據(jù)庫系統(tǒng)（四）- 數(shù)據(jù)庫的備份與恢復
5、13000字！最常問的MySQL面試題集合
6、IT運維面試問題總結-數(shù)據(jù)庫、監(jiān)控、網(wǎng)絡管理（NoSQL、MongoDB、MySQL、Prometheus、Zabbix）
7、【建議收藏】寫給程序員的 MySQL 面試高頻 100 問！
8、MySQL 常用優(yōu)化指南，及大表優(yōu)化思路都在這了！
9、面試問爛的 MySQL 四種隔離級別，看完吊打面試官！
10、超全面的 MySQL 優(yōu)化面試解析關注微信公眾號「杰哥的IT之旅」，后臺回復「1024」查看更多內容，回復「加群」備注：地區(qū)-職業(yè)方向-昵稱?即可加入讀者交流群。 點個[在看]，是對杰哥最大的支持！
評論
圖片
表情