MariaDB [test]> pager md5sum;select * from userlist where id = 23456; PAGER set to 'md5sum' 8f3941802b868b2bbb43390ab5a2f1c8 - 256 rows in set (9.221 sec)
MariaDB [test]> pager md5sum;select * from userlist where id = 23456; PAGER set to 'md5sum' 8f3941802b868b2bbb43390ab5a2f1c8 - 256 rows in set (9.221 sec)
MariaDB [test]> pager md5sum;select * from userlist where userid like 'ltccjc%'; PAGER set to 'md5sum' 2bde22161d7a5bad42c377b7290cdff4 - 256 rows in set (19.196 sec)
MariaDB [test]> pager md5sum;select * from userlist where userid like 'ltccjc%'; PAGER set to 'md5sum' 2bde22161d7a5bad42c377b7290cdff4 - 256 rows in set (19.279 sec)
MariaDB [test]> select count(*) from userlist_fix; +----------+ | count(*) | +----------+ | 13432193 | +----------+ 1 row in set (0.000 sec)
MariaDB [test]> pager md5sum;select * from userlist_fix where id = 23456; PAGER set to 'md5sum' 0871c0088df3f4dbf37b12e56611a0b0 - 128 rows in set (3.535 sec)
MariaDB [test]> pager md5sum;select * from userlist_fix where id = 23456; PAGER set to 'md5sum' 0871c0088df3f4dbf37b12e56611a0b0 - 128 rows in set (3.535 sec)
MariaDB [test]> pager md5sum;select * from userlist_fix where userid like 'ltccjc%'; PAGER set to 'md5sum' Empty set (9.344 sec)
MariaDB [test]> pager md5sum;select * from userlist_fix where userid like 'ltccjc%'; PAGER set to 'md5sum' Empty set (9.325 sec)
MariaDB [test]> select count(*) from userlist; +----------+ | count(*) | +----------+ | 13819650 | +----------+ 1 row in set (13.576 sec)
MariaDB [test]> pager md5sum;select * from userlist where id = 23456; PAGER set to 'md5sum' 8f3941802b868b2bbb43390ab5a2f1c8 - 256 rows in set (9.306 sec)
MariaDB [test]> pager md5sum;select * from userlist where id = 23456; PAGER set to 'md5sum' 8f3941802b868b2bbb43390ab5a2f1c8 - 256 rows in set (9.305 sec)
MariaDB [test]> pager md5sum;select * from userlist_fix where id = 23456; PAGER set to 'md5sum' 6daf8a0815666570cee0011046e3b568 - 128 rows in set (3.526 sec)
MariaDB [test]> pager md5sum;select * from userlist_fix where id = 23456; PAGER set to 'md5sum' 6daf8a0815666570cee0011046e3b568 - 128 rows in set (3.526 sec)
create table userlist_csv ( id int not null, phonenum char(13) not null, userid char(29) not null) engine=CONNECT table_type=CSV file_name='/tmp/data.csv' header=0 sep_char='|' quoted=0;
MariaDB [test]> select count(*) from userlist_csv; 9229c8871853852300fca7ce0bb33598 - 1 row in set (17.835 sec)
MariaDB [test]> select count(*) from userlist_csv; 9229c8871853852300fca7ce0bb33598 - 1 row in set (17.712 sec)
MariaDB [test]> pager md5sum;select * from userlist_csv where id = 23456; PAGER set to 'md5sum' 9a02ca9b5c3044a73f4441c10945ff91 - 140 rows in set (9.356 sec)
MariaDB [test]> pager md5sum;select * from userlist_csv where id = 23456; PAGER set to 'md5sum' 9a02ca9b5c3044a73f4441c10945ff91 - 140 rows in set (9.355 sec)
MariaDB [test]> pager md5sum;select * from userlist_csv where userid like 'ltccjc%'; PAGER set to 'md5sum' Empty set (21.788 sec)
MariaDB [test]> pager md5sum;select * from userlist_csv where userid like 'ltccjc%'; PAGER set to 'md5sum' Empty set (21.781 sec)
Dec 12 16:39:23 localhost groupadd[32556]: group added to /etc/group: name=mariadb, GID=1000 Dec 12 16:39:23 localhost groupadd[32556]: group added to /etc/gshadow: name=mariadb Dec 12 16:39:23 localhost groupadd[32556]: new group: name=mariadb, GID=1000 Dec 12 16:39:23 localhost useradd[32560]: new user: name=mariadb, UID=1000, GID=1000, home=/home/mariadb, shell=/bin/bash Dec 12 16:39:43 localhost passwd[32569]: pam_unix(passwd:chauthtok): password changed for mariadb Dec 12 16:39:46 localhost chfn[32570]: changed user 'mariadb' information Dec 12 16:45:36 localhost su[1805]: Successful su for mariadb by root Dec 12 16:45:36 localhost su[1805]: + /dev/pts/0 root:mariadb Dec 12 16:45:36 localhost su[1805]: pam_unix(su:session): session opened for user mariadb by root(uid=0) Dec 12 16:45:36 localhost su[1805]: pam_systemd(su:session): Cannot create session: Already running in a session Dec 12 16:50:01 localhost sudo: mariadb : TTY=pts/0 ; PWD=/home/mariadb ; USER=root ; COMMAND=/usr/bin/apt-get install build-essential libncurses5-dev gnutls-dev libcurl4-gnutls-dev Dec 12 16:50:01 localhost sudo: pam_unix(sudo:session): session opened for user root by root(uid=0) Dec 12 16:50:04 localhost sudo: pam_unix(sudo:session): session closed for user root Dec 12 16:50:14 localhost sudo: mariadb : TTY=pts/0 ; PWD=/home/mariadb ; USER=root ; COMMAND=/usr/bin/apt-get install build-essential libncurses5-dev gnutls-dev libcurl4-gnutls-dev Dec 12 16:50:14 localhost sudo: pam_unix(sudo:session): session opened for user root by root(uid=0) Dec 12 16:50:25 localhost sudo: pam_unix(sudo:session): session closed for user root Dec 12 16:50:49 localhost sudo: mariadb : TTY=pts/0 ; PWD=/home/mariadb ; USER=root ; COMMAND=/usr/bin/apt-get install zlib1g-dev ccache libnuma-dev libxml2-dev cmake bison Dec 12 16:50:49 localhost sudo: pam_unix(sudo:session): session opened for user root by root(uid=0) Dec 12 16:51:08 localhost sudo: pam_unix(sudo:session): session closed for user root Dec 12 16:56:01 localhost CRON[7999]: pam_unix(cron:session): session opened for user root by (uid=0) Dec 12 16:56:01 localhost CRON[7999]: pam_unix(cron:session): session closed for user root Dec 12 17:17:01 localhost CRON[16493]: pam_unix(cron:session): session opened for user root by (uid=0) Dec 12 17:17:01 localhost CRON[16493]: pam_unix(cron:session): session closed for user root
创建表：
1 2 3 4 5 6 7 8
create table authlog ( month char(3) not null field_format='%n%s%n', day char(2) not null field_format=' %n%s%n', time char(8) not null field_format=' %n%s%n', hostname char(9) not null field_format=' %n%s%n', module char(20) not null field_format=' %n%s%n', message char(255) not null field_format=' %n%255[^\n]%n') engine=CONNECT table_type=FMT file_name='/var/log/auth.log.1' ;
查询一下，现在看格式是不是好看多了：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
MariaDB [test]> select * from authlog limit 10; +-------+-----+----------+-----------+----------------------+-----------------------------------------------------------------------+ | month | day | time | hostname | module | message | +-------+-----+----------+-----------+----------------------+-----------------------------------------------------------------------+ | Dec | 2 | 20:47:26 | localhost | systemd-logind[721]: | Power key pressed. | | Dec | 2 | 20:47:26 | localhost | systemd-logind[721]: | Powering Off... | | Dec | 2 | 20:47:26 | localhost | systemd-logind[721]: | System is powering down. | | Dec | 2 | 20:47:27 | localhost | systemd: | pam_unix(systemd-user:session): session closed for user root | | Dec | 11 | 10:07:26 | localhost | systemd-logind[960]: | New seat seat0. | | Dec | 11 | 10:07:26 | localhost | systemd-logind[960]: | Watching system buttons on /dev/input/event0 (Power Button) | | Dec | 11 | 10:07:26 | localhost | systemd-logind[960]: | Watching system buttons on /dev/input/event2 (QEMU QEMU USB Keyboard) | | Dec | 11 | 10:07:50 | localhost | sshd[2384]: | Server listening on 0.0.0.0 port 22. | | Dec | 11 | 10:07:50 | localhost | sshd[2384]: | Server listening on :: port 22. | | Dec | 11 | 10:07:51 | localhost | sshd[2384]: | Received signal 15; terminating. | +-------+-----+----------+-----------+----------------------+-----------------------------------------------------------------------+
我们来进行一些查询：
1 2 3 4 5 6 7
MariaDB [test]> select count(*) from authlog where month='Dec' and day='12' and module like 'sshd%'; +----------+ | count(*) | +----------+ | 3170304 | +----------+ 1 row in set (15.609 sec)
