共計 24413 個字符，預計需要花費 62 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
本篇文章給大家分享的是有關 CentOS 中怎么安裝部署 MySQL 8.0，丸趣 TV 小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。
Mysql8.0 安裝 (YUM 方式)
1. 首先刪除系統默認或之前可能安裝的其他版本的 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.cnf2. 安裝 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.rpm3. 安裝 Mysql8.0
[root@DB-node01 ~]# yum install mysql-community-server # 啟動 MySQL 服務器和 MySQL 的自動啟動 [root@DB-node01 ~]# systemctl start mysqld [root@DB-node01 ~]# systemctl enable mysqld4. 使用默認密碼初次登錄后, 必須要重置密碼
查看默認密碼, 如下默認密碼為 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 數據庫，使用 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 之后的數據庫里 mysql.user 表里已經沒有 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 安裝 (二進制方式)
1. 首先刪除系統默認或之前可能安裝的其他版本的 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.cnf2. 安裝需要的軟件包
[root@mysql8-node ~]# yum -y install libaio [root@mysql8-node ~]# yum -y install net-tools3. 下載并安裝 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/profile4. 創建數據目錄
[root@mysql-node src]# mkdir -p /data/mysql/{data,log,binlog,conf,tmp} [root@mysql-node src]# chown -R mysql.mysql /data/mysql5. 配置 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.info6. 初始化 (稍等一會兒, 可以到 /data/mysql/log/mysqld.err 日子里查看初始化過程, 看看有沒有 error 信息)
[mysql@mysql-node ~]$ mysqld --defaults-file=/data/mysql/conf/my.cnf --initialize-insecure --user=mysql7. 啟動 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)8. 登錄 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) # 授予用戶權限. 必須先要創建用戶, 才能授權!! (創建用戶時要帶 @并指定地址, 則 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 kevin@172.16.60.% | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 單機多實例安裝配置
通過上面二進制部署可知, 已經起來一個 3306 端口的 MySQL 實例, 現在需要再起來兩個實例, 分別為 3307, 3308. 操作如下:
創建實例的數據目錄
[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 .................... mysql3306, 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)創建用戶和授權 在 mysql8.0 創建用戶和授權和之前不太一樣了，其實嚴格上來講，也不能說是不一樣, 只能說是更嚴格, mysql8.0 需要先創建用戶 (創建用戶時要帶 @并指定地址, 則 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 12)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 的數據庫了
3. 修改 root 賬號權限, 允許 root 賬號遠程登錄后, 用 navicat 進行 mysql 的遠程連接時，出現了彈窗報錯：出現這個原因是 mysql8 之前的版本中加密規則是 mysql_native_password, 而在 mysql8 之后, 加密規則是 caching_sha2_password, 解決問題方法有兩種:
1. 一種是升級 navicat 驅動;
2. 一種是把 mysql 用戶登錄密碼加密規則還原成 mysql_native_password; 這里選擇第二種方法來解決:
# 修改加密規則 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 鏈接時會出現 2058 的異常，此時我們需要修改 mysql，命令行登錄 mysql（與修改密碼中登錄相同，使用修改后的密碼），然后執行下面的命令：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
4. sqlyog 鏈接時出現 2058 異常
5. 修改默認編碼方式 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 (客戶端來源數據使用的字符集) character_set_connection (連接層字符集) character_set_database (當前選中數據庫的默認字符集) character_set_results (查詢結果字符集) character_set_server (默認的內部操作字符集)數據庫連接參數中:
characterEncoding=utf8 會被自動識別為 utf8mb4，也可以不加這個參數，會自動檢測。 而 autoReconnect=true 是必須加上的。6)部分參數配置查詢命令
# 查詢 mysql 最大連接數設置 mysql show global variables like max_conn% mysql SELECT @@MAX_CONNECTIONS AS Max Connections # 查看最大鏈接數 mysql show global status like Max_used_connections # 查看慢查詢日志是否開啟以及日志位置 mysql show variables like slow_query% # 查看慢查詢日志超時記錄時間 mysql show variables like long_query_time # 查看鏈接創建以及現在正在鏈接數 mysql show status like Threads% # 查看數據庫當前鏈接 mysql show processlist; # 查看數據庫配置 mysql show variables like %quer%以上就是 CentOS 中怎么安裝部署 MySQL 8.0，丸趣 TV 小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注丸趣 TV 行業資訊頻道。
向 AI 問一下細節正文完
