数据库介绍:
一、什么是数据库?
简单的说,数据库(Database)就是一个存放数据的仓库,这个仓库是按照一定的数据结构(数据结构是指数据的组织形式或数据之间的联系)来组织、存储的,我们可以通过数据库的多种方法来管理数据库里的数据。
关系型数据库:
关系型数据库模型是把复杂的数据结构归结为简单的二元关系(即二维表格形式),在关系型数据库中,对数据的操作几乎全部建立在一个或多个关系表格上,通过对这些关联的表哥分类、合并、连接或选取等运算赖实现数据的管理。
必记:
1.关系型数据库类似于excel这种,二维表。
2.关系型数据库使用结构化查询语言sql语句来对数据进程存取。
3.关系型数据库代表作品mysql,oracle数据库。
非关系型数据(Nosql)库种类:
1.键值(key-value)存储数据库
键值数据库就类型传统语言中使用的哈希表,可以通过key来添加、查询或者删除数据,因为使用key主键访问,所以会获得很高的性能及扩展性,
键值(key-value)数据库主要是使用一个哈希表,这个表中有一个特定的键和一个指针指向特定的数据。key/value模型对于IT系统来说的优势在于简答、易部署、高并发。
典型产品:memcached、redis、memcacheDB、Berkeley DB
2.列存储(Column-oriented)数据库
列存储数据库将数据储存在列族(column family)中,一个列族存储经常被一起查询的相关数据。
这部分数据库通常是用来应对分布式存储的海量数据,键仍然存在,但是他们的特点是指向了多个列,这些列是由列家族来安排的。
典型产品:Cassandra,HBase
3.面向文档(Document-Oriented)数据库
文档型数据库会将数据以文档的形式储存,每个文档都是自包含的数据单元,是一个系列数据项的集合,每个数据项都有一个名称与对应的值,值即可以是简单的数据类型,如字符串、数字和日期等,也可以是复杂的类型,如有序列表和关联对象。数据存储的最小单位是文档,同一个表中存储的文档属性可以是不同的,数据可以使用xml、json或者jsonb等多种形式存储。
典型产品:MongoDB、CouchDB
4.图形(Graph)数据库
图形数据库允许我们将数据以图形方式存储。
典型产品:Neo4J、InfoGrid
Mysql数据库简介:
Mysql是一种关系型数据库管理系统,关系型数据库的特点是将数据保存在不同的表中,再讲这些表放入不同的数据库中,而不是将所有数据统一放在一个大仓库里,这样设计增加了mysql的读取速度,灵活性可管理性也得到了很大提高,访问以及管理MySQL数据库的最常用标准化语言为SQL结构化查询语言。
mysql优势:
1.mysql性能卓越,服务稳定,很少出现异常宕机
2.mysql开放源代码且无版权制约,自主性及使用成本低
3.mysql历史悠久,社区及用户非常活跃,遇到问题,可以寻求帮助。
4.mysql软件体积小,安装简单,并且易于维护,安装及维护成本低
5.mysql品牌口碑效应,使得企业无需考虑就直接用之,lamp,lemp流行架构
6.mysql支持多种操作系统,提供多种API接口,支持多种开发语言,特别对流行的PHP语言有很好的支持
mysql 参数优化:
innodb_buffer_pool_size
1、单实例性能
2、实例需求
3、实例标准化
innodb_buffer_pool_instances
1、将innodb_buffer_pool划分为不同的小的buffer pool 这样就更好管理instance
2、每个instance独立的LRU、FLUSH、FREE
3、独立的mutex控制
优点:降低锁的争用,降低LRU列表的查询,提高并发。
innodb_log_file_size
设置redo 官方版本不能超过4GB
innodb_log_buffer_size
1、先写入innodb_log_buffer
2、buffer写满或事务提交,刷新数据
3、大事务频繁,增加innodb_log_buffer_size大小
减少事务刷盘的频率和次数
innodb_thread_concurrency
1、innodb_thread_concurrency=0 innodb内部自己控制
–kernel_mutex竞争
– CPU上下文切换
2、innodb_thread_concurrency 设置为CPU的核心数
innodb_io_capacity
1、innodb每秒后台进程处理IO操作的数据页上限
2、innodb_buffer_pool_size总的IO处理能力上限
3、innodb_buffer_pool_instances分割成多个内存块时,每个内存块的IO处理能力为:innodb_io_capacity/innodb_buffer_pool_instances=xxxx
innodb_max_dirty_pages_pct
1、innodb从innodb buffer中刷新脏页的比例
2、刷新脏页,产生checkpoint
3、脏页刷新innodb_max_dirty_pages_pct*innodb_io_capacity
innodb_flush_method
1、O_DSYNC:使用O_SYNC打开和刷新log文件,使用fsync()刷新数据文件
2、O_DIRECT:使用O_DIRECT打开数据文件,使用fsync()刷新日志文件和数据文件
在raid设备上,为了避免数据被innodb_buffer和raid多次cache 设置O_DIRECT方式。
innodb_file_per_table
1、不同的表空间可以灵活设置数据目录的地址
2、避免共享表空间产生的IO竞争
innodb_flush_log_at_trx_commint
0:每秒将log buffer的内容写事务日志并且刷新到磁盘
1:每个事务提交后,将log_buffer的内容写事务日志并数据磁盘
2:每个事务提交,将log_buffer内容写事务日志,但不进行数据刷盘
sync_binlog
1、刷新binlog的数目
2、双1模式,即:innodb_flush_log_at_trx_commit=1 ,sync_binlog=1 ,这样主备的数据是一致的,不会丢失数据。
安装mysql
修改配置文件:
[client] 针对客户端配置
port = 3306
socket = /tmp/mysql3306.sock
[mysqld] 针对于mysql- server配置
port = 3306
socket = /tmp/mysql3306.sock
(启动的时候自动生成)
datadir = /home/mysql3306/mysql3306
数据目录
innodb_buffer_pool_size = 1040M
Innodb缓存池大小
建议设置物理内存75%左右
32GB 建议设置 20GB
64设置50
初始化数据库(初始化mysql的一些系统表和系统文件)
/usr/local/mysql/scripts/mysql_install_db
–basedir=/usr/local/mysql
安装目录
–defaults-file=/etc/my.cnf
配置文件目录
–datadir=/home/mysql3306/mysql3306 -
数据目录
-user=mysql
指定用户
CS client server
服务端 mysqld
客户端 mysql
/usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock 连接mysql
/usr/local/mysql/bin/mysql --help mysql帮助手册
-S 指定socket文件位置
-u 登录mysql用户(root)
-p 密码 (空)
默认读取/etc/my.cnf
mysql权限设置
查看用户,主机名,密码
mysql> select user,host,password from mysql.user;
删除空用户
mysql> delete from mysql.user where user=’’;
Query OK, 2 rows affected (0.01 sec)
列出用户表
mysql> desc mysql.user;
刷新权限
mysql> flush privileges;
给用户赋权
mysql> grant all on . to root@’%’ identified by ‘root’;
mysql> grant all on . to root@‘localhost’ identified by ‘root’;
赋权本地登录权限
mysql> grant all on . to root@’%’ identified by ‘root’ with grant option;
grant all on . to jack@’%’ identified by ‘root’ with grant option;
mysql权限报错
ERROR 2003
没有连接到mysql
先检查mysqld有没有该用户
1.检查网络
2.检查防火墙
3.检查mysqld是否启动
4.检查权限
关闭数据库
pkill mysql(慎用)
最好用这样关闭数据库
/usr/local/mysql/bin/mysqladmin -uroot -proot shutdown
Mysql环境变量设置
vim /etc/profile
export MYSQL_HOME=/usr/local/mysql
PATH=
P
A
T
H
:
PATH:
PATH:MYSQL_HOME/bin
Mysql注册开机启动
vim /etc/rc.local
/usr/local/mysql/bin/mysqld_safe --user=mysql &
如果密码丢失
mysqld_safe --skip-grant-tables --user=mysql &
然后更改密码
更新数据库密码为123456 然后正常重启即可
mysql> update mysql.user set password=password(‘123456’) where user=‘root’;
Mysql 5.7的安装
1.kill掉mysql
2.清空安装目录
rm -fr /usr/local/mysql
3.清空数据目录和日志目录
rm -fr /home/mysql3306/mysql3306/*
rm -fr /home/mysql3306/logs/*
初始化:
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --basedir=/usr/local/mysql --datadir=/home/mysql3306/mysql3306/ --user=mysql
–initialize 初始化后会有随机密码
–initialize-insecure 不会生成随机密码
Oracle:多进程数据库
Mysql:单进程多线程数据库
总的体系
server层
–连接层
处理通信协议(socket tcp/ip协议)
负责协议包的解析
权限处理(root@localhost)
连接池(应用层 java/python)
JAVA C3P0
保存一定量的数据库的连接,然后应用程序在获取连接的时候,直接向连接池获取,使用完后,放回到连接池内部,而不是频繁对mysqld进程连接的获取和销毁操作,减少连接的操作压力。
线程池
可以实现线程的复用,
–sql层
处理sql相关
–sql的解析
–sql的权限
grant all . to root
. 第一个代表所有数据库
第二个代表所有表
–sql的改写
–sql的优化(CBO)
–mysql 查询缓存
就是在mysql第一次执行sql后,如果第二次执行相同的sql,如果开启查询缓存,而直接从查询缓存返回结果集。
mysql> show variables like ‘%query_cache%’;
±-----------------------------------±---------+
| Variable_name | Value |
±-----------------------------------±---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
±-----------------------------±---------+
6 rows in set (0.00 sec)
| query_cache_size | 33554432 | 查询缓存的大小
| query_cache_type | OFF | 是否开启查询缓存
有局限性,在mysql8.0中已取消。
1.sql如果有变动的话,查询缓存失效
2.查询更新的过程中,持有的锁的颗粒度飞铲大,对于mysql的影响比较严重。
使用推荐:
1.如果sql比较稳定,可以考虑使用
2.如果sql不稳定,建议关闭
推荐关闭
参数分类
只读参数
不能在线修改,只能是修改配置文件,然后重启数据库生效
实例:
mysql> set global query_cache_type=1;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it
在线参数,修改参数
可以在线修改
全局参数
全局生效的参数,只对新的session会话起作用。需要exit退出,再连接进来。
永久修改参数:
1.先set global
2.修改配置文件
当前会话生效 set
重连会话生效 set global
会话参数
0/off 代表关闭
1/on 代表开启
显示会话:
1.show full processlist;
所有修改my.cnf 参数,全部必须写到mysqld下面。
mysql> show full processlist;
±—±-----±---------------------±-------------------±--------±-----±------±----------------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±---------------------±-------------------±--------±-----±------±----------------------+
| 4 | root | 192.168.43.189:50782 | performance_schema | Sleep | 49 | | NULL |
| 5 | root | 192.168.43.189:50783 | NULL | Sleep | 47 | | NULL |
| 7 | root | localhost | NULL | Query | 0 | init | show full processlist |
±—±-----±---------------------±-------------------±--------±-----±------±----------------------+
可以查看当前mysql并发
可以在某个session执行了比较大的sql的时候,导致数据库速度慢,可以直接kill会话或者sql
kill 4; 4是ID
不想执行会话,只执行sql,不关闭会话的情况下把sql杀掉
kill query 4;
2.show processlist;
存储引擎层
负责数据的硬盘和内存交换,以及处理锁和事务
显示mysql插件
mysql> show plugins;
显示mysql搜索引擎
mysql> show engines;
InnoDB
5.6 5.7 是默认 存储引擎
简单建表命令
mysql> create table t1(id int,name varchar(1));
mysql> create table t2(id int,name varchar(11));
查看搜索引擎信息:
mysql> show create table t2;
±------±------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE t2
(
id
int(11) DEFAULT NULL,
name
varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±------±------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
ENGINE=InnoDB 说明用的是默认innodb搜索引擎
如果需要创建MyISAM表,修改下面为MyISAM
CREATE TABLE t4
(
id
int(11) DEFAULT NULL,
name
varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM;
Innodb
1.聚簇索引表(索引组织表)
2.文件的物理存储不一样为.ibd 为innodb的数据文件
3.innodb数据+索引一个文件 .frm表结构文件
4.行锁
5.支持事务
–测试:
mysql> begin;
mysql> insert into t2 values(10,‘mat’);
然后另外窗口查看为空
mysql> commit;
然后另外窗口查看有数据,成功。
6.支持外键
目前业务环境很少使用外键,外键约束
表的关联关系 ,一对一、一对多、多对多
学校表
School
scid scname scaddr scemail sc…
1 清华
2 北大
3 北航
4 人民
学生
scid stuid stname stuph stuaddr
1 001
1 002
1 003
主键是唯一标示这一行的,就好像你的身份证号一样,每个人只有一个,也是唯一能证明你是你的东西。外键是别人执行你的东西。也就是说如果一个人想和你有点关系怎么办,那就要关联起来,怎么关联起来,就通过外键啊
定义主键和外键主要是为了维护关系数据库的完整性,总结一下:
一、主键是能确定一条记录的唯一标识,比如,一条记录包括身份正号,姓名,年龄。身份证号是唯一能确定你这个人的,其他都可能有重复,所以,身份证号是主键。
外键用于与另一张表的关联。是能确定另一张表记录的字段,用于保持数据的一致性。比如,A表中的一个字段,是B表的主键,那他就可以是A表的外键。
多对多关系(三张表)
老师和学生
老师表
中间表(teaid stuid)
学生表
MyISAM
1.堆表(数据块,数据堆到一起)
2. 文件为.MYD保存数据文件 .MYI 保存索引
3.表锁 (不能用在高并发场景,因为颗粒太大了。)
4.不支持事务
mysql> insert into t3 values(10,‘mat’);
另外窗口查看有数据
mysql> select * from t3;
±-----±-----+
| id | name |
±-----±-----+
| 10 | mat |
±-----±-----+
5.不支持外键
数据库三范式:
1.字段不可拆分
2.必须有主键
3.数据不能有冗余
强制linux不记录敏感历史命令:
HISTCONTROL=ignorespace
更改mysql数据登录提示符:
1.命令行修改登录提示符:
mysql> prompt \u@simon \r:\m:\s->
PROMPT set to ‘\u@simon \r:\m:\s->’
2.配置文件修改登录提示符:
[mysql]
prompt=\u@simon \r:\m:\s->
学会使用mysql数据库帮助
查看用户权限:
mysql> show grants for ‘root’@‘192.168.10.106’;
删除和创建管理员账户:
delete from mysql.user
增加system用户并提升为超级管理员,即和root等价用户,只是名字不同
mysql> GRANT all privileges ON . TO ‘root’@‘localhost’ identified by ‘123’ WITH GRANT OPTION;
为管理员root用户设置密码 (没有密码的用户设置密码命令)
mysqladmin -u root password ‘123’
mysqladmin -u root -p ‘123’ password ‘123456’ -S /data/3306/mysql.sock
强调:以上的命令是在linux命令行,不是mysql命令行
进入mysql命令行更改用户密码
mysql> update mysql.user set authentication_string=password(‘123’) where user=‘root’ and host=‘192.168.10.106’;
提示:此法适合密码丢失后通过–skip-grant-tables 参数启动数据库后修改密码
mysql> set password=password(‘123’)
此方法适合mysql数据库刚安装之后设置用户密码
找回丢失的mysql root用户密码
1.首先停止mysql
/etc/init.d/mysql stop
2.使用–skip-grant-tables 启动mysql 忽略授权登录验证
mysql_safe --skip-grant-tables --user=mysql &
mysql -u root -p (登录时空密码)
多实例mysql启动修改丢失root密码方法
1.关闭mysql
killall mysqld
2.启动时加–skip-grant-tables参数
mysql_safe --defaults-file=/data/3306/my.cnf --skip-grant-table &
mysql -u root -p -S /data/3306/mysql.sock (登录密码为空)
修改密码:
mysql> update mysql.user set authentication_string=password(‘123’) where user=‘root’ and host=‘192.168.10.106’;
SQL:
sql语言是结构化查询语言,结构化数据。
一、数据查询语言(DQL)
DQL 全称 Data Query Language,其语句,也称为“数据检索语句”,作用是从表中获取数据,确定数据怎样在应用程序给出,关键字select 是dql用的最多的动词,其他dql常用的保留字有where,order by,group by和having,这些dql保留字常于其他类型的sql语句一起使用
二、数据操作语言(DML)
DML全称Data Manipulation Language,其语句包括动词inster,update和delete,他们分别用于添加,修改和删除表中的行(数据),也称为动作查询语言,具体语句列如
delete from mysql.user where user=‘XXX’;
三、事务处理语言(TPL)
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION,COMMIT和ROLLBACK
四、数据控制语言(DCL)
DCL全称Data Control Language ,它的语句通过GRANT或REVOKE获得许可确定单个用户和用户组对数据库对象的访问,某些RDBMS 可用GRANT或REVOKE控制对表单个列的访问。
五、数据定于语言(DDL)
全称Data definition language 其语句包括动词create 和drop 在数据库创建新表或删除表为表加入索引等,DDL包括许多与人数据库目录中获取数据有关的保留字,它也是动作查询的一部分
六、指针控制语言(CCL)
全称 CURSOR Control Language 它的语句,像DECLRE CURSOR,FETCH INTO 和 UPDATE WHERE CURRENT用于对一个或多个表单独行的操作
小结:SQL语句最常见的分类一般就是3类
DDL–数据定于语言(CREATE,ALTER,DROP)
DML–数据操纵语言(SELETE,INSTER,DELECT,UPDATE)
DCL–数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)
数据库常见的管理应用
创建数据库
命令语法:create database <数据库名> 注意数据库名开头不能用数字
在mysql默认字符集情况下建立数据库测试如下:
建立一个名字为oldboy_default的数据库
mysql> create database oldboy;
查看创建的字符集
mysql> show create database oldboy\G;
*************************** 1. row ***************************
Database: oldboy
Create Database: CREATE DATABASE oldboy
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
1 row in set (0.00 sec)
创建一个默认为gbk的字符集
mysql> create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
查看创建的gbk字符集
mysql> show create database oldboy_gbk\G;
*************************** 1. row ***************************
Database: oldboy_gbk
Create Database: CREATE DATABASE oldboy_gbk
/*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)
创建一个默认为utf8字符集的数据库
mysql> create database oldboy_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
查看创建的utf8字符集的数据库
mysql> show create database oldboy_utf8\G;
*************************** 1. row ***************************
Database: oldboy_utf8
Create Database: CREATE DATABASE oldboy_utf8
/*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
提示:字符集的不一致是数据库乱码的罪魁祸首,有关字符集的问题件后文。
提示:如果编译的时候指定了特定的字符集,则,以后创建对应的数据库就不需要指定字符集了。
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
企业里怎么创建数据库:
1.根据开发的程序确定字符集(建议UTF8)
2.编译的时候指定字符集。
3. 编译的时候没有指定字符集或者指定了和程序不同的字符集,如何解决
指定字符集创建数据库即可
数据库要支持创建库的字符集,列如:
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii
显示数据库:
命令 show databases;
查看当前的数据库:
mysql> select database();
删除数据库:
命令:drop database <数据库名>
mysql> drop database test2;
查看当前数据库的版本
mysql> select version();
±-----------+
| version() |
±-----------+
| 5.7.21-log |
±-----------+
1 row in set (0.00 sec)
查看当前的用户
mysql> select user();
查看当前的时间
mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2018-04-07 07:27:31 |
±--------------------+
1 row in set (0.00 sec)
删除mysql系统多余账号
语法:
drop user “user”@“主机名”
例如:
mysql> drop user “rep”@“192.168.10.168”;
用户授权
使用帮助
mysql> help grant
官方实例:
CREATE USER ‘jeffrey’@‘localhost’ IDENTIFIED BY ‘password’;
GRANT ALL ON db1.* TO ‘jeffrey’@‘localhost’;
GRANT SELECT ON db2.invoice TO ‘jeffrey’@‘localhost’;
ALTER USER ‘jeffrey’@‘localhost’ WITH MAX_QUERIES_PER_HOUR 90;
CREATE USER ‘shenliang’@‘localhost’ IDENTIFIED BY ‘123456’;
GRANT ALL ON db1.* TO ‘shenliang’@‘localhost’;
GRANT SELECT ON db2.invoice TO ‘shenliang’@‘localhost’;
GRANT USAGE ON . TO ‘shenliang’@‘localhost’ WITH MAX_QUERIES_PER_HOUR 90;
运维常用授权
GRANT ALL ON db1.* TO ‘shenliang’@‘localhost’ IDENTIFIED BY ‘123456’;
grant命令简单语法:
grant all privileges on dbname.* to username@localhost identified by ‘password’;
列表说明:
grant all privileges on dbname.* to username@localhost identified by ‘password’
授权命令 对应权限 目标:库和表 用户名和客户端主机 用户密码
查看用户权限:
mysql> show grants for shenliang@‘localhost’;
±------------------------------------------------------------+
| Grants for shenliang@localhost |
±------------------------------------------------------------+
| GRANT USAGE ON . TO ‘shenliang’@‘localhost’ |
| GRANT ALL PRIVILEGES ON test
.* TO ‘shenliang’@‘localhost’ |
±------------------------------------------------------------+
注意:USAGE 表示只创建了用户,不能登录,不能操作
二、使用create 和grant配合法
mysql> help create user
1.首先创建用户username及密码password,授权主机localhost
create user ‘username’@‘localhost’ identified by ‘123456’;
2.然后授权localhost主机上通过用户username管理dbname 数据库的所有权限,无需密码
grant all ON dbname.* to ‘username’@‘localhost’;
权限分类
收回权限命令帮助
mysql> help revoke
mysql> show grants for shenliang@‘localhost’;
±------------------------------------------------------------+
| Grants for shenliang@localhost |
±------------------------------------------------------------+
| GRANT USAGE ON . TO ‘shenliang’@‘localhost’ |
| GRANT ALL PRIVILEGES ON test
.* TO ‘shenliang’@‘localhost’ |
±------------------------------------------------------------+
mysql> revoke insert ON test.* from ‘shenliang’@‘localhost’;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show grants for shenliang@‘localhost’;
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for shenliang@localhost |
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘shenliang’@‘localhost’ |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON test
.* TO ‘shenliang’@‘localhost’ |
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
在linux命令行使用
mysql -uroot -p123 -e “show grants for shenliang@‘localhost’;”|grep -i grant|tail -1|tr ‘,’ ‘\n’ > all.txt
[root@localhost ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -u root -p123 -e “show grants for shenliang@‘localhost’;”|grep -i grant|tail -1|tr ‘,’ ‘\n’ > all.txt
权限列表
1 SELECT
2 INSERT
3 UPDATE
4 DELETE
5 CREATE
6 DROP
7 REFERENCES
8 INDEX
9 ALTER
10 CREATE TEMPORARY TABLES
11 LOCK TABLES
12 EXECUTE
13 CREATE VIEW
14 SHOW VIEW
15 CREATE ROUTINE
16 ALTER ROUTINE
17 EVENT
18 TRIGGER
重点:
企业生产环境如何授权:
1.对于博客,CMS等产品的数据库授权
对于web连接用户授权尽量采取最小化原则,在安装期间除了select,insert,update,delete 4个权限外,还需要create,drop等比较危险的权限。
mysql> grant select,insert,update,delete,create,drop on db1.* to ‘shenliang’@‘localhost’ identified by ‘123456’;
注意:常规情况下授权select,insert,update,delete 这4个即可
安装完成之后,需要使用revoke收回create,drop权限
revoke create,drop on db1.* from ‘shenliang’@‘localhost’;
查看用户授权:
mysql> show grants for shenliang@‘localhost’;
建数据库:
mysql> create database oldboy;
建表:
mysql> create table <表名> (
<字段名1><类型>,
…
<字段名n><类型n>
);
建表语句,实例:
mysql> create table student (
-> id int(4) not null,
-> name char(20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL
-> );
注意:int 整型整数 char 字符类型 tinyint 比较小的整型整数 varchar 变长的字符类型
mysql表的字段类型:
1.数字类型
列类型 需要的存储量
TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3个字节
INT 4个字节
INTEGER 4个字节
BIGINT 8个字节
FLOAT(X) 4如果x<=24 或8 如果25<=x<=53
FLOAT 4个字节
DOUBLE 8个字节
DOUBLE PRECISION 8个字节
REAL 8个字节
DECIMAL(M,D) M字节(D+2,如果M<D)
NUMERIC(M,D) M字节(D+2,如果M<D)
2.日期和时间类型,mysql以YYYY-MM-DD格式来显示date值,但是允许你使用字符串或数字把值赋给date列
列类型 需要的存储量
DATE 3个字节
DATATIME 8个字节
TIMESTAMP 4个字节
TIME 3个字节
YEAR 1个字节
3.串类型
列类型 需要的存储量
CHAR(M) M字节,1<=M<=255
VARCHAR(M) L+1字节,在此L<=M和1<=M<=255
TINYBLOB,TINYYEXT L+1字节,在此L<2^8
BLOB,TEXT L+2字节,在此L<2^16
MEDIUMBLOB,MEDIUMTEXT L+2字节,在此L<2^24
LONGBLOB,LONGTEXT L+4字节,在此L<2^32
ENUM(‘value1’,‘value2’,…) 1或2个字节,取决于枚举值的数据(最大值65535)
SET(‘value1’,‘value2’,…) 1,2,3,4或8个字节,取决于集合成员的数量(最多64个成员)
1.INT(M)型:正常大小整数类型
2.CHAR(M)型:定长字符串类型,当存储时,总是用空格填满右边到指定的长度
3.VARCHAR型:边长的字符串类型
查看建表结构:
mysql> desc student;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
±------±------------±-----±----±--------±------+
另外一种方法(不常用)
mysql> show columns from student;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
±------±------------±-----±----±--------±------+
显示表信息
mysql> show create table student\G;
mysql 索引
为表的字段创建索引:
索引就像书的目录一样,如果在字段上建立索引,那么索引列为查询条件时可以加快查询数据的速度。
1.创建主键索引:
查询数据库,按主键索引查询是最快的,每个表只能有一个主键列,但是可以有多个索引列,主键列要求列的所有内容必须唯一,而索引列不要求内容必须唯一。
无论建立主键索引还是普通索引,都要在表的对应列上创建,可以对单列创建索引,也可以对多列创建索引。
建立索引的方法:
1.在建表时,可以增加建立主键索引的语句如下:
mysql>create table student (
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL,
primary key(id),
KEY index_name(name)
);
提示:
1.primary key(id) 主键
2.KEY index_name(name) name字段普通索引
优化:在唯一值多的列上建索引查询效率更高
测试案列:
mysql> create table student (
-> id int(4) not null AUTO_INCREMENT,
-> name char(20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> primary key(id),
-> KEY index_name(name)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc student; ±------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±---------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
±------±------------±-----±----±--------±---------------+
2.建表后通过alter 命令增加主键索引 (不建议)
查看帮助alter
mysql> help alter table
mysql> create table student ( id int(4) not null,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL,
KEY index_name(name)
);
mysql> desc student;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
±------±------------±-----±----±--------±------+
添加主键:
mysql> alter table student change id id int primary key auto_increment;
mysql> desc student;
±------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
±------±------------±-----±----±--------±---------------+
成功!
添加索引:
mysql> alter table student add index index_dept(dept);
mysql> desc student;
±------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
±------±------------±-----±----±--------±---------------+
删除索引:
mysql> alter table student drop index index_dept;
mysql> desc student;
±------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
±------±------------±-----±----±--------±---------------+
另外一种删除索引:
mysql> drop index ind_name_dept on student;
对字段的前n个字符创建普通索引
当遇到表中比较大的列时,列内容的前n个字符在所有内容中已经接近唯一时,这时可以对列的前n个字符建立索引,而无需对整个列建立索引,这样可以节省创建索引占用的系统空间,以及降低读取和更新维护索引消耗的系统资源。
对字段的前n个字符创建普通索引语法:
mysql> create index index_dept on student(dept(8)); 对dept前8个字符创建索引
mysql> desc student;
±------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
±------±------------±-----±----±--------±---------------+
查看详细索引信息:
mysql> show index from student\G;
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: index_dept
Seq_in_index: 1
Column_name: dept
Collation: A
Cardinality: 0
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.01 sec)
为表的多个字段创建联合索引:
如果查询数据的条件是多列时,我们可以为多个查询的列创建联合索引,甚至,可以为多列的前n个字符创建联合索引
实例:
mysql> create index ind_name_dept on student(name,dept);
mysql> show index from student\G;
*************************** 4. row ***************************
Table: student
Non_unique: 1
Key_name: ind_name_dept
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: student
Non_unique: 1
Key_name: ind_name_dept
Seq_in_index: 2
Column_name: dept
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
对前n个字符创建联合索引:
mysql> create index ind_name_dept on student(name(8),dept(10));
mysql> show index from student\G;
*************************** 4. row ***************************
Table: student
Non_unique: 1
Key_name: ind_name_dept
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: 8
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: student
Non_unique: 1
Key_name: ind_name_dept
Seq_in_index: 2
Column_name: dept
Collation: A
Cardinality: 0
Sub_part: 10
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
生产环境创建联合索引:
提示:按条件查询数据时,联合索引是有前缀生效特性的
index(a,b,c)仅a,ab,abc三个查询条件列可以走索引,b,bc,ac,c等无法使用索引。
创建唯一索引(非主键)
说明:一般用来约束这个表的内容的,插入时候必须要唯一。
mysql> create unique index uni_ind_name on student(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
±------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | UNI | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
±------±------------±-----±----±--------±---------------+
索引列的创建及生效条件
索引的缺点
1.索引占用系统空间
2.更新数据库时还需要维护索引数据
所以:数十几百行的小表上无需建立索引,写频繁,更新频繁,读少的表要少建立索引。
什么时候需要创建索引
select user,host from mysql.user where host=…
索引一定要创建在where后的条件列上,而不是select后的选择数据的列,另外我们要尽量选择在唯一值多的大表上建立索引
mysql 插入数据
命令语法:
insert into <表名> [(<字段名1>[,<字段名n>])] values(值1)[,(值n)]
新建一个简单的测试表test
mysql> create table test
(
id int(4) NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL,
PRIMARY KEY(id)
);
插入数据
mysql> insert into test(id,name) values(1,‘oldboy’);
查看数据:
mysql> select * from test;
±—±-------+
| id | name |
±—±-------+
| 1 | oldboy |
±—±-------+
由于id建表时候为自增长,所以id可以忽略不写。仍然按照序号自动增加。所以只需要插入name值即可
mysql> insert into test(name) values(‘shenliang’);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
±—±----------+
| id | name |
±—±----------+
| 1 | oldboy |
| 2 | shenliang |
±—±----------+
3.如果不指定列,就要按规矩为每列都插入恰当的值。
insert into test values(3,‘xiaoming’);
4.批量插入数据方法,提升效率
insert into test values(4,‘xiaohong’),(5,‘kaka’);
删除数据:
mysql> delete from test;
一次性插入多条,提升效率,优化方案之一。
mysql> insert into test values(1,‘xiaogang’),(2,‘shenliang’),(3,‘inco’),(4,‘xiaohong’),(5,‘kaka’);
备份数据库:
[root@localhost ~]# /usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -uroot -p123 -B oldboy > /opt/oldboy.sql
查看备份信息
[root@localhost opt]# grep -E -v “#|/|^$|–” /opt/oldboy.sql
USE oldboy
;
DROP TABLE IF EXISTS student
;
CREATE TABLE student
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
char(20) COLLATE utf8mb4_unicode_ci NOT NULL,
age
tinyint(2) NOT NULL DEFAULT ‘0’,
dept
varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id
),
UNIQUE KEY uni_ind_name
(name
),
KEY index_dept
(dept
(8)),
KEY ind_name_dept
(name
(8),dept
(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
LOCK TABLES student
WRITE;
UNLOCK TABLES;
DROP TABLE IF EXISTS test
;
CREATE TABLE test
(
id
int(4) NOT NULL AUTO_INCREMENT,
name
char(20) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
LOCK TABLES test
WRITE;
INSERT INTO test
VALUES (1,‘xiaogang’),(2,‘shenliang’),(3,‘inco’),(4,‘xiaohong’),(5,‘kaka’);
UNLOCK TABLES;
mysql 查询数据
查询表的所有数据行:
1.命令语法:
select <字段1,字段2,。。。> from <表名> where <表达式>
其中,select,from ,where 是不能随便改的,是关键字,支持大小写
优化:不要用select * 尽量用select id,name
mysql> select id,name from test limit 2; 查询到2条数据
±—±----------+
| id | name |
±—±----------+
| 1 | xiaogang |
| 2 | shenliang |
±—±----------+
mysql> select id,name from test limit 0,3; 从0行开始查,查3行记录
±—±----------+
| id | name |
±—±----------+
| 1 | xiaogang |
| 2 | shenliang |
| 3 | inco |
±—±----------+
根据条件进行查询:
mysql> select id,name from test where id=3;
±—±-----+
| id | name |
±—±-----+
| 3 | inco |
±—±-----+
mysql> select id,name from test where name=‘shenliang’; 字符串需要加引号
±—±----------+
| id | name |
±—±----------+
| 2 | shenliang |
±—±----------+
mysql> select id,name from test where name=‘shenliang’ and id=2; 多条件查询
±—±----------+
| id | name |
±—±----------+
| 2 | shenliang |
±—±----------+
mysql> select id,name from test where name=‘shenliang’ or id=5;
±—±----------+
| id | name |
±—±----------+
| 2 | shenliang |
| 5 | kaka |
±—±----------+
mysql> select id,name from test where id>2 and id<4;
±—±-----+
| id | name |
±—±-----+
| 3 | inco |
±—±-----+
其他查询功能:
1、排序功能
mysql> select id,name from test order by id asc;
±—±----------+
| id | name |
±—±----------+
| 1 | xiaogang |
| 2 | shenliang |
| 3 | inco |
| 4 | xiaohong |
| 5 | kaka |
±—±----------+
mysql> select id,name from test order by id desc;
±—±----------+
| id | name |
±—±----------+
| 5 | kaka |
| 4 | xiaohong |
| 3 | inco |
| 2 | shenliang |
| 1 | xiaogang |
±—±----------+
多表查询:
创建一个测试表:
mysql> create table student (
-> Sno int(10) NOT NULL COMMENT '学号',
-> Sname varchar(16) NOT NULL COMMENT '姓名',
-> Ssex char(2) NOT NULL COMMENT '性别',
-> Sage tinyint(2) NOT NULL default '0' COMMENT '学生年龄',
-> Sdept varchar(16) default NULL COMMENT '学生所在系别',
-> PRIMARY KEY (Sno),
-> key index_Sname (Sname)
-> );
mysql> create table course (
Cno int(10) NOT NULL COMMENT '课程号',
Cname varchar(64) NOT NULL COMMENT '课程名',
Ccredit tinyint(2) NOT NULL COMMENT '学分',
PRIMARY KEY (Cno)
);
mysql> create table SC (
SCid int(12) NOT NULL auto_increment COMMENT '主键',
Cno int(10) NOT NULL COMMENT '课程号',
Sno int(10) NOT NULL COMMENT '学号',
Grade tinyint(2) NOT NULL COMMENT '学习成绩',
PRIMARY KEY (SCid)
);
插入学生表数据:
insert into student values(0001,‘宏志’,‘男’,30,‘计算机网络’),(0002,‘王硕’,‘男’,30,‘computer application’),(0003,‘oldboy’,‘男’,28,‘物流管理’),(0004,‘脉动’,‘男’,29,‘computer application’),(0005,‘oldgirl’,‘女’,26,‘计算机科学与技术’),(0006,‘莹莹’,‘女’,22,‘护士’);
插入课程表数据:
insert into course values(1001,‘Linux中高级运维’,3),(1002,‘Linux高级架构师’,5),(1003,‘mysql高级DBA’,4),(1004,‘python运维开发’,4),(1005,‘java web 开发’,3);
插入选课表
insert into SC(Sno,Cno,Grade) values(0001,1004,4),(0001,1002,3),(0001,1003,1),(0001,1004,6);
insert into SC(Sno,Cno,Grade) values(0002,1001,3),(0002,1002,2),(0002,1003,2),(0002,1004,8);
insert into SC(Sno,Cno,Grade) values(0003,1004,4),(0003,1002,4),(0003,1003,2),(0003,1004,8);
insert into SC(Sno,Cno,Grade) values(0004,1001,1),(0004,1002,1),(0004,1003,2),(0004,1004,3),(0005,1001,5),(0005,1002,3),(0005,1003,2),(0005,1004,9);
mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SSC.Sno and course.Cno=SC.Cno;
±----±--------±---------------------±------+
| Sno | Sname | Cname | Grade |
±----±--------±---------------------±------+
| 1 | 宏志 | python运维开发 | 4 |
| 1 | 宏志 | Linux高级架构师 | 3 |
| 1 | 宏志 | mysql高级DBA | 1 |
| 1 | 宏志 | python运维开发 | 6 |
| 2 | 王硕 | Linux中高级运维 | 3 |
| 2 | 王硕 | Linux高级架构师 | 2 |
| 2 | 王硕 | mysql高级DBA | 2 |
| 2 | 王硕 | python运维开发 | 8 |
| 3 | oldboy | python运维开发 | 4 |
| 3 | oldboy | Linux高级架构师 | 4 |
| 3 | oldboy | mysql高级DBA | 2 |
| 3 | oldboy | python运维开发 | 8 |
| 4 | 脉动 | Linux中高级运维 | 1 |
| 4 | 脉动 | Linux高级架构师 | 1 |
| 4 | 脉动 | mysql高级DBA | 2 |
| 4 | 脉动 | python运维开发 | 3 |
| 5 | oldgirl | Linux中高级运维 | 5 |
| 5 | oldgirl | Linux高级架构师 | 3 |
| 5 | oldgirl | mysql高级DBA | 2 |
| 5 | oldgirl | python运维开发 | 9 |
±----±--------±---------------------±------+
mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno order by Sname; 进行排序
查看执行计划:
mysql> explain select * from test where name=‘shenliang’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: NULL 可能走索引:null 没可能
key: NULL 索引:null 没有
key_len: NULL
ref: NULL
rows: 5 扫描了5行
filtered: 20.00
Extra: Using where
创建索引:
用来判断是否走索引
explain
mysql> create index index_name on test(name);
mysql> select * from test where name=‘shenliang’;
±—±----------+
| id | name |
±—±----------+
| 2 | shenliang |
±—±----------+
1 row in set (0.01 sec)
mysql> explain select * from test where name=‘shenliang’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: index_name
key: index_name
key_len: 80
ref: const
rows: 1
filtered: 100.00
Extra: Using index
说明成功了,索引已经生效了。
修改表中的数据:
1、修改表中指定条件固定列的数据
命令语法: update 表名 set 字段=新值,。。。 where条件 (一定要加条件)
修改指定的行字段内容
查看要修改的表
mysql> select * from test;
±—±----------+
| id | name |
±—±----------+
| 3 | inco |
mysql> update test set name=‘gongli’ where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
±—±----------+
| id | name |
±—±----------+
| 3 | gongli |
| 5 | kaka |
| 2 | shenliang |
| 1 | xiaogang |
| 4 | xiaohong |
±—±----------+
mysql 防止误操作:
登录加 -U
[root@localhost mysql3306]# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -u root -p123 -U
测试 不加where 不可以执行:
mysql> update test set name=‘liming’;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
可以用别名方式:
[root@localhost mysql3306]# alias mysql=‘mysql -U’
还可以写入系统变量中
删除表中的数据:
命令用法:
delete from 表名 where 表达式
实践:删除表test中编号为1的记录
mysql> delete from test where id=1;
删除id大于3的记录:
mysql> delete from test where id>3;
全部清空表中数据(慎用):
mysql> truncate test;
注意:truncate 和 delete 区别
1.truncate 速度更快
2.truncate简单理解为物理删除,delete为逻辑删除。
增删改表的字段:
命令语法:
alter table 表名 add 字段 类型 其他
测试表数据,实例在test表中添加字段sex,age,qq 类型分别为char(4),int(4),varchar(15)
mysql> alter table test add sex char(4);
mysql> alter table test add age int(4) after name; 插入年龄字段,并且在name后面使用after
mysql> select * from test;
±—±----------±-----±-----+
| id | name | age | sex |
±—±----------±-----±-----+
| 1 | xiaogang | NULL | NULL |
| 2 | shenliang | NULL | NULL |
| 3 | gongli | NULL | NULL |
| 4 | xiaohong | NULL | NULL |
| 5 | kaka | NULL | NULL |
±—±----------±-----±-----+
mysql> alter table test add qq varchar(15) first; 第一个
mysql> select * from test;
±-----±—±----------±-----±-----+
| qq | id | name | age | sex |
±-----±—±----------±-----±-----+
| NULL | 1 | xiaogang | NULL | NULL |
| NULL | 2 | shenliang | NULL | NULL |
| NULL | 3 | gongli | NULL | NULL |
| NULL | 4 | xiaohong | NULL | NULL |
| NULL | 5 | kaka | NULL | NULL |
±-----±—±----------±-----±-----+
增加多个字段:
mysql> alter table test add mobile varchar(32) default NULL COMMENT ‘mobile’,add addr varchar(255) default NULL COMMENT ‘地址’;
改变字段:
修改字段类型:
mysql> alter table test modify age char(4) after name;
mysql> desc test;
±-------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------±-------------±-----±----±--------±---------------+
| qq | varchar(15) | YES | | NULL | |
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | char(4) | YES | | NULL | |
| wechat | varchar(255) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| mobile | varchar(32) | YES | | NULL | |
| addr | varchar(255) | YES | | NULL | |
±-------±-------------±-----±----±--------±---------------+
rename 更改表名:
mysql> rename table test to test1;
mysql 插入中文数据乱码
set name utf-8
mysql不乱码的5种方式:
(1)登录mysql,先做 set names latinl 然后再执行更新语句或执行语句文件
set names latinl;
source test.sql
(2)在sql文件中指定set names latinl;然后登录mysql
mysql> source test.sql
(3)在sql文件中指定set names latinl;然后通过mysql 命令导入数据
mysql -uroot -p’oldboy123’ oldboy < test.sql
mysql -uroot -p’olgboy123’ oldboy -e “set names latinl;select * from oldboy.test”
(4)通过指定mysql命令的字符集参数实现–default-character-set=latinl
cat test.sql
insert into student values(9.zhangsan);
mysql -uroot -p’oldboy123’ --default-character-set=laninl oldboy < test.sql
(5)在配置文件里设置客户端及服务器相关参数
[client]
default-character-set=latinl
提示:无需重启服务,退出重新登录就生效了,相当于set names latinl
2.更新my.cnf参数
[mysqld]
default-character-set=latinl 适合5.1及以前版本
charater-set-server=latinl 适合5.5
查看建数据库帮助
mysql> show character set;
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
create database oldboy1 DEFALUT CHARACTER SET UTF8 COLLATE utf8_general_ci;
需要保持全部对应
linux系统字符集:
centos 6
cat /etc/sysconfig/i18n
centos 7
[root@localhost opt]# cat /etc/locale.conf
LANG=“en_US.UTF-8”
mysql查看配置变量:
mysql> show variables\G;
查看状态:
mysql> show status;
mysql> show global status;
数据库常见的字符集:
常用字符集 长度 说明
GBK 2 不是国际标准
UTF-8 3 中英文混合的环境,建议使用此字符集
latinl 1 mysql的默认字符集
UTF8mb4
4 UTF-8 Unicode
查看mysql 当前字符集:
mysql> show variables like ‘character_set%’;
±-------------------------±---------------------------------+
| Variable_name | Value |
±-------------------------±---------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
±-------------------------±---------------------------------+
必须保持统一字符集
数据字符集的修改步骤:
对于已有数据库想修改字符集不能直接通过 alter database chaacter set xxxx 或者 alter database character set xxxx 这两个命令都没有更新已有记录的字符集,而只是对新创建的表或者记录生效
已经有记录的字符的调整,必须先将数据导出,经过修改字符集后重新导入后才可完成
修改数据库密码编码
alter database [your db name ] charset [your character setting]
mysqldump 参数说明:
1.导出表结构
mysqldump -uroot -p --default-character-set=latin1 -d dbname > altable.sql
2.编辑alltable.sql将latin1改成GBK
3.确保数据库不再更新,导出所有数据
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 dbname > alldate.sql
参数说明:
-d 只导出表结构
–quick:用于转储大的表,强制mysqldump从服务器一次一行的检索数据而不是检索所有航,并输出前cache到内存中
–no-create-info:不创建create tables语句
–extended-insert:使用包括几个values列表的多行insert语法,这样文件更小,IO也小,导入数据时会非常快
–default-character-set=latin1:按照原有字符集导出数据,这样导出的文件中,所有中文都是可见的不会保存成乱码
–master-data=1
-F 刷新binlog日志
4.打开alldate.sql将set name latin1修改成 set names gbk;
5.建库
create database dbname default charset gbk;
6.创建表,执行alltable.sql
mysql -uroot -p dbname < alltable.sql
7.导入数据:
mysql -uroot -p dbname < alldata.sql
注意:选择目标字符集时,要注意最好大于等于源字符集(字库更大),否则,可能丢失不被支持的数据。
总结:latin1—>utf8
1、建库建表的语句导出,sed批量修改为utf8
2、导出所有数据
3、修改mysql服务端和客户端编码为utf8
4、删除原有的库表及语句
5、导入新的建库及建表的语句
6、导入mysql的所有数据
mysql 备份
/usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -uroot -p123 -B oldboy > /opt/oldboy.sql
备份最好加-B
对比-B多了下面建表和连接数据库的作用
< CREATE DATABASE /!32312 IF NOT EXISTS/ oldboy
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
<
< USE oldboy
;
mysql 多库备份:
mysql -uroot -p’123’ -e “show databases”;|grep -Evi “database|infor|perfor”|sed -r ‘s#^([a-z].*$)#mysqldump -uroot -p’oldboy123’ -B \1|gzip > /opt/\1.sql.gz#g’|bash
[root@localhost opt]# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -uroot -p’123’ -e “show databases;”|grep -Evi “database|infor|perfor”|sed -r ‘s#^([a-z].*$)#mysqldump -uroot -p’123’ --events -B \1|gzip > /opt/\1.sql.gz#g’|bash
–master-data=1 表示打印position
[root@localhost opt]# /usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -uroot -p’123’ --master-data=1 oldboy
CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000027’, MASTER_LOG_POS=154;
mysqldump 的关键参数说明:
1、-B 指定多个库,增加建库语句和use语句
2、–compact 去掉注释,适合调试输出,(生产环境禁止使用)
3、-A所有库
4、-F 刷新binlog日志
5、–master-data 增加binlog日志文件文件名及对应的位置点
6、-x是–lock-all-tables
7、-l是–lock-tables 意思是lock all tables for read 只读锁表
8、-d 只备份表结构
9、-t 只备份数据
10、–single-transaction 适合innodb事务数据库备份
说明:innodb表在备份时,通常启用选项–single-transaction来保证备份的一致性,实际上它的工作原理是设定本次会话的隔离级别,repeatable read 以确保本次(dump)时,不会看到其他会话已经提交的数据
myisam:
mysqldump -utoot -p123 -A -B --master-data=1 -x --events |gzip > /opt/all.sql.gz
innodb:
mysqldump -uroot -p123 -A -B --naster-data=1 --events --single-transation|gzip > /opt/all.sql.gz
说明:如果混合了myisam和innodb引擎,那么使用myisam引擎为主
mysql分库备份后恢复
gzip -d *
for dbname in ls *.sql
|sed ‘s#_bak.sql##g’;do mysql -uroot -poldboy123 < ${dbname}_bak.sql;done
mysqlbinlog
作用:用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录。
拆库恢复(只恢复某个库):
mysqlbinlog -d oldboy mysql-bin.000020 > oldboy.sql
mysqlbinlog位置点恢复:
mysqlbinlog mysql-bin.000020 --start-position=365 --stop-position=456 -r pos.sql
mysqlbinlog时间点恢复:
mysqlbinlog
/usr/local/mysql/bin/mysqlbinlog /home/mysql3306/mysql3306/mysql-bin.000026 --start-datetime=‘180413 16:33:59’ --stop-datetime=‘180413 16:36:04’ -r /opt/time.sql
sql的分类
–基本查询语句(DQL):
SELECT * | {[column,] group_function(column),…}
FROM table
[WHERE condition]
[GROUP BY column]
[DRDER BY column];
–大写 关键字 不可简写 中间不可换行
– | 管道符 多选一
– [] 中括号 可选项
– , … 后面可以有多个元素
– 小写 需要使用具体的值替代
– 上面语法帮助中 每一行我们称之为一个字句
SELECT 子句
FROM 子句
where 子句
group by 子句
order by 子句;
–查询 表中的所有列 从 表明
SELECT * FROM EMP
–查询特定的列
SELECT empno,ename,sal
FROM emp;
加减乘除:
select 1+2*3/4
from dual;
其中 dual 是空表,虚拟表,并不存在,因为from后面不加表 会报错
–别名和特殊别名
mysql> select ename,empno,sal,SAL*12 AS year_SAL from emp;
±-------±------±-----±---------+
| ename | empno | sal | year_SAL |
±-------±------±-----±---------+
| SMITH | 7369 | 800 | 9600 |
| ALLEN | 7499 | 1600 | 19200 |
| WARD | 7521 | 1250 | 15000 |
±-------±------±-----±---------+
计算出员工12个月工资,年薪.
其中AS 可以忽略,不写。
mysql> select ename,empno,sal,SAL*12 AS ‘year SAL’ from emp;
±-------±------±-----±---------+
| ename | empno | sal | year SAL |
±-------±------±-----±---------+
| SMITH | 7369 | 800 | 9600 |
| ALLEN | 7499 | 1600 | 19200 |
| WARD | 7521 | 1250 | 15000 |
±-------±------±-----±---------+
其中如果不想用_ 想用中间空格,那么可以用‘’ 或者“” 不推荐使用这种方式下划线_比较好
– || 字符串连接
查询员工的名字,职位
oracle用法
select ename|| ‘IS’ ||job from emp;
mysql用法
mysql> select concat(ename,job) from emp;
–查看部门编号
mysql> select DEPTND from emp;
±-------+
| DEPTND |
±-------+
| 20 |
| 30 |
| 30 |
| 20 |
| 30 |
| 30 |
| 10 |
| 20 |
| 10 |
| 30 |
| 20 |
| 30 |
| 20 |
| 10 |
±-------+
–去除重复值
mysql> select distinct deptnd from emp;
±-------+
| deptnd |
±-------+
| 20 |
| 30 |
| 10 |
±-------+
–查找雇员编号为7788的员工信息 where empno=7788 为过滤条件 = 比较运算符
= < <= != <>
mysql> select * from emp where empno=7788;
±—±------±------±--------±-----±-----------±-----±-----±-------+
| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |
±—±------±------±--------±-----±-----------±-----±-----±-------+
| 8 | 7788 | SCOTT | ANALYST | 7566 | 1981-01-09 | 3000 | | 20 |
±—±------±------±--------±-----±-----------±-----±-----±-------+
–查找工资高于2000的员工信息
mysql> select * from emp where sal >2000;
±—±------±------±----------±-----±-----------±-----±-----±-------+
| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |
±—±------±------±----------±-----±-----------±-----±-----±-------+
| 4 | 7566 | JONES | MANAGER | 7839 | 1981-01-05 | 2975 | | 20 |
| 6 | 7698 | BLAKE | MANAGER | 7839 | 1981-01-07 | 2850 | | 30 |
| 7 | 7782 | CLARK | MANAGER | 7839 | 1981-01-08 | 2450 | | 10 |
| 8 | 7788 | SCOTT | ANALYST | 7566 | 1981-01-09 | 3000 | | 20 |
| 9 | 7839 | KING | PRESIDENT | | 1981-01-10 | 5000 | | 10 |
| 13 | 7902 | FORD | ANALYST | 7566 | 1981-01-14 | 3000 | | 20 |
±—±------±------±----------±-----±-----------±-----±-----±-------+
–查找员工工资大于3000 并包含3000的员工信息
mysql> select * from emp where sal >=3000;
±—±------±------±----------±-----±-----------±-----±-----±-------+
| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |
±—±------±------±----------±-----±-----------±-----±-----±-------+
| 8 | 7788 | SCOTT | ANALYST | 7566 | 1981-01-09 | 3000 | | 20 |
| 9 | 7839 | KING | PRESIDENT | | 1981-01-10 | 5000 | | 10 |
| 13 | 7902 | FORD | ANALYST | 7566 | 1981-01-14 | 3000 | | 20 |
±—±------±------±----------±-----±-----------±-----±-----±-------+
–查询员工小于1100 并 包含1100的员工信息
mysql> select * from emp where sal <= 1100 ;
±—±------±------±------±-----±-----------±-----±-----±-------+
| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |
±—±------±------±------±-----±-----------±-----±-----±-------+
| 1 | 7369 | SMITH | CLERK | 7902 | 1981-01-02 | 800 | | 20 |
| 11 | 7876 | ADAMS | CLERK | 7788 | 1981-01-12 | 1100 | | 20 |
| 12 | 7900 | JAMES | CLERK | 7698 | 1981-01-13 | 950 | | 30 |
±—±------±------±------±-----±-----------±-----±-----±-------+
–查询员工小于1100的员工信息
mysql> select * from emp where sal <1100 ;
–查询员工工资不包含5000的员工信息
mysql> select * from emp where sal != 5000 ;
mysql> select * from emp where sal <> 1300 ;
–between and >= <=
–查找工资 范围在1500到3000的员工信息
mysql> select * from emp where sal between 1500 and 3000;
±—±------±-------±---------±-----±-----------±-----±-----±-------+
| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |
±—±------±-------±---------±-----±-----------±-----±-----±-------+
| 2 | 7499 | ALLEN | SALESMAN | 7698 | 1981-01-03 | 1600 | 300 | 30 |
| 4 | 7566 | JONES | MANAGER | 7839 | 1981-01-05 | 2975 | | 20 |
| 6 | 7698 | BLAKE | MANAGER | 7839 | 1981-01-07 | 2850 | | 30 |
| 7 | 7782 | CLARK | MANAGER | 7839 | 1981-01-08 | 2450 | | 10 |
| 8 | 7788 | SCOTT | ANALYST | 7566 | 1981-01-09 | 3000 | | 20 |
| 10 | 7844 | TURNER | SALESMAN | 7698 | 1981-01-11 | 1500 | 0 | 30 |
| 13 | 7902 | FORD | ANALYST | 7566 | 1981-01-14 | 3000 | | 20 |
±—±------±-------±---------±-----±-----------±-----±-----±-------+
– IN 查找工资 等于 5000 或 3000 的员工信息
mysql> select * from emp where sal in (5000,3000);
– null 值 代表的是不确定 并不是没有
select * from emp where comm is null;
select 1*2+3/4+null from dual;
–模糊查询 like
– %通配符 代表是0个带N个字符
– _ 通配符 代表一个字符
select * from emp where ename like ‘k%’;
select * from emp where ename like ‘k__’
–查询员工的名字包含五个字符
select * from emp where ename like ‘_____’’
– 多条件的组合 and 同时满足条件
–查找部门10中 工资高于2000的员工信息
select * from emp where deptno=10 and sal >2000;
–or 只需要满足其中一个条件就可以,当然同时满足两个条件也可以
mysql> select * from emp where deptnd=10 or sal >2000;
–not 运算符
–查找工资大于等于2000的员工信息
mysql> select * from emp where sal>=2000;
–查找工资不小于2000的员工信息
mysql> select * from emp where not sal<2000; (员工工资不低于2000)
–查找员工佣金不是null的信息
mysql> select * from emp where comm is not null;
–查找名字不含字母k的信息
mysql> select * from emp where ename not like ‘%k%’;
– 优先级
– and 的优先级 是高于or的
select * from emp where deptno=10 and sal >2000 or job like ‘sa%’
mysql> select * from emp where deptnd=10 and (sal >2000 or job like ‘sa%’);
– 排序 按照工资排序
mysql> select * from emp order by sal;
– 查询员工的名字,部门编号,工资,年薪
– order by 后加列名
mysql> select ename,deptnd,sal,sal*12 YEAR_SAL from emp order by sal;
– order by 后加列别名
mysql> select ename,deptnd,sal,sal*12 year_sal from emp order by year_sal;
– order by 后加列在select 子句中的位置 asc升序(默认可以不写) desc(降序 必须写)
select ename,comm,deptnd,sal,sal*12 year_sal from emp order by 3; (不推荐)
mysql> select ename,deptnd,sal,sal*12 year_sal from emp order by 3 desc;
– 按多列排序
– 按部门编号升序排列,如果部门编号相同,再按照员工编号降序排列
mysql> select * from emp order by deptnd asc , empno desc;
– 按照佣金排序 升序 null值在最后
mysql> select * from emp order by comm;
– 按照佣金排序 升序 null值再前面
mysql> select * from emp order by comm null first;
– 按照佣金排序 降序
mysql> select * from emp order by comm desc;
– 函数是一个数据库对象
– 函数可以有0-N个参数 但是只能有 而且必须有一个返回值
– 函数可以在sql中直接调用
–字符函数
大小写转换函数***
– upper
mysql> select ‘aBc’,upper(‘aBc’) from dual;
– lower
mysql> select ‘aBc’,lower(‘aBc’) from dual;
– initcap (首字母大写)mysql不可用
mysql> select ‘aBc’,initcap(‘aBc’) from dual;
mysql> select ‘aBc’,initcap(‘aBc def’) from dual;
输出为 Abc Def 中间空格 会识别为两个独立单词
– 补位函数
*补位函数
– LPAD (要补位的字符串,补位后的长度,使用什么字符进行补位)
mysql> select lpad(‘1’,10,’*’) from dual;
±-----------------+
| lpad(‘1’,10,’*’) |
±-----------------+
| *********1 |
±-----------------+
– RPAD
mysql> select rpad(‘1’,10,’*’) from dual;
±-----------------+
| rpad(‘1’,10,’*’) |
±-----------------+
| 1********* |
±-----------------+
左右之分
清除函数*****
– trim
mysql> select ’ abc ’ ,trim(’ abc ') from dual;
±-------------------±----------------------+
| abc | trim(’ abc ') |
±-------------------±----------------------+
| abc | abc |
±-------------------±----------------------+
mysql> mysql> select ‘aaabbbaaaa’ ,trim(‘a’ from ‘aaabbbaaaa’) from dual;
±-----------±----------------------------+
| aaabbbaaaa | trim(‘a’ from ‘aaabbbaaaa’) |
±-----------±----------------------------+
| aaabbbaaaa | bbb |
±-----------±----------------------------+
– ltrim 去除左边的,默认去的是空格
mysql> select ’ aaa ‘,ltrim(’ d aaa d ') from dual;
±-----------±-------------------------+
| aaa | ltrim(’ d aaa d ') |
±-----------±-------------------------+
| aaa | d aaa d |
±-----------±-------------------------+
– rtrim 去除右边的,默认去的是空格
mysql> select ’ aaa ‘,rtrim(’ d aaa d ') from dual;
±-----------±-------------------------+
| aaa | rtrim(’ d aaa d ') |
±-----------±-------------------------+
| aaa | d aaa d |
±-----------±-------------------------+
长度函数*
– length 计算字符
mysql> select length(‘aaa’) from dual;
±--------------+
| length(‘aaa’) |
±--------------+
| 3 |
±--------------+
– lengthb 计算字节
mysql> select lengthB(‘中文’) from dual; mysql不适用
字符串连接***
– concat
mysql> select concat(‘ABC’,‘DEF’,‘hhh’) from dual;
±--------------------------+
| concat(‘ABC’,‘DEF’,‘hhh’) |
±--------------------------+
| ABCDEFhhh |
±--------------------------+
字符串查找**********
– inset (目标字符串,要查找的字符(串),从第几个字符开始查找(默认是1,找这个字符串的第几次出现))
mysql> select instr(‘abcabc’,‘c’) from dual;
字符串截取*******
– substr(目标字符串,从第几个字符开始截取,截取多长)
mysql> select substr(‘123456789’,1,2) from dual;
±------------------------+
| substr(‘123456789’,1,2) |
±------------------------+
| 12 |
±------------------------+
如果不写截取长度,从该字符开始截取到末尾
mysql> select substr(‘123456789’,4) from dual;
±----------------------+
| substr(‘123456789’,4) |
±----------------------+
| 456789 |
±----------------------+
位置可以写负数,含义是倒数几个字符
mysql> select substr(‘123456789’,-4,2) from dual;
±-------------------------+
| substr(‘123456789’,-4,2) |
±-------------------------+
| 67 |
±-------------------------+
–数字函数
舍入函数*
– round (目标值,小数位)
– 保留到小数点后两位
mysql> select round(123.456,2) from dual;
– 不写小数位,保留整数
mysql> select round(123.456) from dual;
– 保留到小数点前两位
mysql> select round(123.456,-2) from dual;
**截断函数
– trunc (目标值,小数位) (oracle)
select trunc(123.456,2) from dual;
– 如果不写小数位,只保留整数
select trunc(123.456) from dual;
– 小数位如果为负数,则舍弃小数点前几位的数值
select trunc(123.456,-2) from dual;
***取余函数
– mod(被除数,除数)
select mod(4,3) from dual;
*******绝对值函数
– abs
selec abs(-123213) from dual;
****向上向下取整
– ceil
select ceil(1.00000000001) from dual;
select ceil(-1.00000000001) from dual;
– fioor
select floor(1.00000000001) from dual;
select floor(-1.00000000001) from dual;
–日期函数
**当前时间
– sysdate 当前时间
mysql> select sysdate() from dual;
– 时间运算
select sysdate,sysdate +1 from dual;
***月份运算
– add_months(日期,月份数)
mysql> SELECT ADDDATE(NOW(), INTERVAL +120 MONTH);
– month_between
– 查询员工的名字,如入职日期,工作月份数
–转换函数 数据类型间的转换
–通用函数 函数的使用不受限于数据类型
–单行函数和多行函数·
内存分配
文件存储
线程
算法+数据结构
innodb体系结构
buffer pool
缓存数据(既缓存索引页缓存数据)
MyISAM只缓存索引,不缓存数据
FREE链表
没有被使用的buffer块,被放置在FREE链表里面
Mysql一个数据页16kb (page) oracle一个数据块8k(block)
如果先缓存的数据,从FREE链表拿空块,如果FREE没有空块,在LRU链表利用LRU算法淘汰旧的数据块。
LRU链表
实现LRU算法的链表
LRU----最近最少未使用算法
innodb_max_dirty_pages_pct
这个参数控制脏页的比例如果是innodb_plugin或是mysql5.5以上的版本,建议这个参数可以设置到75-90%。如果是大量写入,而且写入的数据不是太活跃,可以考虑把这个值设的低一点,如果写入或是更新的数据也就是热数据可以考虑把这个值设为95%
innodb_old_blocks_time
表示页读入mid位置后需要等待多久才会被加入到LRU列表的热端
Mysql 5.7新特性 数据预热
mysql buffer pool数据预热参数
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct = 40
转储每个bp instance LRU上最热的page的百分比
innodb_flush_neighbors
默认是开的,这个一定要着,充分利用顺序IO去写数据
ib_buffer_pool
持久化热数据的文件,存储路径位置/home/mysql3306/mysql3306
FLUSH链表
脏块被刷入FLUSH链表,然后会在一段时间内进行落盘,脏块在LRU链表里面和FLUSH里面都存在,LRU链表管理数据块的可用性,FLUSH用于数据块的写入磁盘。
Redo
1.redo 物理逻辑日志,记录数据页的改变的日志。
2.mysql 的redo buffer到达1/2,oracle的redo buffer是到达1/3,1S 10S 刷新redo
3.commit时候落盘
存储路径位置 /home/mysql3306/mysql3306/ib_logfile0 和 ib_logfile1
innodb_log_files_in_group
innodb每个redo log file的成员数量,默认2
innodb_log_file_size
日志文件的大小,默认值:5242880(5m)
innodb_log_buffer_size
Log buffer的大小,就是日志缓冲的大小,重做日志缓冲,一般情况下8MB足够使用,如果不放心,可以使用16MB
innodb_lock_wait_timeout
事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用失败。
innodb_flush_log_at_trx_commit (重点)
控制redo刷新策略的参数
这个参数有三个值可以设置
0 (效率最高,同时安全性最低,如果宕机,可能丢失一秒的事务)每一条刷新一次redo,同时每秒flush到disk
log buffer每秒写log file一次(数据库),并且lofile的磁盘flush刷新同步进行(系统),这种情况下,log buffer仅仅在master thread的每秒循环中执行。
1 (最安全的参数,效率最低)(一般情况下使用,最安全)
每次事务提交都会进行log buffer的写入到log file(数据库),但是flush操作是每秒进行一次(系统)
2 每次commit,都会进行redo的刷入,但是每秒flush到disk
(如果数据库宕机,不会丢失数据,如果服务器宕机,丢一秒,因为数据缓存在操作系统缓存中,可以理解内存中)
核心业务,双1设置,提高安全性
导入大批量的sql,设置为0,提高很高的效率
undo
1.DML操作导致的数据记录变化,均需要将记录的前镜像写入undo日志(逻辑日志)
2.undo页面的修改,同意需要记录redoi日志
3.rollback–反向使用事务的所有undo日志,回滚整个事务所做的修改
Mysql 5.6新特性 undo表空间的单独设置
Mysql 5.7新特性 在线回收undo表空间
mysql5.7 undo配置
innodb_undo_directory = /home/mysql3306/mysql3306/
innodb_undo_logs = 128
Log段的个数,必须大于等于35,默认128
innodb_undo_tablespaces = 3
Undo表空间的个数
innodb_undo_log_truncate = 1
开启undo的在线回收
innodb_max_undo_log_size = 1000M
Undo的log最大文件限制,超过了就可以在线回收了
innodb_purge_rseg_truncate_frequency = 128
数据访问频率的参数,默认即可
检查点(checkpoint)
检查点解决的问题
- 缩短数据库恢复时间
- 缓冲池不够用的时候,刷新脏页到磁盘
- 重做日志不够用的时候,刷新脏页·
当数据库发生宕机的时候,数据库不需要恢复所有的页面,因为检查点之前的页面都已经刷新回磁盘了。故数据库只需要对检查点以后的日志进行恢复,这就大大减少了恢复时间。
ckpt 刷脏页
Sharp ckpt 刷所有脏页到硬盘,比如在关闭数据库的时候
fuzzy ckpt 刷新部分脏页
1.master thread checkpoint :master每一秒或者十秒落盘
2.sync check point : redo不可用的时候,这时候刷新到磁盘是从脏页链表中刷新的。
3.Flush_lru_list check point : 刷新flush list的时候,落盘的操作是异步的,因此不会阻塞其他事务执行。
检查点的作用:
缩短数据库的恢复时间
缓冲池不够用的时候,将脏页刷新到磁盘
重做日志不可用的时候,刷新脏页(循环使用redo文件,当旧的redo要被覆盖的时候,需要刷新脏页,造成检查点)
Innodb 的关键特性的讲解
1.插入缓冲
(insert buffer)
ibuf
5.5版本以后开始支持del等dml操作,change buffer
索引
导入大批量数据,先把索引清空,导入数据,创建索引
索引的优点:
1.优化查询(select dml)
2.优化排序(索引数据库有序,可以通过索引规避排序)
缺点:
1.针对dml,增加维护成本
2.占用空间
对于没有被缓存到内存的索引,数据库使用插入缓冲的两种条件
1.索引是辅助索引
2.索引不是唯一的
在合并插入缓冲的过程中,数据库并不去判断唯一性
使用insert buffer过程
1.判断辅助索引是否在内存中
2.若在则直接插入
3.如果不在,则先插入insert buffer
4.merge insert buffer
insert buffer的优点
1.减少磁盘的离散读取
2.将多个插入合并成一次操作(配合异步IO)
insert buffer 的合并操作
1.通过master thread定期进行合并
2.用户在读取辅助的时候进行合并
2.两次写 double write
部分写问题:页面的刷新会遇到部分写的问题,也就是说对于只写了其中一个页面,只写了一部分的内容,innodb的页面大小是16kb,但是写入过程中只写了4kb(操作系统仅仅保证512字节写入的完整性),这个是时候因为页面不是完整的,因此不能通过redo来进行恢复,redo恢复的前提条件是页是完成的。
在数据库崩溃后,传统的数据库会使用redo log来进程恢复,恢复的原理是通过redo对数据也进行重新进行物理操作,但是如果这个数据页本身也发生了损坏,那么redo对其进行重做也是没有作用的,innodb的二次写,在写入的时候,创造了一个关于页的副本,这样即使在发生写失效后,也可以通过副本页,对还原重做。
double write的实现机制
double write分为两部分
一部分是内存中的double write buffer ,大小为2MB (16k一个页,一共128页)
第二部分是磁盘共享表空间的128个数据页,
在对脏页进行落盘的时候,并不是直接进行落盘,而是先复制到double write buffer,然后再分别写入到共享空间,然后再写入表空间
innodb_doublewrite
开启关闭double_doublewrite
自适应hash索引
维护索引叶页面中所有记录的索引键值或键值前缀 到索引叶页面为孩子的hash映射关系,能够根据索引键值(前缀)快速定位到叶页面满足条件记录的offset,减少了B+树从root页面到leaf页面的路径定位,优化为hash index的快速查询
适用的sql
where a=1
where a=1 and b=2
不适用的sql
where a>1
选项参数:
innodb_adaptive_hash_index
开启关闭自适应hash索引
后台线程
Mysql有很多后台线程,其中包括了负责IO的相关线程
IO thred
1.write thread 默认四个,负责数据块的写入
2.read thread 默认四个,负责数据块的读取
查看方法
mysql> show variables like ‘%write%’;
±------------------------±---------------------+
| Variable_name | Value |
±------------------------±---------------------+
| delay_key_write | ON |
| innodb_doublewrite | ON |
| innodb_write_io_threads | 4 |
| max_write_lock_count | 18446744073709551615 |
| net_write_timeout | 60 |
±------------------------±---------------------+\
mysql> show variables like ‘%read_io%’;
±-----------------------±------+
| Variable_name | Value |
±-----------------------±------+
| innodb_read_io_threads | 4 |
±-----------------------±------+
mysql> show variables like ‘%innodb_io_capaci%’;
innodb_io_capacity 是硬盘性能关系
设置参考(默认值200)
nnodb_io_capacity
磁盘配置
200
单盘SAS/SATA
2000
SAS*12 RAID 10
5000
SSD
50000
FUSION-IO
±-----------------------±------+
| Variable_name | Value |
±-----------------------±------+
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
±-----------------------±------+
innodb_page_cleaners
在mysql 5.6中,开启了一个独立的page cleaner线程来进行刷lru list 和 flush list 。默认每隔一秒运行一次
mysql 5.7可以设置多个page cleaner线程提高脏页面刷新效率
1.5.6版本以前,脏页的清理工作交由master线程的。
2.page cleaner thread是5.6.2引入的一个新线程(单线程),从master线程中卸下buffer pool刷脏页的工作独立出来的线程(默认是一个线程)
3.5.7开始支持多线程刷脏页
下面配置参数如果值很大,则需要增加innodb_page_cleaners值,同时增加写线程。
mysql> show global status like ‘%wait_free%’;
±-----------------------------±------+
| Variable_name | Value |
±-----------------------------±------+
| Innodb_buffer_pool_wait_free | 0 |
±-----------------------------±------+
根据上面的介绍可以发现,缓冲池的大小,直接影响了数据库的性能,通过增加缓冲池的大小,可以提升数据库的性能,因为这样的情况下,数据库的性能可以提升性能
mysql> show variables like ‘%innodb_buffer_pool_size%’;
±------------------------±-----------+
| Variable_name | Value |
±------------------------±-----------+
| innodb_buffer_pool_size | 1090519040 |
±------------------------±-----------+
在5.7 buffer pool 在线修改buffer pool大小
innodb_buffer_pool_size = 536870912
innodb_buffer_pool_chunk_size = 134217728
在动态调整buffer pool大小的时候,必须是chunk_size的整数倍
配置参数(仅5.7有效):
mysql> set global innodb_buffer_pool_size=805306368;
配置多个buffer pool 配置多个缓冲池实例(重点)
mysql> show variables like ‘innodb_buffer_pool_instances’; ±-----------------------------±------+ | Variable_name | Value | ±-----------------------------±------+
| innodb_buffer_pool_instances | 1 | ±-----------------------------±------+
一般1G 建议配置为1
4GB 2
8GB 2
32 4
64 8
mysql参数调整:
innodb系统表空间的数据目录 /home/mysql3306/mysql3306/ibdata1
innodb_data_file_path
如果方多个,只能在最后表空间文件设置
innodb_file_per_table
独立表空间,默认开启
3.自适应hash索引
4.刷新临页
缓存池中大部分信息都是放置的b+tree索引的非叶子节点和叶子节点,自适应hash通过根据b+tree的访问模式对页中的记录创建hash索引,是完全的内存结构。
异步IO
Mysql在5.6之后开启了异步IO,默认情况下使用linux原生aio,查看mysql是否开启异步IO
参数:
Variable_name: innodb_use_native_aio
Value: ON
- 可以通过开启和关闭Native AIO功能来比较InnoDB性能的提升。官方的测试显示,启用Native AIO,恢复速度可以提高75%。
- 在InnoDB存储引擎中,脏页的刷新,即磁盘的写入操作则全部由AIO完成。
vim /etc/my.cnf
#— INNODB —#
可以修改,但是默认为innidb
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_buffer_pool_size = 1040M
innodb_buffer_pool_instances = 1
innodb_additional_mem_pool_size = 16M
innodb_log_files_in_group = 2
innodb_log_file_size = 256MB
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 30
innodb_flush_method = O_DIRECT
innodb_max_dirty_pages_pct = 75
innodb_io_capacity = 200
innodb_thread_concurrency = 32
innodb_open_files = 65535
innodb_file_per_table = 1
transaction_isolation = REPEATABLE-READ
innodb_locks_unsafe_for_binlog = 0
#innodb_purge_thread = 4
skip_name_resolve = 1
MyISAM
查看innodb引擎状态
mysql> show engine innodb status;
FILE I/O
show engine innodb status
FILE I/O
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
扩容innodb
单机扩容(/home---->/data)
1.关闭mysql
2.拷贝数据文件到新的目录
3.修改配置文件
4.重启数据库
扩容系统表空间
1.关闭mysql服务 2.如果上一个数据文件使用autoextend定义改为固定大小 检查数据文件大小,将其设置到最接近的大小
第三步:将新的数据文件添加到innodb_data_file_path的末尾,可选地使该文件自动扩展。 注意:只能将innodb_data_file_path中的最后一个数据文件指 定为自动扩展。
innodb_data_home_dir = innodb_data_file_path = /data/mydb/ibdata1:12M:autoextend
innodb_data_home_dir = innodb_data_file_path = /data/mydb/ibdata1:28M;/disk1/ibdata2:100M:autoextend
缩小线上的ibddata1的大小
1.使用mysqldump导出所有表结构
2.关闭MySQL
3.删除所有表空间文件
4.重新初始化MySQL
5.重启数据库
6.导入数据
mysql innodb表空间迁移
把一张表从实例迁移到B实例
将表空间迁移到其他实例
1.在目标实例上创建一个相同的表
2.在目标库上执行ALTER TABLE t DISCARD TABLESPACE; 3.在源库上执行FLUSH TABLES t FOR EXPORT;生成.cfg文件 4.讲.ibd文件和.cfg文件拷贝到目标实例
5.在源库执行unlock tables;
6.在目标库执行ALTER TABLE t IMPORT TABLESPACE;
迁移过程
在source server A上,创建一个表
mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
在destination server B上,也创建同样的表结构 mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
在destination server B上,discard 该表(这一步是必须的)
mysql> ALTER TABLE t DISCARD TABLESPACE;
- discard的意思就是从数据库detached,会删除ibd文件,保留frm文件。
- 也就意味着,你可以对frm文件操作,比如:rename table,drop table ,但是不能对ibd文件操作,比如:dml
在source server A上,执行 FLUSH TABLES … FOR EXPORT , 该表这时候处于quiesce状态,只读,且创建.cfg metadata文件 mysql> use test;
mysql> FLUSH TABLES t FOR EXPORT;
此时,.cfg文件在InnoDB的data directory中
flush tables … for export 会加锁,这时候,千万不能退出终端或session,否则加锁无效且.cfg文件自动删除。
拷贝.ibd & .cfg 从source server A 到 在destination server B
shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test 修改权限
在source server A, 执行unlock tables 来释放FLUSH TABLES … FOR EXPORT 加的locks mysql> use test;
mysql> UNLOCK TABLES;
在destination server B上,导入tablespace mysql> use test;
mysql> ALTER TABLE t IMPORT TABLESPACE;
innodb可传输表空间注意事项 ----------------------------- 必须开启 innodb_file_per_table 当这个表处于quiesced状态,甚至不能被select 两边实例的page size 一致
5.7 版本之前,不支持分区表transport
外键相关的表,必须设置 foreign_key_checks=0 才能成功
ALTER TABLE … IMPORT TABLESPACE 不需要.cfg metadata file . 但是,这样的话,MySQL就不会对schema进行verificate 5.6以及更高版本,import&export 版本必须在同一个series
在replication环境中,master & slave 都必须开启 innodb_file_per_table
对于InnoDB general tablespace,不支持discard & import tablespace
如果两边服务器的table row_format设置的不一样,会导致schema mismatch error
加密过的InnoDB tablespace 必须要拷贝.cfp 文件
MyISAM
在目标数据库创建表结构,
建议flush table with read lock
然后直接拷贝MYI MYD
mysql日志
如果删掉错误日志,需要执行:
mysql> falsh logs;
MySQL general日志
会记录所有执行过的sql文本文件
一般情况下,我们不开启general日志,因为对于数据库的影响比较严重,一般选择关闭,在某些特殊情况下,比如要审计sql,或者统计sql执行频次,临时打开,然后关闭
mysql> show variables like ‘%general%’;
±-----------------±-------------------------------+
| Variable_name | Value |
±-----------------±-------------------------------+
| general_log | OFF |
| general_log_file | /home/mysql3306/logs/mysql.log |
MySQL慢查询日志
执行时间长于某个点的sql被记录
| slow_query_log | ON |
| slow_query_log_file | /home/mysql3306/logs/mysql-slow.log |
超过该阀值的sql,被记录到慢日志(s)
mysql> show variables like ‘%long%’;
±-------------------------------------------------------±---------+
| Variable_name | Value |
±-------------------------------------------------------±---------+
| long_query_time | 2.000000 |
log_queries_not_using_indexes 记录没有用索引的查询,默认是关闭的
mysql> show variables like ‘%not_using_indexes%’;
±---------------------------------------±------+
| Variable_name | Value |
±---------------------------------------±------+
| log_queries_not_using_indexes | OFF |
log_throttle_queries_not_using_indexes
设定每分钟记录到日志的未使用索引的语句数目,超过这个数目后只记录语句数量和花费的总时间
min_examined_row_limit
要检查的行数大于等于N时 才记录为慢查询,前提是必须满足long_query_time和 log-queries-not-using-indexes约束。
mysql> show variables like ‘%min_examined_row_limit%’;
±-----------------------±------+
| Variable_name | Value |
±-----------------------±------+
| min_examined_row_limit | 0 |
±-----------------------±------+
2018/3/19
mysql备份
xtrabackup
是一个物理热备,备份数据文件
innobackupex --defaults-file=/etc/my3306.cnf --no-timestamp --user root --port 3306
/data/xtrabackup/all-20170402.bak
增量备份:
全量备份
sysbench
介绍了GTID的概念和变量,现在说下MySQL5.7下GTID增强的一些特性:
①:在线开启GTID。MySQL5.6开启GTID的功能需要重启服务器生效。
复制代码
mysql> set global gtid_mode=on;ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.
rows affected ( rows affected (
mysql> set global gtid_mode=ON;ERROR 3111 (HY000): SET @@GLOBAL.GTID_MODE = ON is not allowed because ENFORCE_GTID_CONSISTENCY is not ON.
mysql> set global enforce_gtid_consistency=on;Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_mode=ON;Query OK, 0 rows affected (0.16 sec)
复制代码
在线开启GTID的步骤:不是直接设置gtid_mode为on,需要先设置成OFF_PERMISSIVE,再设置成ON_PERMISSIVE,再把enforce_gtid_consistency设置成ON,最后再将gtid_mode设置成on,如上面所示。若保证GTID重启服务器继续有效,则需要再配置文件里添加:
gtid-mode=onenforce-gtid-consistency=on