数据导出
数据备份
物理备份
逻辑备份
备份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的部分内容:
- [root@dbsvr1 ~]# grep -vE '^/|^-|^$' /root/alldb.sql | head -15
- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `home` /*!40100 DEFAULT CHARACTER SET latin1 */;
- USE `home`;
- DROP TABLE IF EXISTS `biao01`;
- CREATE TABLE `biao01` (
- `id` int(2) NOT NULL,
- `name` varchar(8) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- LOCK TABLES `biao01` WRITE;
- UNLOCK TABLES;
- DROP TABLE IF EXISTS `biao02`;
- CREATE TABLE `biao02` (
- `id` int(4) NOT NULL,
- `name` varchar(8) DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- .. ..
注意:若数据库都使用MyISAM存储引擎,可以采用冷备份的方式,直接复制对应的数据库目录即可;恢复时重新复制回来就行。
2)只备份指定的某一个库
将userdb库备份为userdb.sql文件:
- [root@dbsvr1 ~]# mysqldump -u root -p userdb > userdb.sql
- Enter password: //验证口令
查看备份文件userdb.sql的部分内容:
- [root@dbsvr1 ~]# grep -vE '^/|^-|^$' /root/userdb.sql
- DROP TABLE IF EXISTS `stu_info`;
- CREATE TABLE `stu_info` (
- `name` varchar(12) NOT NULL,
- `gender` enum('boy','girl') DEFAULT 'boy',
- `age` int(3) NOT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- LOCK TABLES `stu_info` WRITE;
- .. ..
3)同时备份指定的多个库
同时备份mysql、userdb库,保存为mysql+userdb.sql文件:
- [root@dbsvr1 ~]# mysqldump -u root -p -B mysql userdb > mysql+test+userdb.sql
- Enter password: //验证口令
查看备份文件userdb.sql的部分内容:
- [root@dbsvr1 ~]# grep '^CREATE DATA' /root/mysql+userdb.sql
- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `userdb` /*!40100 DEFAULT CHARACTER SET latin1 */;
步骤二:使用mysql 命令恢复删除的数据
以恢复userdb库为例,可参考下列操作。通常不建议直接覆盖旧库,而是采用建立新库并导入逻辑备份的方式执行恢复,待新库正常后即可废弃或删除旧库。
1)创建名为userdb2的新库
- mysql> CREATE DATABASE userdb2;
- Query OK, 1 row affected (0.00 sec)
2)导入备份文件,在新库中重建表及数据
- [root@dbsvr1 ~]# mysql -u root -p userdb2 < /root/userdb.sql
- Enter password: //验证口令
3)确认新库正常,启用新库
- mysql> USE userdb2; //切换到新库
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> SELECT sn,username,uid,gid,homedir //查询数据,确认可用
- -> FROM userlist LIMIT 10;
- +----+----------+-----+-----+-----------------+
- | sn | username | uid | gid | homedir |
- +----+----------+-----+-----+-----------------+
- | 1 | root | 0 | 0 | /root |
- | 2 | bin | 1 | 1 | /bin |
- | 3 | daemon | 2 | 2 | /sbin |
- | 4 | adm | 3 | 4 | /var/adm |
- | 5 | lp | 4 | 7 | /var/spool/lpd |
- | 6 | sync | 5 | 0 | /sbin |
- | 7 | shutdown | 6 | 0 | /sbin |
- | 8 | halt | 7 | 0 | /sbin |
- | 9 | mail | 8 | 12 | /var/spool/mail |
- | 10 | operator | 11 | 0 | /root |
- +----+----------+-----+-----+-----------------+
- 10 rows in set (0.00 sec)
4)废弃或删除旧库
- mysql> DROP DATABASE userdb;
- Query OK, 2 rows affected (0.09 sec)
4 案例4:binlog日志
4.1 问题
启用binlog日志,具体要求如下:
- 启用binlog日志,把日志文件存放到系统的/mylog目录下,日志文件为db50
- 手动创建3个新的日志文件
- 删除编号3之前的日志文件
4.2 步骤
实现此案例需要按照如下步骤进行。
步骤一:启用binlog日志
1)修改配置文件,并重启服务。
- [root@dbsvr1 ~]# vim /etc/my.cnf
- [mysqld]
- server_id=1 //指定server_id
- log-bin=/mylog/db50 //指定日志目录及名称
- :wq
- [root@dbsvr1 ~]# mkdir /mylog //创建目录
- [root@dbsvr1 ~]# chmod mysql /mylog //修改所有者
- [root@dbsvr1 ~]# systemctl restart mysqld.service //重启服务
2)查看日志信息
- [root@dbsvr1 ~]#
- [root@localhost ~]# mysql -uroot -p123qqq...A //管理员登录
- mysql: [Warning] Using a password on the command line interface can be insecure.
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 3
- Server version: 5.7.17-log MySQL Community Server (GPL)
- Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> show master status; //查看日志信息
- +-------------+----------+--------------+------------------+-------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +-------------+----------+--------------+------------------+-------------------+
- | db50.000001 | 154 | | | |
- +-------------+----------+--------------+------------------+-------------------+
- 1 row in set (0.00 sec)
- mysql>
3)手动创建3个新的日志文件
- mysql>
- mysql> flush logs; //刷新日志
- Query OK, 0 rows affected (0.14 sec)
- mysql> flush logs; //刷新日志
- Query OK, 0 rows affected (0.11 sec)
- mysql> flush logs; //刷新日志
- Query OK, 0 rows affected (0.12 sec)
- mysql> system ls /mylog/ //查看日志文件
- db50.000001 db50.000002 db50.000003 db50.000004 db50.index
- mysql>
- mysql> show master status; //查看日志信息
- +-------------+----------+--------------+------------------+-------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +-------------+----------+--------------+------------------+-------------------+
- | db50.000004 | 154 | | | |
- +-------------+----------+--------------+------------------+-------------------+
- 1 row in set (0.00 sec)
- mysql>
4)删除编号3之前的日志文件
- mysql>
- mysql> purge master logs to "db50.000003"; //删除日志
- Query OK, 0 rows affected (0.05 sec)
- mysql> system ls /mylog/ //查看日志文件
- db50.000003 db50.000004 db50.index
- mysql>
- mysql> system cat /mylog/db50.index //查看索引文件
- /mylog/db50.000003
- /mylog/db50.000004
- mysql>