mysql数据库备份恢复

完整备份与恢复数据库

#mysql备份数据库
#语法
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
#常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实
   
#备份整个数据库（全备）
mysqldump -uroot -p123 --all-databases > 存放备份数据的目录/all-`date +%F`.sql

#备份整个数据库，但是不备份a库里面的b表
mysqldump -uroot -p123 -A -R --single-transaction --ignore-table=a.b 

##############
msqldump 备份
-u 用户名
-p 密码
-h 存放备份文件的服务器ip地址

#还原整个数据库
mysql -uroot -p123 -h127.0.0.1 < 存放备份数据的目录/文件名.sql
#备份单个数据库
mysqldump -uroot -p123 数据库名称 > 存放备份数据库的目录/数据库名称-`date +%F`.sql

#还原单个数据库 还原数据库需要手动创建一个数据库，最好和原来的名字一样
mysql -uroot -p123 -h127.0.0.1 数据库名称 < 存放备份数据的目录/文件名.sql


mysql 数据库内部恢复数据库#尾部不用加;
 source D:\test.sql

差异备份与恢复数据库

#开启mysql服务器的二进制日志功能
vim /etc/my.cnf.d/mariadb-server.cnf
###############server.cnf里面的数据##############
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=1			# 设置服务器标识符
log-bin=mysql_bin	 #开启二进制日志功能
###################################
systemctl restart mariadb.service #重启mysql服务

#二进制文件存放位置
ls /var/lib/mysql/

#设置完成之后首先对数据库进行完整备份
mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > /backup/mysqldump/all_`date +%F`.sql

#刷新创建新的mysql二进制文件
 mysqladmin -uroot -p123 flush-logs
#恢复完全备份
mysql -uroot -p123 < 存放备份文件目录/文件.sql

#差异备份恢复
#检查误删数据库的位置在什么地方
#进入mysql里面
mysql -uroot -p123
############################
#查询误删数据库的节点
show binlog events in 'mysql_bin.000004'
#使用mysqlbinlog恢复差异备份
mysqlbinlog --stop-position=607 /var/lib/mysql/mysql_bin.000004 | mysql -uroot -p123

###########################

备份时不备份指定的表

--ignore-table=表明 		或者 --ignore-table=数据库名.表明
#备份整个数据库但是不备份某个数据库的某张表
mysqldump -uroot -proot -A -R --single-transaction  --ignore-table=数据库名.表明 >/opt/mysql/$(date +%F)_full.sql 

大量数据进行恢复参数配置

max_allowed_packet的作用
    在 MySQL 中，客户端和服务器之间的通信是通过数据包进行的。数据包是从客户端发送到服务器，或从服务器发送到客户端的一系列数据。这些数据包可能包含 SQL 查询、查询结果、插入/更新语句等。
    数据包的大小对于 MySQL 性能和功能至关重要。如果数据包太小，可能无法容纳大型查询结果或插入语句，导致数据丢失或截断。另一方面，如果数据包太大，可能会浪费过多的内存或网络资源。因此，MySQL 设置了 “max_allowed_packet” 参数来限制数据包的大小。（单位字节）
max_allowed_packet=500M

innodb_flush_log_at_trx_commit作用
当innodb_flush_log_at_trx_commit=0时, log buffer将每秒一次地写入log file, 并且log file的flush(刷新到disk)操作同时进行. 此时, 事务提交是不会主动触发写入磁盘的操作.
当innodb_flush_log_at_trx_commit=1时(默认), 每次事务提交时, MySQL会把log buffer的数据写入log file, 并且将log file flush(刷新到disk)中去.
当innodb_flush_log_at_trx_commit=2时, 每次事务提交时, MySQL会把log buffer的数据写入log file, 但不会主动触发flush(刷新到disk)操作同时进行. 然而, MySQL会每秒执行一次flush(刷新到disk)操作.
然而, 每秒flush并不能确保100%每秒发生, 因为os调度问题.
默认的1可以获得更好地数据安全, 但性能会打折扣. 不过非1时, 在遇到crash可能会丢失1秒的事务; 设置为0时, 任何mysqld进程crash会丢失上1秒的事务; 设置为2时, 任何os crash或者机器掉电会丢失上1秒的事务; InnoDB的crash recovery运行时会忽略这些数据.
将 innodb_flush_log_at_trx_commit 配置设定为0；按过往经验设定为0，插入速度会有很大提高。
innodb_flush_log_at_trx_commit = 0

innodb_autoextend_increment的作用
此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。
将 innodb_autoextend_increment 配置由于默认8M 调整到 128M
innodb_autoexted_increment=128M

innodb_log_buffer_size的作用
此配置项作用设定innodb 数据库引擎写日志缓存区；将此缓存段增大可以减少数据库写数据文件次数。
将 innodb_log_buffer_size 配置由于默认1M 调整到 16M
innodb_log_buffer_size=16M

innodb_log_file_size的作用
此配置项作用设定innodb 数据库引擎UNDO日志的大小；从而减少数据库checkpoint操作。
将 innodb_log_file_size 配置由于默认 8M 调整到 128M
innodb_log_file_size=128M

innodb_buffer_pool_size的作用
Innodb缓冲池缓存行的数据、自适应索引、插入缓冲、锁、索引页、数据字典以及其他的一些内部数据结构，所以对于Inndo来说是严重的依赖于缓冲池的，当mysql启动的时候，Innodb引擎会想操作系统申请一块连续的内存空间，然后按照页的大小（默认16kb）来划分出一个个空白页，当磁盘上的页缓存到内存的buffer pool 中会对空页进行填充。

经过以上调整，系统插入速度由于原来10分钟几万条提升至1秒1W左右；注：以上参数调整，需要根据不同机器来进行实际调整。特别是 innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要谨慎调整；因为涉及MySQL本身的容灾处理。

最后的参数变化
max_allowed_packet = 128M
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 16M
innodb_autoextend_increment = 128M
innodb_log_file_size = 128M
innodb_buffer_pool_size = 4096M

#参数查看
show variables like '%innodb_flush_log_at_trx_commit%';
#参数数据修改
set global innodb_flush_log_at_trx_commit = 0;