建设建材网站,百度推广工资多少钱一个月,北京市建设工程招标网站,互联网站开发mysql常用操作命令
mysql:单进程多线程模型,一个SQL语句无法利用多个cpu core
一:基本命令
0.查看当前连接数
show global status like Thread$;
show variables like %timeout%;
show variables like log_%;1.查看当前连接状态
show processlist…mysql常用操作命令
mysql:单进程多线程模型,一个SQL语句无法利用多个cpu core
一:基本命令
0.查看当前连接数
show global status like Thread$;
show variables like %timeout%;
show variables like log_%;1.查看当前连接状态
show processlist;2.数据库连接
mysql -h 主机名 -u root -ppassword3.添加用户
insert into user (host,user,password,select_priv,insert_priv,update_priv) values (localhost,guest,password(guest123),Y,Y,Y);4.创建用户
create user usernamehost identified by password;
create user username% identified by password;#新创建用户无法登陆问题?
use mysql
delete from user where user;
flush privileges;# 对db_name下所有表都有查询(SELECT)权限
grant select on db_name.* to username%;# 对所有表有全部权限
grant all on *.* to username%;5.删除用户
drop user usernamehost;6.修改用户密码
set password for usernamehost password(123password);
update user set passwordpassword(你的新密码) where userroot;7.创建数据库病设置字符集和排序规则
create database data_name;
create database data_name character set utf8 collate utf8_general_ci;8.删除数据库
drop database data_name;二:操作命令
1.删除表
SELECT concat(DROP TABLE IF EXISTS , table_name, ;)FROM information_schema.tablesWHERE table_schema mydb;
# 或者
drop table table_name;2.显示表属性
desc user_table;
show columns from user_table;3.显示数据表的索引信息
show index from user_table;4.显示数据库所有以run开头的表信息
show table status likerun%;5.创建表,engine指定存储引擎,每张表都可以指定存储引擎
# CREATE TABLE IF NOT EXISTS table_test(
CREATE TABLE table_test(t_id INT UNSIGNED AUTO_INCREMENT,t_title VARCHAR(100) NOT NULL,t_author VARCHAR(40) NOT NULL,sub_date DATE,PRIMARY KEY(t_id)
)ENGINEInnoDB DEFAULT CHARSETutf8;6.插入数据
insert into table_name(field1,field2,field3,field4) values(value1,value2,value3,value4);7.更改字段类型
alter table system_info modify column ip varchar(100) ;8.更改字段为非空
alter table system_info alter column ip set not null;9.添加字段
alter table system_info add email varchar(30); 10.删除字段
alter table system_info drop column email;11.字段改名
alter table system_info rename email to new_email;12.清空表或删除记录
/* 清空表记录三种方式 */
# 1. delete
delete from table_name; 或 delete from table_name where id1;
# 删除select记录报错:mysql不允许对同一个表同时进行查询和更新
# 解决方案:对查询结果生产一个派生表,对派生表查询
delete from table_user where id in (select id from (select id from table_user where sex is null) as tmp);# 2.truncate
truncate table_name # 3.use information_schema 清空所有表记录
select table_name,table_schema from information_schema.tables where table_schemaalphacapture_bigai
select table_name,table_schema from information_schema.tables where table_schema like %alphacapture_bigai%
select concat(truncate table,table_schema,.,table_name,;) from information_schema.tables where table_schema in (数据库1,数据库2)13.插入记录
insert into table_name(field1,field2,field3) values(value1,value2,value3);1.更新记录
update table_name set field2张三 where id 3;15.修改表名
alter table table_name rename to new_table_name;16.模糊查询,%表示任意字符
select * from Student where name like %三%17.多条件查询and,or
select * from Sudent where age between 18 and 50;
select * from Student where age 18 and age 60 and address上海18.去重
select distinct address from Student;19.排序:asc升序,desc倒序
select * from Student order by age desc;20.查询上做计算
select age*3 name from Student;21.最大max,最小min,平均avg,求和sum,个数count
select count(id) from Student;22.分组查询 group by 将某一列相同数据视为一组
#使用了group by,select后只能跟分组列和聚合函数
#查询人数大于3的地区的最大年龄
select address,max(age) from Student group by address having count(*)3;23.分页
select * from Student limit 3,5; # 从第三条记录,查询五条
select * from Student limit (n-1)*m,m; # 第n也查询m条记录24.join连表查询 on 条件
select Sites.id,Sites.name,Log.count,Log.date from Sites inner join Log on Sites.idLog.site_id;24.查看表占用磁盘空间大小,M单位
# 查看单个表
select concat(round(sum(DATA_LENGTH/1024/1024),2),M) as table_size from information_schema.tables where table_schemacsjdemo AND table_namedemo2;
SELECT (DATA_LENGTH INDEX_LENGTH)/1024/1024 as table_data_size from TABLES WHERE TABLE_NAME tableName and TABLE_SCHEMAdbName;
#查看数据库
SELECT (sum(DATA_LENGTH) sum(INDEX_LENGTH))/1024/1024 as db_data_size from TABLES where TABLE_SCHEMAdbName;25.创建主键primary三种方式
create table table_name(uid INT PRIMARY KEY,uname VARCHAR(10),
)
create table table_name(uid INT,uname VARCHAR(10),PRIMARY KEY(uid), # 或者PRIMARY KEY(uid,uname)联合主键
)
alter table table_name add PRIAMRY KEY(udi); # 添加主键约束
alter table table_name drop PRIMARY KEY; # 删除主键约束 26.创建唯一索引三种方式
create table table_name(uid INT,uname VARCHAR(10),UNIQUE []
)
alter table table_name unique index(filed_name,filed_name)
create UNIQUE INDEX indexName on table_name(filed_name,filed_name)三: 复制,导入导出数据
1.复制n条记录并创建
INSERT into reyo (num,overtime) SELECT num,overtime from reyo where id IN(1,3,5,6,7,9);
INSERT into reyo (num,overtime) SELECT num,overtime from reyo where id IN(1,3,5,6,7,9);2.导出整个数据库
mysqldump -u root -p dbnamedbname.sql 3.导出表 show variables like %secure%查看安全目录
mysqldump -u root -p dbname usersdbname_users.sql
SELECT * FROM runoob_tbl INTO OUTFILE /var/lib/mysql-files/Dbug_manangement.txt;
SELECT * FROM users INTO OUTFILE /var/lib/mysql-files/users.sql;4.导入备份的整个数据库
mysql -u root -p Detector.sql # 需要再sql文件创建或指定数据库
mysqlsource /home/abc/abc.sql # 进入数据库下use Detector5.插入数据到某个表
load data local infile /var/lib/mysql-files/CaseUrl.sql into table CaseUrl;
load data local infile /var/lib/mysql-files/CaseUrl.sql into table CaseUrl
(id,name, url, status_code, result, processresult, proposal,create_time,test_time,case_id) FIELDS TERMINATED BY ,
set create_timeDATE_FORMAT(create_time,%Y-%m-%d %H:%i:%s)四:mysql性能优化(四个维度:1.架构,2.硬件,3.DB优化,4.sql优化) 架构:集群读写分离,数据库切分 硬件:高效的磁盘读写性能 DB:参数优化(日志不能笑,缓存足够大,连接够用) my.ini或my.cnf配置文件: sort/join/read/rnd buffer:4M或8M或16Mtmp/heap table:96M或128Minnodb_flush_log_at_trx_commit:对redo日志刷盘频率的设定 0:缓冲区的redo log会每秒写入到磁盘的日志文件。但每次事务提交不会有任何影响也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下MySQL性能最好但如果 mysqld 进程崩溃通常会导致最后 1s 的日志丢失。 1:每次事务提交时,缓冲区redo log保证一定会被写入到磁盘的日志文件。这也是默认值。这是最安全的配置但由于每次事务都需要进行磁盘I/O所以也最慢。 2:每次事务提交时,缓冲区redo log异步写入(不保证)到磁盘的日志文件。这时如果 mysqld 进程崩溃由于日志已经写入到系统缓存所以并不会丢失数据在操作系统崩溃的情况下通常会导致最后 1s 的日志丢失。 sync_binlog:binlog刷盘的频率(保证主库和主从库的一致性:innodb_flush_log_at_trx_commit和sync_binlog都设置为1)interactive_timeout:交互模式下超时时间,五分钟或十分钟lock_wait_timeout:表锁锁定时间time_zone:使用datetime减少性能消耗wait_timeout:程序连接mysql超时时间,五分钟或十分钟innodb_buffer_pool_size:缓冲池大小(越大,磁盘I/O减少)2个G左右innodb_buffer_pool_instances:配置多个缓冲池实例tmp_table_size:临时表的最大大小 sql优化 https://www.zhihu.com/question/486105337/answer/2538190061 explain检查sql语句定位优化点或开启慢查询定位优化点 优化方向:(避免不要的列|分页优化|索引优化|JOIN优化|排序优化|union优化) 1. 避免不要的列- 避免使用select *- 多用limit- 选择合理的字段类型2. 分页优化(数据量较大)- 延迟关联- 书签方式3. 索引优化- 合理使用索引(一个表索引不超过5个),避免索引失效- 利用覆盖索引- 正确使用联合索引- 避免索引失效情况4. JOIN优化- join表不宜过多(不超过5个)- 小表驱动大表- 用连接查询代替子查询5. 排序优化- 利用索引排序6. union优化- 使用UNION ALL(不去重,所有数据)替代UNION(去重)索引失效情况: (1)联合索引不满足最左匹配原则,联合索引最左边字段必须出现在查询条件中 (2)错误使用like,以%开头如 like ‘%abc’,(当like以%结尾索引有效) (3)错误使用or,or两边字段有一个没有创建索引(where id2 or name“Tom”)或两边为范围查询(where id10 or id20),导致失效 (4)索引列参与运算, 如where id-110 或使用了函数 where SUBSTR(id_no,1,3)100 (5)类型隐式转换,where条件上进行了类型转换,比如字段是字符串类型,却填上了数字 (6)两列做比较,即使两列都有索引,也会失效 where idage (7)不等于比较,where name!“Tom” 或 where id“11” 有可能不走索引,查询结果集较小货走索引否则不走索引 (8)非空判断(is not null / is null / not in / in / exists / not exists/ ) 使用where id_no is not null不走索引,is null走索引 使用not exists 不走索引 使用where id not in (2,3)普通索引则失效,主键走索引 (9)当mysql估计全表扫描速度比索引速度快的时候不会使用索引(order by就是如果是select *则有大量回表,索引不走索引,走全表扫描到内存去排序) 五:索引详解 操作系统和内存:最小单位是页page 操作系统和磁盘:最小单位是块block 磁盘I/O:文件系统每次读取一块(默认4K)单位大小到内存 mysql:存储数据以页(page默认16k)为单位,mysql读取一页页读取 1.索引演化史? 二叉查找树-AVL平衡二叉树-B-Tree(多路平衡查找树)-BTree: B-Tree:多路平衡查找树,每个节点包含多对(父节点指针,子节点指针,键key,值data),相比于AVL缩减了节点数,减少了树的高度 BTree:1.非叶子节点(双向链表)只存储键值叶子节点指针,2.值data顺序存在同一层的叶子节点上相比于B-Tree每个节点能存储更多的key减少了树的高度和磁盘I/O次数3.叶子节点之间有指针,链表支持范围查找 通过非叶子节点的二分查找以及指针确定数据在哪个页中,进而去数据页查找数据 一般高度为1-3:叶子节点16k(一条数据一般1k)存16条数据,非叶子节点存1170个指针117011701621902400** 2.什么是索引? 对一列或多列值进行排序的数据结构(类似目录排序好了的,在小文件查找) 3.mysql有哪些索引?那些字段可以建立对应索引? 普通索引 主键索引(聚簇索引,唯一索引unique唯一且不为NULL), 复合索引(最多包含16列:where多条件最左原则):可用于包含所有列或第一列,前两列,前三列…等,blob和text也能创建索引, 但是必须指定前面多少位 全文索引(char、varchartext 列上可以创建全文索引,一般不使用,不是mysql专长) 唯一索引(innodb不是聚簇索引), 空间索引(对空间数据类型字段建立索引,mysql有四种:GEOMETRY,POINT,LINESTRING,POLYGON) 4.聚集索引和非聚集索引区别? 都是BTree数据结构 聚簇索引:叶子节点存储索引和数据,找到索引就找到数据(其他为辅助索引,存储索引id,没有去覆盖索引的话要回表查找主键索引找到数据)。 非聚簇索引:叶子节点存储索引和数据行的地址,根据索引找到数据行的位置在去磁盘取出数据。 5.什么是回表? 通过非主键索引查询,select所获取的字段不能通过非主键索引获取到,需要回表查询主键索引。 6.覆盖索引,非覆盖索引? 覆盖索引:所查的字段在当前索引叶子节点上存在,不用回表,直接作为结果返回 7.什么是索引下推? where多条件判断,对索引中包含的字段先做判断,再去回表没有索引的字段(减少回表次数) 8.什么情况下不推荐使用索引? 目的是:索引是为了查询更快,占用空间更小 1.数据唯一性差(比如性别只有两种数据) 2.频繁更新的字段不用索引 3.不用无序的的值如身份证,uuid无序不能作为索引 4.字段不在where语句后出现(where含IS NULL/IS NULL/IS NOT NULL/like %等,不用索引) 5.过长的字段使用前缀索引 6.参与计算的字段不适合建立索引 那些情况适合建立索引? 频繁作为where条件语句查询的字段关联字段需要建立索引例如外键字段student表中的classid, classes表中的schoolid 等排序字段可以建立索引 六:InnoDB和MyISAM区别 MyISAM:不支持事务,支持表级别锁(限制了读/写的性能),拥有较高的插入和查询速度,B的非聚簇索引,通常用于只读或以读为主的场景. 怎么快速向数据库插入100万条数据?先用MyISAM插入数据,然后修改存储引擎为InnoDB InnoDB:支持事务,支持行/表级别锁,/外键(数据的完整性和一致性更高),采用B的聚簇索引,通常用于经常更新的场景. 1.InnoDB和MyISAM索引区别 InnoBD主键索引采用B的聚簇索引: 每个InnoDB表都有且只有一个特殊的索引称为聚簇索引 用于存储行数据。通常聚簇索引与主键同义 。 1. 表定义了主键,则pk就是聚集索引2. 没有定义主键,第一个非空唯一索引列就是聚集索引3. 否则,InnoDB会创建一个隐藏的row-id作为聚集索引MyISAM索引采用B的非聚簇索引: 不存储全部数据,只存储数据行的地址 七:mysql预编译(sql执行前会进行解析和校验),参数带入 益处:加快执行速度,防止sql注入 场景:SQL语句一样,参数不一样,可以对SQL语句预编译 sql语句执行流程: 连接层(处理连接/鉴权/安全管理)-服务层(系统管理/sql接口/缓存/解析/预处理/优化)-引擎层(具体与文件系统打交道)-存储层 sql语句-查询缓存(默认关闭不推荐使用,sql语句必须相同,且表数据表动大)-语法解析预处理器(检查是否有语法错误)-优化器-执行器 语法:prepare name from statement; 定义:prepare statement_1 from ‘select * from user where id?’; # 通过?进行占位参数:set id2;执行.execute statement_1 using id; 怎么预防sql注入 不信任用户提交的数据(参数过滤,严格检查参数类型,转义,限制长度).mysql预编译(参数化查询,变量绑定) 八:mysql 事务(不在分割的操作集合)acid实现原理
1.ACID? 原子性(Atomicity):(undo log回滚日志实现)指一个事务不可分割,是一个最小的操作单元(包含若干个操作),要么全部成功,要么全部失败 隔离性(Isolation):(锁和mvcc实现)多个事务并发执行,事务之间相互隔离 持久性(Durability):(redo log实现)InnoDB提供了一个缓存Buffer,读取和写入都先在Buffer中(并同时把操作记录到redo log,防止数据丢失) 一致性(Consistency):数据处于合法状态(满足预定约束就是合法) 从数据库层面数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中C(一致性)是目的A(原子性)、I(隔离性)、D(持久性)是手段是为了保证一致性数据库提供的手段。数据库必须要实现AID三大特性才有可能实现一致性。 从应用层面通过代码判断数据库数据是否有效然后决定回滚还是提交数据。Innodb如何实现事务的? 以update为例 (1).开启事务,Innodb根据接受的update语句,找到数据所在页,并加该页缓存在Buffer pool(change buffer)中 (2).执行update,修改Buffer pool中数据 (3).记录 undo log日志(便于事务回滚和mvcc)并写入Log Buffer中并关联redo log(可刷盘) (4).记录 redo log(prepare状态)日志(便于断电恢复数据)并写入Log Buffer中(可刷盘) (5).记录bin log(数据表结构变更日志:用于主从复制和数据库恢复) (6-1).事务提交,redo log(改为commit状态),可触发redo log刷盘机制 (6-2).事务回滚,则利用undo log日志进行回滚 3.mysql有几种日志? bin log(记录sql),redo log(),undo log,slog log,一般日志 4.mysql日志是否实时写入磁盘?(log buffer) bin log: sync_binlog0:每次提交事务后不会马上写入到磁盘,先写到page cache,由操作系统决定写入什么时候写入磁盘(有丢失事务日志的风险) sync_binlog1:每次提交事务都会执行fsync写入磁盘(强一致性,性能较低) sync_binlogn:每次提交事务,先写到page cache,积累n个事务才fsync到磁盘(有丢失n个事务日志的风险) redo log()和undo log: innodb_flush_log_at_trx_commit0:每秒(log buffer中提交的事务)写入磁盘,系统并调用fsync写入磁盘(可能丢失一秒的数据) innodb_flush_log_at_trx_commit1:有事务提交立即调用fsync写入磁盘(不会丢失,性能差) innodb_flush_log_at_trx_commit2:有事务提交都写给操作系统的page cache,由操作系统决定什么时候调用fsync写入磁盘(一系列丢失) 5.binlog有几种录入格式? STATEMENT:默认方式,基于SQL语句的复制记录sql语句,文件较小 ROW:基于行的复制,文件较大 MIXED:前两种混合 6.为什么将redo log的数据写到磁盘比将Buffer数据持久化到磁盘要快? 1.Buffer数据持久化是随机写I/O,redo log是追加,顺序IO 2.Buffer数据持久化是以页page为单位,redo log只需要写入的真正部分(减少了无效I/O) 7.bin log和redo log(两段提交)区别 1.写入redo log(prepare状态) 2.写入bing log 3.提交事务,redo log(改为commit) 九:主从同步 Mysql主从复制中有三个线程:Master(binlog dump thread) Slave(I/O thread,SQL thread) 使用binlogposition偏移量进行增量同步 1.同步过程
1. Master所有变更都记录到binlog中去(MySQL Server层的实现)
1. 主节点binlog dump线程,当binlog有变动时,binlog dump线程读取内容并发送给从节点
1. 从节点I/O线程接收binlog,并写入到relay log(中继日志)中
1. 从节点SQL线程读取relay log并对数据进行重放2.同步策略
同步策略:Master会等待所有的Slave回应后才提交(这个策略严重影响性能)半同步策略:Master至少等待一个Slave回应后才提交异步策略:Master不会等待Slave回应就可以提交(默认)延迟策略:Slave落后Master指定的时间
十:mysql主从方案 mysql如何保证主从数据的一致性的 分片中间件:myCat/shardingSphere 一主多从:缓解读压力 主服务配置mysql.cnf: #主服务还需要创建对应权限的用户用于数据同步 log-binmysql-bin # 表示启用二进制文件-文件名称 server-id3307 # 表示server编号 从服务执行sql命令 # 1.配置主服务 change master to master_host“192.168.1.2”,master_port3306,master_user“copy”,master_password“123456”,master_log_file“mysql-bin.00001”,master_log_pos154; # 2.开启主从同步 start slave # 3.查看是否成功 show slave status \G;
双主双从:缓解写压力 主1配置 log-binmysql-bin server-id3301 auto_increment_increment2 # 主键递增步长 auto_increment_offset1 # 从1开始 log-slave-updates # 是否记录binglog sync-binlog1 # 几次事务记录binlog 主2配置 log-binmysql-bin server-id3302 auto_increment_increment2 # 主键递增步长 auto_increment_offset2 # 从1开始 log-slave-updates # 是否记录binglog sync-binlog1 # 几次事务记录binlog
十一:主键方案(自增id/uuid/雪花算法) https://www.zhihu.com/question/397289720 https://juejin.cn/post/7153273187366043661 三种方案:
1. 自增id
2. uuid: uuid导致页分裂,性能问题,存储空间较大
3. 雪花算法及其改进算法分布式id实现方式: uuid/单机数据库自增ID步长/Redis自增ID/跳跃式自增ID/Snowflake及其改进算法 雪花算法(保证递增性):64位 1位:符号位 43位:时间戳 10位:机器id 12位:一个节点一毫秒产生ID的数量(4096) 十二:性能极限
mysql性能极限 mysql单表字段数:建议20-50 mysql默认单字段大小 存储最大空间为1M,可以修改max_allowed_packet16 mysql单表 老版mysql3.22中,还是ISAM存储引擎,单表限制为4GB 之后为INNODB 单表限制64TB 数据库: Cmshelp 团队做CMS 系统评测时的结果来看, MySQL单表大约在2千万条记录4G下能够良好运行 经过数据库的优化后5千万条记录10G下运行良好。 阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB才推荐进行分库分表。 PostgreSQL性能极限 最大单个数据库大小:不限 最大数据单表大小:32TB 单条记录大小:1.6TB 单字段最大允许:1GB 单表允许最大记录数:不限 单表最大字段数:250-1600取决于字段类型 单表最大索引数:不限 十三 额外问题及思路
“Lost connection to MySQL server during query” ? 1网络延迟高 2.读/写:导致数据传输超时net_read_timeout/net_write_timeout 3.连接初始化超时,connect_timeout 默认30s 4.传输中使用了较大的string field或blob field 导致超过了max_allowed_packet https://blog.csdn.net/zyself/article/details/91376690 数据库的三大范 为设计冗余小,结构合理的数据库,设计数据库时必须满足一定范式(规则) 一般设计都是反范式,通过冗余的数据避免跨表垮库查询,利用空间换时间,提高性能 第一范式:表中所有字段是不可分解的原子值 第二范式:表中每一列都和主键相关,而不能只与主键一部分相关(针对联合主键)(表中只保存一种数据,不可以把多种数据保存在同一个表中) 第三范式:确保每列都和主键直接相关而不是间接相关 varcahr和char区别 char(最多存放255个字节)适用数据大小固定/较小/经常更新/不容易产生内存碎片: 1.长度固定,插入数据小于固定长度则用空格填充 2.存取速度比varchar快很多(甚至50%),空间换时间 varchar(最多存放65532个字节,等价于21845字符): 1.varchar长度N(实际长度n255,用两个字节存放长度.小于255,用一个字节存放长度) 2.长度可变,按实际插入数据来存储() 3.填写2的n次方,varchar(8),varchar(64) InnoDB表索引前缀长度为767字节,utf-8编码为255(255*3765) 对text,只能添加前缀索引,前缀索引最大能达到1000字节 当varchar大于某些值: varchar大于255自动变为tinytext varchar大于500自动变为tinytext varchar大于20000自动变为tinytext 当varchar255,使用varchar或text没有区别 blob和text区别 blob:二进制大对象(存储二进制数据,没有字符集)最大长度16k text:大对象,存储大字符串,有字符集(根据字符集的校对规则对值进行排序比较)最大长度16k DATETIME和TIMESTAMP区别 都表示日期和时间,格式一致,存储秒后6位小数 区别 日期范围:DATETIME(1000-01-01到9999-12-31),TIMESTAMP(1970-01-01到2038-01-09) 存储空间:DATETIM为8字节,TIMESTAMP为4字节 时区:DATETIME与时区无关,TIMESTAMP与时区有关 默认值:DATETIME默认为null,TIMESTAMP默认为当前时间,不为空(not null) in和exists的区别 https://blog.csdn.net/jinjiniao1/article/details/92666614 DECIMAL记录货币 float和double是二进制存储,有误差 decimal是字符串存储 怎么存储表情包emoji? 字符串存储utf-8mb4编码 大量外键问题 不影响select影响update/insert/delete(当对子表进行写入操作,父表会被加上共享锁,对子表进行高并发时,父表的共享锁长时间不能释放,就不能对父表进行写入而只能读) WAL技术(Write-Ahead Logging)RedoLog(对所有页面的操作写入日志文件,实现事务的持久性) delete删除记录使用binlog回滚 恢复数据时可以先备份,在停止所有写入操作:flush tables with read lock或 set global read_only1同时配置文件里设置read_only防止重启失效 查看binglog是否开启 show variables like %log_bin%; 查看数据文件存放路径 show variables like %datadir%;
show master status; # 查看当前正在写入的binlog
show master logs; # 查看所有binlog show binary logs;查看binlog日志内容 show binlog events in mysql-bin.000001\G; # 确定需要回滚的事务的position将需要恢复的事务里的操作转为sql /opt/bitnami/mysql/bin/mysqlbinlog --no-defaults -v --databasedatabase_name --start-position966048 --stop-position981142 /bitnami/mysql/data/mysql-bin.000007 /tmp/mysqllog.sql将导出导出sql的delete转换为insert shellcat srliao.sql.bak| sed -n /###/p | sed s/### //g;s/\/\*.*/,/g;s/DELETE FROM/;INSERT INTO/g;s/WHERE/SELECT/g; |sed -r s/(17.*),/\1;/g | sed s/1//g| sed s/[1-9]/,/g | sed s/[1-9][0-9]/,/g mysqllogOK.sql执行insert sql source /tmp/mysqllogOK.sql大公司为什么不使用外键强关联问题 主要存在以下问题 1.在该表进行增删改查会触发查询关联表的记录是否存在,该性能消耗系统是允许的 2.数据一致性全部交给数据库,数据库是否能承受 3.查询关联表上会做一个内部锁,是否存在高并发死锁情况 4.后期的分库分表,外键约束格外离谱 这些问题在互联网公司显得很严重,访问量大的时候,mysql系统上无法得到解决的 https://www.cnblogs.com/JethroYu/p/13570630.html delete,truncate,drop有什么区别? 执行速度:droptruncatedelete
1.delete 1.属于数据库DML操作语言,只删数据不删表结构,会走事务,执行时触发trigger 2.delete执行时,会将删除数据缓存到rollback segement中,事务commit之后生效 3.delete删除全部数据,MyISAM会立刻释放磁盘空间,InnoDB不会释放 4.delete from table_name where 带条件的MyISAM和InnoDB都不会释放磁盘空间 5.delete之后optimize table table_name会立刻释放磁盘空间,不管MyISAM或InnoDB 6.delete操作是一行一行删除,且产生删除操作日志,并记录到redo和undo 7.delete删除后id会继续递增 ALTER TABLE TableName AUTO_INCREMENT1 # 将auto_increment重置 2.truncate 1.属于DDL定义语言,不走事务,原数据不放到rollback segement中,执行不触发trigger 2.truncate table table_name会立刻释放磁盘空间不论MyISAM或InnoDB,类似drop table然后create table,做了优化 3.快速清空表,并重置auto_increment的值 MyISAMtruncate会重置auto_increment为1,delete后不变 InnoDB: truncate会重置auto_incrment为1,delete后不变(delete之后重启则auto_increment为1) 也就是说InnoDB的表本身是无法持久保存auto_increment。delete表之后auto_increment仍然保存在内存但是重启后就丢失了只能从1开始。实质上重启后的auto_increment会从 SELECT 1MAX(ai_col) FROM t 开始。 SET FOREIGN_KEY_CHECKS0; #取消外键约束 TRUNCATE TABLE table_name; SET FOREIGN_KEY_CHECKS1; #设置外键约束: 3.drop 1.属于DDL定义的语言 2.drop之后立刻释放磁盘空间,不管是MyISAM或InnoDB 3.drop且删除表结构,被依赖的约束(constrain),触发器(trigger),索引(index) 4.依赖该表的存储过程/函数将保留,状态为invalid delete是把目录撕了truncate是把书的内容撕下来烧了drop是把书烧了 _INCREMENT1 # 将auto_increment重置
2.truncate 1.属于DDL定义语言,不走事务,原数据不放到rollback segement中,执行不触发trigger 2.truncate table table_name会立刻释放磁盘空间不论MyISAM或InnoDB,类似drop table然后create table,做了优化 3.快速清空表,并重置auto_increment的值 MyISAMtruncate会重置auto_increment为1,delete后不变 InnoDB: truncate会重置auto_incrment为1,delete后不变(delete之后重启则auto_increment为1) 也就是说InnoDB的表本身是无法持久保存auto_increment。delete表之后auto_increment仍然保存在内存但是重启后就丢失了只能从1开始。实质上重启后的auto_increment会从 SELECT 1MAX(ai_col) FROM t 开始。 SET FOREIGN_KEY_CHECKS0; #取消外键约束 TRUNCATE TABLE table_name; SET FOREIGN_KEY_CHECKS1; #设置外键约束: 3.drop 1.属于DDL定义的语言 2.drop之后立刻释放磁盘空间,不管是MyISAM或InnoDB 3.drop且删除表结构,被依赖的约束(constrain),触发器(trigger),索引(index) 4.依赖该表的存储过程/函数将保留,状态为invalid delete是把目录撕了truncate是把书的内容撕下来烧了drop是把书烧了
