1.windows版本
从官网下载：https://dev.mysql.com/downloads/mysql/
或者
链接: https://pan.baidu.com/s/14VLl9a-3UmwhfVBKzvQqBA
提取码: mcvq
若先前下载过，请先百度卸载干净！否则会有各种奇怪的问题
解压版安装按如下链接，注意，配置 my.ini时 data等磁盘目录需要 两个斜杠 \\
https://blog.csdn.net/qq_41772384/article/details/124251792
自定义安装窗口参考如下链接。本文就不做多描述。
https://zhuanlan.zhihu.com/p/164991093
2.linux版本
参考阿里云搭建：https://help.aliyun.com/document_detail/116727.html
yum源替换成阿里的
# 备份系统默认源
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
#选择自己对应的版本执行
# Centos6
#wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-6.repo
# Centos7
#wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
# Centos8
#wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-8.repo
# 运行以下命令生成缓存
yum makecache
# 产生报错Couldn't resolve host 'mirrors.cloud.aliyuncs.com'报错信息后执行 (没报错就不执行）
#sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentOS-Base.repo```
1.更新yum源
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
2.安装mysql
sudo yum -y install mysql-community-server --enablerepo=mysql80-community --nogpgcheck
3.执行 mysql -V,输出如下版本号即为安装成功。
4. 启动mysql
systemctl start mysqld
#设置开机自启动
systemctl enable mysqld
5.查看初始密码
grep 'temporary password' /var/log/mysqld.log
调整 mysql的数据目录 datadir，不调整就跳过，记得先停止mysql
1.停用MySQL数据库
[root@mysql ~]# systemctl stop mysqld
2.查看数据现存目录迁移至所需目录
[root@mysql ~]# cat /etc/my.cnf | grep datadir
datadir=/var/lib/mysql
[root@mysql ~]# cp -ar /var/lib/mysql /data/
3.修改MySQL的配置文件
[root@mysql ~]# vim /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock
4.将socket软链接至原有目录（否则会出现启动失败的情况）
[root@mysql ~]# ln -s /data/mysql/mysql.sock /var/lib/mysql
#通过该命令可看出原文件的权限和新数据目录权限不一样 SELinux安全策略
sudo ls -Zl /var/lib/mysql
sudo ls -Zl /data/mysql
# 如果执行上面的，发现权限不一样，还要将原路径的权限给新路径
#semanage fcontext -a -e /var/lib/mysql /data/mysql
#chcon -R --reference=/var/lib/mysql /data/mysql
5.启动MySQL服务
[root@mysql ~]# systemctl start mysqld
6.查看服务状态是否正常
[root@mysql ~]# systemctl status mysqld.service
● mysqld.service - MySQL
Loaded: loaded (/etc/init.d/mysql; enabled; vendor preset: disabled)
Active: active (running) since 二 2020-04-07 11:55:26 CST; 1min 10s ago
Process: 37229 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
Process: 37311 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
Main PID: 37318 (mysqld_safe)
Tasks: 28
Memory: 151.5M
CGroup: /system.slice/mysqld.service
├─37318 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --...
└─37449 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data...
4月 07 11:55:25 mysql systemd[1]: Starting MySQL...
4月 07 11:55:26 mysql systemd[1]: Started MySQL.
[root@mysql ~]# ps -ef | grep mysqld
mysql 37318 1 0 11:55 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/usr/local/mysql/data/mysqld.pid
mysql 37449 37318 0 11:55 ? 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/mysql/data/mysql.err --pid-file=/usr/local/mysql/data/mysqld.pid --socket=/data/mysql/mysql.sock
root 37551 36896 0 11:56 pts/1 00:00:00 grep --color=auto mysqld
[root@mysql ~]# netstat -anpt | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 37449/mysql
7.检查数据是否正常
[root@mysql ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| wordpress |
+--------------------+
6 rows in set (0.00 sec)
-----------------------------------
附一个我最后的my.cnf配置
[mysqld]
log-bin=mysql-bin
server-id = 1
port = 3306
# 千万别加 NO_AUTO_CREATE_USER mysql8已经没有了
sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#transaction_isolation = READ-COMMITTED
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1
max_connections = 1000
expire_logs_days=30
#日志中会记录成每一行数据被修改的形式
binlog-format=ROW
#2022-02-09 新增配置
#指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制
max_user_connections = 800
#MySQL客户端的数据库连接闲置最大时间值,超过该时间后将会被强行关闭
wait_timeout = 3600
#一个交互连接在被服务器在关闭前等待行动的秒数
interactive_timeout = 3600
#如果客户端(同一IP主机)尝试连接的错误数量超过这个参数设置的值，则服务器不再接受新的客户端连接。
max_connect_errors = 10000
#所有线程能打开的数据库表的数量(高速缓存)，会直接引响查询性能，尤其是高并发的情况下
#2G及以下内存建议512, 4G内存建议2048
table_open_cache = 2048
#控制mysql 服务端接受的数据包大小，批量插入与修改大数据量受限
max_allowed_packet = 16M
#控制innodb缓冲池大小，用来存储innodb表和索引的数据
innodb_buffer_pool_size = 16G
#将缓冲池分为用户指定数目的独立区，每个有自己的LRU链表和相关的数据结构，以减少并发内存读写期间的冲突
innodb_buffer_pool_instances = 16
#该参数决定着mysql事务日志文件（ib_logfile0）的大小,一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容
innodb_log_file_size = 128M
#数据和日志刷新到磁盘的方式 O_DIRECT 直接写入磁盘，禁止系统Cache了
innodb_flush_method = O_DIRECT
#在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log
back_log = 128
6.修改密码
这种方式的密码要很复杂才能通过，蛋疼，嫌麻烦使用第二种方式。
#linux中直接执行，不用登录mysql
mysql_secure_installation
Enter password for user root: #输入已获取的root用户初始密码
The existing password for the user account root has expired. Please set a new password.
New password: #输入新的MySQL密码 # 要求：长度大于等于8，数字、大小写字母、特殊符号
Re-enter new password: #重复输入新的MySQL密码
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :Y #输入Y选择更新MySQL密码。您也可以输入N不再更新MySQL密码。
New password: #输入新的MySQL密码
Re-enter new password: #重复输入新的MySQL密码
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) :Y #输入Y确认使用已设置的密码。
#删除匿名用户。
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) :Y #输入Y删除MySQL默认的匿名用户。
Success.
#禁止root账号远程登录。
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :Y #输入Y禁止root远程登录。
Success.
#删除test库以及对test库的访问权限。
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :Y #输入Y删除test库以及对test库的访问权限。
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
#重新加载授权表。
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) :Y #输入Y重新加载授权表。
Success.
All done!
或者
#登录
mysql -u root -p
#将密码规则设置为LOW，就可以使用纯数字纯字母密码
set global validate_password_policy=0;
# 把复杂密码的安全校验去掉 最低位数为4位
#set global validate_password_length=4;
#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
#检查mysql服务状态：
systemctl status mysql
# 刷新权限
mysql> flush privileges;
#退出
mysql> exit
#命令重启mysql：
systemctl restart mysql
- 开放远程访问
#由于我前面禁用了root，所以此处我新建一个账号
mysql -uroot -p
mysql> create user 'dmsTest'@'%' identified by '123456'; #创建数据库用户dmsTest,并授予远程连接权限。
mysql> grant all privileges on *.* to 'dmsTest'@'%'; #为dmsTest用户授权数据库所有权限。
mysql> flush privileges; #刷新权限。
最后记得开放下 3306端口，完事
3.操作权限控制
- 利用“REVOKE ALL ON *.*”取消全局权限；
- 利用“REVOKE ALL ON 数据库名.*”取消数据库级别的权限；
- 利用“REVOKE ALL ON 数据库名.表名”取消表级别的权限。
例如果想撤销账号的某个权限：
REVOKE DROP on *.* FROM ‘dmsTest’@‘%’;
其中 DROP是其中一个权限（删表），具体可以在客户端看
扫一扫
281
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
