MySQL数据库

MySQL数据库 | 起源和分支 | 应用架构演变

• MySQL5.7文档：https://dev.mysql.com/doc/refman/5.7/en/

• MySQL8文档：https://dev.mysql.com/doc/refman/8.4/en/

MySQL起源和发展

MySQL 是最流行的关系型数据库软件之一，由于其体积小、速度快、开源免费、简单易用、维护成本低，在集群架构中易于扩展、高可用，因此深受开发者和企业的欢迎。

MySQL发展历程

MySQL主流分支

MySQL从最初的1.0、3.1到后来的8.0，发生了各种各样的变化。

• 被Oracle收购后，MySQL的版本演化出了多个分支，除了需要付费的MySQL企业版本，还有很多MySQL社区版本。
• 还有一条分支非常流行的开源分支版本叫Percona Server，它是MySQL的技术支持公司Percona推出的，也是在实际工作中经常碰到的。Percona Server在MySQL官方版本的基础上做了一些补丁和优化，同时推出了一些工具。
• 另外一个非常不错的版本叫MariaDB，它是MySQL的公司被Oracle收购后，MySQL的创始人Monty先生，按原来的思路重新写的一套新数据库，同时也把 InnoDB 引擎作为主要存储引擎，也算 MySQL 的分支。

MySQL应用架构演变

• 架构V1.0 - 单机单库

  • 数据量太大，超出一台服务器承受
  • 读写操作量太大，超出一台服务器承受
  • 一台服务器挂了，应用也会挂掉（可用性差）

• 架构V2.0 - 主从架构 主库抗写压力，通过从库来分担读压力，对于写少读多的应用，V2.0主从架构完全能够胜任。

  • 数据量太大，超出一台服务器承受
  • 写操作太大，超出一台M服务器承受

• 架构V3.0 - 分库分表

  • 对于V1.0和V2.0遇到写入瓶颈和存储瓶颈时，可以通过水平拆分来解决，水平拆分和垂直拆分有较大区别，垂直拆分拆完的结果，每一个实例都是拥有全部数据的，而水平拆分之后，任何实例都只有全量的1/n的数据。
  • 水平拆分后数据如何路由成为一个关键问题， 一般可以采用范围拆分，List拆分、Hash拆分等。如何保持数据的一致性也是个难题。

• 架构V4.0 - 云数据库 对于数据存储的MySQL来说，如何让其成为一个saas（Software as a Service）是关键点。 MySQL作为一个saas服务，服务提供商负责解决可配置性，可扩展性，多用户存储结构设计等问题

MySQL安装和配置

MySQL是一个C/S架构的软件，我们安装的MySQL 是服务端。

  [client]
  port=3306

  [mysql]
  default-character-set=utf8

  [mysqld]
  character-set-server=utf8
  default-storage-engine=INNODB
  explicit_defaults_for_timestamp=true
  basedir = D:\Develop\Database\mysql-5.7.37
  datadir = D:\Develop\Database\mysql-5.7.37\data

mysqld --initialize-insecure

mysqld -install

net start mysql

mysqladmin -u root password 123456

mysql -uroot -p

net stop mysql

mysqld -remove mysql

getent passwd mysql                      
# 查看当前用户的主目录：mysql:x:113:120:MySQL Server,,,:/nonexistent:/bin/false

sudo usermod -d /var/lib/mysql/ mysql    # 修改用户的主目录
# 修改主目录之前，请确保 /var/lib/mysql/ 目录已经存在，并且是 MySQL 数据文件的正确存放位置

cd /usr/local/software
wget http://dev.MySQL.com/get/Downloads/MySQL-5.7/mysql-5.7.37-Linux-glibc2.12-x86_64.tar.gz

tar -xvf mysql-5.7.37-Linux-glibc2.12-x86_64.tar.gz

mv ./mysql-5.7.37-linux-glibc2.12-x86_64/* /usr/local/mysql/

yum install libaio   （Ubuntu使用：sudo apt-get install libaio1）

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

cd /usr/local/mysql/

mkdir mysql-files
chown mysql:mysql mysql-files
chmod 750 mysql-files

bin/mysqld --initialize --user=mysql  # 记住末尾自动生成的root密码 如：lZw.qSdCS7pV
bin/mysql_ssl_rsa_setup
bin/mysqld_safe --user=mysql &

vim /etc/profile.d/mysql.sh  # 在里面加入: 
export PATH=$PATH:/usr/local/mysql/bin

source  /etc/profile  # 使用source命令使修改立刻生效：  

cp support-files/mysql.server /etc/init.d/mysql.server

chkconfig --add mysql.server  （ 或 systemctl enable mysql.server）

mysql -uroot -p

mysql> alter user user() identified by "123456";

/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
/etc/init.d/mysql.server restart

>mysql status;                            # 查看mysql基本信息
>mysql SHOW VARIABLES LIKE 'character%';  # 查看数据库字符集相关设置信息

vi /etc/my.cnf   # 添加如下信息：

[client]
port=3306

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

1. 打开数据库：use mysql。检索 user 和 host 字段
mysql> use mysql;
mysql> select user, host from user; 

2. 如果没有 user=root ，host = % 的用户，则执行下面语句：
mysql> update user set host='%' where user='root';

3. 给 root 授权（所有权限）, 并让赋予的权限立即生效
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql> flush   privileges;

MySql配置文件

查看mysql启动时读取my.cnf配置文件的目录

mysql --help|grep 'my.cnf'      

在 MySQL 中，如果某个配置项在多个配置文件中都出现了，MySQL 服务器会按一定的顺序读取这些配置文件，并且后读取的配置文件中的设置会覆盖先前读取的相同配置项。这意味着即使某个配置项在多个文件中出现，最终生效的是最后读取的那个文件中的值。

MySQL 的配置文件通常由多个节（sections）组成，每个节针对不同的功能进行配置。常见的节包括 [mysqld]、[client]、[mysql] 和 [mysqld_safe] 等。以下是一个示例配置文件 /etc/my.cnf，包含了常用的关键配置项：

# [client] 节：用于客户端工具（如 mysql 命令行工具）的全局设置
[client]
# 默认字符集：设置客户端默认字符集为 utf8mb4，支持更多字符，包括 Emoji
default-character-set=utf8mb4

# [mysql] 节：用于 mysql 命令行工具的特定设置
[mysql]
# 默认字符集：设置 mysql 命令行工具的默认字符集为 utf8mb4
default-character-set=utf8mb4

# [mysqld] 节：用于 MySQL 服务器的主要设置
[mysqld]
# 服务器监听的 IP 地址：0.0.0.0表示服务器监听所有网络接口
bind-address = 0.0.0.0

# 数据目录：指定 MySQL 数据文件的存储目录
datadir = /var/lib/mysql

# 错误日志文件：指定错误日志文件的位置
log_error = /var/log/mysql/error.log

# 慢查询日志
slow_query_log = 1   # 启用慢查询日志
slow_query_log_file = /var/log/mysql/slow-query.log  # 指定慢查询日志文件的位置
long_query_time = 2  # 慢查询的时间阈值，单位为秒

# 二进制日志
log_bin=/var/lib/mysql/mysql-binlog
binlog-format=ROW
server-id=1
expire_logs_days =30

# InnoDB 设置
innodb_buffer_pool_size = 128M  # InnoDB 缓冲池大小
innodb_log_file_size = 48M    # InnoDB 日志文件大小
innodb_flush_log_at_trx_commit = 1  # 每个事务提交时刷新日志到磁盘

# 最大连接数
max_connections = 150

# 字符集
character-set-server = utf8mb4         # 服务器默认字符集
collation-server = utf8mb4_unicode_ci  # 服务器默认排序规则

# 性能优化
query_cache_size = 0  # 禁用查询缓存
table_open_cache = 2000  # 表缓存大小
tmp_table_size = 16M  # 内存临时表的大小
max_heap_table_size = 64M  # 用户创建的内存表的最大大小

# 安全相关
skip-name-resolve  # 禁用 DNS 解析
local-infile = 0  # 禁用本地文件加载

# [mysqld_safe] 节：用于 mysqld_safe 启动脚本的设置
[mysqld_safe]
# 错误日志文件的位置
log-error = /var/log/mysql/error.log
# 设置打开文件的限制
open-files-limit = 1024

# [mysqldump] 节：用于 mysqldump 工具的设置
[mysqldump]
# 快速导出
quick
# 允许最大数据包大小
max_allowed_packet = 16M

MySql常用命令

MySQL 客户端提供了丰富的命令来帮助你管理数据库、执行 SQL 语句、查看数据库信息等：

1. 连接 MySQL 服务器

mysql -u username -p

• -u: 指定用户名。
• -p: 提示输入密码。

附加参数:

mysql -u username -p -h hostname -P port

• -h: 指定服务器的主机名或 IP 地址。
• -P: 指定 MySQL 服务器的端口号，默认为 3306。

显示当前连接的 MySQL 版本：

SELECT VERSION();           

帮助命令和退出客户端：

HELP command_name;   --  显示帮助信息

EXIT;                -- 退出 MySQL 客户端 还可以使用 QUIT;

2. 使用数据库

查看数据库列表，并选择需要使用的数据库：

SHOW DATABASES;        -- 显示数据库列表

USE database_name;     -- 选择数据库

SELECT DATABASE();     -- 查看当前使用的数据库

创建/ 删除数据库：

CREATE DATABASE database_name;

DROP DATABASE database_name;

查看表结构:

DESCRIBE table_name;

SHOW COLUMNS FROM table_name;

DROP TABLE table_name;     -- 删除表

3. 当前会话用户

SELECT USER();                -- 查看当前会话的用户

SELECT CURRENT_USER();        -- 查看当前会话的用户名和主机

SHOW STATUS;                  -- 查看当前会话的状态
SHOW SESSION STATUS;          -- 显示当前会话的会话状态
SHOW GLOBAL STATUS;           -- 显示当前会话的全局状态

SHOW VARIABLES;               -- 查看当前会话的变量
SHOW SESSION VARIABLES;       --显示当前会话的会话变量
SHOW GLOBAL VARIABLES;        -- 显示当前会话的全局变量


SHOW WARNINGS;                -- 查看当前会话的警告
SHOW ERRORS;                  --  查看当前会话的错误


SET SESSION variable_name = value;   -- 设置会话变量
SET @variable_name = value;          -- 设置会话变量
SET GLOBAL variable_name = value;    -- 设置全局变量

SHOW VARIABLES LIKE 'sql_mode';      -- 查看当前会话的 SQL 模式
SET sql_mode = 'mode_value';         -- 设置当前会话的 SQL 模式

SHOW VARIABLES LIKE 'character_set_client';    -- 查看当前会话的字符集
SET NAMES 'charset';                           -- 设置当前会话的字符集

SHOW VARIABLES LIKE 'collation_connection';    -- 查看当前会话的编码
SET collation_connection = 'encoding';         -- 设置当前会话的编码

4. sql文件

SOURCE file_path;   -- 执行 SQL 文件

-- 导出数据到文件
SELECT * INTO OUTFILE '/path/to/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"'
ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM table_name;

创建/删除用户

1. 创建用户: 在 MySQL 中创建用户的基本语法如下：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• username：用户名。
• host：用户允许连接的主机地址。可以是具体的 IP 地址、域名或者 '%' 表示任何主机。
• password：用户的密码。

示例: 创建一个名为 newuser 的用户，允许从任何主机连接，并设置密码为 newpass：

CREATE USER 'newuser'@'%' IDENTIFIED BY 'newpass';

2. 删除用户: 删除用户的语法如下：

DROP USER 'username'@'host';

示例: 删除名为 newuser 的用户：

DROP USER 'newuser'@'%';

MySQL用户表

在 MySQL 中，用户信息存储在 mysql 系统数据库中的几个表中。这些表主要用于存储用户账号信息、权限和认证数据。

1. mysql.user：存储用户的用户名、主机名、密码以及其他认证信息。
2. mysql.db：存储用户对特定数据库的权限。
3. mysql.tables_priv：存储用户对特定表的权限。
4. mysql.columns_priv：存储用户对特定列的权限。
5. mysql.profiles：存储用户的资源限制。
6. mysql.plugin：存储用户的插件信息。

1. 查看用户信息

SELECT Host, User FROM mysql.user;                            -- 查看所有用户

SELECT Host, User FROM mysql.user WHERE User = 'username';    -- 查看特定用户

2. 修改用户密码

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';       -- 使用 `ALTER USER`

SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');   -- 使用 `SET PASSWORD`

3. 修改用户属性

-- 修改用户的最大连接数
ALTER USER 'username'@'host' MAX_QUERIES_PER_HOUR 10000 MAX_CONNECTIONS_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 1000 MAX_USER_CONNECTIONS 10;

-- 修改用户的默认角色
ALTER USER 'username'@'host' DEFAULT ROLE 'role_name';

注意：在 MySQL 8.0 中引入了默认角色的概念，可以使用 DEFAULT ROLE 来设置用户的默认角色。

4. 禁用或启用用户

ALTER USER 'username'@'host' ACCOUNT LOCK;                          -- 禁用用户

ALTER USER 'username'@'host' ACCOUNT UNLOCK;                        -- 启用用户

DROP USER 'username'@'host';                                        -- 删除用户

重置root密码

无论是在 MySQL 5.7 还是 MySQL 8.0 中，设置密码的方法基本相同：

1. 登录 MySQL

   首先以 root 用户登录 MySQL：

   sh 复制

   sudo mysql -u root -p

2. 设置密码

   使用 ALTER USER 语句设置 root 用户的密码：

   sql 复制

   ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

   或者，如果你需要为其他用户设置密码：

   sql 复制

   CREATE USER '新用户名'@'localhost' IDENTIFIED BY '新密码';

   MySql5.7还可以使用下列方式：

   bash 复制

   SET PASSWORD FOR 'username'@'localhost' = PASSWORD('新密码');
   # 或者
   SET PASSWORD FOR 'username'@'localhost' = '新密码';

3. 刷新权限

   为了确保新设置的密码立即生效，需要刷新权限：

   bash 复制

   FLUSH PRIVILEGES;

4. 退出 MySQL

   退出 MySQL 命令行：

   bash 复制

   EXIT;

用户权限设置

权限授予：授予用户权限的基本语法如下：

GRANT privileges ON database.table TO 'username'@'host' [WITH GRANT OPTION];

• privileges：可以是一个或多个权限的列表，如 SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, EXECUTE, PROCESS, RELOAD, SHUTDOWN, FILE, REFERENCES, TRIGGER, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT, TRIGGER, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION MASTER, REPLICATION SLAVE, REPLICATION CLIENT, REPLICATION SLAVE, CREATE VIEW, SHOW DATABASES, SUPER, PROXY, GRANT OPTION 等。
• database.table：指定权限应用的对象。如果是 *.* 表示全局权限。
• WITH GRANT OPTION：允许用户授予其他用户相同的权限。

示例: 授予 newuser 对 mydb 数据库的 SELECT 和 INSERT 权限：

GRANT SELECT, INSERT ON mydb.* TO 'newuser'@'%' WITH GRANT OPTION;

查询权限：查询用户权限的基本语法如下：

SHOW GRANTS FOR 'username'@'host';

示例：查询 newuser 用户的权限：

SHOW GRANTS FOR 'newuser'@'%';

撤销权限：撤销用户权限的基本语法如下：

REVOKE privileges ON database.table FROM 'username'@'host';

示例：撤销 newuser 用户对 mydb 数据库的 SELECT 和 INSERT 权限：

REVOKE SELECT, INSERT ON mydb.* FROM 'newuser'@'%';

允许远程连接

MySQL的远程连接需要两个必要条件：

• 用户的 Host 字段设置为 % 或 指定的IP地址
• MySQL 服务器需要通过 bind-address 参数正确配置以监听相应的网络接口

由于MySQL的 mysql.user 表中的 root 用户的 Host属性默认为localhost, 意味着该用户只能在从本地访问数据库, 所以需要修改Host属性或者重新 新建一个用户，这里以新建用户为例：

create user 'root'@'%' identified by 'root';  # 或
create user 'root'@'%' identified with mysql_native_password by 'root';

grant all on *.* to 'root'@'%' with grant option;

flush privileges;

执行上述命令后，可使 root 用户从任何主机都可以连接到 MySQL 服务器，并且拥有所有数据库操作的最高权限。但此时还需要检查 MySQL 的 bind-address 配置：

SHOW VARIABLES LIKE 'bind_address';
# 如果是 127.0.0.1, 则需要修改my.cnf配置文件

# 查看所有配置文件的路径
mysql --help | grep my.cnf

sudo vim /etc/mysql/my.cnf

在配置文件中添加如下配置：

[mysqld]
bind-address = 0.0.0.0  # 监听所有网络接口

修改后重启MySQL服务即可

sudo systemctl restart mysql

jdbc:mysql://hostname:3306/db_name?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC

ALTER USER 'your_username'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';

FLUSH PRIVILEGES;

数据库设计理论

数据完整性

数据完整性是数据库制定的了一些规范，是为了防止用户错误的输入（防止数据库出现错误的数据）

语法示例（以MySQL为例）：

主键约束（primary key）：

# 1. 在 CREATE TABLE 语句中，通过 PRIMARY KEY 关键字来指定主键
<字段名> <数据类型> PRIMARY KEY [默认值]

# 2. 或者是在定义完所有字段之后指定主键：
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

# 3. 在创建表时设置联合主键（所谓的联合主键，就是这个主键是由一张表中多个字段组成的）
PRIMARY KEY [字段1，字段2，…,字段n]

# 4. 在修改表时添加主键约束
ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

# 删除主键约束
ALTER TABLE <数据表名> DROP PRIMARY KEY;

唯一约束 (unique)：

# 1. 在创建表时设置唯一约束
<字段名> <数据类型> UNIQUE

# 2. 在修改表时添加唯一约束
ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

# 删除唯一约束
ALTER TABLE <表名> DROP INDEX <唯一约束名>;

自动增长列 (auto_increment)：

# 1. 给字段添加 AUTO_INCREMENT 属性来实现主键自增长
字段名 数据类型 AUTO_INCREMENT

# 2. 指定自增字段初始值
CREATE TABLE tb_student2 (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY(ID)
)AUTO_INCREMENT=100;

• 默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）
• AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
• AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。
• AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

外键约束（foreign key）:

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]
-- 例：
constraint fk_score_sid foreign key(sid) references student(id) );

-- 删除外键约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

使用外键会影响效率：在插入子行的数据的时候，会去父表中查询。在删除父表中的数据的时候，会去子表中查询数据是否被使用。

在工作中，一般很少使用外键。外键虽然可以保证我们数据的正确性，但是会比较大程度上的影响效率。

三大范式

范式是数据库设计中用来减少数据冗余和提高数据一致性的标准。以下是三个最重要的范式：

1. 第一范式（1NF）：确保每个表中的每个列都包含原子（不可再分）值。这意味着每个单元格只能包含一个值，不能包含列表或集合。
2. 第二范式（2NF）：在满足第一范式的基础上，消除非主键列对部分主键的依赖。也就是说，所有非主键列必须完全依赖于整个主键。
3. 第三范式（3NF）：在满足第二范式的基础上，消除非主键列之间的传递依赖。也就是说，非主键列之间不应存在依赖关系。

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,           -- 学生的唯一标识符（主键）
    StudentName VARCHAR(100),            -- 学生姓名
    DepartmentID INT,                    -- 学生所在系的唯一标识符
    DepartmentName VARCHAR(100)          -- 学生所在系的名称
);

在这个表结构中，DepartmentName 依赖于 DepartmentID，而不是直接依赖于主键 StudentID。这意味着 DepartmentName 的值是由 DepartmentID 决定的，而不是由 StudentID 决定的。这种依赖关系导致了数据冗余和潜在的不一致性。

多表设计原则

多表设计是数据库设计中的一个重要方面，通过合理地拆分数据表来减少数据冗余和提高查询效率。

1. 一对多关系：一个表中的记录可以与另一个表中的多个记录相关联。例如，一个订单可以包含多个订单项。

   sql 复制

   CREATE TABLE Orders (
       OrderID INT PRIMARY KEY,
       CustomerID INT,
       OrderDate DATE
   );
   
   CREATE TABLE OrderItems (
       ItemID INT PRIMARY KEY,
       OrderID INT,
       ProductID INT,
       Quantity INT,
       FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
   );

2. 多对多关系：两个表中的记录可以相互关联。通常通过一个中间表来实现。

   sql 复制

   CREATE TABLE Students (
       StudentID INT PRIMARY KEY,
       Name VARCHAR(100)
   );
   
   CREATE TABLE Courses (
       CourseID INT PRIMARY KEY,
       CourseName VARCHAR(100)
   );
   
   CREATE TABLE Enrollments (
       EnrollmentID INT PRIMARY KEY,
       StudentID INT,
       CourseID INT,
       FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
       FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
   );

3. 一对一关系：一个表中的记录与另一个表中的一个记录相关联。通常通过共享主键来实现。

   sql 复制

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50)
   );
   
   CREATE TABLE Salaries (
       EmployeeID INT PRIMARY KEY,
       Salary DECIMAL(10, 2),
       FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
   );

MySQL常见问题

配置文件权限

在使用Docker安装MySQL的过程中发现配置文件不生效，查看日志docker logs d71e2aab9816发现：

mysqld: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored.

为了方便，之前将主系统的挂载目录改成了 777 的权限，MySQL可能觉得不安全… 这里将配置文件权限改为 644 即可：

sudo chmod 644 /path/to/my.cnf

修改后，重启mysql即可：docker restart d71e2aab9816

数据库备份与恢复

MySQL数据库的备份与恢复是数据库管理中的重要组成部分，它确保了数据的安全性和完整性。下面我将详细介绍如何进行MySQL数据库的备份和恢复。

数据备份

1. 使用mysqldump命令

mysqldump 是一个非常强大的命令行工具，用于创建MySQL数据库的逻辑备份。它可以导出整个数据库、单个表或特定的数据集。

• 全库备份：

  sh 复制

  mysqldump -u username -p database_name > backup_file.sql

  这里 username 是你的数据库用户名，database_name 是要备份的数据库名称，backup_file.sql 是输出的SQL文件名。

• 指定表备份：

  sh 复制

  mysqldump -u username -p database_name table1 table2 > tables_backup.sql

• 包含数据库结构（不包括数据）：

  sh 复制

  mysqldump -u username -p --no-data database_name > structure_only.sql

• 压缩备份： 可以使用管道将输出直接传递给压缩工具，如gzip。

  sh 复制

  mysqldump -u username -p database_name | gzip > backup_file.sql.gz

• 排除某些表： 如果需要备份整个数据库但排除一些特定的表，可以使用 --ignore-table 选项。

  sh 复制

  mysqldump -u username -p --ignore-table=database_name.table_to_ignore database_name > partial_backup.sql

• 增量备份： 对于大型数据库，可能需要考虑增量备份。这通常涉及到二进制日志 (binary logs) 的复制。你可以设置 binlog_format=ROW 和启用二进制日志记录来支持增量备份。

2. 物理备份

对于更大的数据库或者对性能有更高要求的情况，可以使用物理备份工具如 Percona XtraBackup 或者 MySQL Enterprise Backup。这些工具能够提供在线热备份，并且备份速度更快。

数据恢复

1. 从逻辑备份恢复

恢复逻辑备份时，你只需要执行之前备份生成的SQL脚本。

• 全库恢复：

  sh 复制

  mysql -u username -p database_name < backup_file.sql

• 创建新数据库并恢复： 如果数据库不存在，你需要先创建数据库，然后恢复。

  sh 复制

  mysql -u username -p -e "CREATE DATABASE new_database_name;"
  mysql -u username -p new_database_name < backup_file.sql

• 解压后再恢复： 如果备份是经过压缩的，需要先解压再恢复。

  sh 复制

  gunzip < backup_file.sql.gz | mysql -u username -p database_name

2. 从物理备份恢复

物理备份的恢复过程依赖于使用的工具，通常涉及准备步骤和应用二进制日志。例如，使用 Percona XtraBackup 时：

• 准备备份：

  sh 复制

  xtrabackup --prepare --target-dir=/path/to/backup

• 停止MySQL服务： 在恢复之前，需要停止MySQL服务。

• 移动数据文件： 将备份的数据文件移动到MySQL数据目录中。

• 启动MySQL服务： 启动MySQL服务后，根据情况应用二进制日志以确保数据的一致性。

xxxxxxxxxxxxx

1. 全面理解MySQL架构

2. 深入理解Mysql索引底层数据结构与算法

3. Explain详解与索引优化最佳实践 Explain笔记补充

4. Mysql索引优化实战一 索引优化补充 Mysql索引优化实战二

5. Mysql事务隔离级别与锁机制 事务补充

6. MVCC与BufferPool缓存机制

7. Innodb底层原理与Mysql日志机制深入剖析

8. Mysql全局优化与Mysql 8.0新特性详解

9. MySQL8.0主从复制原理分析与实战

10. Mysql8.0高可用集群架构实战