Basic backup of MySQL database

Time:2021-8-3

Database is very important. Without backup, you can only run away. So make a backup!
Basic backup of MySQL database
Mysqldump backup
Basic backup of MySQL database
1. Backup command

Format: mysqldump – H host name – P port – U user name – P password – database database name > file name. SQL

For example: mysqldump – H 192.168.1.100 – P 3306 – uroot – ppassword – database CMDB > / data / backup / cmdb.sql

2. Backup compression

The exported data may be large and difficult to back up to the remote. At this time, it needs to be compressed

Format: mysqldump – H host name – P port – U user name – P password – database database name | gzip > file name. Sql.gz

For example: mysqldump – h192.168.1.100 – P 3306 – uroot – ppassword – database CMDB | gzip > / data / backup / cmdb.sql.gz

3. Backing up multiple tables in the same library

Format: mysqldump – H host name – P port – U user name – P password – database database name table 1 Table 2… > file name. SQL

For example, mysqldump – h192.168.1.100 – p3306 – uroot – ppassword CMDB T1 T2 > / data / backup / CMDB_ t1_ t2.sql

4. Backing up multiple libraries at the same time

Format: mysqldump – H hostname – P port – U username – P password – databases database name 1 database name 2 database name 3 > file name. SQL

For example: mysqldump – h192.168.1.100 – uroot – ppassword – databases CMDB BBS blog > / data / backup / mutil_ db.sql

5. Back up all databases on the instance

Format: mysqldump – H hostname – P port – U username – P password – All Databases > file name. SQL

For example: mysqldump – h192.168.1.100 – p3306 – uroot – ppassword – All Databases > / data / backup / all_ db.sql

6. SQL backup for deleting database or table with backup data

Format: mysqldump – H host name – P port – U user name – P password – add drop table – add drop database database database name > file name. SQL

For example: mysqldump – uroot – ppassword – add drop table – add drop database CMDB > / data / backup / all_ db.sql

7. Back up the database structure without backing up data

Format: mysqldump – H hostname – P port – U username – P password – no data database name 1 database name 2 database name 3 > file name. SQL

For example: mysqldump – no data – databases db1 DB2 CMDB > / data / backup / structure.sql
Basic backup of MySQL database

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Royal programming editor teaches you to learn more programming knowledge