MySQL详解

最新推荐文章于 2023-03-07 08:59:18 发布

等的人心好烦

最新推荐文章于 2023-03-07 08:59:18 发布

阅读量341

点赞数

分类专栏：运维

本文链接：https://blog.csdn.net/yongchaocsdn/article/details/79570870

版权

运维专栏收录该内容

62 篇文章 2 订阅

订阅专栏

安装MySQL 5.7:https://www.linuxidc.com/Linux/2016-09/135288.htm

忘记root密码:http://www.jb51.net/article/77858.htm

关系型数据库基础理论

文件：文件：

数据冗余和不一致性表示层

数据访问困难文件

数据孤立逻辑层

完整性问题文件系统：存储引擎

原子性问题物理层

并发访问异常元数据

安全性问题数据：数据块

DBMS：关系型数据：

层次模型表示层：表

网状模型逻辑层：存储引擎

关系模型物理层：数据文件

RDBMS

关系模型：（结构化数据模型）

关系模型（一张二维表）

实体-关系模型

对象关系模型，基于对象的数据模型

半结构化数据模型：XML(扩展标记语言)

<name>Jerry</name>

gender:

name:

uid:

birthdate:

name:age:gender

name:uid:birthdate

关系:关系代数运算

交集：

并集：

差集：

全集：

补集：

SQL：Structure Query Language

SystemR:SQL

Ingres,Oracle,Sybase

ANSI:ansi-sql 标准

DML：数据操作语言

INSERT

DELETE

SELECT

UPDATE

DDL：数据定义语言

CREATE

DROP

ALTER

DCL:数据控制语言

GRANT

REVOKE

访问权限

RDB对象：

库，表，索引，视图，用户，存储过程，存储函数，触发器，事件调度器

约束：

域约束：数据类型约束

外键约束：引用完整性约束（参照完整性约束）

主键约束：某字段能唯一标识此字段所属的实体，并且不允许为空

唯一性约束：每一行的某字段都不允许出现相同值，可以为空

一张表中可以有多个

检查性约束：age：int

constraint

数据存储和查询

存储管理器：

权限及完整性管理器

事务管理器

文件管理器

缓冲区管理器

查询管理器

DML解释器

DDL解释器

查询执行引擎

MySQL

单进程

多线程

守护线程

应用线程

10个用户

256M

Thread recuse线程重用

32bit：

2.7G

64bit:

smp:对称多处理器

数据库体系结构：

关系运算：

投影：只输出指定属性

选择：只输出符合条件的行

自然连接：具有相同名字的属性上所有取值相同的行

笛卡尔积：

(a+b)*(c+d)=ac+ad+bc+bd

并：集合运算

SQL查询语句：

sequel-->SQL

SQL-86

SQL-89

SQL-92

SQL-99

SQL-03

SQL-08

SQL语言的组成部分

DDL

DML

完整性定义语言：DDL的一部分功能

视图定义：

事务控制：

嵌入式SQL和动态SQL：

授权：DCL

使用程序设计语言如何跟RDBMS交互：

嵌入式SQL：与动态SQL类似，但其语言必须程序编译时完全确定下来；

ODBC

动态SQL：程序设计语言使用函数(mysql_connect())或者方法与RDBMS服务器建立连接，并进行交互；通过建立连接向SQL服务器发送查询语句，并将结果保存至变量中而后进行处理；

JDBC

MySQL体系结构：

表管理器：负责创建，读取或修改表定义文件；维护表描述符高速缓存；管理表锁

表结构定义文件

表修改模块：表创建，删除，重命名，移除，更新或插入之类的操作

表维护模块：检查，修改，备份，恢复，优化(碎片整理)及解析

行：定长，变长

文件中记录组织：

堆文件组织：一条记录可以放在文件中的任何地方

顺序文件组织：根据”搜索码”值顺序存放

散列文件组织：

表结构定义文件，表数据文件

表空间：table space

数据字典：Data Dictionary

关系的元数据：

关系的名字

字段名字

字段的类型和长度

视图

约束

用户名字，授权，密码

缓冲区管理器：

缓存置换策略

被钉住的块

访问路径的选择性：一个访问路径的选择性是所有获取的页面数（如果使用这个访问路径去获取所有想要的元组）。如果一个表包含一个与给定条件相匹配的索引，就至少存在两条访问路径：使用索引和扫描整个数据文件。

最有选择性的路径是检索最少页数的路径；使用最有选择性的路径将使用获取数据的代价降到最小。而一个访问路径的选择性依赖于选择条件中的主合取体（与涉及的索引有关），每个合取体就好比表上的一个过滤器，满足一个给定合取的元组在表中所占的百分比称为缩减因子。

MySQL数据库基础及编译安装

MySQL 

MariaDB

Percona

MySQL安装：

专用软件包管理器包

deb, rpm

rpm:

RHEL(OracleLinux)， CentOS

SUSE

通用二进制格式包

gcc: x86, x64

源代码

5.5, 5.6

cmake

MySQL用户密码修改：

1、# mysqladmin -u USERNAME -hHOSTNAME password 'NEW_PASS' -p

2、mysql> SET PASSWORD FOR'USERNAME'@'HOST'=PASSWORD('new_pass');

3、mysql> UPDATE mysql.user SETPASSWORD=PASSWORD('new_pass') WHERE CONDITION;

MySQL安装：

源码安装MySQL

cmake

字符集：

人：00100110 00101010

人：10101011 10010001

汉字：字符集

GBK

GB2312

GB18030

UTF8

排序规则：

性能分析PROFILING

mysql mysqld

Unix

mysql mysql.sock mysqld

Windows

mysql memory(pipe) mysqld

不在同一主机上，基于TCP/IP协议

mysql

-uroot –h172.16.100.1

MySQL客户端工具：

mysql

mysqldump

mysqladmin

mysqlcheck

mysqlimport

[client]

-u USERNAME

-h HOST

-p ''

--protocol {tcp|socket|pipe|memory}

--port PORT

MySQL非客户端工具

myisamchk

myisampack

MyISAM:

每表三个文件：

.frm: 表结构

.MYD：表数据

.MYI：表索引

InnoDB：

所有表共享一个表空间文件；

建议：每表一个独立的表空间文件；

.frm: 表结构

.ibd: 表空间（表数据和表索引）

showvariables like ‘%innodb%’;

vim my.cnf

innodb_file_per_table= 1

servicemysqld restart

mysql>

mysql

--user, -u

--host, -h

--password, -p

--port

--protocol

--database DATABASE, -D 将数据库设为默认数据库

mysql -Dmydb

selectdatabase();

mysql>

交互式模式

批处理模式(脚本模式)

mysql < init.sql

vim test.sql

create database testdb;

create table testdb.tb1(idint,name char(20));

mysql >\. /root/test.sql

也可使用

#mysql < test.sql

mysql>

命令两类：

客户端命令

服务器语句：有语句结束符，默认;

\d: 定义语句结束符

客户端命令：

\c: 提前终止语句执行

\g: 无论语句结束符是什么，直接将此语句送至服务器端执行；

\G: 无论语句结束符是什么，直接将此语句送到服务器端执行，而且结果以竖排方式显示；

\! COMMAND: 执行shell命令

\W: 语句执行结束后显示警告信息；

\#: 对新建的对象，支持补全功能；

mysql>

补全：

名称补全

#mysql--html

服务器端命令获取帮助：

help KEYWORD

#mysqladmin [options] command [arg] [command [arg]] ...

#mysqladmin -uroot -p password 'NEW_PASS'

create DATABASE

#mysqladmin create hellodb

drop DATABASE

ping

mysqladmin ping

processlist

mysqladmin processlist

status

mysqladmin status

--sleep N：显示频率

--count N: 显示多个状态

extended-status: 显示状态变量

mysqladmin extended-status

variables: 显示服务器变量

flush-privileges: 让mysqld重读授权表, 等同于reload；

flush-status: 重置大多数的服务器状态变量

flush-logs: 二进制和中继日志滚动

flush-hosts:

refresh: 相当于同时执行flush-hosts和flush-logs

shutdown: 关闭mysql服务器进程

version: 服务器版本及当前状态信息；

start-slave: 启动复制，启动从服务器复制线程；

SQL thread

IO thread

stop-slave: 关闭复制；

mysqldump,mysqlimport, mysqlcheck

开发视角：

数据类型

约束

数据库、表、索引、视图

SELECT

MySQL数据类型及sql模型

存储引擎，也被称为表类型：

MyISAM表: 无事务，表锁

.frm: 表结构定义文件

.MYD: 表数据

.MYI: 索引

InnoDB表：事务，行锁

.frm: 表结构

.ibd: 表空间（数据和索引）

MySQL:

mysql: MyISAM

SHOWENGINES

SHOWTABLE STATUS [LIKE ...]

show table status \G

程序语言连接数据的方式：

动态SQL：通过函数或方法与数据库服务建立连接，

嵌入式SQL：

JDBC, ODBC

客户端：mysql、mysqladmin、mysqldump、mysqlimport、mysqlcheck

服务器：mysqld, mysqld_safe, mysqld_multi

my.cnf

/etc/my.cnf--> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf -->--default-extra-file=/path/to/somefile --> ~/.my.cnf

[mysqld]

[mysqld_safe]

[client]

host =

[mysql]

# mysqld--help --verbose

#killallmysqld

datadir =/mydata/data

hostname.err:错误日志

1、此前服务未关闭；

2、数据初始化失败；

3、数据目录位置错误；

4、数据目录权限问题；

DBA:

开发DBA：数据库设计、SQL语句、存储过程、存储函数、触发器

管理DBA：安装、升级、备份、恢复、用户管理、权限管理、监控、性能分析、基准测试

数据类型：

数值型

精确数值

int

decimal

近似数值

float

double

real

字符型

定长：CHAR(#)、BINARY

变长：VARCHAR(#)、VARBINARY

text, blob

ENUM, SET

日期时间型

date, time, datetime,timestamp

域属性，修改符：

数据类型：

1、存入的值类型；

2、占据的存储空间；

3、定长还变长；

4、如何比较及排序；

5、是否能够索引；

SQL

mysql>SHOW CHARACTER SET;

mysql>SHOW COLLATION;

AUTO_INCREMENT

整型

非空

无符号

主键或惟一键

CREATE TABLE test(ID INTUNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, Name

CHAR(20))

mysql>SELECT LAST_INSERT_ID();

DNS

RRtypeCHAR(5)

A, PTR, CNAME, AAAA, MX, NS, SOA, SRV

RRtypeENUM('A','PTR')

SQL模型：

abc, abcdefg

CHAR(3)

MySQL服务器变量

作用域，分为两类：

全局变量

SHOW GLOBALVARIABLES

show global variables like ‘sql_mode’;

会话变量

SHOW [SESSION]VARIABLES

show variables like ‘sql_mode’;

生效时间，分为两类：

动态：可即时修改

静态：

写在配置文件中

通过参数传递给mysqld

动态调整参数的生效方式：

全局：对当前会话无效，只对新建立会话有效；

会话：即时生效，但只对当前会话有效；

服务器变量：@@变量名

显示：SELECT

设定：SET GLOBAL|SESSION 变量名='value'

select @@global.sql_mode;

set global sql_mode=’strict_all_tables’;

select @@session.sql_mode;

select @@global.sql_mode;

set session sql_mode=’strict_trans_tables’;

select @@sql_mode;

select @@global.sql_mode;

MySQL管理表和索引

SQL语句：

数据库

表

索引

视图

DML

数据库：

create database | schema [if not exists]db_name [character set=] [collate=]

show character set;

show collation;

create schema if not exists studentscharacter set ‘gbk’ collate ‘gbk_chinese_ci’;

cd /mydata/data/

cd students

file db.opt

cat db.opt

helpalter database;

help dropdatabase;

drop {database | schema} [if exists] db_name

表

helpcreate table;

1.直接定义一张空表

2.从其他表中查询出数据，并以之创建新表

3.以其他表为模板创建一个空表

CREATETABLE [IF NOT EXISTS] tb_name (col_name col_defination, constraint )

CREATETABLE tb1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(20)NOT NULL, Age TINYINT NOT NULL) ENGINE[=] engine_name

CREATETABLE tb2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT, Name CHAR(20) NOT NULL, AgeTINYINT NOT NULL, PRIMARY KEY(id),UNIQUE KEY(name),INDEX(age))

单字段：

PRIMARY KEY

UNIQUE KEY

单或多字段：

PRAMARY KEY (col,...)

UNIQUEKEY (col,...)

INDEX (col,...)

键也称作约束，可用作索引，属于特殊索引(有特殊限定)：B+Tree

CREATE INDEX创建索引

SHOW INDEXES FROM tb_name：显示指定表上的索引

修改表定义：

ALTERTABLE

添加、删除、修改字段

添加、删除、修改索引

改表名

修改表属性

drop table testcourses;

alter table test rename to testcourses;改表名

rename testcourses to test;

show talbes;

alter table courses engine=InnoDB;

show table status\G

删除表：

InnoDB支持外键

索引创建：

CREATEINDEX index_name ON tb_name (col,...);

col_name[(length)] [ASC | DESC]

drop index name_on_student on student;

create index name_on_student on student(Name(5) desc) using btree;

show indexs from student;

单表查询，多表查询和子查询

DDL:

DML:

SELECT

INSERTINTO

DELETE

UPDATE

SELECT select-list FROM tb WHEREqualification

查询语句类型：

简单查询

多表查询

子查询

SELECT * FROM tb_name;

SELECT field1,field2 FROM tb_name; 投影

SELECT [DISTINCT] * FROM tb_name WHEREqualification; 选择

FROM子句：要查询的关系表、多个表、其它SELECT语句

WHERE子句：布尔关系表达式

=、>、>=、<=、<

逻辑关系：

AND

NOT

BETWEEN... AND ...

LIKE‘’

%:任意长度任意字符

_：任意单个字符

select Name from students where Name like ‘Y%’;

select Name from students where Name like ‘%ing%’;

REGEXP,RLIKE

select Name from studentswhere Name rlike ‘^[XY].*$’;

ISNULL

ISNOT NULL

ORDER BY field_name {ASC|DESC}

select Name from students where CID2 is not null order by Name;

select Name from students where CID2 is not null order by Name desc;

字段别名：AS

select Name as Student_Name from students;

select 2+1 as SUM;

LIMIT子句：LIMIT[offset,]Count

聚合：SUM(), MIN(), MAX(), AVG(), COUNT()

select avg(age) from students;

select sum(age) from studnets;

select count(age) from students;

select avg(age) from students where Gender=’M’;

GROUP BY: 分组

HAVINGqualification

select Age,Gender from students group byGender;

select count(CID1) as Persons,CID1 fromstudents group by CID1;

多表查询：

连接：

交叉连接：笛卡尔乘积

自然连接：

外连接：

左外连接：... LEFTJOIN ... ON ...

右外连接: ...RIGHT JOIN ... ON ...

自连接：

自然连接

左，右外连接

自连接

子查询：

比较操作中使用子查询：子查询只能返回单个值；

IN():使用子查询；

在FROM中使用子查询；

联合查询：

UNION

多表查询，子查询及视图

练习：

1、挑选出courses表中没有被students中的CID2学习的课程的课程名称；

select distinct CID2 from students whereCID2 is not null;

附加：挑选出没有教授任何课程的老师，每个老师及其所教授课程的对应关系在courses表中；

select Tname from tutors where TID not in (selectdistinct TID from courses);

找出students表中CID1有两个或两个以上同学学习了的同一个门课程的课程名称；

2、显示每一位老师及其所教授的课程；没有教授的课程的保持为NULL；

3、显示每一个课程及其相关的老师，没有老师教授的课程将其老师显示为空；

4、显示每位同学CID1课程的课程名及其讲授了相关课程的老师的名称；

视图：存储下来的select语句

基于基表的查询结果；

view

create view

help create view

物化视图

show create table courses;

写脚本往数据库添加内容

MySQL事务和隔离级别

选择：SELECT * FROM tb_name WHERE

布尔表达式

算术运算：

比较操作符：

其它运算符：IN,BETWEEN ... AND ..., LIKE, RLIKE(REGEXP), IS NULL, IS NOT NULL

逻辑运算：AND, OR,NOT, XOR

DISTINCT

投影：SELECT field1, ... FROM tb_name;

ORDER BY field,... {ASC|DESC}

聚合计算：COUNT()、SUM()、MAX()、MIN()和AVG()；

GROUP BY field1,...

HAVING

LIMIT [offset],num

多表查询：

交叉连接

自然连接

WHEREtb1.field=tb2.field

外连接

左外

FROMtb1 LEFT JOIN tb2 ON condition

右外

FROM tb1 RIGHT JOIN tb2 ON condtion

自连接

子查询：

FROM

WHERE

比较操作符：子查询只能返回一个字段的单值；

IN：列表(某字段的多个值)

广义查询：

DML：

DELETE

INSERTINTO

UPDATE

INSERT INTO tb_name (col1, col2, ...)VALUES (val1, val2, ...)[,(val1, val2, ...),...]

字符型：单引号

数值型：不需要引号

日期时间型：

空值：NULL， ''

REPLACE INTO

DELETE：

DELETEFROM tb_name WHERE condition;

TRUNCATE tb_name: 清空表，并重置AUTOINCREMENT计数器；

UPDATE tb_name SET col1=..., col2=... WHERE

连接管理器：

接受请求

创建线程

认证用户

建立安全连接

并发控制：

mbox：MDA

C/S： 100

10分钟：

多版本并发控制： MVCC

锁：

读锁：共享锁：同一个文件允许读

写锁：独占锁

LOCK TABLES tb_name{READ|WRITE}

UNLOCK TABLES

锁粒度：从大到小，MySQL服务器仅支持表级锁，行锁需要由存储引擎完成；

表锁

页锁

行锁

unlock tables;

事务：

RDBMS：ACID(原子性，一致性，隔离性，持久性，

MyISAM：不支持事务

InnoDB：

事务日志

重做日志

redo log

撤销日志

undo log

隔离性：

隔离级别：

READ UNCOMMITTED：读未提交

READ COMMITTED：读提交

REPATABLE READ：可重读

SERIABLIZABLE:可串行

服务器变量：

动态：

全局变量

修改后不影响当前会话，只对新建的会话有效；

会话变量

仅对当前会话有效，而且是立即生效；

永久有效：修改配置文件

修改：SET {SESSION|GLOBAL} VAR_NAME=‘’;

ＭySQL事务和隔离级别

多事务同时执行：彼此之间互不影响的方式进行并行；

事务之间交互：

通过数据集

事务：CPU， I/O

RDBMS,

ACID:

Automicity：原子性，事务所引起的数据库操作，要么都完成，要么都不执行；

Consistency：一致性，A（3000）-->B(2000)

1:A：3000-->2500,

2: A+B:4500,

Isolation: 隔离性

事务调度：事务之间影响最小

MVCC：多版本并发控制

Durability：一旦事务成功完成，系统必须保证任何故障都不会引起事务表示出不一致性；

1、事务提交之前就已经写出数据至持久性存储；

2、结合事务日志完成；

事务日志：顺序IO

数据文件：随机IO

事务的状态：

活动的：active

部分提交的：最后一条语句执行后

失败的：

中止的：

提交的：

事务：并发执行

1、提高吞吐量和资源利用率

2、减少等待时间

事务调度：

可恢复调度；

无级联高度：

隔离级别：

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

并发控制依赖的技术手段：

锁

时间戳

多版本和快照隔离

饿死：

死锁：

SQL，ODBC

STARTTANSACTION：启动

SQL

COMMIT：提交

ROLLBACK:回滚

不能回滚

不能恢复Xuzhu;

如果没有明确启动事务：

autocommit：能实现自动提交，每一个操作都直接提交；

建议：明确使用事务，并且关闭自动提交；

保存点：SAVEPOINT sid

回滚至保存点：ROLLBACK TO sid

help savepoint

隔离级别

启动两个连接：

第一个连接

第二个连接查看

rollback

验证读提交

set tx_isolation=’READ-COMMITTED’;

start transaction;

update tutors set Age=50 where TID=12;

select * from tutors;

commit;

select * from tutors;

可重读验证：

set tx_isolation=’REPEATABLE-READ’;

start transaction;

update tutors set Age=60 and where TID=12;

commit;

MySQL用户和权限管理

MySQL

用户

密码：password();

账号：认证

权限

授权

用户和权限管理

Informationabout account privileges is stored in the user, db, host, tables_priv,columns_priv, and procs_priv tables in the mysql database. The MySQL server reads the contents of thesetables into memory when it starts and reloads them under the circumstances.Access-control decisions are based on the in-memory copies of the grant tables.

user:Contains user accounts, global privileges, and other non-privilege columns.

user: 用户帐号、全局权限

db:Contains database-level privileges.

db: 库级别权限

host:Obsolete.

host: 废弃

tables_priv:Contains table-level privileges.

表级别权限

columns_priv:Contains column-level privileges.

列级别权限

procs_priv:Contains stored procedure and function privileges.

存储过程和存储函数相关的权限

proxies_priv:Contains proxy-user privileges.

代理用户权限

There areseveral distinctions between the way user names and passwords are used by MySQLand the way they are used by your operating system:

User names, as used by MySQL forauthentication purposes, have nothing to do with user names (login names) asused by Windows or Unix.

MySQL user names can be up to 16characters long.

The server uses MySQL passwords storedin the user table to authenticate client connections using MySQL nativeauthentication (against passwords stored in the mysql.user table).

MySQL encrypts passwords stored in the user table using itsown algorithm. This encryption is the same as that implemented by the PASSWORD()SQL function but differs from that used

duringthe Unix login process.

It is possible to connect to the serverregardless of character set settings if the user name and password contain onlyASCII characters.

用户帐号：

用户名@主机

用户名：16字符以内

主机：

主机名：www.magedu.com, mysql

IP: 172.16.10.177

网络地址:

172.16.0.0/255.255.0.0

通配符：%，_

172.16.%.%

%.magedu.com

--skip-name-resolve

权限级别：

全局级别： SUPER、

库

表: DELETE,ALTER, TRIGGER

列: SELECT,INSERT, UPDATE

存储过程和存储函数

字段级别：

临时表：内存表

heap:16MB

触发器：主动数据库

INSERT,DELETE, UPDATE

user:log

创建用户

CREATE USER username@host [IDENTIFIED BY'password']

GRANT

GRANT ALL PRIVILEGES ON [object_type] db.*TO username@'%';

TABLE

|FUNCTION

|PROCEDURE

GRANT EXECUTE ON FUNCTION db.abc TOusername@'%';

INSERT INTO mysql.user

mysql> FLUSH PRIVILEGES;

SHOW GRANTS FOR 'username@host';

GRANT OPTION

|MAX_QUERIES_PER_HOUR count

|MAX_UPDATES_PER_HOUR count

|MAX_CONNECTIONS_PER_HOUR count

|MAX_USER_CONNECTIONS count

grant create on cactidb.* to ‘cactiuser’@’%’;

grant insert on catidb.* to ‘cactiuser’@’%’;

grant select on catidb.* to ‘cactiuser’@’%’;

grant alter on catidb.* to ‘cactiuser’@’%’;

重新连接生效

grant update(Age) on cactidb.testtb to ‘cactiuser’@’%’;

grant super on *.* to ‘cactiuser’@’%’;

flush privileges;

DROP USER 'username'@'host'

RENAME USER old_name TO new_name

REVOKE

启动mysqld_safe时传递两个参数：

--skip-grant-tables

--skip-networking

通过更新授权表方式直接修改其密码，而后移除此两个选项重启服务器。

vim /etc/init.d/mysqld

vim .my.cnf

service mysqld start

rm .my.cnf

mysql

恢复原文件

MySQL日志管理

错误日志

log_error

log_warnings

一般查询日志：

general_log

general_log_file

log

log_output

慢查询日志

long_query_time

log_slow_queries={YES|NO}

slow_query_log

| slow_query_log_file

二进制日志：任何引起或可能引起数据库变化的操作；

复制、即时点恢复；

mysqlbinlog

二进制日志的格式：

基于语句: statement

基于行: row

混合方式: mixed

二进制日志事件：

产生的时间

相对位置

二进制日志文件：

索引文件

二进制日志文件

查看当前正在使用的二进制日志文件

mysql> SHOW MASTER STATUS;

mysql> SHOW BINARY LOGS;

mysql> SHOW BINLOG EVENTS IN '二进制日志文件名' [FROM pos]；

mysql> PURGE BINARY LOGS TO '某二进制日志文件'

mysqlbinlog

--start-datetime

--stop-datetime

--start-position

--stop-position

中继日志

事务日志：ACID，将随机IO转换为顺序IO；

mysql>show global variables like '%log%';

+-----------------------------------------+---------------------------------+

|Variable_name |Value |

+-----------------------------------------+---------------------------------+

|back_log |50 |

|binlog_cache_size |32768 |

|binlog_direct_non_transactional_updates | OFF |

|binlog_format |STATEMENT |

|expire_logs_days |0 |

|general_log |OFF |

|general_log_file | /var/run/mysqld/mysqld.log |

|innodb_flush_log_at_trx_commit |1 |

|innodb_locks_unsafe_for_binlog |OFF |

|innodb_log_buffer_size |1048576 |

|innodb_log_file_size |5242880 |

|innodb_log_files_in_group |2 |

|innodb_log_group_home_dir |./ |

|innodb_mirrored_log_groups |1 |

|log |OFF |

|log_bin |OFF |

|log_bin_trust_function_creators |OFF |

|log_bin_trust_routine_creators |OFF |

|log_error |/var/log/mysqld.log |

|log_output |FILE |

|log_queries_not_using_indexes |OFF |

|log_slave_updates |OFF |

|log_slow_queries |OFF |

|log_warnings |1 |

|max_binlog_cache_size |18446744073709547520 |

|max_binlog_size |1073741824 |

|max_relay_log_size |0 |

|relay_log | |

|relay_log_index | |

|relay_log_info_file | relay-log.info |

|relay_log_purge |ON |

|relay_log_space_limit |0 |

|slow_query_log |OFF |

|slow_query_log_file |/var/run/mysqld/mysqld-slow.log |

|sql_log_bin |ON |

|sql_log_off |OFF |

|sql_log_update |ON |

|sync_binlog |0 |

+-----------------------------------------+---------------------------------+

38 rowsin set (0.00 sec)

错误日志：

服务器启动和关闭过程中的信息；

服务器运行过程中的错误信息；

事件调度器运行一个事件时产生的信息；

在从服务器上启动从服务器进程是产生的信息

删除某二进制日志文件

日志管理

知识回顾：

用户及权限管理：

'username'@'host'

password()

--skip-grant-tables--skip-networking

UPDATEmysql.user

权限：

服务管理类：super

库: CREATE

表: DELETE、ALTER

列: INSERT,SELECT, UPDATE

GRANT权限,... ON [对象类型] db.{table|routine} TO 'username'@'host' [IDENTIFIED BY'password'];

REVOKE权限,... ON [对象类型] db.{table|routine} FROM 'username'@'host';

SHOWGRANTS FOR 'username'@'host';

CREATEUSER 'username'@'host' [IDENTIFIED BY 'password'];

DROPUSER 'username'@'host';

RENAMEUSER old_name TO new_name;

日志:

错误日志：

一般查询日志：

慢查询日志：

log_output{TABLE|FILE|NONE}

二进制日志:

复制、即时点恢复

二进制日志事件：

基于语句：statement

基于行：row

混合方式：mixed

mysql>SHOW BINARY LOGS;

mysql>SHOW MASTER STATUS;

mysql>SHOW BINLOG EVENTS IN '二进制日志文件' FROM 'position';

mysql>PURGE BINARY LOGS TO '日志文件';

mysql>FLUSH LOGS;

mysqlbinlog

--start-position

--stop-position

--start-datetime'yyyy-mm-dd hh:mm:ss'

--stop-datetime''；

中继日志：

从主服务器的二进制日志文件中复制而来的事件，并保存为的日志文件；

事务日志：

事务性存储引擎用于保证原子性、一致性、隔离性和持久性；

innodb_flush_log_at_trx_commit:

0:每秒同步，并执行磁盘flush操作；

1：每事务同步，并执行磁盘flush操作；

2:每事务同步，但不执行磁盘flush操作；

MyISAM:

不支持事务

表锁

不支持外键

B树索引、FULLTEXT索引、空间索引

支持表压缩

.frm

.MYD

.MYI

InnoDB:

事务

行级锁

B树索引、聚簇索引、自适应hash索引

表空间，raw磁盘设备；

.frm

.ibd

MRG_MYISAM：

MySQL的备份和还原

备份：副本

RAID1，RAID10：保证硬件损坏而不会业务中止；

DROPTABLE mydb.tb1;

备份类型：

热备份、温备份和冷备份

热备份：读、写不受影响；

温备份：仅可以执行读操作；

冷备份：离线备份；读、写操作均中止；

物理备份和逻辑备份

物理备份：复制数据文件；

逻辑备份：将数据导出至文本文件中；

完全备份、增量备份和差异备份；

完全备份：备份全部数据；

增量备份：仅备份上次完全备份或增量备份以后变化的数据；

差异备份：仅备份上次完全备份以来变化的数据；

在线：物理完全备份

还原：

备份什么：

数据、配置文件、二进制日志、事务日志

热备份：

MyISAM:温备份热备份基本不可能

InnoDB:xtrabackup, mysqldump

MySQL --> 从：

物理备份：速度快

逻辑备份：速度慢、丢失浮点数精度；方便使用文本处理工具直接对其处理、可移植能力强；

备份策略：完全+增量；完全+差异

MySQL备份工具：

mysqldump: 逻辑备份工具、MyISAM(温)、InnoDB(热备份)

mysqlhotcopy：物理备份工具、温备份

文件系统工具：

cp：冷备

lv: 逻辑卷的快照功能，几乎热备；

mysql>FLUSH TABLES;

mysql>LOCK TABLES

创建快照：释放锁，而后复制数据

InnoDB:

第三组工具：

ibbackup:商业工具

xtrabackup:开源工具

mysqldump: 逻辑备份

mysqldump（完全备份）+ 二进制日志

完全+增量：

备份单个数据库，或库中特定表

mysqldumpDB_NAME [tb1] [tb2]

启动一个终端施加读锁

flush tables with read lock;

能查询，不能插入

现在备份

mysqldump -uroot -p studb >/root/studb.sql

unlock tables;

插入成功

--master-data={0|1|2}

0:不记录二进制日志文件及路位置；

1：以CHNAGEMASTER TO的方式记录位置，可用于恢复后直接启动从服务器；

2：以CHANGEMASTER TO的方式记录位置，但默认为被注释；

--lock-all-tables：锁定所有表

--flush-logs: 执行日志flush；

如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备；

备份多个库：

--all-databases:备份所有库

--databasesDB_NAME,DB_NAME,...: 备份指定库

--events

--routines

--triggers

mysqldump -uroot -p --lock-all-tables--flush-logs --all-databases > /root/all.sql

mysqldump -uroot -p --lock-all-tables--flush-logs --all-databases --master-data=2 > /root/all.sql

备份策略：每周完全+每日增量

完全备份：mysqldump

增量备份：备份二进制日志文件(flush logs)

模拟：

mysqldump -uroot-p --master-data=2 --flush-logs --all-databases --lock-all-tables >/root/alldatabases.sql

lessalldatabases.sql

purge binarylogs to ‘mysql-bin.0000qq’;

show binarylogs;

增量备份

模拟错误，删除数据库

cd /usr/local/mysql

scripts/mysql_install_db --user=mysql--datadir=/mydata/data/

service mysqld start

mysql -uroot -p < alldatabases.sql

mysql -uroot -p < mon-incremental.sql

mysqlbinlog mysql-bin.000012 > temp.sql

mysql -uroot -p < temp.sql

use studb;

select * from tutors;

expire_logs_days={0..99}

设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时。作用范围为全局，可用于配置文件，属动态变量。

general_log={ON|OFF}

设定是否启用查询日志，默认值为取决于在启动mysqld时是否使用了--general_log选项。如若启用此项，其输出位置则由--log_output选项进行定义，如果log_output的值设定为NONE，即使用启用查询日志，其也不会记录任何日志信息。作用范围为全局，可用于配置文件，属动态变量。

general_log_file=FILE_NAME

查询日志的日志文件名称，默认为“hostname.log"。作用范围为全局，可用于配置文件，属动态变量。

binlog-format={ROW|STATEMENT|MIXED}

指定二进制日志的类型，默认为STATEMENT。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。作用范围为全局或会话，可用于配置文件，且属于动态变量。

log={YES|NO}

是否启用记录所有语句的日志信息于一般查询日志(general query log)中，默认通常为OFF。MySQL 5.6已经弃用此选项。

log-bin={YES|NO}

是否启用二进制日志，如果为mysqld设定了--log-bin选项，则其值为ON，否则则为OFF。其仅用于显示是否启用了二进制日志，并不反应log-bin的设定值。作用范围为全局级别，属非动态变量。

log_bin_trust_function_creators={TRUE|FALSE}

此参数仅在启用二进制日志时有效，用于控制创建存储函数时如果会导致不安全的事件记录二进制日志条件下是否禁止创建存储函数。默认值为0，表示除非用户除了CREATE ROUTING或ALTER ROUTINE权限外还有SUPER权限，否则将禁止创建或修改存储函数，同时，还要求在创建函数时必需为之使用DETERMINISTIC属性，再不然就是附带READS SQL DATA或NO SQL属性。设置其值为1时则不启用这些限制。作用范围为全局级别，可用于配置文件，属动态变量。

log_error=/PATH/TO/ERROR_LOG_FILENAME

定义错误日志文件。作用范围为全局或会话级别，可用于配置文件，属非动态变量。

log_output={TABLE|FILE|NONE}

定义一般查询日志和慢查询日志的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的组合(用逗号隔开)，默认为TABLE。如果组合中出现了NONE，那么其它设定都将失效，同时，无论是否启用日志功能，也不会记录任何相关的日志信息。作用范围为全局级别，可用于配置文件，属动态变量。

log_query_not_using_indexes={ON|OFF}

设定是否将没有使用索引的查询操作记录到慢查询日志。作用范围为全局级别，可用于配置文件，属动态变量。

log_slave_updates

用于设定复制场景中的从服务器是否将从主服务器收到的更新操作记录进本机的二进制日志中。本参数设定的生效需要在从服务器上启用二进制日志功能。

log_slow_queries={YES|NO}

是否记录慢查询日志。慢查询是指查询的执行时间超出long_query_time参数所设定时长的事件。MySQL 5.6将此参数修改为了slow_query_log。作用范围为全局级别，可用于配置文件，属动态变量。

log_warnings=#

设定是否将警告信息记录进错误日志。默认设定为1，表示启用；可以将其设置为0以禁用；而其值为大于1的数值时表示将新发起连接时产生的“失败的连接”和“拒绝访问”类的错误信息也记录进错误日志。

long_query_time=#

设定区别慢查询与一般查询的语句执行时间长度。这里的语句执行时长为实际的执行时间，而非在CPU上的执行时长，因此，负载较重的服务器上更容易产生慢查询。其最小值为0，默认值为10，单位是秒钟。它也支持毫秒级的解析度。作用范围为全局或会话级别，可用于配置文件，属动态变量。

max_binlog_cache_size{4096 ..18446744073709547520}

二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。作用范围为全局级别，可用于配置文件，属动态变量。

max_binlog_size={4096 .. 1073741824}

设定二进制日志文件上限，单位为字节，最小值为4K，最大值为1G，默认为1G。某事务所产生的日志信息只能写入一个二进制日志文件，因此，实际上的二进制日志文件可能大于这个指定的上限。作用范围为全局级别，可用于配置文件，属动态变量。

max_relay_log_size={4096..1073741824}

设定从服务器上中继日志的体积上限，到达此限度时其会自动进行中继日志滚动。此参数值为0时，mysqld将使用max_binlog_size参数同时为二进制日志和中继日志设定日志文件体积上限。作用范围为全局级别，可用于配置文件，属动态变量。

innodb_log_buffer_size={262144 ..4294967295}

设定InnoDB用于辅助完成日志文件写操作的日志缓冲区大小，单位是字节，默认为8MB。较大的事务可以借助于更大的日志缓冲区来避免在事务完成之前将日志缓冲区的数据写入日志文件，以减少I/O操作进而提升系统性能。因此，在有着较大事务的应用场景中，建议为此变量设定一个更大的值。作用范围为全局级别，可用于选项文件，属非动态变量。

innodb_log_file_size={108576 .. 4294967295}

设定日志组中每个日志文件的大小，单位是字节，默认值是5MB。较为明智的取值范围是从1MB到缓存池体积的1/n，其中n表示日志组中日志文件的个数。日志文件越大，在缓存池中需要执行的检查点刷写操作就越少，这意味着所需的I/O操作也就越少，然而这也会导致较慢的故障恢复速度。作用范围为全局级别，可用于选项文件，属非动态变量。

innodb_log_files_in_group={2 .. 100}

设定日志组中日志文件的个数。InnoDB以循环的方式使用这些日志文件。默认值为2。作用范围为全局级别，可用于选项文件，属非动态变量。

innodb_log_group_home_dir=/PATH/TO/DIR

设定InnoDB重做日志文件的存储目录。在缺省使用InnoDB日志相关的所有变量时，其默认会在数据目录中创建两个大小为5MB的名为ib_logfile0和ib_logfile1的日志文件。作用范围为全局级别，可用于选项文件，属非动态变量。

relay_log=file_name

设定中继日志的文件名称，默认为host_name-relay-bin。也可以使用绝对路径，以指定非数据目录来存储中继日志。作用范围为全局级别，可用于选项文件，属非动态变量。

relay_log_index=file_name

设定中继日志的索引文件名，默认为为数据目录中的host_name-relay-bin.index。作用范围为全局级别，可用于选项文件，属非动态变量。

relay-log-info-file=file_name

设定中继服务用于记录中继信息的文件，默认为数据目录中的relay-log.info。作用范围为全局级别，可用于选项文件，属非动态变量。

relay_log_purge={ON|OFF}

设定对不再需要的中继日志是否自动进行清理。默认值为ON。作用范围为全局级别，可用于选项文件，属动态变量。

relay_log_space_limit=#

设定用于存储所有中继日志文件的可用空间大小。默认为0，表示不限定。最大值取决于系统平台位数。作用范围为全局级别，可用于选项文件，属非动态变量。

slow_query_log={ON|OFF}

设定是否启用慢查询日志。0或OFF表示禁用，1或ON表示启用。日志信息的输出位置取决于log_output变量的定义，如果其值为NONE，则即便slow_query_log为ON，也不会记录任何慢查询信息。作用范围为全局级别，可用于选项文件，属动态变量。

slow_query_log_file=/PATH/TO/SOMEFILE

设定慢查询日志文件的名称。默认为hostname-slow.log，但可以通过--slow_query_log_file选项修改。作用范围为全局级别，可用于选项文件，属动态变量。

sql_log_bin={ON|OFF}

用于控制二进制日志信息是否记录进日志文件。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。

sql_log_off={ON|OFF}

用于控制是否禁止将一般查询日志类信息记录进查询日志文件。默认为OFF，表示不禁止记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。

sync_binlog=#

设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步。

使用LVM快照进行数据库备份

知识回顾：

1、hot(r/w)、warm(r)、cold

2、logical, raw

mysql:

raw:

3、备份数据集

full

incremental

differential

二进制日志：

format:

statment

row

mixed

mysqldump:

--databases DB1,DB2,...

--all-databases

showglobal variables;

set sql_log_bin=0;

set sql_log_bin=1;

恢复备份

show database;

一切恢复正常

然后

set sql_log_bin=1;

MyISAM: 温备份

--lock-all-tables

--lock-tables

InnoDB: 热备份

--single-transaction

--flush-logs

--events

--routines

--triggers

--master-data={0|1|2}

逻辑备份：

1、浮点数据丢失精度；

2、备份出的数据更占用存储空间；压缩后可大大节省空间；

3、不适合对大数据库做完全备份；

对InnoDB:

mysql> FLUSH TABLES WITH READ LOCK;

MVCC,REPEATABLE-READ

--single-transaction

备份：

SELECT * INTO OUTFILE'/path/to/somefile.txt' FROM tb_name [WHERE clause];

select *into outfile ‘/tmp/tutor.txt’ from tutors;

还原：

LOAD DATA INFILE'/path/to/somefile.txt' INTO TABLE tb_name;

select * from tutor;

mysqlbinlog--start-position=605 /mydata/data/mysql-bin.000001 > /root/a.sql

usejiaowu;

createtable tutors like tutor;

truncatetable tutor;

setsql_log_bin=0;

source/root/a.sql;

select *from tutors

几乎热备：LVM

snapshot:

前提：

1、数据文件要在逻辑卷上；

2、此逻辑卷所在卷组必须有足够空间使用快照卷；

3、数据文件和事务日志要在同一个逻辑卷上；

步骤：

1、打开会话，施加读锁，锁定所有表；

mysql> FLUSHTABLES WITH READ LOCK;

mysql> FLUSHLOGS;

2、通过另一个终端，保存二进制日志文件及相关位置信息；

$ mysql -uroot -p -e'SHOW MASTER STATUS\G' > /path/to/master.info

3、创建快照卷

# lvcreate -L # -s-p r -n LV_NAME /path/to/source_lv

4、释放锁

mysql> UNLOCKTABLES;

5、挂载快照卷，备份

mount

6、删除快照卷；

7、增量备份二进制日志；

start transaction;

另一个连接

commit

flush tables with read lock;

flush logs;

show master status;

不要退出

在启动一个连接

lvcreate -L 50M -s -p r -n mydata-snap/dev/myvg/mydata

lvs

mount

unlock tables;

插入完成

插入提交

commit

mount /dev/myvg/mydata-snap /mnt -o ro

cd /mnt

cd data/

模拟损坏

等的人心好烦

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录