mysql备份指定数据库表bat脚本
时间: 2023-06-23 14:09:24 浏览: 57
以下是一个备份指定数据库表的 MySQL bat 脚本:
```
@echo off
setlocal
set MYSQL_HOME=C:\mysql\bin
set DB_NAME=mydatabase
set TABLE_NAME=mytable
set BACKUP_DIR=C:\backup
set TIMESTAMP=%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~6,2%
if not exist %BACKUP_DIR% mkdir %BACKUP_DIR%
%MYSQL_HOME%\mysqldump -u root -p mypassword %DB_NAME% %TABLE_NAME% > %BACKUP_DIR%\%TABLE_NAME%_%TIMESTAMP%.sql
echo Backup of table %TABLE_NAME% in database %DB_NAME% completed.
```
请注意,您需要将 `MYSQL_HOME` 设置为您的 MySQL 安装目录下的 `bin` 目录,将 `DB_NAME` 和 `TABLE_NAME` 设置为您要备份的数据库和表的名称,将 `BACKUP_DIR` 设置为您要将备份文件保存到的目录。您还需要将 `root` 和 `mypassword` 替换为您的 MySQL 用户名和密码。
相关问题
mydql备份远程数据库指定数据库表bat脚本
可以使用以下的命令来备份远程MySQL数据库中的指定表:
```
@echo off
setlocal
set MYSQL_USER=root
set MYSQL_PASSWORD=your_password
set MYSQL_HOST=remote_host
set MYSQL_DATABASE=your_database
set TABLE_NAME=your_table
set BACKUP_FOLDER=%cd%\backup
if not exist %BACKUP_FOLDER% mkdir %BACKUP_FOLDER%
set TIMESTAMP=%DATE:/=-%_%TIME::=-%
set TIMESTAMP=%TIMESTAMP: =0%
set BACKUP_FILE=%BACKUP_FOLDER%\%MYSQL_DATABASE%_%TABLE_NAME%_%TIMESTAMP%.sql
echo Backup started: %BACKUP_FILE%
echo.
echo mysqldump --user=%MYSQL_USER% --password=%MYSQL_PASSWORD% --host=%MYSQL_HOST% --single-transaction --skip-lock-tables %MYSQL_DATABASE% %TABLE_NAME% ^> %BACKUP_FILE%
mysqldump --user=%MYSQL_USER% --password=%MYSQL_PASSWORD% --host=%MYSQL_HOST% --single-transaction --skip-lock-tables %MYSQL_DATABASE% %TABLE_NAME% > %BACKUP_FILE%
echo.
echo Backup completed: %BACKUP_FILE%
```
这个脚本中,你需要设置以下变量:
- `MYSQL_USER`: 远程MySQL数据库的用户名
- `MYSQL_PASSWORD`: 远程MySQL数据库的密码
- `MYSQL_HOST`: 远程MySQL数据库的主机名或IP地址
- `MYSQL_DATABASE`: 要备份的数据库名
- `TABLE_NAME`: 要备份的表名
- `BACKUP_FOLDER`: 备份文件存储的目录路径
运行此脚本后,它将创建一个以当前日期和时间命名的备份文件,并将指定的表备份到该文件中。
K8S mysql 数据库备份脚本
根据提供的引用内容,K8S中的MySQL数据库备份脚本可以使用多种工具和脚本来实现。其中,常用的工具包括mysqldump、xtrabackup、mysqlbackup和mysql shell。这些工具可以通过编写shell脚本或Windows bat脚本来执行备份操作。
一个示例的备份脚本如下所示:
```bash
#!/bin/bash
backup_path="/opt/app-ai/deepwise/data/mysql-backup"
data_dir="/opt/app-ai/deepwise/data/mysql-data"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
backup(){
if \[ ! -z "${data_dir}" \];then
mkdir -p ${backup_path}
date >> ${backup_path}/${DATE}.log
echo "innobackupex --user=root --password=password --host=mysql-headless ${backup_path} > ${backup_path}/${DATE}.log 2>&1 &"
innobackupex --user=root --password=password --host=mysql-headless ${backup_path} > ${backup_path}/${DATE}.log 2>&1
date >> ${backup_path}/${DATE}.log
fi
}
clean(){
find ${backup_path}/ -name "*" -mtime +14 -exec rm -rf {} \;
}
backup
clean
```
这个脚本使用innobackupex工具来执行MySQL备份操作,并将备份文件存储在指定的路径中。同时,还包括了清理过期备份文件的功能。
此外,还可以使用Kubernetes的CronJob来定期执行增量备份操作。一个示例的CronJob配置如下所示:
```yaml
apiVersion: batch/v1beta1
kind: CronJob
metadata:
namespace: deepwise
name: mysql-incdump
spec:
jobTemplate:
spec:
completions: 1
template:
spec:
restartPolicy: Never
volumes:
- name: mysql-script
hostPath:
path: /opt/app-ai/deepwise/data/scripts
- name: mysql-backup
hostPath:
path: /opt/app-ai/deepwise/data/mysql-backup
- name: local-time
hostPath:
path: /etc/localtime
- name: mysql-data
hostPath:
path: /opt/app-ai/deepwise/data/mysql-data
containers:
- name: mysqldump-container
image: percona/percona-xtrabackup:2.4
volumeMounts:
- name: mysql-script
mountPath: /opt/app-ai/deepwise/data/scripts
- name: local-time
mountPath: /etc/localtime
- name: mysql-backup
mountPath: /opt/app-ai/deepwise/data/mysql-backup
- name: mysql-data
mountPath: /var/lib/mysql
command:
- "sh"
- "/opt/app-ai/deepwise/data/scripts/backup_inc.sh"
schedule: "0 0 * * *"
```
这个CronJob配置使用percona/percona-xtrabackup镜像来执行增量备份操作,并将备份文件存储在指定的路径中。备份脚本backup_inc.sh位于/opt/app-ai/deepwise/data/scripts目录下。
请根据实际情况修改脚本和配置文件中的路径和参数,以适应您的环境和需求。
#### 引用[.reference_title]
- *1* [分享(2)一个 mysqldump 备份 MySQL 数据库的脚本](https://blog.csdn.net/weixin_44496870/article/details/129706015)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item]
- *2* *3* [[kubernetes]-使用cronjob定时备份mysql](https://blog.csdn.net/xujiamin0022016/article/details/124648643)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item]
[ .reference_list ]