MySQL入门及安装什么是数据
数据(data)是事实或观察的结果，是对客观事物的逻辑归纳，是用于表示客观事物的未经加工的的原始素材。数据可以是连续的值，比如声音、图像，称为模拟数据。也可以是离散的，如符号、文字，称为数字数据。在计算机系统中，数据以二进制信息单元0,1的形式表示。
数据的定义: 数据是指对客观事件进行记录并可以鉴别的符号，是对客观事物的性质、状态以及相互关系等进行记载的物理符号或这些物理符号的组合。它是可识别的、抽象的符号。
什么是数据库管理系统
DBMS（database management system）
数据库管理系统分类关系型数据库（RDBMS）以多张二维表的方式来存储，又给多张表建立了一定的关系（关系型数据库）非关系型数据库（NoSQL）左边rdbms右边nosql 很多以json格式进行存储数据的（mogodb）key:value关系型数据库和非关系型数据库对比功能对比关系型数据库非关系型数据库
强大的查询功能√×强一致性√×二级索引√×灵活模式×√扩展性×√性能×√关系型数据库（RDBMS）的特点：
二维表典型产品Oracle传统企业，MySQL互联网企业数据存取是通过SQL（Structured Query Language结构化查询语言）最大特点数据安全性方面强（ACID）
非关系型数据库（（NoSQL：Not only SQL）的特点：
不是否定关系型数据库，而是做关系型数据库的补充。
时代特点对比:
web1.0时代企业提供内容，用户浏览，所以关系型数据库够用，并发并不高，所以不需要nosqlweb2.0时代核心是企业提供平台，用户参与提供内容，这个时代关系型数据库无法满足需求了2003NoSQL出现memcache的诞生，关注的点是性能，但是针对安全性能关注比较低，随着安全性能需求不断提升，所以有了redisredis的特点依然高性能高并发数据持久化功能支持多数据类型，主从复制和集群管理不再使用SQL了
NoSQL特性总览
1.不是否定关系型数据库，而是做关系型数据库的补充，现在也有部分替代的趋势mongodb2.关注高性能，高并发，灵活性，忽略和上述无关的功能3.现在也在提升安全性和使用功能4.典型产品：redis（持久化缓存，两个半天）、MongoDB（最接近关系型数据库的NoSQL）、memcached5.管理不适用SQL管理，而是用一些特殊的API或数据接口
NoSQL的分类、特点、典型产品
键值（KV）存储：memcached、redis列存储（column-oriented）：HBASE（新浪、360）Cassandra（200台服务器集群）文档数据库（document-oriented）：MongoDB（最接近关系型数据库的NoSQL）图形存储（Graph）：Neo4jMySQL安装yum源码二进制版本选型
5.6：GA 6-12个月，小版本号是偶数版
5.7：GA 6-12个月，小版本号是偶数版，必须是5.7.20以上版本（MGR）
MySQL官网：https://www.mysql.com/
MySQL—5.6源码安装## 0.安装MySQL依赖[root@db02 ~]# yum install -y cmake zlib-devel openssl-devel autoconf ncurses-devel## 1.下载MySQL5.6[root@db02 ~]# wget wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50.tar.gz## 2.解压[root@db02 ~]# tar xf mysql-5.6.50.tar.gz## 进入解压好的目录[root@db02 ~]# cd mysql-5.6.50/## 3.生成# MySQL的安装目录cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.50 \#数据存放位置-DMYSQL_DATADIR=/application/mysql-5.6.50/data \#socket文件存放位置-DMYSQL_UNIX_ADDR=/application/mysql-5.6.50/tmp/mysql.sock \#使用utf8字符集-DDEFAULT_CHARSET=utf8 \#校验规则-DDEFAULT_COLLATION=utf8_general_ci \#使用其他额外的字符集-DWITH_EXTRA_CHARSETS=all \#支持的存储引擎-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \#禁用的存储引擎-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \#启用zlib库支持（zib、gzib相关）-DWITH_ZLIB=bundled \#启用SSL库支持（安全套接层）-DWITH_SSL=system \#启用本地数据导入支持-DENABLED_LOCAL_INFILE=1 \#编译嵌入式服务器支持-DWITH_EMBEDDED_SERVER=1 \# mysql5.6支持了google的c++mock框架了，允许下载，否则会安装报错。-DENABLE_DOWNLOADS=1 \#禁用debug（默认为禁用）-DWITH_DEBUG=0[root@db02 mysql-5.6.50]# cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.50 \-DMYSQL_DATADIR=/application/mysql-5.6.50/data \-DMYSQL_UNIX_ADDR=/application/mysql-5.6.50/tmp/mysql.sock \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \-DWITH_ZLIB=bundled \-DWITH_SSL=system \-DENABLED_LOCAL_INFILE=1 \-DWITH_EMBEDDED_SERVER=1 \-DENABLE_DOWNLOADS=1 \-DWITH_DEBUG=0## 4.编译 && 安装[root@db02 mysql-5.6.50]# make && make install———————————————————-## 5.做软链接[root@db02 mysql-5.6.50]# ln -s /application/mysql-5.6.50 /application/mysql### 目录结构[root@db02 mysql-5.6.50]# cd /application/mysql[root@db02 mysql]# lltotal 220drwxr-xr-x 2 root root 4096 Aug 2 17:19 bindrwxr-xr-x 3 root root 18 Aug 2 17:19 datadrwxr-xr-x 2 root root 55 Aug 2 17:19 docsdrwxr-xr-x 3 root root 4096 Aug 2 17:19 includedrwxr-xr-x 3 root root 291 Aug 2 17:19 lib-rw-r–r– 1 root root 198041 Sep 23 2020 LICENSEdrwxr-xr-x 4 root root 30 Aug 2 17:19 mandrwxr-xr-x 10 root root 4096 Aug 2 17:19 mysql-test-rw-r–r– 1 root root 587 Sep 23 2020 READMEdrwxr-xr-x 2 root root 30 Aug 2 17:19 scriptsdrwxr-xr-x 28 root root 4096 Aug 2 17:19 sharedrwxr-xr-x 4 root root 4096 Aug 2 17:19 sql-benchdrwxr-xr-x 2 root root 136 Aug 2 17:19 support-files## 6.拷贝配置文件[root@db02 mysql]# cp support-files/my-default.cnf /etc/my.cnfcp: overwrite ‘/etc/my.cnf’? yes## 7.拷贝启动脚本[root@db02 mysql]# cp support-files/mysql.server /etc/init.d/mysqld## 8.做初始化[root@db02 mysql]# cd scripts/[root@db02 scripts]# ./mysql_install_db –basedir=/application/mysql –datadir=/application/mysql/data## 9.创建socket存放目录[root@db02 scripts]# mkdir /application/mysql-5.6.50/tmp## 10.创建MySQL用户[root@db02 scripts]# useradd mysql -s /sbin/nologin -M## 11.授权MySQL安装目录[root@db02 scripts]# chown mysql.mysql -R /application/mysql-5.6.50/## 12.启动数据库[root@db02 scripts]# /etc/init.d/mysqld startStarting MySQL.Logging to ‘/application/mysql-5.6.50/data/db02.err’. SUCCESS! ## 13.添加环境变量[root@db02 scripts]# echo ‘PATH="/application/mysql/bin:$PATH"’ >> /etc/profile.d/mysql.sh## 14.加载环境变量[root@db02 scripts]# source /etc/profile## 15.连接数据库[root@db02 scripts]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.50 Source distributionCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql> MySQL—5.6二进制安装
## 0.安装依赖[root@db02 ~]# yum install -y autoconf libaio-devel## 1.下载[root@db02 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz## 2.解压[root@db02 ~]# tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz## 3.创建安装目录[root@db02 ~]# mkdir /application## 4.移动到安装目录[root@db02 ~]# mv mysql-5.6.50-linux-glibc2.12-x86_64 /application/mysql-5.6.50## 5.做软链接[root@db02 ~]# ln -s /application/mysql-5.6.50 /application/mysql## 6.拷贝配置文件[root@db02 ~]# cd /application/mysql[root@db02 mysql]# cp support-files/my-default.cnf /etc/my.cnfcp: overwrite ‘/etc/my.cnf’? yes## 7.拷贝脚本[root@db02 mysql]# cp support-files/mysql.server /etc/init.d/mysqld## 8.创建MySQL用户[root@db02 mysql]# useradd mysql -s /sbin/nologin -M## 9.初始化[root@db02 mysql]# cd scripts/[root@db02 scripts]# ./mysql_install_db –basedir=/application/mysql –datadir=/application/mysql/data## 10.替换脚本中的/usr/local为/application[root@db02 scripts]# vim /etc/init.d/mysqld:%s#/usr/local#/application#g## 11.授权[root@db02 scripts]# chown -R mysql.mysql /application/mysql-5.6.50/## 12.添加环境变量[root@db02 scripts]# echo ‘PATH="/application/mysql/bin:$PATH"’ >> /etc/profile.d/mysql.sh## 13.加载环境变量[root@db02 scripts]# source /etc/profile## 14.启动数据库[root@db02 scripts]# /etc/init.d/mysqld startStarting MySQL.Logging to ‘/application/mysql/data/db02.err’. SUCCESS! ## 15.连接数据库[root@db02 scripts]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.50 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql> MySQL—5.7源码安装
## 0.安装MySQL依赖[root@db03 ~]# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf openssl openssl-devel ## 创建目录[root@db03 tools]# mkdir /opt/tools## 1.下载MySQL5.7[root@db03 tools]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.36.tar.gz## 2.解压[root@db03 tools]# tar xf mysql-boost-5.7.36.tar.gz## 进入解压好的目录[root@db02 tools]# cd mysql-5.7.36/## 3.生成cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.7.36 \-DMYSQL_DATADIR=/application/mysql-5.7.36/data \-DMYSQL_UNIX_ADDR=/application/mysql-5.7.36/tmp/mysql.sock \-DDOWNLOAD_BOOST=1 \-DWITH_BOOST=/opt/tools/mysql-5.7.36/boost \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \-DWITH_ZLIB=bundled \-DWITH_SSL=system \-DENABLED_LOCAL_INFILE=1 \-DWITH_EMBEDDED_SERVER=1 \-DENABLE_DOWNLOADS=1 \-DWITH_DEBUG=0## 4.编译 && 安装[root@db03 mysql-5.7.36]# make && make install## 5.做软链接[root@db04 local]# ln -s /application/mysql-5.7.36-linux-glibc2.12-x86_64/ /application/mysql## 6.创建数据库用户[root@db03 mysql-5.7.36]# useradd mysql -s /sbin/nologin -M## 7.授权[root@db03 mysql-5.7.36]# chown mysql:mysql -R /application/mysql-5.7.36/## 8.修改 my.cnf配置文件[root@db03 mysql-5.7.36]# vim /etc/my.cnf## 9.拷贝启动脚本[root@db03 mysql-5.7.36]# cp support-files/mysql.server /etc/init.d/mysqld## 10.初始化[root@db03 mysql]# mkdir -p data[root@db03 mysql]# mkdir -p tmp[root@db03 mysql]# cd bin[root@db03 bin]# ./mysqld –initialize –user=mysql –basedir=/application/mysql –datadir=/application/mysql/data## 11.重新授权[root@db03 bin]# chown mysql:mysql -R /application/mysql-5.7.36/## 12.启动数据库[root@db03 mysql-5.7.36]# chmod +x /etc/init.d/mysqld [root@db03 mysql-5.7.36]# /etc/init.d/mysqld startStarting MySQL.Logging to ‘/application/mysql-5.7.36/data/db03.err’. SUCCESS! ## 13.添加环境变量[root@db03 mysql-5.7.36]# echo ‘PATH="/application/mysql/bin:$PATH"’ >> /etc/profile.d/mysql.sh## 14.加载环境变量[root@db03 mysql-5.7.36]# source /etc/profile## 15.连接数据库[root@db03 mysql-5.7.36]# mysql -uroot -pEnter password: ## 16.修改密码mysql> alter user root@localhost identified by ‘123456’;Query OK, 0 rows affected (0.00 sec)## 17.退出重新登录mysql> \qBye[root@db03 mysql-5.7.36]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.36 Source distributionCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql> MySQL—5.7二进制安装
## 0.安装依赖[root@db04 ~]# yum install -y autoconf libaio-devel## 1.下载[root@db04 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz## 2.解压[root@db04 ~]# tar xf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz## 3.创建安装目录[root@db04 ~]# mkdir /application## 4.移动到安装目录[root@db04 ~]# mv mysql-5.7.36-linux-glibc2.12-x86_64 /application/## 5.做软链接[root@db04 ~]# ln -s /application/mysql-5.7.36-linux-glibc2.12-x86_64/ /application/mysql## 6.修改配置文件[root@db04 ~]# vim /etc/my.cnf[mysqld]basedir=/application/mysqldatadir=/application/mysql/data## 7.拷贝脚本[root@db04 ~]# cd /application/mysql[root@db04 mysql]# cp support-files/mysql.server /etc/init.d/mysqld## 8.创建MySQL用户[root@db04 mysql]# useradd mysql -s /sbin/nologin -M## 9.初始化[root@db04 mysql]# mkdir -p tmp[root@db04 mysql]# cd bin/### 带密码[root@db04 bin]# ./mysqld –initialize –user=mysql –basedir=/application/mysql –datadir=/application/mysql/data### 不带密码[root@db04 bin]# ./mysqld –initialize-insecure –user=mysql –basedir=/application/mysql –datadir=/application/mysql/data## 10.替换脚本中的/usr/local为/application[root@db04 bin]# vim /etc/init.d/mysqld:%s#/usr/local#/application#g## 11.授权[root@db04 bin]# chown mysql:mysql -R /application/mysql-5.7.36-linux-glibc2.12-x86_64## 12.启动数据库[root@db04 ~]# /etc/init.d/mysqld startStarting MySQL.Logging to ‘/application/mysql-5.7.36-linux-glibc2.12-x86_64/data/db04.err’. SUCCESS! ## 13.添加环境变量[root@db04 ~]# echo ‘PATH="/application/mysql/bin:$PATH"’ >> /etc/profile.d/mysql.sh## 14.加载环境变量[root@db04 ~]# source /etc/profile## 15.连接数据库[root@db04 ~]# mysql -uroot -pEnter password:## 16.修改密码mysql> alter user root@localhost identified by ‘123456’;Query OK, 0 rows affected (0.00 sec)## 17.退出重新登录mysql> \qBye[root@db03 mysql-5.7.36]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.36 Source distributionCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql> 使用systemd管理MySQL## 编辑配置文件[root@db04 ~]# vim /usr/lib/systemd/system/mysqld.service[Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/application/mysql/bin/mysqld –defaults-file=/etc/my.cnfLimitNOFILE = 5000## 编辑配置文件[root@db04 data]# vim /etc/my.cnf[mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESbasedir=/application/mysql/datadir=/application/mysql/data~ [root@db04 ~]# systemctl start mysqld.service[root@db04 ~]# netstat -lntup | grep 3306tcp6 0 0 :::3306 :::* LISTEN 1802/mysqld [root@db04 ~]# systemctl stop mysqld.service[root@db04 ~]# netstat -lntup | grep 3306[root@db04 ~]# 误删除root用户或忘记root密码 ## 1.停mysql/etc/init.d/mysqld stop# 2.跳过授权表，只能本地连接启动数据库[root@m01 ~]# mysqld_safe –skip-grant-tables –skip-network &# 3.刷新授权表mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)# 4.创建root用户mysql> grant all on *.* to root@’localhost’ identified by ‘123’ with grant option;Query OK, 0 rows affected (0.00 sec)# 5.重启mysql[root@m01 ~]# /etc/init.d/mysqld restart 走自己的路，让人家去说吧。
