备份整个数据库
[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