完整备份与恢复数据库
#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;