如何在Linux环境下备份并恢复MySQL数据库到自建的MySQL服务器？

mysqldump -u 用户名 -p 数据库名 > backup.sql; mysql -u 用户名 -p 新数据库

在Linux环境下，使用MySQL数据库时，备份和恢复数据是至关重要的，尤其是在生产环境中，以下是关于如何在Linux系统上进行MySQL数据库备份和恢复的详细步骤。

备份MySQL数据库

1、备份单个数据库：

   mysqldump -u username -p database_name > /path/to/backup/filename.sql

   mysqldump -u root -p test_db > /home/bak/test_db_backup.sql

这条命令将把test_db数据库的所有表结构和数据导出到指定路径下的SQL文件中。

2、备份多个数据库：

   mysqldump -u username -p --databases db1 db2 > /path/to/backup/filename.sql

   mysqldump -u root -p --databases test_db mysql > /home/bak/multiple_db_backup.sql

该命令会将指定的多个数据库导出到一个SQL文件中。

3、全库备份：

   mysqldump -u username -p --all-databases > /path/to/backup/filename.sql

   mysqldump -u root -p --all-databases > /home/bak/all_db_backup.sql

此命令将所有数据库导出到一个SQL文件中。

4、备份单个表：

   mysqldump -u username -p database_name table_name > /path/to/backup/filename.sql

   mysqldump -u root -p test_db users > /home/bak/users_table_backup.sql

5、备份表结构：

   mysqldump -u username -p --no-data database_name > /path/to/backup/structure.sql

   mysqldump -u root -p --no-data test_db > /home/bak/structure_only.sql

6、备份表数据：

   mysqldump -u username -p --no-create-info database_name > /path/to/backup/data.sql

   mysqldump -u root -p --no-create-info test_db > /home/bak/data_only.sql

压缩备份文件

为了节省存储空间，可以将备份文件进行压缩：

mysqldump -u username -p database_name | gzip > /path/to/backup/filename.sql.gz

mysqldump -u root -p test_db | gzip > /home/bak/test_db_backup.sql.gz

恢复MySQL数据库

1、恢复单库备份：

   mysql -u username -p database_name < /path/to/backup/filename.sql

   mysql -u root -p test_db < /home/bak/test_db_backup.sql

2、恢复多库备份：

   mysql -u username -p < /path/to/backup/filename.sql

   mysql -u root -p < /home/bak/multiple_db_backup.sql

3、恢复全库备份：

   mysql -u username -p < /path/to/backup/all_db_backup.sql

   mysql -u root -p < /home/bak/all_db_backup.sql

4、从压缩文件直接恢复：

   gunzip < /path/to/backup/filename.sql.gz | mysql -u username -p database_name

   gunzip < /home/bak/test_db_backup.sql.gz | mysql -u root -p test_db

定时备份脚本示例

创建一个定时任务以每天凌晨1点执行备份：

1、编写备份脚本backupdb.sh：

    #!/bin/bash
    # 定义参数
    DB_HOST="127.0.0.1"
    DB_PORT="3306"
    DB_NAME="test_db"
    DB_USER="root"
    DB_PWD="password"
    BACKUP_PATH="/home/bak"
    DATE=$(date +%Y%m%d)
    BACKUP_FILE="$BACKUP_PATH/$DB_NAME$DATE.sql"
    # 创建备份文件夹（如果不存在）
    mkdir -p $BACKUP_PATH
    mkdir -p $BACKUP_PATH/$DATE
    # 执行备份操作
    mysqldump -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PWD $DB_NAME > $BACKUP_FILE

2、添加执行权限并设置定时任务：

    chmod +x backupdb.sh
    crontab -e

在crontab文件中添加以下行：

    0 1 * * * /path/to/backupdb.sh

保存并退出编辑器，这将每天凌晨1点执行备份脚本。

FAQs

Q1: 如何更改MySQL数据库的默认字符集？

A1: 可以通过修改MySQL配置文件my.cnf或my.ini中的[client]、[mysql]和[mysqld]部分来设置默认字符集。

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

修改后重启MySQL服务使更改生效。

Q2: 如何在MySQL中创建用户并赋予权限？

A2: 可以使用以下命令创建用户并赋予权限：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
FLUSH PRIVILEGES;

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'userpassword';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;