mysql记录-文件路径更改，备份还原，分区，主从复制

原创已于 2025-11-04 10:34:27 修改 · 1.6k 阅读

20 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

mysql

于 2024-06-19 10:27:32 首次发布

调整mysql数据库文件路径

1.把原来的data文件复制到新的data文件

2.关闭mysql80服务

3.修改my.ini，把datadir路径改为新的路径，注意：路径模式是D:/mysql8.0\data

4.重启服务器

数据库的备份和还原

方式一：命令

备份

使用mysqldump来实现对所有数据库，指定数据库，表的备份，存储过程，视图等备份。

-- 1指定表结构和数据
mysqldump -u root -p123456 [数据库名称] [table1,table2,table3...] > [导出路径]

-- 2数据结构进行备份，不需要表数据
mysqldump -u root -p123456 --no-data [数据库名称]  [table1,table2,table3...]> [导出路径]

-- 3所有表结构和数据及其他视图、存储过程
mysqldump -u root -p123456 --routines --events [数据库名称] > [导出路径] 

-- 4只导出存储过程
mysqldump -R -ndt [数据库名称] -u root -p > [导出路径]

总结：选自文章 mysqldump关于存储过程，事件等的导出_mysqldump events procedure-CSDN博客

-d 结构(--no-data:不导出任何数据，只导出数据库表结构)
-t 数据(--no-create-info:只导出数据，而不添加CREATE TABLE 语句)
-n (--no-create-db:只导出数据，而不添加CREATE DATABASE 语句）
-R (--routines:导出存储过程以及自定义函数)
-E (--events:导出事件)
--triggers (默认导出触发器，使用--skip-triggers屏蔽导出)
-B (--databases:导出数据库列表，单个库时可省略）
--tables 表列表（单个表时可省略）

使用搭配:

①同时导出结构以及数据时可同时省略-d和-t
②同时不导出结构和数据可使用-ntd
③只导出存储过程和函数可使用-R -ntd
④导出所有(结构&数据&存储过程&函数&事件&触发器)使用-R -E(相当于①，省略了-d -t;触发器默认导出)
⑤只导出结构&函数&事件&触发器使用 -R -E -d

还原

使用命令行来还原数据

mysql -uroot -p  -P 3306 [要导入的数据库] < [sql文件路径]

方式二：navicat

可以在备份前先把日志文件存储路径改下，更易看。

工具-->选项

使用数据传输工具

(将数据库中的数据从一个数据库系统传送到另一个数据库系统，也可用这个)

参考自：Navicat导出数据库选择只导表结构或者表记录_navicat怎么导出数据库怎么选择部分表不需要数据-CSDN博客

实战演练 | Navicat 安全可靠的数据传输功能_navicat数据传输视图-CSDN博客

点击工具-->数据传输

点击选项-->出现 1.表选项 2.记录选项
不选2只有表结构无记录，只选1只有表记录

存储为sql文件

备份：

还原：

右键 --> 运行sql文件

直接用备份

备份:

点击开始

还原：

定时备份

Navicat15在Win10电脑里设置Mysql数据库备份任务_navicat premium 15 自动定时备份mysql-CSDN博客

分区分库分表

对MySQL 8.0分区表探索总结_mysql8 分区表-CSDN博客

分区就是在“一张逻辑表”内部画界线，
物理上虽然被拆成若干分区段（segment），但：

对外你仍旧只看见 一张表
不需要改 SQL、不需要路由、不需要 ShardingKey
更谈不上出现 user_0、user_1 这种“多表”

-- 你看到的
SHOW TABLES;
+-------+
| user  |
+-------+

-- MySQL 磁盘里实际
datadir/
   db1/
      user#p#p0.ibd
      user#p#p1.ibd
      user#p#p2.ibd

文件被切成 3 份，但 SQL 层面还是 SELECT * FROM user;
优化器自动帮你定位到对应分区，感觉不到它们存在。

分库分表才是拆成多个表
把一张大表的数据拆到多个数据库 + 多张物理表里，让单机扛不住的数据量/并发分散到多台机器。
这个就不是mysql的原生功能了，分库分表是人为主动把一张“逻辑表”拆成 N 张“物理表”并放到不同库，必须由框架 / 中间件帮你做路由、聚合、事务，MySQL 只认单独的 db0.user_0、db1.user_1 … 它根本不知道它们属于“同一张大表”。

主从复制

是什么：主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

好处：企业中的业务通常数据量都比较大，而单台数据库在数据存储、安全性和高并发方面都无法满足实际的需求，所以需要配置多台主从数据服务器，以实现主从复制，增加数据可靠性，读写分离，也减少数据库压力和存储引擎带来的表锁定和行锁定问题。

原理：

Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。
2.从库读取主库的二进制日志文件Binlog(io线程)，写入到从库的中继日志Relay Log。
3.slave重做中继日志中的事件，将改变反映它自己的数据。(sql线程)

1.准备

(1) 在linux中下载mysql

在Ubuntu 22.04 LTS 上安装 MySQL两种方式：在线方式和离线方式_ubuntu离线安装mysql-CSDN博客

遇到问题：

您好我打上mysql-u root-p后提示这个ERROR 1698 (28000): Access denied for user 'root'@'localhost'该怎么办呢
把这一步改成 ①sudo mysql ②ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';③exit; 再用新密码进行登录

(2) 关闭linux和windows的防火墙

linux:Sudo ufw disable

windows:控制面板\所有控制面板项\Windows Defender 防火墙

2.查看二进制日志
show variables like '%log_bin%';

其中，log_bin_index文件存的是所有的二进制日志所在位置
log_bin_basename文件就是二进制日志所在位置

3.主机配置
(1)

修改my.ini文件
[mysqld]下添加
# 主从分离

server-id=1

read-only=0 //可读可写

重启服务器生效。

(2)

登录mysql，创建远程连接的账号，并授予主从复制权限
#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务

CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';

#为'itcast'@'%'用户分配主从复制权限

GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

flush privileges;

(3)

通过指令，查看二进制日志坐标

show master status;

4.从库配置

(1) 修改配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf

重启MySQL服务器
sudo systemctl restart mysql

(2) 登录mysql，设置主库配置

CHANGE MASTER TO MASTER_HOST='192.168.211.1',MASTER_USER='itcast',MASTER_PASSWORD='Root@123456',MASTER_LOG_FILE='binlog.000014',MASTER_LOG_POS=1917;

5.开启同步操作

start slave;
查看主从同步状态
show slave status;

均为yes，代表环境配置成功。

MySQL主从环境下存储过程，函数，触发器，事件的复制情况_mysql主从复制从库需要禁止触发器,事件,存储过程吗-CSDN博客

标签

#mysql #数据库