SQL Notes
From artserver wiki
2019
How to pull and load data from SELECT query
05.06.2019
Login to mariadb; Save the result of a SELECT query to a file
Links:
- https://mariadb.com/kb/en/library/load-data-infile/
- https://mariadb.com/kb/en/library/select-into-outfile/
use wiki2016;
SELECT * INTO OUTFILE 'pages.dat' FROM wiki2016.page WHERE page_id < 10 AND page_namespace = 0;
This is possible in the localhost, however it not viable if you connecting to a remote myslq/maridb server.
In which case mariadb/myql can be invoked with an expression and the output written to a local file.
mariadb -h your.db.host -u dbuser -p -e 'SELECT * FROM wiki2016.page WHERE page_id < 10 AND page_namespace = 0;
' > pages.dat
Pages pages.dat stored in /var/lib/mysql/wiki2016
dump only the db schema without data
$ mysqldump --user=andre --default-character-set=utf8 --no-data --skip-triggers "wiki2016" -p
create a new db
CREATE DATABASE wikitest;
And import the import schema
$ mysql --user=andre wikitest -p < wiki2016.sql
Load the data file with default tab separator and one entry per line. And test if the data was inserted to the DB
LOAD DATA INFILE '/var/lib/mysql/wiki2016/pages.dat' REPLACE INTO TABLE wikitest.page;
use wikitest;
SELECT * FROM page;
+---------+----------------+--------------------+-------------------+------------------+-------------+----------------+----------------+--------------------+-------------+----------+--------------------+-----------+
| page_id | page_namespace | page_title | page_restrictions | page_is_redirect | page_is_new | page_random | page_touched | page_links_updated | page_latest | page_len | page_content_model | page_lang |
+---------+----------------+--------------------+-------------------+------------------+-------------+----------------+----------------+--------------------+-------------+----------+--------------------+-----------+
| 1 | 0 | Main_Page | | 0 | 0 | 0.849653152175 | 20190115085635 | 20190519111542 | 2458 | 1776 | wikitext | NULL |
| 2 | 0 | Test | | 0 | 1 | 0.473474610327 | 20150318120109 | 20140909225556 | 4 | 18 | wikitext | NULL |
| 6 | 0 | WDKA | | 0 | 0 | 0.43307080073 | 20190305091605 | 20190305091605 | 2464 | 12863 | wikitext | NULL |
| 8 | 0 | PageName | | 0 | 1 | 0.033588984951 | 20150318120053 | 20140910082629 | 18 | 30 | wikitext | NULL |
| 9 | 0 | Calendars:PageName | | 0 | 1 | 0.976488411003 | 20150318120218 | 20140910082630 | 19 | 0 | wikitext | NULL |
+---------+----------------+--------------------+-------------------+------------------+-------------+----------------+----------------+--------------------+-------------+----------+--------------------+-----------+
5 rows in set (0.00 sec)