国内的数据库:TIDB、人大金仓、达梦
国外的:MySQL,oracle,SQL server,INFOMIX,DB2
数据库的类型:关系型(表的字段之间可以有联系、表和表之间可以有关系):MySQL,oracle,SQL server
非关系型(No-SQL) :redis,mongodb,TIDB,TSDB(一般是key-value型)
安装
安装步骤:
官方文档: https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
1,去官网下载二进制压缩包
二,解压
tar xf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz
3,新建存放数据的目录
4,进入/usr/local/mysql/bin目录
cd /usr/local/mysql/bin/5,初始化操作
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql
6,让mysql支持ssl方式登录的设置
./mysql_ssl_rsa_setup --datadir=/data/mysql/7, 修改PATH变量,加入mysql bin目录的路径
#临时修改PATH变量的值
export PATH=/usr/local/mysql/bin/:$PATH
#重新启动linux系统后也生效,永久修改(如果想要在虚拟目录结构中的任何位置执行某个程序,办法是把这个程序所在的目录添加到 PATH 环境变量中)
echo 'PATH=/usr/local/mysql/bin:$PATH' >>/root/.bashrc8,复制support-files里的mysql.server文件到/etc/init.d/目录下叫mysqld
cp ../support-files/mysql.server /etc/init.d/mysqld9,修改/etc/init.d/mysqld脚本文件里的datadir目录的值
sed -i '70c datadir=/data/mysql' /etc/init.d/mysqld10,#生成/etc/my.cnf配置文件
之后启动mysqld:service mysqld start
11,将mysqld添加到linux系统里服务管理名单里
/sbin/chkconfig --add mysqld
#设置mysqld服务开机启动
/sbin/chkconfig mysqld on12,登录验证
mysql -uroot -p'之前说一定要记得的那串文字'
一些概念
DBMS(database management system)管理数据的软件;mysql就是一个DBMS
进程关系
使用pstree命令可以看到mysqld_safe是父进程,mysqld是子进程,当mysqld挂掉后,mysqld_safe会再起mysqld子进程,而当mysqld_safe挂掉后,mysqld仍然能运行,但是再删除mysqld之后 就整个挂掉了,
配置文件的讲解
mysql的配置文件是/etc/my.cnf
查看mysql里所有的参数:show variables;
[client]代表客户端默认设置内容; [mysql]代表我们使用mysql命令登录mysql数据库时的默认设置; [mysqld]代表数据库自身的默认设置;
[client],[mysqld]都是客户端的配置;[mysql]是服务器端配置 [root@wudang-mysql-2 mysql]# cat /etc/my.cnf [mysqld_safe] #指定mysqld_safe进程的配置 #需要在mkdir /data/mysql 目录,然后使用命令chown mysql:mysql /data/mysql #因为mysql进程需要往/data/mysql目录里写东西,需要权限,不然不能往目录下写日志 [client] socket=/data/mysql/mysql.sock #对客户端的配置 ##socket是用来指定socket文件的路径,方便客户端连接找到 [mysqld] log-error=/data/mysql/mysql.log #这是指定mysql的日志文件的路径 socket=/data/mysql/mysql.sock port = 3309 open_files_limit = 8192 innodb_buffer_pool_size = 256M #mysqld是服务器端的配置 #port 指定端口号 #open_files_limit 指定mysql进程可以打开的文件数量,需要在操作系统里允许进程打开更多的文件 #使用ulimit -n 65535 #innodb_buffer_pool_size 指定缓存池的大小,提升数据性能的参数,最大可以达到物理内存的80% #破解密码用 #user=mysql #skip-grant-tables #指定mysqld的socket文件所在的路径,/data/mysql目录一定要先存在。 #指定mysqld进程的配置 [mysql] auto-rehash prompt=\\u@\\d \\R:\\m mysql> #对mysql命令执行的配置 #auto-rehash 支持tab键补齐 #prompt 登录进去后的提示符 [root@wudang-mysql-2 mysql]#
socket的分类:
1.文件socket
是实现一台电脑里的不同进程之间通信的文件
[root@wudang-mysql-2 mysql]# mysql -uroot -p'Sanchuang1234#' -S /data/mysql/mysql.sock在my.cnf配置文件里打开下面的配置,在使用mysql客户端命令连接的时候,就不需要接-S指定socket文件的路径了
[client]
socket=/data/mysql/mysql.sockmysql -uroot -p'Sanchuang123#' -S /data/mysql/mysql.sock
2.网络socket是ip+port --》格式ip:port 192.168.0.163:3309
网络中通过ip地址找到对方
是实现不同的电脑之间的不同的进程之间的通信的
[root@mysql-2 ~]# mysql -h 192.168.0.163 -P3309 -ucali -p'Sanchuang1234#'
-h 指定主机名(ip) host
-P port 端口 (大写)mysql -uroot -p'Sanchuang123#' -h 127.0.0.1 -P 3306
mysql的密码破解
本质上是修改mysql表里的user表中对应的用户的auth_string
步骤:
-----超级用户
1停止mysql进程
service mysqld stop
2修改配置文件(Innodb引擎,将内存中的数据写入磁盘,将磁盘中的数据读入内存)
3刷新服务
service mysqld start
4之后不接密码登录
5刷新权限(会加载原来没有加载的权限表,比如用户名和密码所在的表user等)
flush privileges;
6alter user 'root'@'localhost' identified by '你要重设的密码';
7退出登录,并将之前在/etc/my.cnf中添加的skip-grant-tables行注释掉
再刷新服务
---其他的普通用户
方法1:alter user 'cali'@'%' identified by 'Sanchuang123#' (cali是你要修改用户的用户名,%表示允许任意IP连接过来)
方法2:set password for 'cali'@'%' identified by '1234567';
mysql中的字符集
mysql默认字符集是latin1,不支持中文,在/etc/my.cnf中添加character-set-server=utf8来设置服务器字符集为utf8,服务器字符集决定数据库字符集,数据库字符集决定表的字符集,表的字符集决定字段的字符集
查看机器支持的字符集
show character set;
查询你正在使用哪些字符集
show variable like "%character%";
#################
创建一个库本质上就是创建一个文件夹,在你存放数据的地方新建文件夹就是新建一个库,删除也是如此
create database jiacai;--创建数据库jiacai
drop database jiacai--删除数据库jiacai
---------建表操作
help create table;--查看建表说明手册
主键:本质上也是一个字段或者多个字段组合而成,不允许出现重复的数据 复合主键:多个字段组成的主键,
------------根据已有的表建新表
-------------alter修改操作
查看alter的使用手册
向已经建好的Students表中增加一个sex字段
--------------修改字段名
----删除表
delete--删除表里的数据的时候是一行一行的删除,删除后会产生二进制日志,可以根据日志恢复 truncate--删除数据特别快,整个表锁定表操作然后删除,不会记录二进制日记,不能恢复
mysql中的数据类型
一、数值类型
length()查询存储空间的大小 char_length()查看字符的长度--》字符的个数
二、日期类型
三、字符串
char与varchar
时间戳用法:now()获取当前时间戳
字段属性
not null 不能为空 signed 有符号整数 unsigned 无符号整数 auto_increment 自增 zerofill 拿0去填充
操作mysql里的变量--使用两个@@符号
操作自定义变量
----
default设置默认值
----comment给表加注释
----unique唯一键(设置为唯一键之后不允许重复)
主键和unique的 两者都有唯一性,但是主键不能为空,unique可以为空
空值与NULL 设置唯一键之后空值只能出现一次,NULL可以出现多次
存储引擎
存储引擎是mysql里面的一个组件,主要作用将内存中的数据存储到磁盘上和将磁盘中的数据加载到缓存池中
查看当前mysql支持的引擎 show engines;
- InnoDB:表名.frm表结构,表名.ibd索引和数据,特性是 Supports transactions, row-level locking, and foreign keys
- MyISAM:表名.frm表结构,表名.MYI索引文件,表名.MYD数据文件
MyISAM和InnoDB的区别: .InnoDB支持事务,而MyISAM不支持事务。 .InnoDB支持外键,而MyISAM不支持外键。 .InnoDB是行锁,而MyISAM是表锁(每次更新增加删除都会锁住表)。 .InnoDB是聚簇索引,必须要有主键,一定会基于主键查询,但是辅助索引就会查询两次。MyISAM是非聚簇索引,索引和数据是分离的,索引里保存的是数据地址的指针,主键索引和辅助索引是分开的。 .InnoDB不存储表的行数,所以select count( * )的时候会全表查询。而MyISAM会存放表的行数,select count(*)的时候会查的很快。
- csv:存储数据时,用逗号分隔各个数据项。CSV引擎可以将普通的CSV文件作为MySQL的表来处理,但不支持索引。其可以作为一种数据交换的机制,存储的数据直接可以在操作系统里,用文本编辑器或者Excel读取。对于数据的快速导入、导出是有明显优势的。创建CSV表时,服务器会创建一个纯文本数据文件,其名称以表名开头并带有.CSV 扩展名。当你将数据存储到表中时,存储引擎将其以逗号分隔值格式保存到数据文件中。
- memory置于内存的表。Memory采用的逻辑介质是内存,响应速度很快,但是当mysqld守护进程崩溃的时候数据会丢失,数据保存在内存里,特别适用于适用临时表的场景
mysql默认的四个库
mysql安装好后有4个默认的库 information_schema 信息库:数据字典库 information_schema是一个信息数据库,它保存着关于MySQL服务器所维护的所有其他数据库的信息。(如数据库名,数据库的表,表栏的数据类型与访问权限等 数据字典--》元数据:描述其他数据的数据 中央情报局(统计局):收录了整个MySQL里的信息(能统计的一切信息) show engiens ; show character set; performance_schema 性能架构库 主要用于收集数据库服务器性能参数。 执行某些操作会有性能相关的参数 sys: MySQL系统 Sys库所有的数据源来自:performance_schema。目标是把performance_schema的把复杂度降低,让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。 mysql :存放的是MySQL程序相关的表:登录用户表、时间相关表、db、权限表 mysql的核心数据库,类似于sql server中的master表,主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
用户和权限管理
------查看创建用户使用手册
help create user
------创建用户
-----授权
对某些库、表有权限(局部权限)就写在db表里,如果对所有库、表有权限就写在user表中
root用户创建了一个yangzhi用户 root@(none) 20:49 mysql>create user 'yangzhi'@'%' identified by '123456'; Query OK, 0 rows affected (0.01 sec) root用户授予无论从哪登陆过来的yangzhi对所有库所有表有所有权限及授权权限 root@(none) 20:49 mysql>grant all on *.* to 'yangzhi'@'%' with grant option; Query OK, 0 rows affected (0.00 sec)
登录yangzhi用户 [root@sc-mysql ~]# mysql -uyangzhi -p'123456'; yangzhi用户创建vivi用户 yangzhi@(none) 21:04 mysql>create user 'vivi'@'%'identified by '123456'; Query OK, 0 rows affected (0.00 sec) 授予vivi也有授权权限 yangzhi@(none) 21:04 mysql>grant all on *.* to 'vivi'@'%' with grant option; Query OK, 0 rows affected (0.00 sec) 撤销yangzhi的所有权利 yangzhi@(none) 20:54 mysql>revoke all on *.* from 'yangzhi'@'%'; Query OK, 0 rows affected (0.00 sec)
-------insert
select database();可以知道当前在那个库里
有子查询优先子查询
sum()函数求和 select sum(amount) from players;
count()函数用来统计有多少行的数据 select count(*) from PLAYERS;--括号里面可以放字段名
-------update
update中可以使用order by子句,要求以排序的顺序来一次更新行, select * from PENALTIES order by amount asc;--升序 select * from PENALTIES order by amount desc;--降序
replace--当数据已存在的时候replace会把他替换掉,主键冲突的时候insert插入不了数据
----delete
!!!但是在where子查询中不允许访问要删除的行
所以正确做法是复制一张表,现在复制的表里查询
事务
什么是事务?
一个数据库事务由一条或者多条sql语句组成,形成了一个逻辑的工作单元,这些sql要不全部执行成功,要不全部执行失败(一件事情完成一个任务,里面包含很多的操作)
对于DDL和DCL事务@@autocommit对他们没有影响
日志
应用程序把工作过程中的事情记录下来保存到文件里,保存下来的就叫做日志
作用:了解程序运行是否正常,用来排错和数据分析
错误日志默认开启,存放在你数据目录下以.err结尾
修改配置文件
---------------------慢查询
作用:记录消耗时间比较长的sql语句,为数据库性能的提升提供线索,方便我们去优化
默认是10ms,超过10ms就认为慢了
默认存放在数据目录下以主机名+slow.log命名
默认没有开启慢日志
开启并设置为0.001ms
vim /etc/my.cnf
--------------------通用日志(我们敲过的所有sql语句几乎都可以记录下来)
默认不开启 (消耗磁盘空间太大,消耗cpu,内存)
------------二进制日志(存放的是二进制数据)
默认不开启
一个二进制文件记录了整个mysql进程里所有库的操作
会产生新的日志文件的情况 1,service mysqld restart 2,当日志到达最大值1G的时候也会产生新日志 3,flush logs
使用mysqlbinlog工具查看二进制日志
清除、删除所有二进制文件(一次性) reset master 将指定的日志文件之前的日志删除 purge binary logs to '日志名'; 将指定事件之前的日志清理 purge binary logs before '2018-02-01 12:00:00';
---------二进制日志格式
①row level:记录操作的每一行数据 --》表里的哪些行的数据发生了变化,默认格式
②statement level:记录用户输入的SQL语句
③mixed level:混合前两种
--------------redo和undo
https://zhuanlan.zhihu.com/p/410270379
----------------备份与还原
https://dev.mysql.com/doc/refman/5.7/en/backup-and-recovery.html
灾备:
热备(online):mysqld服务是运行的情况下去备份,
冷备(offline):mysql服务需停止,然后去拷贝数据
备份方式:
全备(全部备份):数据恢复方便,但是消耗空间大,时间长
增量备份:备好处是每次备份需要备份的数据较少,耗时较短,占用的空间较小;坏处是数据恢复比较麻烦
------------------------ Using mysqldump for Backups
https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html
-------------------------还原
--------查看当前记录的二进制日志
show master status;
主从复制
replay log中继日志
是主服务器主动通知从服务器来拉取二进制文件,还是从服务器每隔一段时间来拿二进制日志? --是主服务器主动通知从服务器来拉去二进制文件 master上有个log dump thread会通知从服务器来拉取数据
##################搭建一个主从复制的MySQL集群
项目环境:2台2c/4的服务器,centos7.9+mysql5.7.33
项目描述:为了提升MySQL的并发能力构建一个简单的MySQL集群,实现读写分离,大大提升MySQL集群的性能;同时为了深入学习MySQL的整体部署和对集群架构的理解
项目步骤:
①准备好两台安装好的centos7系统,使用脚本一键安装二进制版本mysql,并且设置好my.cnf配置
②在master服务器上开启二进制日志、配置server_id,从服务器上也配置好server_id
③在master服务器上创建一个用户,允许他从slave服务器上过来拿数据
create user 'lanqing'@'192.168.173.140' identified by'123456';
④在master上做全备,导出数据到slave上,保持master和slave的数据一致
主服务器上导出数据 mysqldump -uroot -p'beautiful888' --all-database >/backup/all_db.sql 远程拷贝到从服务器上 scp /backup/all_db.sql root@192.168.173.140:/root 从服务器导入数据 mysql -uroot -p'beautiful888' <all_db.sql
⑤在slave上配置去master上拉去二进制日志的用户名和密码以及日志文件的名称和位置
查看日志的记录位置 show master stauts 刷新日志 flush logs; 再次查看日志记录 show master status 之后 root@(none) 17:06 mysql>CHANGE MASTER TO MASTER_HOST='192.168.173.139',(master的ip) -> MASTER_USER='lanqing',(前面创建的允许slave过来拿数据的用户) -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='sc-mysql-bin.000004',(记录的日志文件) -> MASTER_LOG_POS=154;(记录的日志开始位置) Query OK, 0 rows affected, 2 warnings (0.02 sec
⑥master、slave上都关闭防火墙和selinux
⑦在slave服务器上启动slave服务,查看IO线程和SQL线程是否正常启动
root@(none) 17:07 mysql>show slave status\G;--查看slave的状态 root@(none) 17:10 mysql>start slave;--开启slave
⑧验证主从复制的效果
主服务器新建一个库,从服务器上show databases;也能查看到