MariaDB&MySQL数据库的增删改查及备份和恢复
1、创建数据库
1 | $ sudo mysql -uroot //进入数据库 |
2、创建及删除表
1 | MariaDB [(none)]> use study_test; |
删除表命令：1
MariaDB [study_test]> DROP TABLE study_test;
3、修改表名
1 | ALTER TABLE study_test RENAME study_table; |
4、修改字段
（1）删除字段
1 | MariaDB [study_test]> ALTER TABLE study_table DROP submit_date; |
（2）添加字段
1 | MariaDB [study_test]> ALTER TABLE study_table ADD submit_time timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'; |
又比如给表files新增创建时间字段:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37-- 表内数据
MariaDB [development]> select * from files;
+----+--------------+------------------------+------+---------+-------+---------------------+
| id | filename | path | mark | scale | info | updateTime |
+----+--------------+------------------------+------+---------+-------+---------------------+
| 1 | 1-a.jpg | ./uploads/1-a.jpg | 1 | 800*600 | HGMM | 2022-11-03 13:35:53 |
| 2 | blue2red.jpg | ./uploads/blue2red.jpg | 0 | 400*300 | HGMM2 | 2022-11-03 15:01:58 |
+----+--------------+------------------------+------+---------+-------+---------------------+
2 rows in set (0.495 sec)
-- 查询表结构
MariaDB [development]> DESC files;
+------------+------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------------------+-------------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| filename | varchar(30) | YES | | NULL | |
| path | varchar(255) | YES | | NULL | |
| mark | tinyint(3) | YES | | NULL | |
| scale | varchar(255) | YES | | NULL | |
| info | varchar(255) | YES | | NULL | |
| updateTime | timestamp | YES | | current_timestamp() | on update current_timestamp() |
+------------+------------------+------+-----+---------------------+-------------------------------+
7 rows in set (0.186 sec)
-- 新增 createTime 字段
MariaDB [development]> ALTER TABLE files ADD creatTime timestamp NULL DEFAULT CURRENT_TIMESTAMP;
Query OK, 2 rows affected (0.510 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- 表内新数据
MariaDB [development]> select * from files;
+----+--------------+------------------------+------+---------+-------+---------------------+---------------------+
| id | filename | path | mark | scale | info | updateTime | creatTime |
+----+--------------+------------------------+------+---------+-------+---------------------+---------------------+
| 1 | 1-a.jpg | ./uploads/1-a.jpg | 1 | 800*600 | HGMM | 2022-11-03 13:35:53 | 2022-11-05 13:24:58 |
| 2 | blue2red.jpg | ./uploads/blue2red.jpg | 0 | 400*300 | HGMM2 | 2022-11-03 15:01:58 | 2022-11-05 13:24:58 |
+----+--------------+------------------------+------+---------+-------+---------------------+---------------------+
2 rows in set (0.022 sec)
注意
注意更新时间字段和创建时间字段区别：1
2
3
4
5
6
7
8
9
10
11
12
13
14MariaDB [development]> DESC files;
+------------+------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------------------+-------------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| filename | varchar(30) | YES | | NULL | |
| path | varchar(255) | YES | | NULL | |
| mark | tinyint(3) | YES | | NULL | |
| scale | varchar(255) | YES | | NULL | |
| info | varchar(255) | YES | | NULL | |
| updateTime | timestamp | YES | | current_timestamp() | on update current_timestamp() |
| creatTime | timestamp | YES | | current_timestamp() | |
+------------+------------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.152 sec)
（2）修改字段
①修改字段名
语法如下：1
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；
比如改表study_table的结构，将 test_discribe 字段名称改为 test_disc，同时将数据类型变为CHAR(30)：1
2
3
4
5
6
7
8
9
10
11
12MariaDB [study_test]> ALTER TABLE study_table CHANGE test_discribe test_disc CHAR(30);
+---------+-----------+---------------+-------------------+
| test_id | test_name | test_disc | submit_time |
+---------+-----------+---------------+-------------------+
| 1 | ONE | 第一条 | 2021-08-17 12:21:41 |
| 2 | TWO | 第二条 | 2021-08-17 12:21:41 |
| 3 | THREE | 第三条 | 2021-08-17 12:21:41 |
| 4 | FOUR | 第四条 | 2021-08-17 12:21:41 |
| 5 | FIVE | 第五条 | 2021-08-17 12:21:41 |
| 6 | SIX | 第六条 | 2021-08-17 12:21:41 |
+---------+-----------+---------------+-------------------+
6 rows in set (0.001 sec)
②修改字段数据类型
语法如下：1
2ALTER TABLE <表名> MODIFY <字段名> <数据类型>;
-- ALTER TABLE files MODIFY updateTime timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 修改更新时间类型
比如修改表files的结构，将 filename 字段数据类型称改为变为VARCHAR(30)：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34-- 原来数据结构
MariaDB [development]> DESC files;
+------------+------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------------------+-------------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| filename | varchar(255) | YES | | NULL | |
| path | varchar(255) | YES | | NULL | |
| mark | tinyint(3) | YES | | NULL | |
| scale | varchar(255) | YES | | NULL | |
| info | varchar(255) | YES | | NULL | |
| updateTime | timestamp | YES | | current_timestamp() | on update current_timestamp() |
+------------+------------------+------+-----+---------------------+-------------------------------+
7 rows in set (0.017 sec)
-- 变更数据类型
MariaDB [development]> ALTER TABLE files MODIFY filename VARCHAR(30);
Query OK, 1 row affected (0.235 sec)
Records: 1 Duplicates: 0 Warnings: 0
-- 变更后：
MariaDB [development]> DESC files;
+------------+------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------------------+-------------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| filename | varchar(30) | YES | | NULL | |
| path | varchar(255) | YES | | NULL | |
| mark | tinyint(3) | YES | | NULL | |
| scale | varchar(255) | YES | | NULL | |
| info | varchar(255) | YES | | NULL | |
| updateTime | timestamp | YES | | current_timestamp() | on update current_timestamp() |
+------------+------------------+------+-----+---------------------+-------------------------------+
7 rows in set (0.013 sec)
5、插入数据
（1）插入单条
1 | MariaDB [study_test]> INSERT INTO study_table (test_name, test_discribe) VALUES ("ONE", "第一条"); |
（2）插入多条
1 | MariaDB [study_test]> INSERT INTO study_table (test_name, test_discribe) VALUES ("TWO", "第二条"), ("THREE", "第三条"); |
6、修改数据
1 | MariaDB [study_test]> UPDATE study_table SET test_discribe="第三条" WHERE test_id=3; //删除“第三条”前面的 空格 |
7、删除数据
1 | MariaDB [study_test]> DELETE FROM study_table WHERE test_id=6; //删除第6条 |
8、查询数据
（1）一般查询
1 | MariaDB [study_test]> SELECT test_id, test_name FROM study_table WHERE test_discribe="第六条"; |
使用LOCATE(检索条件,字段)查询（以下实例对应study_test数据库下usr_score_table表）：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34# 表内数据如下：
MariaDB [study_test]> SELECT * FROM usr_score_table ORDER BY usr_id;
+--------+---------------------+---------+---------+
| usr_id | submit_time | y_score | s_score |
+--------+---------------------+---------+---------+
| 1 | 2021-11-17 11:06:43 | 68 | 66 |
| 1 | 2021-11-17 12:03:22 | 76 | 78 |
| 1 | 2021-11-17 12:03:22 | 72 | 71 |
| 2 | 2021-11-17 12:03:22 | 68 | 73 |
| 2 | 2021-11-17 11:06:43 | 86 | 88 |
| 3 | 2021-11-17 11:17:46 | 68 | 66 |
| 3 | 2021-11-17 12:03:22 | 77 | 80 |
| 4 | 2021-11-17 11:06:43 | 86 | 88 |
| 5 | 2021-11-17 11:17:46 | 68 | 66 |
| 5 | 2021-11-17 12:03:22 | 80 | 82 |
| 6 | 2021-11-17 11:06:43 | 86 | 88 |
| 6 | 2021-11-17 12:03:22 | 88 | 82 |
+--------+---------------------+---------+---------+
12 rows in set (0.372 sec)
MariaDB [study_test]> SELECT * FROM usr_score_table WHERE LOCATE('6',y_score) ORDER BY usr_id;
+--------+---------------------+---------+---------+
| usr_id | submit_time | y_score | s_score |
+--------+---------------------+---------+---------+
| 1 | 2021-11-17 11:06:43 | 68 | 66 |
| 1 | 2021-11-17 12:03:22 | 76 | 78 |
| 2 | 2021-11-17 11:06:43 | 86 | 88 |
| 2 | 2021-11-17 12:03:22 | 68 | 73 |
| 3 | 2021-11-17 11:17:46 | 68 | 66 |
| 4 | 2021-11-17 11:06:43 | 86 | 88 |
| 5 | 2021-11-17 11:17:46 | 68 | 66 |
| 6 | 2021-11-17 11:06:43 | 86 | 88 |
+--------+---------------------+---------+---------+
8 rows in set (0.001 sec)
使用INSTR(字段,检索条件)查询：1
2
3
4
5
6
7
8
9
10
11
12
13
14MariaDB [study_test]> SELECT * FROM usr_score_table WHERE INSTR(y_score,'6') ORDER BY usr_id;
+--------+---------------------+---------+---------+
| usr_id | submit_time | y_score | s_score |
+--------+---------------------+---------+---------+
| 1 | 2021-11-17 11:06:43 | 68 | 66 |
| 1 | 2021-11-17 12:03:22 | 76 | 78 |
| 2 | 2021-11-17 11:06:43 | 86 | 88 |
| 2 | 2021-11-17 12:03:22 | 68 | 73 |
| 3 | 2021-11-17 11:17:46 | 68 | 66 |
| 4 | 2021-11-17 11:06:43 | 86 | 88 |
| 5 | 2021-11-17 11:17:46 | 68 | 66 |
| 6 | 2021-11-17 11:06:43 | 86 | 88 |
+--------+---------------------+---------+---------+
8 rows in set (0.008 sec)
此外还可以用POSITION(检索条件 IN 字段)及REGEXP 检索条件查询：1
2
3
4
5
6
7
8
9
10
11
12
13
14MariaDB [study_test]> SELECT * FROM usr_score_table WHERE y_score REGEXP '6' ORDER BY usr_id;
+--------+---------------------+---------+---------+
| usr_id | submit_time | y_score | s_score |
+--------+---------------------+---------+---------+
| 1 | 2021-11-17 11:06:43 | 68 | 66 |
| 1 | 2021-11-17 12:03:22 | 76 | 78 |
| 2 | 2021-11-17 11:06:43 | 86 | 88 |
| 2 | 2021-11-17 12:03:22 | 68 | 73 |
| 3 | 2021-11-17 11:17:46 | 68 | 66 |
| 4 | 2021-11-17 11:06:43 | 86 | 88 |
| 5 | 2021-11-17 11:17:46 | 68 | 66 |
| 6 | 2021-11-17 11:06:43 | 86 | 88 |
+--------+---------------------+---------+---------+
8 rows in set (0.136 sec)
（2）通配符%查询
%表示任何字符出现任意次数;1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31MariaDB [study_test]> SELECT * FROM study_table WHERE test_name LIKE 'F%';
+---------+-----------+---------------+---------------------+
| test_id | test_name | test_discribe | submit_time |
+---------+-----------+---------------+---------------------+
| 4 | FOUR | 第四条 | 2021-08-17 12:21:41 |
| 5 | FIVE | 第五条 | 2021-08-17 12:21:41 |
+---------+-----------+---------------+---------------------+
2 rows in set (0.000 sec)
MariaDB [study_test]> SELECT * FROM study_table WHERE test_name LIKE 'T%';
+---------+-----------+---------------+---------------------+
| test_id | test_name | test_discribe | submit_time |
+---------+-----------+---------------+---------------------+
| 2 | TWO | 第二条 | 2021-08-17 12:21:41 |
| 3 | THREE | 第三条 | 2021-08-17 12:21:41 |
+---------+-----------+---------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [study_test]> SELECT * FROM study_table WHERE test_discribe LIKE '%条%';
+---------+-----------+---------------+---------------------+
| test_id | test_name | test_discribe | submit_time |
+---------+-----------+---------------+---------------------+
| 1 | ONE | 第一条 | 2021-08-17 12:21:41 |
| 2 | TWO | 第二条 | 2021-08-17 12:21:41 |
| 3 | THREE | 第三条 | 2021-08-17 12:21:41 |
| 4 | FOUR | 第四条 | 2021-08-17 12:21:41 |
| 5 | FIVE | 第五条 | 2021-08-17 12:21:41 |
| 6 | SIX | 第六条 | 2021-08-17 12:34:07 |
+---------+-----------+---------------+---------------------+
6 rows in set (0.000 sec)
其中对int字段的可以先进行转换为CHAR再查询（以下实例对应study_test数据库下usr_score_table表），如：1
2
3
4
5
6
7
8
9
10MariaDB [study_test]> SELECT * FROM usr_score_table WHERE CAST(y_score AS CHAR) LIKE '6%' ORDER BY usr_id;
+--------+---------------------+---------+---------+
| usr_id | submit_time | y_score | s_score |
+--------+---------------------+---------+---------+
| 1 | 2021-11-17 11:06:43 | 68 | 66 |
| 2 | 2021-11-17 12:03:22 | 68 | 73 |
| 3 | 2021-11-17 11:17:46 | 68 | 66 |
| 5 | 2021-11-17 11:17:46 | 68 | 66 |
+--------+---------------------+---------+---------+
4 rows in set (0.000 sec)
（3）通配符_查询
_只匹配单个字符1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18MariaDB [study_test]> SELECT * FROM study_table WHERE test_name LIKE 'T__';
+---------+-----------+---------------+---------------------+
| test_id | test_name | test_discribe | submit_time |
+---------+-----------+---------------+---------------------+
| 2 | TWO | 第二条 | 2021-08-17 12:21:41 |
+---------+-----------+---------------+---------------------+
1 row in set (0.000 sec)
MariaDB [study_test]> SELECT * FROM study_table WHERE test_name LIKE 'T___';
Empty set (0.000 sec)
MariaDB [study_test]> SELECT * FROM study_table WHERE test_name LIKE 'T____';
+---------+-----------+---------------+---------------------+
| test_id | test_name | test_discribe | submit_time |
+---------+-----------+---------------+---------------------+
| 3 | THREE | 第三条 | 2021-08-17 12:21:41 |
+---------+-----------+---------------+---------------------+
1 row in set (0.001 sec)
9、备份与恢复数据库;
（1）利用mysqldump备份与恢复命令：
①在Linux下备份和恢复
1 | $ sudo mysqldump -uroot -p wpDB > ~/mysql-wpDB.dump //备份数据库名及保存路径 |
还可以创建定时备份和定量删除脚本
1、创建backup.sh脚本1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
username=user # 数据库用户名
password=pass # 数据库用户密码
database=dbname # 要备份的数据库名称
backup_dir=~/backups/ # 配置备份文件路径
date=$(date +"%Y-%m-%d") # 设置备份文件的日期格式
max_backups=30 # 设置要保存的时间长度
# 利用mysqldump操作备份
mysqldump -u $username -p$password $database > "$backup_dir$database-$date.sql"
# 删除过期的备份
cd $backup_dir
find . -type f -name "*.sql" -mtime +$max_backups -exec rm {} \;
# -mmin n 对文件数据的最近一次修改是在 n 分钟之前。
# -mtime n 对文件数据的最近一次修改是在 n*24 小时之前。
若要备份多个数据库，参考脚本语句如下：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24DB_USER="root" # 定义数据库用户名和密码
DB_PASS="your_password" # 定义数据库密码
DB_NAMES=("db1" "db2" "db3") # 定义要备份的数据库名
BACKUP_TIME=$(date +"%Y-%m-%d_%H-%M-%S") # 设置备份文件的日期格式
BACKUP_PATH="/backup/mariadb" # 配置备份文件路径
BACKUP_FILE="mariadb_$BACKUP_TIME.tar.gz"
max_backups=30 # 设置要保存的时间长度
# 判断备份文件夹是否存在，不存在就创建
if [ ! -d "$BACKUP_PATH" ];
then
mkdir - p $BACKUP_PATH
fi
# 开始备份数据库
for db in "${DB_NAMES[@]}";
do
# 导出数据库
mysqldump -u $DB_USER -p$DB_PASS $db > "$BACKUP_PATH/$db.sql"
# 将数据库文件打包压缩
tar -rvf "$BACKUP_PATH/$BACKUP_FILE" "$BACKUP_PATH/$db.sql"
# 删除导出的数据库文件
rm -f "$BACKUP_PATH/$db.sql"
done
删除30天前的备份文件
find $BACKUP_PATH -mtime +$max_backups -name "*.tar.gz" -exec rm {} \;
2、然后，使用chmod 命令给该脚本添加执行权限：1
chmod +x backup.sh
3、最后，使用cron来设置定时任务来定期执行该脚本。
例如，要每天凌晨 1 点执行该脚本,并记录操作日志，可以在 crontab 文件中添加以下内容：1
crontab -e
然后在最后添加如下内容：1
0 1 * * * /path/to/backup.sh >> ~/backups/backup.log 2>&1 &
此外还可以执行开机运行脚本执行备份操作，具体如下：
创建/home/用户名/.config/autostart/start_backup.desktop文件，输入以下内容:1
2
3
4
5
6
7
8
9
10
11
12[Desktop Entry]
Encoding=UTF-8
Version=0.9.4
Type=Application
Name=start_backup
Comment=数据库开机备份
Exec=/home/用户名/文档/backups/start_backup.sh # 用户名及脚本路径根据实际情况填写
OnlyShowIn=XFCE;
RunHook=0
StartupNotify=false
Terminal=false
Hidden=false
②windows10下备份
运行cmd模式
输入命令：1
2mysqldump -u root -P3307 -p wpDBtest > E:\数据备份\Linux\mysql-wpDB.dump
yourpassword:******
其中-P3307如果没改数据库端口可不输入。
运行Powershell模式
输入命令：1
mysqldump -uroot -P3307 -p yourpassword --default-character-set=gbk wpDBtest > E:\数据备份\Linux\mysql-wpDB.dump
其中--default-character-set=gbk解决备份后中文数据可能会出现的乱码，默认utf8。
③windows10下恢复
运行cmd管理员模式
输入命令：1
mysql -u root -P3307 -p yourpassword --default-character-set=utf8 wpDBtest < E:\数据备份\Linux\mysql-wpDB.dump
运行Powershell管理员模式
输入命令：1
Get-Content E:\数据备份\Linux\mysql-wpDB.dump | .\mysql -uroot -P3307 -p yourpassword --default-character-set=utf8 wpDBtest
问题：Powershell下命令出现错误如下：ERROR at line 224: Unknown command '\''.
提示此错误--default-character-set=utf8也无法解决
④在Windows<==>Linux互相备份恢复时可能遇到的编码问题解决：
1> 可尝试用iconv -c -f UTF-8 -t UTF-16LE win10-study_db_UTF8.dump > win10-study_db_UTF-16LE.dump进行转码【此命令为UTF-8到UTF-16LE】
2> Debian系统可用iconv -f UTF-16LE -t UTF-8 win10-study_db_UTF-16LE.dump > win10-study_db_UTF8.dump进行转码
3> ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'错误解决：
• 把文件中的所有的utf8mb4_0900_ai_ci替换为utf8_general_ci。
• 以及utf8mb4替换为utf8。
⑤注意事项
上诉备份方案存在一定的弊端如：用户正在执行数据操作时，数据则会丢失等情况。
解决方案：在备份是给库表加上锁，保证数据的完整性。--lock-all-tables:请求锁定所有表
实例：mysqldump --databases wpDBtest --lock-all-tables >/tmp/testdb.sql--single-transaction:单事务，能够对innodb存储引擎做热备--events:备份事件调度器代码--routines:备份存储过程和存储函数--triggers:备份触发器
备份时请求锁之后滚动日志：--flush-logs
复制时同步位置标记：--master-data=[0|1|2]
注意：恢复时需关闭二进制文件，恢复成功后则开启
关闭二进制日志：set session sql_log_bin=0
开启二进制日志：set session sql_log_bin=1
（2）锁表备份步骤：
1)锁表并且给只读权限flush tables with read lock;
2)滚动日志flush logs;
3)查看二进制日志信息show master status;
4)使用mysqldump进行数据备份mysqldump --databases wpDBtest >/tmp/wpDBtest.sql
5)备份完成之后则需解锁unlock tables;
命令行备份实例如下：1
2
3
4
5
6
7
8
9mysqldump -p --databases wpDBtest --lock-all-tables --flush-logs >/tmp/testdb2.sql
mysqldump -p --databases wpDBtest --single-trasaction --flush-logs >/tmp/testdb3.sql
mysqldump -p --databases wpDBtest --lock-all-tables --flush-logs --master-data=2 >/tmp/testdb4.sql
```
## （3）查看恢复的数据
```bash
$ sudo mysql -uroot //进入数据库`
$ use wpDBtest; //选择查看数据库`
MariaDB [wpDBtest]> select * from wp_options where option_name in("siteurl","home"); # 查看恢复信息
（4）用二进制日志恢复命令：
1》修改my.cnf配置文件,开启binlog
$ sudo vim /etc/alternatives/my.cnf //添加如下内容，开启binlog1
2
3
4
5
6
7[mysqld]
log_bin=mysql-bin
binlog_format=ROW
expire_logs_days=7 #7天滚动
max_binlog_size=100M #每个日志最大100M
binlog_cache_size=4M
max_binlog_cache_size=256M #binlog最大缓存
添加完后重启。
2》查看log_bin是否开启
MariaDB [(none)]> show variables like 'log_bin';
查看二进制log$ sudo mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001;--no-defaults避免出现字符问题错误
默认二进制文件存于/var/lib/mysql/下，文件名为一系列mysql-bin.00000*。
3》测试过程
1、创建测试数据库并新增表和记录1
2
3
4
5
6MariaDB [wpDBtest]> create database testDB;
MariaDB [testDB]> create table testtable (id int null, name varchar(255) null);
MariaDB [(none)]> source ~/wpDBtest-re.sql
MariaDB [testDB]> insert into testtable values(1,"xiaoming");
MariaDB [testDB]> insert into testtable values(2,"wang");
MariaDB [testDB]> insert into testtable values(3,"王");
2、删除一条测试数据1
2
3MariaDB [testDB]> delete from testtable where id =2;`
##MariaDB [testDB]> delete from testtable;
##Query OK, 5 rows affected (0.027 sec)
查询删除后的结果：1
2
3
4
5
6
7
8MariaDB [testDB]> select * from testtable;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
| 3 | 王 |
+------+----------+
2 rows in set (0.001 sec)
3、导出二进制log为sql,--database=testDB 指定导出数据库testDB的数据$ sudo mysqlbinlog --no-defaults --database=testDB /var/lib/mysql/mysql-bin.000001>~/testDB.sql;
4、进入~/文件夹，复制原数据并另存testDB-re.sql$ cp testDB.sql testDB-re.sql$ sudo vim testDB-re.sql //打开sql文件，删除其中“delete from testtable where id =2”语句
5、进入mariadb，恢复数据1
2
3
4
5
6
7
8
9
10
11
12MariaDB [testDB]> source ~/testDB-re.sql
MariaDB [testDB]> select * from testtable;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
| 3 | 王 |
| 1 | xiaoming |
| 2 | wang |
| 3 | 王 |
+------+----------+
5 rows in set (0.001 sec)
mysqldump其他常用命令
①备份多个数据库结构和数据(--databases或者-B)
$ mysqldump -uyk -p --databases study_test study_db > /media/yk/文档1/Backup/Debian11_study_db_study_test_UTF8.dump
②备份所有数据库结构(-d)
$ mysqldump -uyk -p -A-d > /media/yk/文档1/Backup/Debian11_db.dump
③备份所有数据库数据(-t)
$ mysqldump -uyk -p -A-t > /media/yk/文档1/Backup/Debian11_db.dump
恢复XXX.dump见9、备份与恢复数据库
④备份某库中某表,并导出为xml格式( --xml(或-X) 库名 表名 )
$ mysqldump -uyk -p --xml study_test usr_table > /media/yk/文档1/Backup/Debian11_study_test_usr_table.xml
⑤还原单个数据库(需指定数据库)
1 | mysql>use mydb |
或者：1
$ mysql -uroot -p123456 mydb < f:\mydb.sql
⑥还原单个数据库的多个表(需指定数据库)
1 | mysql>use mydb |
或者：1
$ mysql -uroot -p123456 mydb<f:\multables.sql
参考资料：mariadb二进制日志和备份恢复