mysql基础
一.mysql体系结构
二.简介
1.什么是mysql ?
MySQL是最受欢迎的开源SQL数据库管理系统,由Oracle Corporation开发,分发和支持MySQL网站([http://www.mysql.com/]{.underline})提供有关MySQL软件的最新信息。
2.MySQL是一个数据库管理系统。
数据库是数据的结构化集合。从简单的购物清单到图片库,或者企业网络中的大量信息,它都可以是任何东西。要添加,访问和处理存储在计算机数据库中的数据,就需要一个数据据库管理系统 mysql就是其中之一
3.mysql 数据库的类型?
MySQL数据库是关系型的。
关系数据库将数据存储在单独的表中,而不是将所有数据放在一个大的库房中。数据库结构被组织成针对速度优化的物理文件。逻辑模型具有数据库,表,视图,行和列等对象,可提供灵活的编程环境
存放数据的仓库,数据存放在一张张表中 表与表之间存在一定的关系 因此 就称之为关系型数据库
4.MySQL软件是开源的。
开源意味着任何人都可以使用和修改该软件。任何人都可以从互联网上下载MySQL软件并使用它而无需支付任何费用
5.数据库技术构成
数据库系统 DBS
A. 数据库管理系统(DataBase Management System, DBMS):
SQL(RDS): ORACLE、Oracle MySQL、MariaDB、Percona server、DB2 NoSQL: Redis、MongoDB、Memcache
B. DBA
SQL语言(数据库结构化查询语言)
A. DDL语句 数据库定义语言: 数据库、表、视图、索引、存储过程、函数, CREATE DROP ALTER //开发人员
B. DML语句 数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE //开发人员
C. DQL语句 数据库查询语言: 查询数据 SELECT
D. DCL语句 数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE
数据访问技术
A. ODBC PHP <.php>
B. JDBC JAVA <.jsp>
6.常见的关系型数据库和非关系统型数据库关系型:
[MariaDB]{.underline} [DB2]{.underline}
非关系型:
[Memcached]{.underline} [MongoDB]{.underline}
7.mysql的默认端口: 3306
三.mysql5.7安装
1.mariadb安装
- 清理环境
# rpm -qa | grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
#rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps #rpm -qa | grep mysql
# id mysql
id: mysql: no such user
# userdel -r mysql 有mysql用户就删除没有就不管
- 查看本地yum源是否有mariadb安装包
# ls /mnt/iso/Packages/ | grep mariadb mariadb-5.5.60-1.el7_5.x86_64.rpm mariadb-bench-5.5.60-1.el7_5.x86_64.rpm mariadb-devel-5.5.60-1.el7_5.x86_64.rpm mariadb-libs-5.5.60-1.el7_5.x86_64.rpm mariadb-server-5.5.60-1.el7_5.x86_64.rpm mariadb-test-5.5.60-1.el7_5.x86_64.rpm
- 安装mariadb
yum -y install mariadb*
- 启动数据库,查看端口3306 #systemctl start mariadb
ss -tanlp | grep mysql
LISTEN 0 50 *:3306 *:* users:(("mysqld",pid=7885,fd=13))
- 初始化数据库设置密码
# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.
Set root password? [Y/n] y (给数据库设置密码) New password:
Re-enter new password:
Password updated successfully! Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB 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? [Y/\]
... Success!
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? [Y/n]
... Success!
By default, MariaDB 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? [Y/n]
- 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? [Y/n]
... Success! Cleaning up...
All done! If you\'ve completed all of the above steps, your MariaDB installation should now be secure.
- 进入数据库
# mysql -u root -p1 -p 初始化数据库时的密码
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 10
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]>
- 退出数据库
MariaDB [(none)]> exit Bye
至此数据库安装成功
=======================================================================
2.数据库配置文件
# cat /etc/my.cnf
[mysqld] datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
# Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the # instructions in [[http://fedoraproject.org/wiki/Systemd]{.underline}](http://fedoraproject.org/wiki/Systemd)
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#!includedir /etc/my.cnf.d
3.修改密码
# mysql -p1
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 19
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c