MySQL基础学习① 进入MySQL基础学习，安装、启动、停止、访问、卸载MySQL

1、什么是MySql？

截取自百度百科 。

互联网时代，我们都是在跟数据打交道，例如：

• 天猫淘宝上热卖产品，个人历史订单
• 微信朋友圈聊天记录
• 手机本地通信录
• …

可以毫不夸张地说，只要涉及到互联网，始终离不开数据。

那么数据就得有地方去存放，并且是持久化存放（总不能全部数据直接加载在内存中，然后断电GG）。

并且为了快速查询到我们需要的数据（一些大系统数据库至少过T，总不能全库扫描），那么总得有一个系统去帮我们做这件事情。

那么就得有一个系统能满足以下两点要求：

• 持久化存储（硬盘文件系统）
• 快速查询目标数据（也叫做快速索引，那么就意味着我们的数据肯定处于某种有效的组织中 —— 从算法角度来说，就是数据结构，举个例子：教育局为了管理学生资料，给每一个学生分配一个唯一的学号，通过学号来找到每个学生）

能满足以上两个条件的系统就是我们常说的数据库管理系统（DataBase Manager System，DBMS），在DBMS下管理着数据库（DataBase，DB），最终我们通过SQL（
结构化查询语言
Structured query language）来对数据库进行CURD（增删改查）。

注意：我们常说数据库，其实指的是整个数据库管理系统，包括DBMD和DB。

在众多的数据库管理系统中，最受大众喜欢也是目前绝大部分公司都在使用的就是MySQL（开源、免费，意味着接入成本低），这也是我们为什么要学习它的原因。

2、安装MySql

Mysql采用C-S架构，也就是Client-Server，所以我们首先需要安装 MySql Server。

2.1 下载地址

2.2.1 下载地址1 —— 可以下载免安装版本

https://dev.mysql.com/downloads/mysql/

习惯性我比较喜欢后退两个版本（说白了就是求稳）。
比如说，我这里下载 5.6.49版本（这个是免安装版本）：

点击Download

静静等待下载成功。

如果你还是下载不了，那么我也上传到了CSDN，免积分下载，请点击

下载地址 免积分下载

2.1.2 下载地址2 —— 下载MSI文件（推荐这种方式。。。）

http://mirrors.sohu.com/mysql/MySQL-5.5/?C=S&O=A

我这里下载了 mysql-5.5.60-winx64.msi 这个文件、
如果你还是下载不了，那么我也上传到了CSDN，免积分下载，请点击

下载地址 免积分下载

2.2 推荐安装步骤 —— 针对下载方式2

2.2.1 安装 mysql-5.5.60-winx64.msi —— 图片讲解，认真看好每一步

• 点击 mysql-5.5.60-winx64.msi ，弹出界面，点击Next
  

• 勾选 I accept the terms in the License Agreement，点击 Next
  

• 选择需要的构建类型，这里我们选择custom
  
  
  点击 Next
  

• 点击 install，进行安装
  
  安装成功
  
  到这一步我们仅仅是安装好了环境，但是还没有进行配置。

我们来看看一下安装目录 C:\Program Files\MySQL\MySQL Server 5.5：

一般熟悉win应用程序的人来说，我们会更关注bin目录（因为一般执行命令都是在里面）：

先了解到这里，点击 Finish，进行下一步的配置。

2.2.2 配置MySQL

• 点击 Finish，正常情况会弹出这个页面
  

如果不小心关闭了，那么也可以通过bin命令来调起：

• 点击 Next，选择Detailed Configuration
  

• 选择服务类型
  

• 选择数据库类型
  
  
  点击Next

• 设置并发数，默认点击Next
  

• 设置MySql的端口号，默认即可，点击next
  

• 设置默认字符集
  

• 设置windows上的选项
  

• 配置MySql Server的安全选项（账号密码）
  

• 配置完毕，点击执行，等待配置成功
  
  

2.2.3 查看配置（可忽略）

使用记事本打开，可以看到以下内容：

# 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=utf8


# 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=utf8

# 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 ***


# 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

里面的内容就是我们刚才配置好的。

2.3 免安装步骤 —— 针对下载方式1（可忽略）

ZIP 文件下载下来后，找个路径解压一下，我这里放到D盘：

2.3.1 配置环境变量 Path

D:\mysql-5.6.49-winx64\bin

2.3.2 新增data目录 + my.ini配置文件

修改配置文件内容为：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# 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.
basedir = "D:\mysql-5.6.49-winx64"

datadir = "D:\mysql-5.6.49-winx64\data"

port = 3307

character-set-server=utf8
# server_id = .....


# 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 

2.3.3 执行初始化命令

mysqld --initialize --user=mysql --console

2.3.4 进行MySQL服务安装

mysqld –install mysql

2.3.5 启动服务

net start mysql

其他操作流程 可以参考下面的。

3、启动、停止MySql

3.1 方法1 —— 任务管理器（服务）

去到Windows任务管理器看看有没有对应服务名字，比如我配置的时候命名为MySql2020
我当前状态是正在运行，那么停止它可以通过右键 “停止服务”或者 “启动服务”

3.2 方法2 —— cmd命令

输入cmd命令，然后以管理员身份运行：

然后分别测试输入以下两条命令：

• 启动命令 net start mysql服务名
  比如我这里 是 net start mysql2020

• 停止命令 net stop mysql服务名
  比如我这里 是 net stop mysql2020

4、访问MySql

因为Mysql基于c/s架构，我们需要通过client端去访问服务。

4.1 访问方式1 —— MySqL自带的命令行工具

缺点：

• 只支持Root用户

输入：exit或者 ctrl + c
表示退出访问。

4.2 访问方式2 —— Window Cmd命令

命令：

mysql -h localhost -P 3306 -u root -p

• mysql 表示命令
• -h localhost 表示主机，目前我们是本地
• -P 3306 服务器端口
• -u root 账号
• -p 密码

输入：exit或者 ctrl + c
表示退出访问。

4.3 访问方式3 —— 利用第三方客户端（推荐，也是目前开发人员习惯方式）

比如博主这里利用 Navicat Premium 软件：

创建连接：

到这里也是可以访问成功的。

断开连接
表示退出访问。

5、卸载MySQL（理解一下完全卸载）

如果想卸载MySQL怎么办：

去到控制面板，点击卸载

然后删除安装目录：

再删除 C:\ProgramData\MySQL 目录

6、总结

作为MySql的开篇，主要讲解工具的安装以及简单使用，后面会陆续更新新的知识点，敬请关注。觉得可以，请点赞，谢谢支持。