mysql 数据备份

数据导出

mysql 数据备份

数据备份

mysql 数据备份

物理备份

mysql 数据备份

逻辑备份

mysql 数据备份

备份MySQL服务器上的所有库

将所有的库备份为mysql-all.sql文件：

[root@dbsvr1 ~]# mysqldump -u root -p --all-databases > /root/alldb.sql
Enter password: //验证口令
[root@dbsvr1 mysql]# file /root/alldb.sql //确认备份文件类型
/root/alldb.sql: UTF-8 Unicode English text, with very long lines
查看备份文件alldb.sql的部分内容：
1. [root@dbsvr1 ~]# grep -vE '^/|^-|^$' /root/alldb.sql | head -15
2. CREATE DATABASE /*!32312 IF NOT EXISTS*/ `home` /*!40100 DEFAULT CHARACTER SET latin1 */;
3. USE `home`;
4. DROP TABLE IF EXISTS `biao01`;
5. CREATE TABLE `biao01` (
6. `id` int(2) NOT NULL,
7. `name` varchar(8) DEFAULT NULL
8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
9. LOCK TABLES `biao01` WRITE;
10. UNLOCK TABLES;
11. DROP TABLE IF EXISTS `biao02`;
12. CREATE TABLE `biao02` (
13. `id` int(4) NOT NULL,
14. `name` varchar(8) DEFAULT NULL,
15. PRIMARY KEY (`id`)
16. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
17. .. ..
注意：若数据库都使用MyISAM存储引擎，可以采用冷备份的方式，直接复制对应的数据库目录即可；恢复时重新复制回来就行。

2）只备份指定的某一个库

将userdb库备份为userdb.sql文件：
1. [root@dbsvr1 ~]# mysqldump -u root -p userdb > userdb.sql
2. Enter password: //验证口令
查看备份文件userdb.sql的部分内容：
1. [root@dbsvr1 ~]# grep -vE '^/|^-|^$' /root/userdb.sql
2. DROP TABLE IF EXISTS `stu_info`;
3. CREATE TABLE `stu_info` (
4. `name` varchar(12) NOT NULL,
5. `gender` enum('boy','girl') DEFAULT 'boy',
6. `age` int(3) NOT NULL
7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
8. LOCK TABLES `stu_info` WRITE;
9. .. ..
3）同时备份指定的多个库

同时备份mysql、userdb库，保存为mysql+userdb.sql文件：
1. [root@dbsvr1 ~]# mysqldump -u root -p -B mysql userdb > mysql+test+userdb.sql
2. Enter password: //验证口令
查看备份文件userdb.sql的部分内容：
1. [root@dbsvr1 ~]# grep '^CREATE DATA' /root/mysql+userdb.sql
2. CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
3. CREATE DATABASE /*!32312 IF NOT EXISTS*/ `userdb` /*!40100 DEFAULT CHARACTER SET latin1 */;
步骤二：使用mysql 命令恢复删除的数据

以恢复userdb库为例，可参考下列操作。通常不建议直接覆盖旧库，而是采用建立新库并导入逻辑备份的方式执行恢复，待新库正常后即可废弃或删除旧库。

1）创建名为userdb2的新库
1. mysql> CREATE DATABASE userdb2;
2. Query OK, 1 row affected (0.00 sec)
2）导入备份文件，在新库中重建表及数据
1. [root@dbsvr1 ~]# mysql -u root -p userdb2 < /root/userdb.sql
2. Enter password: //验证口令
3）确认新库正常，启用新库
1. mysql> USE userdb2; //切换到新库
2. Reading table information for completion of table and column names
3. You can turn off this feature to get a quicker startup with -A
5. Database changed
6. mysql> SELECT sn,username,uid,gid,homedir //查询数据，确认可用
7. -> FROM userlist LIMIT 10;
8. +----+----------+-----+-----+-----------------+
9. | sn | username | uid | gid | homedir |
10. +----+----------+-----+-----+-----------------+
11. | 1 | root | 0 | 0 | /root |
12. | 2 | bin | 1 | 1 | /bin |
13. | 3 | daemon | 2 | 2 | /sbin |
14. | 4 | adm | 3 | 4 | /var/adm |
15. | 5 | lp | 4 | 7 | /var/spool/lpd |
16. | 6 | sync | 5 | 0 | /sbin |
17. | 7 | shutdown | 6 | 0 | /sbin |
18. | 8 | halt | 7 | 0 | /sbin |
19. | 9 | mail | 8 | 12 | /var/spool/mail |
20. | 10 | operator | 11 | 0 | /root |
21. +----+----------+-----+-----+-----------------+
22. 10 rows in set (0.00 sec)
4）废弃或删除旧库
1. mysql> DROP DATABASE userdb;
2. Query OK, 2 rows affected (0.09 sec)
4 案例4：binlog日志

4.1 问题

启用binlog日志，具体要求如下：
- 启用binlog日志，把日志文件存放到系统的/mylog目录下，日志文件为db50
- 手动创建3个新的日志文件
- 删除编号3之前的日志文件
4.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：启用binlog日志

1）修改配置文件，并重启服务。
1. [root@dbsvr1 ~]# vim /etc/my.cnf
2. [mysqld]
3. server_id=1 //指定server_id
4. log-bin=/mylog/db50 //指定日志目录及名称
5. :wq
6. [root@dbsvr1 ~]# mkdir /mylog //创建目录
7. [root@dbsvr1 ~]# chmod mysql /mylog //修改所有者
8. [root@dbsvr1 ~]# systemctl restart mysqld.service //重启服务
2）查看日志信息
1. [root@dbsvr1 ~]#
2. [root@localhost ~]# mysql -uroot -p123qqq...A //管理员登录
3. mysql: [Warning] Using a password on the command line interface can be insecure.
4. Welcome to the MySQL monitor. Commands end with ; or g.
5. Your MySQL connection id is 3
6. Server version: 5.7.17-log MySQL Community Server (GPL)
8. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
10. Oracle is a registered trademark of Oracle Corporation and/or its
11. affiliates. Other names may be trademarks of their respective
12. owners.
14. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
16. mysql> show master status; //查看日志信息
17. +-------------+----------+--------------+------------------+-------------------+
18. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
19. +-------------+----------+--------------+------------------+-------------------+
20. | db50.000001 | 154 | | | |
21. +-------------+----------+--------------+------------------+-------------------+
22. 1 row in set (0.00 sec)
24. mysql>
3）手动创建3个新的日志文件
1. mysql>
2. mysql> flush logs; //刷新日志
3. Query OK, 0 rows affected (0.14 sec)
5. mysql> flush logs; //刷新日志
6. Query OK, 0 rows affected (0.11 sec)
8. mysql> flush logs; //刷新日志
9. Query OK, 0 rows affected (0.12 sec)
11. mysql> system ls /mylog/ //查看日志文件
12. db50.000001 db50.000002 db50.000003 db50.000004 db50.index
13. mysql>
14. mysql> show master status; //查看日志信息
15. +-------------+----------+--------------+------------------+-------------------+
16. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
17. +-------------+----------+--------------+------------------+-------------------+
18. | db50.000004 | 154 | | | |
19. +-------------+----------+--------------+------------------+-------------------+
20. 1 row in set (0.00 sec)
22. mysql>
4）删除编号3之前的日志文件
1. mysql>
3. mysql> purge master logs to "db50.000003"; //删除日志
4. Query OK, 0 rows affected (0.05 sec)
6. mysql> system ls /mylog/ //查看日志文件
7. db50.000003 db50.000004 db50.index
8. mysql>
9. mysql> system cat /mylog/db50.index //查看索引文件
10. /mylog/db50.000003
11. /mylog/db50.000004
12. mysql>

mysql 数据备份

4 案例4：binlog日志

4.1 问题

4.2 步骤

相关推荐