mysql的备份

mysql
- 字
2024-06-24

一、dockerdump※

#备份单个
mysqldump -u [username] -p[password] [database_name] > [backup_file].sql
mysql -u [username] -p[password] [database_name] < [backup_file].sql

#备份多个（如果备份文件中包含了多个数据库，例如使用 --databases 或 --all-databases 选项备份的，恢复时不需要指定数据库名：）
mysqldump -u [username] -p[password] --databases [database1] [database2] > [backup_file].sql
mysql -u [username] -p[password] < [backup_file].sql


#备份所有

mysqldump -u [username] -p[password] --all-databases > [backup_file].sql

#只备份结构
mysqldump -u [username] -p[password] --no-data [database_name] > [backup_file].sql

##在执行恢复操作之前，确保目标数据库已经存在。如果不存在，可以先创建数据库：


mysqldump -u root -p123 --databases hr >> hr.sql 备一个库
mysql -u root < hr.sql  恢复
mysqldump -u root -p123 --databases hr db > hr.db.sql备多个库
mysql -u root <hr.db.sql
mysqldump -u root -p123 hr emp > emp.sql 备份表
mysql -u root -p123 hr < emp.sql 恢复
mysqldump -u root -p123 db emp dpt> /backup/emp.sql  多表备份

表的导入导出※

• 当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
• 当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
• 当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

/etc/my.cnf
secure_file_priv =

lvm的快照(mysql备份)※

1.将lv挂载mysql数据目录data( 注意: 自动挂载 /etc/fstab) 得使用ext4文件系统

a. 先停止数据库;mv /usr/local/mysql/data /tmp
b. 创建lv 并且挂载到 /usr/local/mysql/data
c. mv /tmp/data/* /usr/local/mysql/data
b. chown -R mysql.mysql /usr/local/mysql
e. 启动数据库

2.保证数据完整(全备 完全恢复)

3.锁库

mysql> FLUSH TABLES WITH READ LOCK;

4.创建快照

 lvcreate -L 300M -s -n /dev/vgmysql/lvmysql-snampshot /dev/vgmysql/lvmysql

5.解锁

mysql> unlock tables;

6.备份(tar)

mount /dev/vgmysql/lvmysql-snampshot /opt
tar -cvf /backup/mysql.bak.tar /opt/*

7.删除快照

umount /opt/
lvremove /dev/vgmysql/lvmysql-snampshot

恢复

1.确定mysql进程结束

[root@robin backup]# killall -9 mysqld

2.解压恢复

[root@robin backup]# tar -xvf /backup/mysql.bak.tar -C /usr/local/mysql/data/

问题:

1.锁表(时间不好估算)

2.快照大小

3.使用源lv时快照使用率会上升(服务器最空闲时)

echo " FLUSH TABLES WITH READ LOCK; system lvcreate -L 300M -s -n /dev/vgmysql/lvmysql-snampshot /dev/vgmysql/lvmysql ; unlock tables" | mysql

二进制日志的使用log-bin※

二进制日志的使用log-bin

binlog日志是把数据库的每一个变化都记载到一个专用的文件里,这种文件叫日志文件,mysql默认只打开错误日志文件,因为过多的日志会影响系统的处理性能

启用方法

方法1.

/usr/local/mysql/bin/mysqld_safe --log-bin --user=mysql&

方法2.

vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/var/lib/mysql
port=3306
socket=/tmp/mysql.sock

server-id=1

# 启用错误日志
log-error=/var/log/mysql/error.log

# 启用二进制日志
log-bin=/var/lib/mysql/mysql-bin
log-bin-index=/var/lib/mysql/mysql-bin.index
binlog_format=row

# 设置二进制日志过期时间为7天（7 * 24 * 60 * 60秒）
binlog_expire_logs_seconds=604800

max_binlog_size=100M

查询日志状态

mysql中
mysql> show binlog events\G
mysql中显示语句
mysql> set binlog_rows_query_log_events=1;
系统中
[root@localhost data]# mysqlbinlog -v /usr/local/mysql/data/mysql.000001
-v 显示具体语句信息

在 MySQL 8.0 及以上版本中，expire_logs_days 参数已被弃用，并被 binlog_expire_logs_seconds 替代。你可以使用这个新参数来设置二进制日志的过期时间，以秒为单位。

主从复制

A数据库

root@debian:~# cat /etc/mysql/my.cnf
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mysqld]
bind-address=0.0.0.0
server-id=1
log-bin=/var/lib/mysql/binlog/msl
log-bin-index=/var/lib/mysql/binlog/msl
log_error = /var/log/mysql/error.log

授权slave：

grant replication slave on *.* to slave@'10.0.0.22' identified by 'admin';

全备份：

 mysqldump -uroot -padmin --all-databases >> /root/all.sql
 mysql -u root -padmin < /root/all.sql

将备份文件scp给另一个数据库恢复，以保证两个服务器初始相同

遇到的问题：

  1. 环境 debian mariadb
  2. 修改my.cnf之后无法重启---强行pkill再启动
  3. 另一个服务器无法登录----mycnf没有配置远端登录

 

目录