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, <username> is the username of the database, <password> is the password of the database, <database_name&gt ; is the name of the database to be backed up, <backup_file.sql> 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, <username> is the user name of the database, <database_name> 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, <username> is the username of the database, <password> is the password of the database,<database_name> is the name of the database to be restored, <backup_file.sql> 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, <username> is the user name of the database, <database_name> is the name of the database to be restored , <backup_file.tar> 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!