mysqldump

Created2023-06-10|Updated2026-07-30|DatabaseMySQLBackup-Restore

|Word Count:79|Reading Time:1mins|Post Views:

mysqldump

Backup

mysqldump -uroot -p'welcome' \
--all-databases \
--single-transaction \
--routines \
--source-data=1 \
--flush-logs > backup.sql

Restore

# 设置还原时不记录binlog日志
sed -i '1i set sql_log_bin=0;' backup.sql

mysql -uroot -p'welcome@123' < backup.sql
mysqlbinlog --start-position=154 binlog.00003 binlog.00004 | mysql -uroot -p'welcome@123'
systemctl restart mysqld

--stop-position, --start-position 误删除时，可以用来跳过误删除语句

Author: Sakura Yumeno

Link: https://meno.lostyu.qzz.io/posts/fdedf5dc.html

Copyright Notice: All articles on this blog are licensed under CC BY-NC-SA 4.0 unless otherwise stated.

Related Articles

Using LVM Snapshot to Backup MySQL

Using LVM Snapshot to Backup MySQL 数据库全局读锁 flush tables with read lock; LVM 快照 lvcreate -L 500M -s -n mysql-snap /dev/datavg/mysql-snap mysql -uroot -p'Jove@123' -e 'show master status' > /backup/date +%F_position.log 释放全局读锁（关闭全局锁会话时，锁将自动失效） unlock tables; 挂载快照卷（只读）从快照卷中复制数据（cp/tar）卸载并删除快照卷 #! /bin/bash# LVM Snapshot for mysql backupbackup_dir=/backup/`date +%F`[ -d $backup_dir ] || mkdir -p $backup_direcho "FLUSH TABLES WITH READ LOCK; \SYSTEM lvcreate -L 500M -s -n mysql-snap /dev...

SQLServer Backup & Restore

SQLServer Backup & Restore 1.Full Backup BACKUP DATABASE [zsyydb] TO DISK=N'\\192.0.33.222\Backup\zsyydb.bak'WITH NOFORMAT,NOINIT,NAME=N'zsyydb-full',SKIP,NOREWIND,NOUNLOAD,STATS=5GO 2.Full Restore RESTORE DATABASE [zsyydb] FROM DISK=N'D:\backup\zsyydb.bak' WITH FILE=1,REPLACE,MOVE N'zsyydb'TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\zsyydb.mdf',MOVE N'HistoryData'TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\HistoryData.ndf...

Percona XtraBackup 2.4(EOL)

Percona XtraBackup 2.4(EOL) Percona XtraBackup 2.4.29 is the final release of the Percona XtraBackup 2.4 series. Percona XtraBackup 2.4 does not support making backups of databases created in MySQL 8.0 or higher. Full Backup # 备份mkdir -p /xtarbackup/fullinnobackupex --user=root --password='welcome' /xtarbackup/full# 停止MySQL服务，删除数据systemctl stop mysqldrm -rf /var/lib/mysql/*rm -rf /var/log/mysqld.logrm -rf /var/log/mysql-slow/slow.log# 备份还原innobackupex --apply-log /xtarbackup/full/2022-01-01...

Loading Database