共计 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 问一下细节正文完
