设为首页收藏本站
开启辅助访问

3.Mysql8.0安装初始配置

 您所在的位置：网站首页 › mysql8查看用户 › 3.Mysql8.0安装初始配置

3.Mysql8.0安装初始配置

2023-03-20 05:17| 来源: 网络整理| 查看: 265

1.YUM安装Mysql8.0 2.二进制安装Mysql8.0 3. 登录mysql,重置密码 4. mysql8.0授权及远程登录踩坑记录 4.1 创建用户并授权 4.2 允许root用户远程登录 4.3 修改mysql8.0加密规则

====================================================================

1.YUM安装Mysql8.0 1.1 删除系统默认或之前安YUM安装Mysql8.0 for i in $(rpm -qa|grep mysql);do rpm -e $i --nodeps;done rm -rf /var/lib/mysql && rm -rf /etc/my.cnf 1.2 安装Mysql8.0的yum资源库 mysql80-community-release-el7-1.noarch.rpm ~]# yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm 1.3 安装Mysql8.0 ~]# yum install mysql-community-server #启动MySQL服务器和MySQL的自动启动 ~]# systemctl start mysqld ~]# systemctl enable mysqld 1.4 使用默认密码初次登录后必须重置密码 查看默认密码, 如下默认密码为"e53xDalx.*dE" ~]# 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 ~]# 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

~]# 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; 2.二进制安装Mysql8.0 2.1.删除系统默认或之前可能安装的其他版本的mysql for i in $(rpm -qa|grep mysql);do rpm -e $i --nodeps;done rm -rf /var/lib/mysql && rm -rf /etc/my.cnf 2.2.下载软件包 https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.31-el7-x86_64.tar.gz 2.3.安装需要的软件包 yum -y install libaio yum -y install net-tools 2.4.安装mysql8.0 #创建用户 [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 2.5.创建mysql8.0数据目录 [root@mysql-node src]# mkdir -p /data/mysql/{data,log,binlog,conf,tmp} [root@mysql-node src]# chown -R mysql.mysql /data/mysql 2.6.配置mysql8.0 [root@mysql-node src]# su - mysql [mysql@mysql-node ~]$ vim /etc/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 2.7.初始化数据库 [mysql@mysql-node ~]$ mysqld --initialize-insecure

or

[mysql@mysql-node ~]$ mysqld --initialize

ps: 两种方法的区别： --initialize ： 初始化时，会自动创建超级管理员（root@'localhost'）,生成随机密码，12位，4种密码复杂度。这个密码，需要在第一次登陆时修改掉才可以正常管理数据。 ps: 如果遇到以下报错： [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting ps:

[ERROR] libaio ......

yum install -y libaio-devel

2.8 启动数据库 2.8.1 脚本启动 [mysql@mysql-node data]# cd /usr/local/mysql/support-files/ [mysql@mysql-node support-files]# ./mysql.server start Starting MySQL.Logging to '/data/mysql/data/mysql.err'. . [ OK ] 2.8.2 SYS-V启动方式 [mysql@mysql-node support-files]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [mysql@mysql-node ~]# service mysqld restart Shutting down MySQL. [ OK ] Starting MySQL.. [ OK ] [mysql@mysql-node ~]# service mysqld stop Shutting down MySQL.. [ OK ] [mysql@mysql-node ~]# service mysqld start Starting MySQL.. [ OK ] [mysql@mysql-node ~]# [mysql@mysql-node ~]# service mysqld status MySQL running (11842) [ OK ] [mysql@mysql-node ~]# service mysqld stop Shutting down MySQL.. [ OK ] 2.8.3 systemd方式 [mysql@mysql-node ~]# systemctl cat mysqld # /usr/lib/systemd/system/mysqld.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf LimitNOFILE = 5000 [mysql@mysql-node ~]# systemctl enable mysqld [mysql@mysql-node ~]# systemctl start mysqld [mysql@mysql-node ~]# systemctl restart mysqld [mysql@mysql-node ~]# systemctl stop mysqld [mysql@mysql-node ~]# systemctl start mysqld [mysql@mysql-node ~]# systemctl status mysqld 3. 登录mysql，重置密码 3.1 登录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) 3.2授予用户权限. 必须先要创建用户, 才能授权!!

(创建用户时要带@并指定地址, 则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) 4. mysql8.0授权及远程登录踩坑记录 4.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 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 4.2 允许root用户远程登录

Mysql8.0默认是不能使用root账号进行远程登录的! root账号只能本地登录!

mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | bobo | $A$005$1VY")q?G6 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 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)


【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3