MySQL数据库—备份与恢复

systemctl stop mysqld
cd /usr/local/mysql
tar zcf /opt/mysql_backup_$(date +%Y%m%d) data/
cd /opt/backup/
tar tf tar tf mysql_backup_20240619    #查看tar压缩包里的数据文件
tar xf tar tf mysql_backup_20240619    #解压缩tar压缩包里的数据文件

恢复数据库

###远端已备份数据文件主机（IP:192.168.80.100）
cd  /opt
scp mysql_backup_20240619 192.168.80.100:/opt
 
###需要恢复数据库文件主机（IP:192.168.80.100）
systemctl stop mysqld
cd /opt
tar xf mysql_backup_20240619 
mv /usr/local/mysql/data /usr/local/mysql/data.bak    #将原有的数据库文件移走
mv /opt/data/ /usr/local/mysql/
systemctl restart mysqld   #重新启动数据库服务
mysql -uroot -pabc123    #此数据库密码为之前备份数据库的密码

mysqldump进行逻辑备份

mysqldump备份数据库

备份指定数据库中的文件：
mysqldump -u用户名 -p密码数据库名 > 指定路径的绝对路径/数据库名.sql

备份指定数据库和文件：
mysqldump -u用户名 -p密码 --databases 数据库名 > 指定路径的绝对路径/数据库名.sql

备份多个库及文件
mysqldump -u用户名 -p密码 --databases 数据库1 数据库2 > 指定路径的绝对路径/数据库名.sql

备份所有数据库文件：
mysqldump -u用户名 -p密码 --all-databases > 指定路径的绝对路径/数据库名.sql

mysqldump备份数据表

备份指定数据库中的表数据文件：
mysqldump -u用户名 -p密码数据库名表名 > 指定路径的绝对路径/数据库名_表名.sql

恢复数据库

使用source命令恢复数据库

mysql -u root -pabc123; 登录数据库
source /opt/backup/xxxxx  #使用soure 指定原先库备份的路劲
show  databases;   #查看库

使用mysql命令恢复数据库

恢复表操作

MySQL增量备份与恢复

二进制日志文件

vim /etc/my.cnf
------------在“server-id”行下面添加mysql数据库的日志信息---------
server-id = 1
---------------------------------------------------------------
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志
log_bin=mysql_bin
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=2
 
systemctl restart mysqld
mysql -uroot -pabc123

刷新生成二进制日志文件的两种方式：

（1）重启数据库服务：systemctl restart mysqld;

（2）flush-logs命令刷新：mysqladmin -uroot -pabc123 flush-logs；

二进制日志(binlog)有3种不同的记录格式：

STATEMENT(基于SQL语句)：默认格式是STATEMENT。该方式记录语句快，占用内存空间少。但高并发情况下会导致记录日志顺序紊乱，造成恢复数据时发生偏差。
ROW(基于行)：基于数据内容行进行记录，不仅记录执行的命令语句，还会记录命令影响的相关数据行。
MIXED(混合模式)：高并发情况下ROW方式进行记录，一般情况下采用STATEMENT方式进行记录。

vim /etc/my.cnf
------------在“server-id”行下面添加mysql数据库的日志信息---------
server-id = 1
---------------------------------------------------------------
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志
log_bin=mysql_bin
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=2
 
systemctl restart mysqld
mysql -uroot -pabc123

刷新生成二进制日志文件的两种方式：

（1）重启数据库服务：systemctl restart mysqld;

（2）flush-logs命令刷新：mysqladmin -uroot -pabc123 flush-logs；

增量备份总结

查看二进制日志文件

<span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql">mysqlbinlog <span style="color:#6272a4">--no-defaults --base64-output=decode-rows -v  二进制日志文件</span></code></span></span>

刷新二进制日志

<span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql">mysqladmin <span style="color:#6272a4">-</span>uXXX <span style="color:#6272a4">-</span>pXXX flush<span style="color:#6272a4">-</span>logs</code></span></span>

使用二进制日志实现增量恢复

<span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql">mysqlbinlog <span style="color:#6272a4">--no-defaults 二进制日志文件 | mysql -uXXX -pXXX</span></code></span></span>

断点恢复

mysqlbinlog --no-defaults  --start-position="位置点"  --stop-position="位置点"  二进制日志文件 | mysql -uXXX -pXXX

mysqlbinlog --no-defaults  --start-datetime="YYYY-mm-dd HH:MM:SS"  --stop-datetime=