MySQL 备份之 into outfile
逻辑数据导出（备份）
用法：
- select xxx into outfile '/path/file' from table_name;
- mysql>select * into outfile '/back/emp3.sql' from emp;
无论是什么存储引擎，本身是一种数据导出的方法，同时可以用来辅助备份，它可以对一个表的其中一列或者某几列做备份，如果是多列的话用逗号隔开
逻辑数据导入（恢复）
方法一：
- load data local infile '/path/file' into table table_name;
- mysql> load data local infile '/back/emp.sql' into table emp;
说明：该方法要求在编译数据库时要加上--enable-local-infile参数才可以使用
方法二：
- # mysqlimport dbname /path/filename
说明：filename 必须和数据库里面表名一样
综合示例
例一
- # 创建表
- mysql> create table t1(id int, name varchar(32));
- # 插入数据
- mysql> insert into t1 values(1,'haha'),(2,'wowo'),(3,'lili'),(4,'yoyo');
- # 查看表
- mysql> select * from t1;
- +------+------+
- | id | name |
- +------+------+
- | 1 | haha |
- | 2 | wowo |
- | 3 | lili |
- | 4 | yoyo |
- +------+------+
-
- # 备份
- mysql> select id, name into outfile '/tmp/back_mysql' from t1;
- Query OK, 4 rows affected (0.01 sec)
查看备份
- [root@Admin ~]# cat /tmp/back_mysql
- 1 haha
2 wowo
3 lili
4 yoyo
删除数据并测试
- mysql> delete from t1 where id=3;
- Query OK, 1 row affected (0.00 sec)
- mysql> select * from t1;
- +------+------+
- | id | name |
- +------+------+
- | 1 | haha |
- | 2 | wowo |
- | 4 | yoyo |
- +------+------+
- 3 rows in set (0.00 sec)
- mysql> load data local infile '/tmp/back_mysql' into table t1;
- Query OK, 4 rows affected (0.00 sec)
- Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
- mysql> select * from t1;
- +------+------+
- | id | name |
- +------+------+
- | 1 | haha |
- | 2 | wowo |
- | 4 | yoyo |
- | 1 | haha |
- | 2 | wowo |
- | 3 | lili |
- | 4 | yoyo |
- +------+------+
- 7 rows in set (0.00 sec)
View Code例二
创建一个表，把系统里的/etc/passwd导入数据库
- create table password(
- username varchar(40),
- password char(5),
- uid int,
- gid int,
- comment varchar(30),
- homedir varchar(30),
- shell varchar(30));
处理passwd文件，将：替换成制表符
- [root@Admin ~]# sed -n 's/:/\t/gp' /etc/passwd >/tmp/passwd
导入数据库
方法一：
- mysql> load data local infile '/tmp/passwd' into table password;
- mysql> select * from password;
- +---------------+----------+-------+-------+------------------------------+------------------------+----------------+
- | username | password | uid | gid | comment | homedir | shell |
- +---------------+----------+-------+-------+------------------------------+------------------------+----------------+
- | root | x | 0 | 0 | root | /root | /bin/bash |
- | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
- | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
- | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
- | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
- | sync | x | 5 | 0 | sync | /sbin | /bin/sync |
- | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
- | halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
- | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
- | uucp | x | 10 | 14 | uucp | /var/spool/uucp | /sbin/nologin |
- | operator | x | 11 | 0 | operator | /root | /sbin/nologin |
- | games | x | 12 | 100 | games | /usr/games | /sbin/nologin |
- | gopher | x | 13 | 30 | gopher | /var/gopher | /sbin/nologin |
- | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin |
- | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin |
- | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |
- | usbmuxd | x | 113 | 113 | usbmuxd user | / | /sbin/nologin |
- | rpc | x | 32 | 32 | Rpcbind Daemon | /var/lib/rpcbind | /sbin/nologin |
- | rtkit | x | 499 | 499 | RealtimeKit | /proc | /sbin/nologin |
- | avahi-autoipd | x | 170 | 170 | Avahi IPv4LL Stack | /var/lib/avahi-autoipd | /sbin/nologin |
- | vcsa | x | 69 | 69 | virtual console memory owner | /dev | /sbin/nologin |
- | abrt | x | 173 | 173 | | /etc/abrt | /sbin/nologin |
- | rpcuser | x | 29 | 29 | RPC Service User | /var/lib/nfs | /sbin/nologin |
- | nfsnobody | x | 65534 | 65534 | Anonymous NFS User | /var/lib/nfs | /sbin/nologin |
- | haldaemon | x | 68 | 68 | HAL daemon | / | /sbin/nologin |
- | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin |
- | apache | x | 48 | 48 | Apache | /var/www | /sbin/nologin |
- | saslauth | x | 498 | 76 | Saslauthd user | /var/empty/saslauth | /sbin/nologin |
- | postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin |
- | gdm | x | 42 | 42 | | /var/lib/gdm | /sbin/nologin |
- | pulse | x | 497 | 496 | PulseAudio System Daemon | /var/run/pulse | /sbin/nologin |
- | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
- | tcpdump | x | 72 | 72 | | / | /sbin/nologin |
- | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/bash |
- +---------------+----------+-------+-------+------------------------------+------------------------+----------------+
方法二：
通过mysqlimport进行导入
注意：导入的文件名必须和表名一样
- [root@Admin ~]# cp /etc/passwd /tmp/password
- [root@Admin ~]# mysqlimport --fields-terminated-by=':' --lines-terminated-by='\n' login '/tmp/password'
- login.password: Records: 34 Deleted: 0 Skipped: 0 Warnings: 0
# 说明：文件不需处理， 通过--fields-terminated-by=':' 和 --lines-terminated-by='\n' 将passwd文件中的冒号替换为制表符
- mysql> select * from password;
- +---------------+----------+-------+-------+------------------------------+------------------------+----------------+
- | username | password | uid | gid | comment | homedir | shell |
- +---------------+----------+-------+-------+------------------------------+------------------------+----------------+
- | root | x | 0 | 0 | root | /root | /bin/bash |
- | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
- | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
- | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
- | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
- | sync | x | 5 | 0 | sync | /sbin | /bin/sync |
- | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
- | halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
- | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
- | uucp | x | 10 | 14 | uucp | /var/spool/uucp | /sbin/nologin |
- | operator | x | 11 | 0 | operator | /root | /sbin/nologin |
- | games | x | 12 | 100 | games | /usr/games | /sbin/nologin |
- | gopher | x | 13 | 30 | gopher | /var/gopher | /sbin/nologin |
- | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin |
- | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin |
- | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |
- | usbmuxd | x | 113 | 113 | usbmuxd user | / | /sbin/nologin |
- | rpc | x | 32 | 32 | Rpcbind Daemon | /var/lib/rpcbind | /sbin/nologin |
- | rtkit | x | 499 | 499 | RealtimeKit | /proc | /sbin/nologin |
- | avahi-autoipd | x | 170 | 170 | Avahi IPv4LL Stack | /var/lib/avahi-autoipd | /sbin/nologin |
- | vcsa | x | 69 | 69 | virtual console memory owner | /dev | /sbin/nologin |
- | abrt | x | 173 | 173 | | /etc/abrt | /sbin/nologin |
- | rpcuser | x | 29 | 29 | RPC Service User | /var/lib/nfs | /sbin/nologin |
- | nfsnobody | x | 65534 | 65534 | Anonymous NFS User | /var/lib/nfs | /sbin/nologin |
- | haldaemon | x | 68 | 68 | HAL daemon | / | /sbin/nologin |
- | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin |
- | apache | x | 48 | 48 | Apache | /var/www | /sbin/nologin |
- | saslauth | x | 498 | 76 | Saslauthd user | /var/empty/saslauth | /sbin/nologin |
- | postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin |
- | gdm | x | 42 | 42 | | /var/lib/gdm | /sbin/nologin |
- | pulse | x | 497 | 496 | PulseAudio System Daemon | /var/run/pulse | /sbin/nologin |
- | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
- | tcpdump | x | 72 | 72 | | / | /sbin/nologin |
- | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/bash |
- +---------------+----------+-------+-------+------------------------------+------------------------+----------------+
- 34 rows in set (0.00 sec)
例三
把用户登录系统的信息存储到数据库里
需要显示如下：
- select username 用户名, tty 登录终端, ip 来源IP from login;
创建数据库
- mysql> create table login( username varchar(40), tty varchar(40), ip varchar(50));
处理登录数据并存储在文件里
- [root@Admin ~]# last -f /var/log/wtmp |sed -r '/^reboot|^wtmp/d' |awk '{print $1"\t"$2"\t"$3}'|tee /tmp/login
存储
- 方法一：
- mysql> load data local infile '/tmp/login' into table login;
- 方法二：
- [root@Admin ~]# mysqlimport login '/tmp/login'
查看结果
- mysql> select username 用户名, tty 登录终端, ip 来源IP from login;
- +-----------+--------------+---------------+
- | 用户名 | 登录终端 | 来源IP |
- +-----------+--------------+---------------+
- | root | pts/2 | 192.168.1.131 |
- | root | pts/1 | 192.168.1.131 |
- | root | pts/1 | 192.168.1.131 |
- | root | pts/0 | :0.0 |
- | root | tty1 | :0 |
- | root | pts/0 | :0.0 |
- | root | tty1 | :0 |
- | | | |
- +-----------+--------------+---------------+
- 8 rows in set (0.00 sec)
经验首页