mysql杂谈

版本8.0.17 win64，之前也试过5.*版本大致问题如下

• 配置文件my.ini

#1. 在路径:D:\devSoftWare\mysql-8.0.17-winx64下新建my.ini文件,文件内容如下:::
[mysqld]
#skip-grant-tables
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
  innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# mysql根目录
  basedir = D:\devSoftWare\mysql-8.0.17-winx64
# 数据文件存放目录
  datadir = D:\devSoftWare\mysql-8.0.17-winx64\data
# 端口，默认3306  
  port = 3306
# 服务实例的唯一标识
# server_id = MySQL

# 允许最大连接数
  max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
  max_connect_errors=10
# 服务端使用的字符集默认为utf8
  character_set_server=utf8mb4
# 创建新表时将使用的默认存储引擎
  default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
  default_authentication_plugin=mysql_native_password
  
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
  join_buffer_size = 128M
  sort_buffer_size = 2M
  read_rnd_buffer_size = 2M 
  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306   
default-character-set=utf8

• 管理员的身份运行终端进入bin目录

#依次执行：
mysqld --initialize
#如果上面这个命令执行错误，删除根目录下的data，自己会创建
mysqld -install
net start mysql

• 登录mysql，修改账号密码

#找到初始密码:
在D:\devSoftWare\mysql-8.0.17-winx64\data\MIBOOK-JGMa.err文件中
generated for root@localhost: zH7hJfi:Cfz&

mysql -uroot -p
输入密码:zH7hJfi:Cfz&

• 登录成功，修改root 密码

mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

有人说navcat12连接失败，我没遇到过，记录以下

在用Navicat Premium 12连接本地数据库时会报错如下

原因:mysql8.0之前的版本加密规则是mysql_native_password，mysql8.0之后，加密规则是caching_sha2_password

• 解决办法:

#1.查看默认加密方式
	use mysql;
	select user,plugin from user where  user = 'root';
#2.修改加密方法:
	LTER USER'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
#3.更新密码:
	ALTER USER'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';	
#4.刷新权限：
	FLUSH PRIVILEGES;	

华丽分割线====

安装

1. 将压缩包传到服务器
   https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

2. 解压文件
   tar -zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

3. 重命名解压后的文件
   mv mysql-5.6.36-linux-glibc2.5-x86_64/ mysql

4. 检查是否有mysql用户组和mysql用户
   groups mysql

若有----跳过此步

若无----添加

groupadd mysql

useradd -r -g mysql mysql

5. 进入mysql目录-----更新权限
   cd mysql/

chown -R mysql:mysql ./

6. 执行安装脚本
   ./scripts/mysql_install_db --user=mysql

如果提示 FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db:Data::Dumper的话，那么原因是缺少perl-module

yum -y install perl-Module-Install.noarch

安装perl-module模块，等待perl-module安装成功之后，再继续执行上一步安装

7. 安装完之后修改当前目录拥有者为root用户，修改data目录拥有者为mysql
   chown -R root:root ./

chown -R mysql:mysql data

8. 启动mysql
   ./support-files/mysql.server start

9. 修改密码
   ./bin/mysqladmin -u root password ‘你的密码’

10. 登录
    ./bin/mysql -h127.0.0.1 -uroot -p

输入刚设置的密码即可登录

11. 添加远程登录权限
    grant all privileges on . to root@‘%’ identified by ‘你的密码’;

flush privileges;

12. 将MySQL注册为服务
    cp support-files/mysql.server /etc/init.d/mysqld

13. 添加软连接

    //添加软连接
    ln -s /xz/mysql5.7/support-files/mysql.server /etc/init.d/mysql
    ln -s /xz/mysql5.7/bin/mysql /usr/bin/mysql
    //重启mysql服务
    service mysql restart
    

chkconfig --add

mysqld chkconfig mysqld on

service mysqld restart（重启）

service mysqld status （状态）

重启mysql

service mysql restart

防火墙开放3060端口

//开放3306端口命令
firewall-cmd --zone=public --add-port=3306/tcp --permanent
//配置立即生效
firewall-cmd --reload

修改全局变量

查看
show variables
修改
SET GLOBAL query_timeout = 3000;