mysql的安装及配置连接

Linux下安装mysql

mysql国内镜像地址：http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-8.0/
mysql官网地址：https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz

• 解压安装

--跳转到下载目录
cd /usr/local
--下载mysql8.0的版本 wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz

--解压
tar -xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz

--移动
mv mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz mysql

--添加mysql组和mysql用户
--添加mysql组
groupadd mysql

--添加mysql用户
useradd -r -g mysql mysql

• 配置mysql配置文件与初始化mysql

--给mysql组/usr/local/mysql文件权限
chown -R mysql:mysql /usr/local/mysql

初始化mysql服务
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
#注意此时会产生初始密码 如下
#2021-04-15T08:44:21.775801Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Bqxid:Dde4s>

chown -R root:root /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql/data
--修改/etc/my.cnf配置文件权限
chmod 755 /etc/my.cnf

• 以下是配置文件内容：

[mysql]
default-character-set=utf8

[client]
port=3306
default-character-set=utf8
socket=/usr/local/mysql/tmp/mysql.sock

[mysqld]
socket=/usr/local/mysql/tmp/mysql.sock
port=3306
default_authentication_plugin=mysql_native_password
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
character-set-server=utf8
default-storage-engine=InnoDb
max_connections=1000
collation-server=utf8_unicode_ci
init_connect='SET NAMES utf8'
innodb_buffer_pool_size=64M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=120
innodb_log_buffer_size=4M
innodb_log_file_size=256M
interactive_timeout=120
join_buffer_size=2M
key_buffer_size=32M
log-error=/usr/local/mysql/data/data.err
log_error_verbosity=1
max_allowed_packet=256M
max_heap_table_size=64M
myisam_max_sort_file_size=64G
myisam_sort_buffer_size=32M
read_buffer_size=512kb
read_rnd_buffer_size=4M
log_bin=mysql-bin
server-id=1
skip-external-locking=on
sort_buffer_size=256kb
table_open_cache=256
thread_cache_size=16
tmp_table_size=64M
wait_timeout=2288800
general_log=ON
general_log_file=/usr/local/mysql/data/query_log.pid
slow_query_log=ON
slow_query_log_file=sql-slow.log
long_query_time=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

• 创建mysql.sock存储文件tmp

mkdir tmp
chmod 777 tmp

• 加入开机自启

--将mysql服务添加到/etc/init.d
cp support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
--添加服务
chkconfig --add mysql
--查看是否添加成功
chkconfig --list mysql

• 开启mysql服务并配置环境变量

--启动mysql服务
service mysql start
vi /etc/profile
--添加：
export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib
source /etc/profile#刷新配置

• 登录mysql，修改密码 （注意初始密码编译时会产生）

mysql -uroot -p
--修改密码
alter user 'root'@'localhost' identified by 'root';
# 修改加密规则 
--8.0+ caching_sha2_password  
--8.0-  mysql_native_password 
ALTER USER 'test'@'%' IDENTIFIED WITH caching_sha2_password BY '123456';
--创建用户
create user 'username'@'%' identified by 'password';
-- 查看新创建的用户
select * from user where user = 'username';
-- 查看权限
show grants for 'username'@'localhost';

#### 超管权限授权
-- grant：授权
-- all privileges：所有的权限
-- on *.*：在哪个数据库的那个表
-- to username@localhost：对哪个用户的哪个主机
-- with grant option：是不是将username用户自己本身的权限赋给其他账户

grant all privileges on *.* to 'username'@'localhost' with grant option;


#### 普通权限授权
-- usage：无权限，当你想创建一个没有权限的用户时候，指定usage
-- show：查看的权限，赋权限报错了，我也不知道咋回事
-- view：视图的权限
-- create temporary tables：创建临时表的权限
-- excute：执行的权限

grant usage,select,insert,update,delete,create temporary tables,execute on *.* to username@localhost;

-- 刷新配置
flush privileges;