mysql数据库备份与恢复

备份整个数据库

[root@localhost ~]# mysqldump -uroot -p --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
Enter password: 
[root@localhost ~]# ls
all-20230218165245.sql  anaconda-ks.cfg

备份单个表

mysqldump -uroot -p school tb_course > tb_course_backup-$(date '+%Y%m%d%H%M%S').sql
Enter password: 
[root@localhost ~]# ls
all-20230218165245.sql  anaconda-ks.cfg  tb_course_backup-20230218171303.sql

备份单个数据库

[root@localhost ~]# mysqldump -uroot -p --databases school > school_backup-$(date '+%Y%m%d%H%M%S').sql
Enter password: 
[root@localhost ~]# ls
all-20230218165245.sql  school_backup-20230218172339.sql
anaconda-ks.cfg         tb_course_backup-20230218171303.sql

差异备份与恢复

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=1        //设置服务器标识符
log-bin=mysql_bin   //开启二进制日志功能
~     
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost ~]# mysql -root -p0123abcX!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[root@localhost ~]# mysqldump -uroot -p0123abcX! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20230218165245.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll
total 876
-rw-r--r--. 1 root root 885487 Feb 18 17:57 all-20230218165245.sql
-rw-------. 1 root root   1074 Feb 14 04:52 anaconda-ks.cfg
-rw-r--r--. 1 root root      0 Feb 18 17:23 school_backup-20230218172339.sql
-rw-r--r--. 1 root root   1970 Feb 18 17:13 tb_course_backup-20230218171303.sql