【mysql】windows,linux安装mysql教程 解决安装过程报错问题

官网下载mysql

社区免费版下载：

windows安装mysql

tips: 在次之前先创建一份my.ini文件 如果需要重装 安装版需要先在程序中卸载 然后注意清理MySQL的3个注册表(不一定都有) ；当免安装版已经注册到服务中时，重新安装会提示服务已存在，需要已管理员身份运行DOS， 输入 sc delete MySQL 删除服务(MySQL替换为你具体的服务名 也可能是Mysql5.7之类的)

需要清理的注册表：

HKEY_LOCAL_MACHINE/SYSTEM/ControlSet001/Services/Eventlog/Application/MySQL

HKEY_LOCAL_MACHINE/SYSTEM/ControlSet002/Services/Eventlog/Application/MySQL

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Eventlog/Application/MySQL

windows mysql安装版

mysql安装版比较简单 下载后一路点击下一步。

要注意的是 如果之前安装过 需要把mysql卸载干净 如mysql相关注册表都删掉。(ctrl+r 输入regedit)

windows mysql 免安装版

免安装版没有ini文件的话 需要复制一份到mysql根目录 ,可以在有安装版的服务器上复制一份 或新建my.ini 内容到网上复制一份可用的 ,后文中博主也给出了示例，具体大小和目录需要自己调整

1. 配置mysql bin目录到环境变量

2. cd至mysql bin目录 输入命令： mysqld –install

3. mysqld --initialize-insecure --user=mysql

4. net start mysql

5. 踩坑点：
   第2点如果提示Install/Remove of the Service Denied! 那么可能你的是Win10/11系统 需要以管理员身份运行dos/powershell界面

   第3、4两点 一般第一次安装不会出错,有可能在某些情况下会报错（比如删除mysql服务重装时）, my.ini配置的 datadir 要指定data文件夹路径 并手动新建该data空文件夹,如果路径已有data文件夹 最好把它清空，网上很多人说要保持没有创建data文件夹 让初始化时自动创建 ，根据我遇到的各种情况 在没报错时 它是能够初始化自动创建 无需指定,但如果报了没有该目录的错误，那肯定必须手动创建了。且第四点启动时，可能会报错 错误日志在data/文件夹下的 .err文件或者指定的log_error文件夹下的error.log文件中 , 一般可能是my.ini配置有问题, 更改配置后需要先remove 再重复上面的操作命令
   mysqld --remove

6. 设置密码预步骤：
   mysql -u root
   mysql> use mysql

7. 踩坑点:（这里如果复制别的电脑使用过的mysql文件夹 或者重装等情况 可能会进不了mysql 并报错提示 host’::1’ is not allowed ） 先在ini加一行skip-grant-tables并重启mysql服务，配置完一切再删除并再次重启.

8. 设置密码：
   mysql5:
   update user set authentication_string=password("root") where user = "root"; 需要加上分号, (或者6步骤换成
   set password for root @localhost=password('root'); 这里的第一个root是指root用户名, 注意 如果设置了跳过权限检查行skip-grant-tables 则只能用第一种方法 )

   mysql8:
   ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'yourPassword';

9. flush privileges;
   (以MySQL8为例 验证 )
   

10. 允许远端连接： update user set host = '%' where user ='root'; (这里则是解决mysql不能远程连接的问题)

11. flush privileges;

12. 注意根据具体业务情况 是否需要兼容旧版本（5.7默认开启，5.5默认关闭） 如果需要把严格模式关闭 ：

       my.ini配置文件中 如果没有sql-mode那么新起一行:
       sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 
       
       my.ini配置文件中 如果存在
       sql-mode=STRICT_TRANS_TABLES，NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIO
       替换成：
      sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIO
    

13. 如果需要全局忽略表名大小写 在my.ini中新起一行： lower_case_table_names=1

这里贴一个官方的ini文件(mysql5,非mysql8)

# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by the MySQL Server Instance Configuration Wizard
#
#
# Installation Instructions
# ----------------------------------------------------------------------
#
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.
#
# On Windows you should keep this file in the installation directory 
# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To
# make sure the server reads the config file use the startup option 
# "--defaults-file". 
#
# To run run the server from the command line, execute this in a 
# command line shell, e.g.
# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# To install the server as a Windows service manually, execute this in a 
# command line shell, e.g.
# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# And then execute this in a command line shell to start the server, e.g.
# net start MySQLXY
#
#
# Guildlines for editing this file
# ----------------------------------------------------------------------
#
# In this file, you can use all long options that the program supports.
# If you want to know the options a program supports, start the program
# with the "--help" option.
#
# More detailed information about the individual options can also be
# found in the manual.
#
#
# CLIENT SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]

port=3306

[mysql]

default-character-set=latin1


# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306


#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=latin1

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=100

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=0

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache=256

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=35M


# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=8

#*** MyISAM Specific options

# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G

# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method.  This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=69M

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=55M

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K
read_rnd_buffer_size=256K

# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=256K


#*** INNODB Specific options ***
innodb_data_home_dir="H:/MySQL/"

# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=3M

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=2M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=107M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=54M

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=10

再贴一个博主测试用的mysql8 my.ini文件，已知mysql8用上面mysql5的配置文件会报找不到data文件的错误

# MySQL Server Instance Configuration File

# ----------------------------------------------------------------------

# CLIENT SECTION

[client]

port=3306

[mysql]

default-character-set=utf8mb4

# ----------------------------------------------------------------------

# SERVER SECTION

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Path to installation directory. All paths are usually resolved relative to this.
basedir=D:\dev-software\mysql-8.0.32-winx64

# Path to the database root
datadir=D:\dev-software\mysql-8.0.32-winx64\data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8mb4

# The default storage engine that will be used when create new tables when
# default-storage-engine is not defined
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode=""

# The maximum allowed packet size (16M by default)
max-allowed-packet=16M

# The number of open tables for all threads (40 by default)
table_open_cache=256

# The maximum number of simultaneous connections (100 by default)
max_connections=100

# The size of the buffer pool, the memory area where InnoDB caches table and index data
innodb_buffer_pool_size=512M

# Set the default storage engine for new tables to InnoDB
default-storage-engine=INNODB

# Set the default collation for the default storage engine
collation-server=utf8mb4_unicode_ci

# Set the server character set
character-set-server=utf8mb4

# InnoDB settings
innodb_data_home_dir=D:\dev-software\mysql-8.0.32-winx64\innodb\innodb_home
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir=D:\dev-software\mysql-8.0.32-winx64\innodb\log_group_home
innodb_log_files_in_group=2
innodb_log_file_size=128M

# Logging and Replication
log_error=D:\dev-software\mysql-8.0.32-winx64\log\log_error\error.log
log-bin=D:\dev-software\mysql-8.0.32-winx64\log\log-bin
expire_logs_days=10
max_binlog_size=100M

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

linux安装mysql

centos安装mysql (mariadb)

1.注意:基于centOS 其它linux系统不一定通用
2.centOS7开始 放弃了mysql 使用mariaDB，mariaDB是mysql的一个分支，mariaDB:由于mysql被oracle收购 mysql创建者担心会闭源而建立的新分支。对我们来说，就是换了个名字，可以理解为就是mysql，所以不要使用 yum install mysql的命令，按照网上大部分的命令及教程 centos中mysql服务启动不起来 ,我们直接安装完全兼容mysql的mariadb

---

---

yum i install 和yum install的区别：i会自动安装， 没有i 会提示输入y或n 表示是否安装，看个人习惯，我是习惯使用不带i的命令
正文开始：

1. 如果已经使用了安装mysql的命令，先用yum remove mysql卸载
   

---

彻底卸载步骤：
yum remove mysql*
find / -name mysql*
yum remove mariadb*
find / -name mariadb*
把找出来的mysql ,mariadb文件全部删除 注意带*号，不然有些文件找不出来

2. 输入安装mariadb命令： yum install mariadb-server 类似上图 提示输入的时候输入y,
   这里马上使用mysql -u root -p 命令，可能会报错连接不了mysql.sock错误，按照步骤来操作 所以别急

3. 安装完成后输入 启动mariadb服务命令：
   systemctl start mariadb.service
   （这里如果mysql没有卸载干净 启动会报错 报错了就重复上面彻底卸载的命令 如果重复了还不行
   那你可能和博主一样 花了很多时间去捣鼓怎么安装mysql 弄出很多问题来了 那就再彻底卸载一次 并重启linux）

4. systemctl enable mariadb.service (允许开机自启)

5. mysql_secure_installation （配置mysql）

   出现如图提示 直接按回车
   
   控制台提示 Set root password? [Y/n] 输入 y
   为数据库设置密码

   Remove anonymous users? [Y/n] 删除匿名用户？ 输入 y

   下面这条注意 ！！！输入n
   Disallow root login remotely? [Y/n] 是否禁止root远程登录？ 因为我们一般都是远程windows可视化界面操作的 不能禁止 输入n,如果说为了安全 非得在linux本机登录 那选y 看个人选择了。

   Remove test database and access to it? [Y/n] 删除test数据库， 输入y

   Reload privilege tables now? [Y/n] 重新加载权限表，输入y

   注意：没禁止root远程登录还不够 需要在mysql里面配置权限：
   先登陆

   mysql -u root -p
   Enter password: 数据库密码

   输入以下命令：表示允许任何主机上使用数据库root账户及其密码登陆 ，其中%可以替换为某一个具体的ip地址，表示仅允许该主机远程登录 注意命令不要少任何符号 包括结尾分号

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '你的数据库密码' with grant option;
   
   

   再输入下面命令: 刷新权限 同样不能漏了分号
   flush privileges;

   最后一步：云服务上面放通端口 以腾讯云为例，
   云服务设置位置：
   
   轻量级服务器设置：
   
   

   我们可以在windows上用Navicat登陆了：主机不要习惯性使用默认的localhost 记得要换成linux的ip !
   

   拓展：设置全局忽略表名大小写：
   (先找到my.ini路径 一般是在/etc下面)
   vim /etc/my.ini
   新增一行 lower_case_table_names=1

本教程已经多次在不同centos服务器上成功启动。

linux通用版本安装mysql (如alibaba cloud linux)

这里介绍一种比较通用的安装方法 虽然阿里说alibaba cloud linux兼容centos7 但是安装mysql的时候 发现上述centos安装教程很明显就是不适用 ，同样的 如果当前服务器安装过mysql 需要卸载干净。个人比较喜欢叫这种免安装版 也有叫二进制版本安装的

mysql官方下载地址： https://dev.mysql.com/downloads/mysql/

点击download跳转至下载界面，可以windows端下载 通过filezilla、xftp等传输软件传输至linux端，也可以直接在linux端wget+下载地址进行下载 ，看你windows linux两端网速差异大不大。

1. 解压下载包：

    tar -xvf mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz 
   

2. 移动文件夹:

      mv mysql-8.0.27-linux-glibc2.12-x86_64    /usr/local/mysql
   

3. 修改文件夹归属

    chown -R mysql /usr/local/mysql
   

4. 初始化数据和指定安装目录和数据目录 注意：在mysql8之前 lower-case-table-names=1我们可以放进my.cnf配置文件里面 初始化可以不加上 但是在mysql8中，初始化的时候就要声明 输入下面命令后会产生一个临时密码 我们先把这个临时密码记录一下

    /usr/local/mysql/bin/mysqld --initialize --lower-case-table-names=1 --console --skip-grant-tables --user=mysql --basedir=/usr/local/mysql/ --datadir=/mnt/mysql_data/
   

5. 创建所需目录，并配置权限

    mkdir /var/log/mysql
    mkdir /var/lib/mysql
    touch /mnt/mysql_data/mysql.pid
    touch /var/lib/mysql/mysql.sock
    
    chown -R mysql:mysql /var/lib/mysql/
    chown -R mysql:mysql /var/log/mysql
    chown -R mysql:mysql /mnt/mysql_data
    chmod 777 /var/lib/mysql/mysql.sock
    chmod 777 /mnt/mysql_data/mysql.pid
   

6. 复制启动文件 添加到服务

    cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
    chmod +x /etc/rc.d/init.d/mysqld
   

7. 修改启动路径

   vi /etc/rc.d/init.d/mysqld

   basedir=/usr/local/mysql
   datadir=/mnt/mysql_data

8. 增加环境变量 （已添加则忽略）

   vi /etc/profile

   export PATH=$PATH:/usr/local/mysql/bin

9. 刷新配置文件(未更改则忽略)
   source /etc/profile && ldconfig

10. 修改my.cnf文件 （注意与windows不同的是 windows该配置放在mysql根目录 且叫my.ini linux放在/etc 的根目录 叫my.cnf）

# linux mysql config
[client]
port = 3306
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock
 
[mysqld]

 #  NO_ENGINE_SUBSTITUTION : mysql 在create table 时可以指定engine子句；这个engine子句用于指定表的存储引擎 如myisam 、innodb 
 # STRICT_TRANS_TABLES 严格模式 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
# 仅供参考 具体需要看文章中 sql_model 部分
sql_mode=NO_ENGINE_SUBSTITUTION 
basedir = /usr/local/mysql
datadir = /mnt/mysql_data
socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mysql/error.log
pid-file = /mnt/mysql_data/mysql.pid
max_connections = 300
character-set-server = utf8mb4
default-storage-engine = INNODB
# 忽略表名 在mysql8之后 初始化时就要声明
lower_case_table_names = 1
max_allowed_packet = 16M
user = mysql
tmpdir = /tmp
default_authentication_plugin = mysql_native_password
 
[mysql]
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock

11. 添加开机自启

    chkconfig --add mysqld && chkconfig mysqld on
    /etc/rc.d/init.d/mysqld start
    

12. 建立软链接

     ln -s /usr/local/mysql/bin/mysql /usr/bin
    

13. 输入mysql -uroot -p
    在这一步很可能会报错 需要排查的因素也比较多 (如果没报错则忽略 继续往下)：

    	 1.首先确定/etc/rc.d/init.d/mysqld start是否启动成功
    	  如果启动失败 可能是my.cnf配置写的有问题 ， 
    	  或my.cnf配置的相关文件夹mysql用户没有权限
    	  （使用chown mysql:mysql命令将文件夹归属修改为mysql）
    	  也可能因为在之前的安装中反复报错反复安装 但3306端口已经被占用了
          具体的原因我们应该查看日志，错误日志路径就是my.cnf里面配置的log-error路径
        
    	  2. 如果是启动成功 报错bash mysql command not found ：
    	     那可能是环境变量没有配置 或者路径有问题（vim /etc/profile进行修改配置） 
    	     或者环境变量没有重启刷新(source /etc/profile 进行刷新)
        3. 如果提示的是一个文件夹路径 而这个文件夹是一个你比较眼熟的 
           比如是之前安装过的mysql目录，那可能你受到别的文章的蛊惑 写了一个
           alias mysql=/xxx/xxxx/mysql  ，因为这个导致mysql -uroot -p命令失效
           解决方案：unalias mysql
    

14. 提示输入密码 我们输入初始化时的临时密码再按回车
    (如果忘记了 可以在my.cnf里面配置skip-grant-tables跳过密码 弄好后务必记得把这行删了！)

[mysqld]
# other config 
# 跳过密码
skip-grant-tables

15. use mysql

    【mysql5版本】如果到这里没报错 那接下来就是很熟悉的操作了 输入开放远程连接的sql语句 输入修改密码的sql语句 (注意最后有分号)

     	# 开放root远程连接
     	update user set host = '%' where user ='root';
     	
     	# 修改密码
     	update user set authentication_string="你要设置的密码" where user = "root";
    

    【mysql8】由于我的是mysql8 免安装版与之前的版本及安装方式 略有不同 在这里还是报错：
    （遭最毒的打 遇最多的bug 招bug体质）
    

    解决方式:

     #（注意 这个时候用update set方式修改密码是会报错的）
    
     mysql> ALTER USER USER() IDENTIFIED BY '你要设置的密码';
    

    按下回车之后 我们就可以用上面那两句传统的update了

    
    

    【mysql8跳过密码方式】博主的教程都绝对是经历过实操并成功才写下的，但是在经历过大量不同环境、版本安装mysql后 发现次次踩坑 坑坑不一样 才发现mysql安装比想象中的难多了，个人非常不喜欢yum方式安装 可维护性太差了 还是喜欢通用（免安装）版本，这次的环境是在centos8虚拟机，mysql8.0.36版本，在安装时 忘记了记录临时密码 于是在my.cnf配置了skip-grant-tables ,但是发现设置密码时就会出现问题了,注意点比较多：
    用上面方式修改密码可能会报错，博主这次是用的alter命令修改密码

    ALTER user 'root'@'localhost' IDENTIFIED BY 'abcABC@'
    

    (如果已经set host = ‘%’ 命令则如下 )

    ALTER user 'root'@'%' IDENTIFIED BY 'abcABC@'
    

    其中abcABC@是密码 必须要是复杂密码 否则会报错
    ERROR 1396 (HY000): Operation ALTER USER failed for ‘root‘@

如果报错：The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
则使用命令（在use mysql;下）

flush privileges;

如果还报错ERROR 1396 (HY000): Operation ALTER USER failed for ‘root‘@) ，那还可能是因为加密插件的原因导致的，mysql5是mysql_native_password插件，而mysql8默认为caching_sha2_password, 如果希望修改回mysql_native_password, 我们可以使用以下命令：

# 先查看host user 以及plugin信息
select host,user,plugin from user;
# 将root用户的插件改回去 其它用户同理
update user set plugin='mysql_native_password' where user='root';
# 刷新
flush privileges;

如果没改插件或处理不当 很可能出现的问题就是 明明修改了密码 但是再次登录却提示密码错误
MySQL Access denied for user ‘root‘@‘localhost‘ (using password: YES/NO)

最后还需要将my.cnf跳过密码这一行注释掉 重启mysql，否则会出现的问题就是 别的机器telnet 3306端口不通

16. flush privileges;

17. 开启防火墙

    	firewall-cmd --add-port=3306/tcp --permanent
    	firewall-cmd --reload
    	systemctl restart firewalld 或 service firewalld start
    

18. navicat 测试
    正常来说 这个时候是可以连接的 但是不知道是因为mysql8还是linux安装版本的特性 它还是报错了： Authentication plugin ‘caching_sha2_password‘cannot be loaded 后面一串乱码
    解决方案：
    登陆mysql : mysql -u root -p (注意此时密码是修改后的密码了)

输入命令：

	ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的mysql密码';

19. navicat再次测试 成功
20. 最后 如果你的配置文件中不是复制上面的 且在配置文件中设置了跳过密码 一定要记得把skip-grant-tables删除

找不到mysql.sock问题

Can’t connect to local MySQL server through socket '/tmp/mysql.sock’是比较经典的一个报错 网上的解答方法可能五花八门 然而却没有用 。 因为造成找不到mysql.sock的原因有很多 我们需要明白的是 mysql.sock是mysql socket连接时产生的文件 换句话说 mysql服务启动时 该文件存在, mysql关闭时 该文件不存在，如果我们启动时报这个错 那说明就是启动失败了，所以启动失败的原因有多少 mysql.sock的原因几乎就有多少,所以与其花费心思网上搜mysql.sock问题 还不如自己从头梳理mysql的安装过程，按照上述教程 可以避免启动失败的问题发生
如果还很不幸报错 那同样的 我们应该查看日志，错误日志路径就是my.cnf里面配置的log-error路径
比较大概率是文件夹归属没有改成mysql用户

sql_model 含义

mysql的sql_mode合理设置

sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题

sql_mode常用值如下：

	ONLY_FULL_GROUP_BY：
	
	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
	

NO_AUTO_VALUE_ON_ZERO：

该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。
如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES：

在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

NO_ZERO_IN_DATE：

在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：

设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：

在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER：

禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION：

如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT：

将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

ANSI_QUOTES：

启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

ORACLE的sql_mode设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

如果使用mysql，且需要保留大家使用oracle的习惯，可以对mysql的sql_mode设置如下：

在my.cnf添加如下配置
(强调：等效于oracle默认配置，并不是让各位直接复制粘贴！！！
选择哪些项，请认真阅读每一项的用途，尤其是生产环境)

[mysqld]
sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'

注：ONLY_FULL_GROUP_BY 请结合业务中的sql代码 慎重开启！

临时关闭严格模式

关闭严格模式，很大程度上等效于关闭 ONLY_FULL_GROUP_BY，通常这个对我们开发影响最大
永久关闭自然是my.cnf中配置了，如果是临时关闭 我们可以
（仅供参考 最好不要随意复制 还是那句话 请认真阅读每一项的用途 ）:

# 先看看有没有活动的进程
SHOW FULL PROCESSLIST;
# 看看当前的sql_model 备份一下
SHOW VARIABLES LIKE 'sql_mode';
# 注： 旧配置为 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
SET @@session.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';