爱梦的博客

常常与我陪伴的就是梦

0%

MySQL数据库基础

发表于 更新于 分类于
本文字数： 20k 阅读时长 ≈ 19 分钟

数据库简介

使用数据库的必要性

可以结构化存储大量的数据信息, 方便用户进行有效的检索和访问.
可以有效地保持数据信息的一致性、完整性, 降低数据冗余.
可以满足应用的共享和安全方面的要求

经典数据模型

网状模型
层次模型
关系模型

当今主流数据库

关系数据库
SQL Servel、Oracle、IBM DB2、MySQL
非关系数据库
Memcached、Redis、MongoDB、HBase
优点 数据库高并发读写的需求 对海量数据高效率存储与访问 数据库的高扩展性与高可用性的需求

MySQL服务基础

使用二进制安装MySQL-5.7.30

1.下载安装包
mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

2.通过WinSCP上传安装包

3.解压安装包并重命名

1
2
3
cd /usr/local/src
tar zxf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.30-linux-glibc2.12-x86_64 ../mysql

4.创建运行用户和数据目录, 赋予权限.

1
2
3
4
5
groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql
mkdir -p /data/mysql  
chown -R mysql /data/mysql/
chown -R mysql:mysql /usr/local/mysql/

5.建立配置文件
vim /etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
[client]    
port        = 3306
socket      = /data/mysql/mysql.sock

[mysqld]    
port        = 3306
socket      = /data/mysql/mysql.sock
user = mysql

basedir = /usr/local/mysql
datadir = /data/mysql

skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 1024M
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 8K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size = 16M
tmp_table_size = 32M
performance_schema_max_table_instances = 1000
explicit_defaults_for_timestamp = true
#skip-networking
max_connect_errors = 100
open_files_limit = 65535

log_bin=mysql-bin
binlog_format=mixed
server_id   = 232
expire_logs_days = 10
early-plugin-load = ""

default_storage_engine = InnoDB
character_set_server=utf8
innodb_file_per_table = 1
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M

6.初始化数据库

1
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

7.添加mysql到环境变量

1
2
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile

8.添加mysqld到系统服务

1
2
3
cd /usr/local/mysql/
cp support-files/mysql.server /etc/rc.d/init.d/mysqld                               
chmod +x /etc/rc.d/init.d/mysqld

9.将mysql添加为systemd标准服务
vim /lib/systemd/system/mysqld.service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[Unit]
Description=mysqld
After=network.target

[Service]
Type=forking
ExecStart=/etc/rc.d/init.d/mysqld start
ExecReload=/etc/rc.d/init.d/mysqld restart
ExecStop=/etc/rc.d/init.d/mysqld stop

PrivateTmp=true

[Install]
WantedBy=multi-user.target
1
2
3
4
systemctl daemon-reload 
systemctl enable mysqld
systemctl start mysqld
netstat -lnt | grep 3306

Linux访问MySQL数据库

1.登录MySQL

1
2
3
4
5
6
mysql -u root -p
Enter password: #输入初始化数据库时设置root用户的密码
set password='root密码'; #重设root密码

mysqladmin -u root -p password '更改root密码'
Enter password: #原root用户的密码

2.远端登录MySQL

1
2
3
4
5
# tcp/ip登录(本机, 远端)
mysql -u root -p[root密码] -P 端口号 -h IP地址

# socket登录(本机)
mysql -u root -p[root密码] -S sock文件目录

3.使用Navicat连接数据库

1
2
3
4
5
6
7
8
9
10
11
# 添加用户权限
grant all privileges on 数据库名.数据表名 to '用户名'@'ip地址' identified by '密码'; 
flush privileges;

# 撤销用户权限
revoke 权限列表 on 数据库名.数据表名 from '用户名'@'ip地址'

# 查看用户权限
show grants for '用户名'@'ip地址';

quit;/exit;

基本操作命令

DDL(Data Definition Language)

数据定义语言:CREATE、DROP、ALTER.

DML(Data Manipulation Language)

数据操纵语言:INSERT、UPDATE、DELETE.

DQL(Data Query Language)

数据查询语言:SELECT … FROM … WHERE

DCL(Data Control Language)

数据控制语言:GRANT、REVOKE.

TCL(Thing Control Language)

事务控制语言:COMMIT、ROLLBACK、SAVEPOINT.

使用MySQL数据库

查看数据库结构

1.查看当前服务器下所包含的数据库

1
show databases;

2.查看当前使用的库下所包含的数据表

1
2
use mysql;
show tables;

3.查看数据表的结构

1
2
use mysql;
describe `user`;

查看字符编码格式

1
2
3
4
5
6
7
8
# 查看数据库中所有的默认字符编码格式
show variables like 'character%';

# 查看数据库的默认字符编码格式
show create database 数据库名;

# 查看数据表的默认字符编码格式
show create table 数据库名.数据表名;

指定字符编码格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 设置字符编码格式, 下次登录MySQL时失效.
set names utf8;

# 创建数据库指定
create database 数据库名 character set utf8 collate utf8_general_ci;

# 修改数据库结构
alter database 数据库名 charset utf8;

# 创建表指定
create table 数据表名
(
    ...
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 修改表结构
alter table 数据库名.数据表名 convert to charset utf8;

# 修改MySQL配置文件
vim /etc/my.cnf
  # ---version: 5.6---
  [mysqld]
  default-character-set=utf8

  # ---version: 5.7---
  [mysqld]
  character_set_server=utf8
  ...

查看存储引擎

1
2
3
4
5
# 查看字段
show table status from 数据库名 where `name`='数据表名' 

# 查看信息
show create table 数据表名

指定存储引擎

1
2
3
4
5
6
7
8
# 创建表指定
create table 数据表名
(
    ...
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 修改表结构
alter table 数据库名.数据表名 engine=InnoDB;

创建及删除库和表

建库规范:建表规范:
1.库名不能有大写字母1.表名小写
2.建库要加字符集2.注意字符集和存储引擎
3.库名不能是数字开头3.表名不能是数字开头
4.库名要和业务相关4.表名和业务有关
5.选择合适的数据类型
6.每个列都要有注释
7.每个列设置为非空, 无法保证非空, 用0来填充.

1.创建新的数据库

1
create database 数据库名;

2.创建新的数据表
数据类型:官方资料

1
2
3
4
5
6
7
create table 数据表名
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '字段注释',
    列名 类型 PRIMARY KEY,
    列名 类型 NOT NULL,
    ...
);

3.删除数据表

1
drop table 数据库名.数据表;

4.删除数据库

1
drop database 数据库名;

查询、修改、添加、删除数据

1.插入数据记录

1
2
3
4
5
6
7
8
9
10
# 单条插入
insert into 数据库名.数据表名 values(值1, 值2, 值3,...);
insert into 数据库名.数据表名(列名1, 列名2, 列名3,...) values(值1, 值2, 值3,...);

# 多条插入
insert into 数据库名.数据表名(列名1, 列名2, 列名3,...)
values
	(值1, 值2, 值3,...),
	(值1, 值2, 值3,...)
    ...;

2.查询数据记录

1
select * from 数据库名.数据表名;

3.修改数据记录

1
update 数据表名 set 列名1=值1, 列名2=值2,... where 条件表达式;

4.删除数据记录

1
delete from 数据表名 where 条件表达式;

MySQL索引与事务

索引

概念

索引是一种特殊的文件, 包含着对数据表中所有记录的引用指针.就是为了提高表的搜索效率而对某些字段中的值建立的目录.

作用

1.设置了合适的索引之后, 数据库利用各种快速的定位技术, 可以大大加快数据的查询速度, 这也是创建索引最主要的原因.
2.当表很大时, 或者查询涉及多个表时, 使用索引可使查询速度加快成干倍.
3.可以降低数据库的IO成本, 并且索引还可以降低数据库的排序成本.
4.通过创建唯一性索引, 可以保证数据库表中每一行数据的唯一性.
5.可以加快表和表之间的连接
6.在使用分组和排序进行数据查询时, 可以显著减少查询中分组和排序的时间.

建立索引的目的是加快对表中记录的查找或排序. 为表设置索引要付出代价:
一: 增加了数据库的存储空间
二: 在插入和修改数据时要花费较多的时间(以为索引也要随之变动)

分类

1.普通索引
2.唯一性索引
3.主键索引
4.全文索引
5.单列索引与多列索引

创建原则依据

1.表的主键、外键必须有索引.
2.数据量超过300行的表应该有索引
4.唯一性太差的字段不适合建立索引
5.更新太频繁的字段不适合创建索引
6.经常出现在Where中的字段, 特别是大表的字段, 应该建立索引.
7.索引应该建在选择性的字段上. 如果很少的字段拥有相同值, 即有很多独特值, 则选择性很高.
8.索引应该建在小字段上, 对于大的文本字段甚至超长字段, 不要建索引.

创建索引

1
2
3
4
5
6
# 查看该表上的索引
show index from 数据表名
show keys from 数据表名

# 解释当前查询的SQL语句
explain 查询SQL语句 limit 从多少条后开始查询,查询条数

1.普通索引

1
2
3
4
5
# 表创建
create index 索引名 on 数据表名 (列名1, 列名2,...);

# 修改表
alter table 数据表名 add index 索引名(列名1, 列名2,...);

2.创建唯一性索引

1
create unique index 索引名 on 数据表名 (列名1, 列名2,...);

3.创建主键索引

1
2
3
4
5
6
7
8
9
10
# 表创建
create table 数据表名
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ...,
    PRIMARY KEY(列名1, 列名2,...)
);

# 修改表
alter table 数据表名 add primary key(列名1, 列名2,...);

4.全文索引
只有使用MyISAM类型表的时候有效(MyISAM是默认的表类型)
全文索引可以建立在TEXT、CHAR或者VARCHAR类型的字段或者字段组合上.

1
2
3
4
5
6
7
8
9
10
11
# 表创建
create table 数据表名
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    document TEXT,
    ...,
    FULLTEXT(列名1, 列名2,...)
);

# 修改表
alter table 数据表名 add fulltext(列名1, 列名2,...);

5.多列索引

1
create index 索引名 on 数据表名 (列名1, 列名2,...);

性能测试

1
mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --iterations=1 --create-schema='数据库名' --query="select * from salaries where emp_no='54329'" engine=innodb --number-of-queries=查询数量 -u[用户名] -p[密码] -verbose

删除索引

1
2
3
4
5
# 表删除
drop index 索引名 on 数据表名;

# 修改表
alter table 数据表名 drop index 索引名;

事务

概念

事务是一种机制\一个操作序列, 包含了一组数据库操作命令, 并且把所有的命令作为一个整体一起向系统提交或撤销操作请求, 即这一组数据库命令要么都执行, 要么都不执行.

事务是一个不可分割的工作逻辑单元, 在数据库系统上执行并发操作时, 事务是最小的控制单元.

ACID特性

事务具体有四个特性:
原子性: 事务是一个完整的操作, 各元素是不可分的, 即原子的. 事务中的所有元素必须作为一个整体提交或回滚, 如果事务中的任何元素失败, 则整个事务将失败.

一致性: 当事务完成时, 数据必须处于一致状态; 在事务开始之前, 数据库中存储的数据处于一致状态; 在正在进行的事务中, 数据可能处于不一致的状态; 当事务成功完成时, 数据必须再次回到已知的一致状态.

隔离性: 对数据进行修改的所有并发事务是彼此隔离的, 这表明事务必须是独立的, 它不应以任何方式依赖于或影响其他事务. 修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据, 或者在另一个使用相同数据的事务结束之后访问这些数据.

持久性: 指不管系统是否发生了故障, 事务处理的结果都是永久的. 一旦事务被提交, 事务的效果会被永久地保留在数据库中.

事务命令

begin: 表示开始一个事务, 后面会有多条数据库操作语句执行.
commit: 表示提交一个事务, 对应前面的begin操作, 它们之间的数据库操作语句一起完成.
rollback: 表示回滚一个事务, 在begin和commit之间, 如果某一个数据库操作语句出现错误, 执行rollack回滚, 数据库回到begin之前的状态, 也就是操作语句都没执行.
savepoint: 定义回滚点, rollback to savepoint 回滚点名决定回滚到的位置.

设置set控制事务

1
2
set autocommit = 0; # 禁止自动提交, 即手动commit.
set autocommit = 1; # 允许自动提交

MYSQL数据库管理

用户

1.新建用户

1
2
3
4
5
create user '用户名'@'%/localhost/ip地址' identified by '明文密码';
create user '用户名'@'%/localhost/ip地址' identified by password '密文密码';

# 查看user表中新建的用户
select host,user,password from user;

2.删除用户

1
2
drop user '用户名'@'%/localhost/ip地址';
select host,user,password from user;

3.重命名用户

1
2
rename user '用户名'@'%/localhost/ip地址' to '用户名'@'%/localhost/ip地址';
select host,user,password from user;

4.给用户设置密码

1
2
3
4
5
6
7
8
# ---version: 5.6---
# 修改当前登录密码
set password = password('密码');
select host,user,password from user;

# 修改其他用户密码
set password for '用户名'@'%/localhost/ip地址' = password('密码');
select host,user,password from user;

5.重设root密码
(1)停止mysqld服务进程

1
2
systemctl stop mysqld
netstat -utpln | grep 3306

(2)使用mysqld_safe结合skip-grant-tables启动数据库, 作用是用户登录时不使用授权表.

1
2
mysqld_safe --skip-grant-tables&
netstat -utpln | grep 3306

(3)使用update修改root密码

1
2
3
4
5
6
# ---version: 5.6---
update mysql.user set password=password("密码") where user='root';

# ---version: 5.7---
update mysql.user set authentication_string=password('密码') where user='root';
select User,authentication_string,Host from user;

(4)刷新数据库

1
flush privileges;

(5)登录测试

1
mysql -u[用户名] -p[密码]

授权

1.授予权限
权限控制主要是出于安全因素, 因此需要遵循以下几个经验原则:
(1).只授予能满足需要的最小权限
(2).创建用户的时候限制用户的登录主机, 一般是限制成指定IP或者内网IP段.
(3).初始化数据库时删除没有密码的用户. 安装完数据库时会自动创建一些用户, 这些用户默认没有密码.
(4).为每个用户设置满足密码复杂度的密码
(5).定期清理不需要的用户. 回收权限或者删除用户.

授予权限使用GRANT命令

1
grant 权限列表 on 数据库名.数据表名 to '用户名'@'主机地址' [identified by '密码'];

2.查看权限

1
show grants for '用户名'@'主机地址';

3.撤销权限

1
revoke 权限列表 on 数据库名.数据表名 from '用户名'@'主机地址';
4.权限列表说明
权限说明
ALL设置GRANT OPTION之外的所有权限
ALTER允许使用ALTER TABLE
CREATE允许使用CREATE TABLE
CRFATE USER允许使用CREATE USER
DELETE允许使用DELETE
INDEX允许使用INDEX
INSERT允许使用INSERT
SELECT允许使用SELECT
UPDATE允许使用UPDATE
DROP允许使用DROP TABLE
REPLICATION SLAVE允许从主服务器中读取二进制日志文件
SHOW DATABASES允许显示所有数据库

日志管理

MySQL的日志类型有错误日志、通用查询日志、二进制日志、慢查询日志.

错误日志

1.错误日志主要记录当MySQL启动和停止时, 以及在运行过程中发生任何错误时的相关信息. 错误日志默认保存在MySQL的安装路径data文件夹下, 后缀名是.err.

1
2
3
ls /usr/local/mysql/data
  主机名.err
  ...

2.在MySQL的配置文件中, 可以指定日志文件的保存位置和日志的文件名. log-error=file_ name选项来指定保存错误日志的位置, file_name指定日志文件名, 如果没有指定文件名, 使用host_name.err作为文件名.

1
2
vim /etc/my.cnf
  log-error=/usr/local/mysql/data/mysql_error.log

重启MySQL后, mysql_error.log文件生成.

1
2
3
4
systemctl restart mysqld
ls /usr/local/mysql/data
  mysql_error.log
  ...

注: MySQL必须对指定的文件夹有读写的权限, 否则无法操作文件.

通用查询日志

1.通用查询日志用来记录MySQL的所有连接和语句, 默认是关闭的. 使用SHOW语句可以查询出日志的信息.

1
show variables like'general%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | OFF                                 |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)

2.修改MySQL配置文件的general_log=ON选项, 可以打开通用查询日志,
general_log_file=file_name 定义通用查询日志的位置, 如果没有指定file_name的值, 默认名是host_name.log.

1
2
3
vim /etc/my.cnf
  general_log=ON
  general_log_file=/usr/local/mysql/data/mysql_general.log

3.重启MySQL后, mysql_error.log文件生成.

1
2
3
4
systemctl restart mysqld
ls /usr/local/mysql/data
  mysql_general.log
  ...

二进制日志

1
show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /data/mysql/mysql-bin       |
| log_bin_index                   | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+

1.二进制日志用来记录所有更新了数据或者已经潜在更新了数据的语句, 记录了数据的更改, 主要目的是在恢复数据时能够最大程度地恢复数据库.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# ---version: 5.6---
vim /etc/my.cnf
  [mysqld]
  log-bin=mysql_bin

systemctl restart mysqld
ls /usr/local/mysql/data
  mysql_bin.000001
  mysql_bin.000002
  mysql_bin.000003
  ...

# ---version: 5.7---
vim /etc/my.cnf
  [mysqld]
  server-id = 1
  log_bin = mysql-bin
  log-bin-index = mysql-bin.index

systemctl restart mysqld
ls /usr/local/mysql/data

mysql -u 用户名 -p [密码]
show variables like 'log_bin';

2.二进制日志可以使用MySQL的工具mysqlbinlog查看二进制文件

1
mysqlbinlog mysql-bin.000001

慢查询日志

慢查询日志记录所有执行时间超过long_query_time秒的SQL语句, 用于找到哪些查询语句执行时间长, 以便对其进行优化.

1
show variables like '%slow%';
+---------------------------+------------------------------------------+
| Variable_name             | Value                                    |
+---------------------------+------------------------------------------+
| log_slow_admin_statements | OFF                                      |
| log_slow_slave_statements | OFF                                      |
| slow_launch_time          | 2                                        |
| slow_query_log            | OFF                                      |
| slow_query_log_file       | /usr/local/mysql/data/localhost-slow.log |
+---------------------------+------------------------------------------+
5 rows in set (0.00 sec)

1.可以使用SQL语句开启慢查询功能, 文件名为hostname-slow.log.

1
set global slow_query_log=ON;

慢查询时间设置默认为10秒, 记录10秒内的查询, 可以通过global long_query_
time选项进行修改.

1
2
3
show variables like 'long_query_time';
set long_query_time=8; #立即生效, 下次登录MySQL时失效.
set global long_query_time=5; #需重新登录MySQL

2.在MySQL配置文件中进行设置开启慢查询功能

1
2
3
4
5
6
7
8
9
vim /etc/my.cnf
  slow_query_log=ON 
  slow_query_log_file=mysql_slow_query.log 
  long_query_time = 1

systemctl restart mysqld
ls /usr/local/mysql/data
  mysql_slow_query.log 
  ...

3.测试慢查询日志

1
2
3
select sleep(6);
quit;
cat /usr/local/mysql/data/mysql_slow_query.log

MySQL数据库备份与恢复

备份重要性与数据丢失原因

数据库备份的重要性主要体现在:
(1)提高系统的高可用性和灾难可恢复性, 在数据库系统崩溃时, 没有数据库备份就没法找到数据.
(2)使用数据库备份还原数据库, 是数据库崩溃时提供数据恢复最小代价的最优方案, 如果让用户重新添加数据, 代价未免太大.
(3)没有数据就没有一切, 数据库备份是一种防范灾难的强力手段.

使用数据库的过程中, 有多种原因造成数据的丢失:
(1)程序错误
(2)人为错误
(3)计算机失败
(4)磁盘失败
(5)灾难和偷窃

备份分类

从物理与逻辑的角度:
1.物理备份
  |_脱机备份(冷备份)
  |_联机备份(热备份)

2.逻辑备份
完全备份
差异备份
增量备份

备份方式比较
备份方式完全备份差异备份增量备份
完全备份时的状态表1、表2表1、表2表1、表2
第1次添加内容创建表3创建表3创建表3
备份内容表1、表2、表3表3表3
第2次添加内容创建表4创建表4创建表4
备份内容表1、表2、表3、表4表3、表4表4

完全备份

MySQL的备份方式主要有完全备份与增量备份. 完全备份是对整个数据库的备份、数据库结构和文件结构的备份, 保存的是备份完成时刻的数据库, 是增量备份的基础.

完全备份优点是备份与恢复操作简单方便, 缺点是数据存在大量重复, 占朋大量的备份空间, 备的时间长.

在生产环境中, 这两种备份方式都会使用, 需要制定合理高效的方案达到备份数据的目的, 避免数据丢失造成严重的后果.

使用tar打包文件夹备份

MySQL的数据库文件默认都是保存在安装目录的data文件夹下面, 可以直接保存data文件夹, 但是占用的空间较大, 可以使用tar打包压缩进行保存.

(1)数据库文件很大, 可以使用压缩率较大的xz格式压缩, 首先需要安装xz压缩格式工具.

1
yum -y install xz

(2)对数据库文件夹/usr/local/mysq/data/进行打包操作

1
2
tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/
ls /opt

(3)对比打包前后的文件大小, 可以看到压缩的备份文件占用空间很小.

1
2
du -sh /opt/mysql-20*
du -sh /usr/local/mysql/data

(4)如果数据库文件损坏数据丢失, 可以解压缩备份文件, 相当于做了数据恢复的工作.

1
tar Jxvf /opt/ mysql-20* /usr/local/mysql/data/

使用mysqldump工具备份

使用mysqldump可以更加灵活地控制备份的内容, 比如某几个表或库都可以单独备份.

(1)使用mysqldump命令对某些表进行完全备份

1
mysqldump -u 用户名 -p [密码] [选项] [数据库名] [数据表名] [数据表名]... > /备份路径/备份文件名

(2)使用mysqldump命令对单个库进行完全备份

1
mysqldump -u 用户名 -p [密码] [选项] [数据库名] > /备份路径/备份文件名

(3)使用mysqldump命令对多个库进行完全备份

1
mysqldump -u 用户名 -p [密码] [选项] [数据库名] [数据库名]... > /备份路径/备份文件名

(4)使用mysqldump命令对所有库进行完全备份

1
mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名

(5)使用mysqldump命令也可以直接备份表结构

1
mysqldump -u 用户名 -p [密码] -d 数据库名 数据表名 > /备份路径/备份文件名

完全恢复

1.source命令库、表恢复

1
2
3
4
5
6
7
mysql -u 用户名 -p [密码]
#数据库恢复
source 备份脚本路径

#数据表恢复
use 数据库名
source 备份脚本路径

2.MySQL命令整库恢复

1
mysqldump -u 用户名 -p [密码] 数据库名 < /备份路径/备份文件名

3.MySQL命令整表恢复

1
mysql -u 用户名 -p [密码] 数据库名 < /备份路径/备份文件名

增量备份

备份自上一次备份之后增加或改变的文件或者内容

增量备份的优点是没有重复数据, 备份量不大, 时间短. 缺点也很明显, 需要上次完全备份及完全备份之后所有的增量备份才能恢复, 而且对所有增量备份进行逐个反推恢复, 操作较为繁锁.

MySQL没有提供直接的增量备份方法, 但是可以通过MySQL的二进制日志(binary logs)间接实现增量备份.

二进制日志对备份的意义如下:
(1)二进制日志保存了所有更新或者可能更新数据库的操作
(2)二进制日志在启动MySQL服务器后开始记录, 并在文件达到max_binlog_size所设置的大小或者接收到fush logs命令后重新创建新的日志文件.
(3)只需要定时执行fush logs方法重新创建新的日志, 生成二进制文件序列,
并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份.

实现备份

1.开启二进制日志功能
(1)在MySQL配置文件的[mysqld]项中加入log-bin=filepath项, 然后重启mysqld服务.

1
2
3
4
vim /etc/my.cnf
  [mysqld]
  log-bin=mysql-bin
systemctl restart mysqld

(2)使用mysqld –log-bin=filepath重新启动MySQL服务

1
/etc/init.d/mysqld restart --log-bin=mysql-bin

2.确定字符集使用的是utf-8的形式, 避免出现乱码问题.

1
show variables like '%char%';

3.创建备份文件保存的文件夹/opt/bak_sgl, 备份文件都保存在这个文件夹中.

1
mkdir /opt/bak_sgl

4.每周选择服务器负载较轻的时间段, 或者用户访问较少的时间段进行备份.
使用mysqldump命令, 可以对数据库或表进行备份.
(1)使用mysqldump备份数据库、数据表

1
mysqldump -u 用户名 -p [密码] [选项] [数据库名] [数据表名] > /备份路径/备份文件名

(2)使用fush-logs生成新的二进制文件

1
2
mysqladmin -u 用户名 -p [密码] flush-logs
ls /usr/local/mysql/data/

(3)插入新的数据, 以模拟数据的增加或者变更.

1
2
3
4
insert into 数据库名.数据表名(列名1, 列名2, 列名3,...)
values
	(值1, 值2, 值3,...),
	(值1, 值2, 值3,...);

(4)使用mysqlbinlog命令查看二进制文件内容

1
mysqlbinlog 二进制日志文件名

(5)复制二进制文件至保存备份文件的目录, 完成增量备份.

1
2
cp 二进制日志文件名 /opt/bak_sql/
ls /opt/bak_sql

实现恢复

恢复场景
(1)人为的SQL语句破坏了数据库
(2)在进行下一次全备之前发生系统故障导致数据库数据丢失
(3)在主从架构中, 主库数据发生了故障.

根据数据丢失的情况可以分为两类:
(1)只丢失了完全备份之后更改的数据
(2)完全备份之后丢失所有数据

丢失完全备份之后更改的数据恢复步骤

1.进行一次完全备份, 确保生成了完全备份的sql文件.

1
mysqldump -u 用户名 -p [密码] [选项] [数据库名] [数据表名] > /备份路径/备份文件名

2.使用flush-logs生成新的二进制日志文件, 用于保存之后的数据库操作语句.

1
2
mysqladmin -u 用户名 -p [密码] flush-logs
ls /usr/local/mysql/data/

3.插入数据并执行flush-logs生成新的二进制日志文件

1
2
3
4
5
6
7
insert into 数据库名.数据表名(列名1, 列名2, 列名3,...)
values
	(值1, 值2, 值3,...),
	(值1, 值2, 值3,...);

mysqladmin -u 用户名 -p [密码] flush-logs
ls /usr/local/mysql/data/

4.删除插入的数据进行增量恢复

1
mysqlbinlog /usr/local/mysql/data/二进制日志文件-序号 | mysql -u 用户名 -p [密码]

完全备份之后丢失所有数据的恢复步骤

1.进行一次完全备份, 确保生成了完全备份的sql文件.

1
mysqldump -u 用户名 -p [密码] [选项] [数据库名] [数据表名] > /备份路径/备份文件名

2.使用flush-logs生成新的二进制日志文件, 用于保存之后的数据库操作语句.

1
2
mysqladmin -u 用户名 -p [密码] flush-logs
ls /usr/local/mysql/data/

3.插入数据并执行flush-logs生成新的二进制日志文件

1
2
3
4
5
6
7
insert into 数据库名.数据表名(列名1, 列名2, 列名3,...)
values
	(值1, 值2, 值3,...),
	(值1, 值2, 值3,...);

mysqladmin -u 用户名 -p [密码] flush-logs
ls /usr/local/mysql/data/

4.删除完全备份的数据表, 即完全备份数据和完全备份之后的数据丢失.

1
drop table 数据库名.数据表;

5.使用mysql命令进行完全备份的恢复

1
mysql -u 用户名 -p [密码] 数据库名 < /备份路径/备份文件名

6.完全备份之后的数据进行增量恢复

1
mysqlbinlog /usr/local/mysql/data/二进制日志文件-序号 | mysql -u 用户名 -p [密码]

基于时间点与位置的恢复

1.基于时间点的恢复
将某个起始时间的二进制日志导入数据库中, 从而跳过某个发生错误的时间点实现数据的恢复.

使用mysqlbinlog加上
–stop-datetime选项表示在哪个时间点停止执行, 后面误操作的语句不执行,
–start-datetime选项表示在哪个时间点开始执行后面的语句,
注意的是二进制文件中保存的日期格式需要调整为用”-“分割:00-00-00 0:00:00.

1
2
mysqlbinlog --stop-datetime='00-00-00 0:00:00' /usr/local/mysql/data/二进制日志文件 mysql -u 用户名 -p [密码]
mysqlbinlog --start-datetime='00-00-00 0:00:00' /usr/local/mysql/data/二进制日志文件 mysql -u 用户名 -p [密码]

2.基于位置的恢复
基于位置的恢复, 就是使用基于时间点的恢复.

1
2
mysqlbinlog --stop-datetime='at序号' /usr/local/mysql/data/二进制日志文件 mysql -u 用户名 -p [密码]
mysqlbinlog --start-datetime='at序号' /usr/local/mysql/data/二进制日志文件 mysql -u 用户名 -p [密码]

MySQL存储过程

MySQL的体系架构

Connectors:连接组件
Management Service & Utilities:管理服务和控制组件
Connection Pool:连接池组件
SQL Interface:SQL接口组件
Parser:查询分析器组件
Optimizer:优化器组件
Caches & Buffers:缓存组件
Pluggable Storage Engines:插件式存储引擎
File System:文件系统
FIle & Logs:存储文件和日志

MySOL支持插件式的表存储引擎,这种独有的插件式体系架构,让存储引擎直了依赖于应用的多样性. 存储引擎处于文件系统之上, 在数据保存到数据文件之前会先传输到存储引擎, 然后按照各个存储引擎的存储格式进行数据存储.

使用这种存储引擎的主要优点在于, 仅仅需要提供特殊应用所需的特性即可; 数据库中的系统开销较小; 具有更有效和更高效的数据库性能.

MyISAM存储引擎

MyISAM存储引擎是MySQL关系数据库系统5.5版本之前默认的存储引擎, 它的前身是ISAM, ISAM是一个定义明确且历经时间考验的数据表格管理方法, 它在设计之时就考虑到数据库被查询的次数要远大于更新的次数. 因此ISAM读取操作的速度很快, 而且不占用大量的内存和存储资源.

ISAM的两个主要不足之处在于, 它不支持事务处理也不能够容错. 如果硬盘崩溃, 那么数据文件就无法恢复了.

MyISAM管理非事务表, 是MySQL的ISAM扩展格式. 除了提供ISAM里所没有的索引和字段管理的大量功能, MyISAM还使用一种表格锁定的机制, 来优化多个并发的读写操作. MyISAM提供高速存储和检索, 以及全文搜索能力.

特点

1.不支持事务, 需要事务支持的系统不能使用MyISAM作为存储引擎.
2.表级锁定形式, 数据在更新时锁定整个表.
3.数据库在读写过程中相互阻塞:
(1)在数据写入的过程中阻塞用户对数据的读取
(2)在数据读取的过程中阻塞用户写入数据

4.可以通过key_buffer_size来设置缓存索引, 提高访问的性能, 减少磁盘IO的压力.
5.采用MyISAM存储引擎进行数据单独写入或读取, 速度较快且占用资源相对要少.
6.MyISAM存储引擎不支持外键约束, 只支持全文索引.
7.每个MyISAM在磁盘上存储成三个文件. 每一个文件的名字均以表的名字开始, 扩展名指出文件类型:
(1).frm文件存储表定义
(2)数据文件的扩展名为: .MYD(MYData)
(3)索引文件的扩展名为: .MYI(MYIndex)

InnoDB存储引擎

InnoDB存储引擎不同于MyISAM存储引擎, 能够支持事务安全表(ACID兼容), 具有提交、回滚和崩溃恢复的能力.

InnoDB支持行级锁定, 并且可以在SELECT语句中提供一个与Oracle风格类似的非锁定读, 也支持FOREIGN KEY强制.

种种特性使得InnoDB存储引擎的多用户部署和性能得以提升.

InnoDB存储引擎还支持SQL查询中将InnoDB类型的表与其他MySQL的表类型混合使用, 甚至在同一个查询中也可以混合使用.

特点

1.支持事务, 支持四个事务隔离级别.
2.行级锁定, 但是全表扫描仍然会是表级锁定.
3.读写阻塞与事务隔级别相关
4.具有非常高效的缓存特性, 能缓存索引, 也能缓存数据.
5.表与主键以簇的方式存储
6.支持分区、表空间, 类似Oracle数据库.
7.支持外键约束, MySQL 5.5以前不支持全文索引, 5.5版本以后支持.
8.适合对硬件资源要求比较高的场合

配置存储引擎

查看数据库可配置的存储引擎类型

1
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Engine列表示当前版本的MySQL所支持的引擎类型;
Support列表示对应引擎是否能使用;
Transactions列表示对应引擎是否支持事务;
DEFAULT表示该引擎为当前默认存储引擎.

查看数据表正在使用的存储引擎

1
2
3
4
# 查看创建数据表的SQL语句
show create table 数据表名;

show table status from 数据库名 where name = '数据表名';
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| salaries | InnoDB |      10 | Dynamic    | 2752484 |             41 |   114966528 |               0 |     43597824 |   3145728 |           NULL | 2020-08-19 21:27:34 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)

配置存储引擎为所选择的类型

1.修改数据表结构

1
alter table 数据表名 engine = 存储引擎;

2.修改MySQL的配置文件my.cnf

1
2
3
4
5
6
vim /etc/my.cnf
  [mysqld]
  default-storage-engine=MyISAM
  ...

systemctl restart mysqld

3.创建数据表指定

1
2
3
4
create table 数据表名
(
    ...
) ENGINE=InnoDB;

4.批量转换存储引擎

1
2
3
4
5
6
7
yum -y install prel=DBI prel-DBD-MySQL

vim /usr/local/mysql/bin/mysql_convert_table_format
  "$opt_engine"   => \$opt_engine, # 32行
  ...

mysql_convert_table_format --user=root --password=密码 --socket=/temp/mysql.sock --engine = 存储引擎 数据库名 数据表名
-------------------本文结束 感谢阅读-------------------