How to backup and restore database on Linux

How to back up and restore the database on Linux

In the Linux operating system, backing up and restoring the database is a very important task. Whether it is to prevent data loss or to migrate a database, you need to master this skill. This article will introduce how to backup and restore databases on Linux and provide corresponding code examples.

1. Back up the database

1. Use the mysqldump command to back up the MySQL database

MySQL is an open source relational database management system. You can use the mysqldump command to back up the MySQL database. . As shown below:

mysqldump -u <username> -p<password> <database_name> > <backup_file.sql>

Where, d6025a37ea8687b5422f951f7288bdc5 is the username of the database, cb1ebc435675187bdcfb539b370c2e37 is the password of the database, d37a44c6402b970776f6a2c443441393 is the path and file name of the backup file. For example, to back up the database named "mydb", you can execute the following command:

mysqldump -u root -p123456 mydb > /backup/mydb_backup.sql

2. Use the pg_dump command to back up the PostgreSQL database

Similarly, to back up the PostgreSQL database you can use pg_dump command. As shown below:

pg_dump -U <username> -W -Ft <database_name> -f <backup_file.tar>

Where, d6025a37ea8687b5422f951f7288bdc5 is the user name of the database, 6b7267f525327f2a23dcb01791a146aa is the name of the database to be backed up, &lt ;backup_file.tar> is the path and file name of the backup file. For example, to back up the database named "mydb", you can execute the following command:

pg_dump -U postgres -W -Ft mydb -f /backup/mydb_backup.tar

2. Restore the database

1. Restore the MySQL database

To To restore the MySQL database, you can use the following command:

mysql -u <username> -p<password> <database_name> < <backup_file.sql>

Among them, d6025a37ea8687b5422f951f7288bdc5 is the username of the database, cb1ebc435675187bdcfb539b370c2e37 is the password of the database,6b7267f525327f2a23dcb01791a146aa is the name of the database to be restored, 0d961a72f705c85f286562928e09463c is the path and file name of the backup file. For example, to restore the backup file "mydb_backup.sql" to the database named "mydb", you can execute the following command:

mysql -u root -p123456 mydb < /backup/mydb_backup.sql

2. Restore PostgreSQL database

To To restore the PostgreSQL database, you can use the following command:

pg_restore -U <username> -d <database_name> <backup_file.tar>

where, d6025a37ea8687b5422f951f7288bdc5 is the user name of the database, 6b7267f525327f2a23dcb01791a146aa is the name of the database to be restored , 1c92970fa88b6759818996c6c108bcd5 is the path and file name of the backup file. For example, to restore the backup file "mydb_backup.tar" to the database named "mydb", you can execute the following command:

pg_restore -U postgres -d mydb /backup/mydb_backup.tar

3. Regular backup of the database

Regular backup of the database can ensure Data security and integrity. By writing Shell scripts and using crontab scheduled tasks, you can automatically back up the database.

The following is a simple backup script example:

#!/bin/bash

#数据库备份路径
backup_dir="/backup"
#数据库用户名
username="root"
#数据库密码
password="123456"
#需要备份的数据库名称
database_name="mydb"
#备份文件名
backup_file="${backup_dir}/${database_name}_backup_$(date +%Y%m%d%H%M%S).sql"

#执行备份命令
mysqldump -u ${username} -p${password} ${database_name} > ${backup_file}

#删除过期备份（保留最近7天的备份）
find ${backup_dir} -name "${database_name}_backup_*" -type f -mtime +7 -exec rm -f {} ;

Save the above script as backup.sh and add executable permissions.

Next, use crontab to add a scheduled task:

crontab -e

In the opened file, add the following content to indicate that the backup task will be executed at 2 a.m. every day:

0 2 * * * /bin/bash /path/to/backup.sh

Save and Just exit.

Through the above methods, you can easily back up and restore the database on Linux, and perform backup tasks regularly. Ensuring the security and integrity of the database is one of the important measures to ensure that data is not lost.

The above is the detailed content of How to backup and restore database on Linux. For more information, please follow other related articles on the PHP Chinese website!