Databases: Difference between revisions

From Braindump
Jump to navigation Jump to search
 
Line 224: Line 224:
  SELECT COUNT(date) FROM epguides;
  SELECT COUNT(date) FROM epguides;
  PreparedStatement ps = con.prepareStatement("INSERT INTO epguides VALUES(?, ?, ?, ?, ?, ?, ?)");
  PreparedStatement ps = con.prepareStatement("INSERT INTO epguides VALUES(?, ?, ?, ?, ?, ?, ?)");
== IEEE ==
https://standards-oui.ieee.org/
CREATE TABLE ieee (mac varchar(6) NOT NULL, name varchar(255), UNIQUE(mac) );
LOAD DATA LOCAL INFILE 'oui.txt'
INTO TABLE ieee
(@row)
SET
mac = TRIM(SUBSTR(@row,1,6)),
name = TRIM(SUBSTR(@row,33));
LOAD DATA LOCAL INFILE 'oui.txt' INTO TABLE ieee FIELDS TERMINATED BY '\t\t';


== Restore ==
== Restore ==

Revision as of 12:00, 7 December 2023

Links

mysqlcommands

mysql survival

adding users

how to recover mysql data from innodb

mysql_real_escape_string

Configuration

/etc/mysql/my.cnf 

bind-address 127.0.0.1
#skip-networking

/etc/my.conf.d/server.cnf 

max_connections         = 30
thread_cache_size       = 1

key_buffer              = 8M
query_cache_size        = 8M
query_cache_limit       = 512K
thread_stack            = 128K

http://opensourcehacker.com/2011/03/31/reducing-mysql-memory-usage-on-ubuntu-debian-linux/

Connecting

mysql -p
mysql -h localhost -u username -ppasswd mysql

Database Information

STATUS
SHOW DATABASES;
USE mysql;
SELECT database();

Table Information

USE mysql;
SHOW TABLES;
DESC user;
SELECT user,host,password FROM user;
SELECT user(), version(), current_date;

Create Database and Table

CREATE DATABASE test
USE test
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
DESCRIBE pet;
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
INSERT INTO pet VALUES ('Chico','Owner','cat','m','1995-08-02',NULL);
SELECT * FROM pet;
SELECT name,owner FROM pet WHERE sex = 'm';
DELETE FROM pet WHERE sex = 'm';
DROP table pet;
DROP database test;

USE joomla
UPDATE jos_content SET introtext='bold' WHERE id = 5;
QUIT

LOAD DATA LOCAL INFILE '/root/project/bewerkt.txt' 
INTO TABLE Testtabel2  FIELDS TERMINATED BY ',' LINES TERMINATED BY ';' (Dag,Maand,Tijd,Send,Received,Srcip,Dstip,Srcport,Dstport);

Users

CREATE DATABASE ninan;
CREATE USER 'ninan'@'localhost' IDENTIFIED BY 'ninan';
GRANT ALL PRIVILEGES ON ninan.* TO 'ninan'@'localhost' WITH GRANT OPTION;

CREATE USER 'logstash'@'localhost' IDENTIFIED BY 'truelyrandom';
GRANT SELECT ON phpipam.* TO 'logstash'@'localhost';

USE mysql
SELECT host,user,password FROM user;
UPDATE user SET host = 'server.islief.com' WHERE host = 'rataplan.islief.com';
GRANT ALL PRIVILEGES ON *.* TO 'jan'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'jan'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON joomla.* TO joomla_admin@localhost IDENTIFIED BY 'joomla';
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
GRANT USAGE ON *.* TO 'dummy'@'localhost';
FLUSH PRIVILEGES;

DB Replication

http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html 

grant replication slave on *.* to 'replication'@x.islief.com identified by 'rataplan';

CHANGE MASTER TO
 MASTER_HOST='islief.com',
 MASTER_USER='replication',
 MASTER_PASSWORD='rataplan',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.log',
 MASTER_LOG_POS=4,
 MASTER_CONNECT_RETRY=10;

Replace substring

UPDATE volumes SET provider_location = REPLACE(provider_location, '10.0.0.123', '10.0.0.120');

Joomla

mysqladmin -u root -p create Joomla
mysql -u root -p 
GRANT ALL PRIVILEGES ON joomla.* TO joomla_admin@localhost IDENTIFIED BY 'joomla';
FLUSH PRIVILEGES;
QUIT

Financials

For personal use I collect a couple of financial figures and put them in a database and create figures from this. I allows me to understand where I'm loosing money and why I'm not rich yet (millionair by christmas) =)

Scripts are run from cron. 

20 23 * * 1-5	/usr/bin/ing.sh > /dev/null 2>&1
30 23 * * 1-5	/usr/bin/euribor.sh > /dev/null 2>&1
40 23 * * 1-5   /usr/bin/ericb.sh 2> /dev/null
50 23 * * 1-5   /usr/bin/xrate.sh 2> /dev/null

Java

javac -cp . util/URI.java
javac -cp . util/HTTPDownloader.java
javac -cp jcommon-1.0.15.jar:jfreechart-1.0.12.jar:mysql-connector-java-5.1.7-bin.jar:poi-3.5-FINAL-20090928.jar:util:. FeedDatabase.java
java -cp jcommon-1.0.15.jar:jfreechart-1.0.12.jar:mysql-connector-java-5.1.7-bin.jar:poi-3.5-FINAL-20090928.jar:util:. FeedDatabase

javac -cp jcommon-1.0.15.jar:jfreechart-1.0.12.jar:mysql-connector-java-5.1.7-bin.jar:poi-3.5-FINAL-20090928.jar EuriGraph.java
java -cp jcommon-1.0.15.jar:jfreechart-1.0.12.jar:mysql-connector-java-5.1.7-bin.jar:poi-3.5-FINAL-20090928.jar:. EuriGraph

javac -cp jcommon-1.0.15.jar:jfreechart-1.0.12.jar:mysql-connector-java-5.1.7-bin.jar:poi-3.5-FINAL-20090928.jar EricGraph.java
java -cp jcommon-1.0.15.jar:jfreechart-1.0.12.jar:mysql-connector-java-5.1.7-bin.jar:poi-3.5-FINAL-20090928.jar:. EricGraph

Euribor

select max(date) from euribor;
select date,3m,12m from euribor order by date desc limit 1;
select date,3m,12m from euribor where date =(select max(date) from euribor);

USE mysql;
GRANT SELECT ON euribor.* TO euribor@'%' IDENTIFIED BY 'euribor';
GRANT SELECT ON euribor.* TO euribor@'localhost' IDENTIFIED BY 'euribor';

CREATE database euribor;
USE euribor;
CREATE TABLE euribor (date DATE key, 1W DECIMAL(4,3), 2W DECIMAL(4,3), 3W DECIMAL(4,3), 1M DECIMAL(4,3), 2M DECIMAL(4,3), 3M DECIMAL(4,3), 4M DECIMAL(4,3), 5M DECIMAL(4,3), 6M DECIMAL(4,3), 7M DECIMAL(4,3), 8M DECIMAL(4,3), 9M DECIMAL(4,3), 10M DECIMAL(4,3), 11M DECIMAL(4,3), 12M DECIMAL(4,3));
INSERT INTO euribor VALUES('2009-01-15','2.013','2.066','2.129','2.200','2.394','2.510','2.532','2.552','2.572','2.592','2.605','2.618','2.627','2.639','2.651');
SELECT date,3M,12M FROM euribor;

EricB

CREATE database ericb;
USE ericb;
CREATE TABLE ericb (date DATE key,price DECIMAL(5,2));
INSERT INTO ericb VALUES('2009-12-29',66.90);
DELETE FROM ericb WHERE date='2009-12-29';

GRANT SELECT,INSERT ON ericb.ericb TO ericb@'localhost' IDENTIFIED BY 'ericb';
curl -s www.ericsson.com/thecompany/investors | grep -A 1 "ERIC-B" | tail -1 | sed 's/<[^>]*>//g' | mysql -uericb -pericb -Dericb -Be "INSERT INTO ericb VALUES('`date +%F`',`xargs`);"

mysql -uericb -pericb ericb -e "LOAD DATA INFILE '/tmp/table.cvs' INTO TABLE ericb FIELDS TERMINATED BY ',' (date,price)"

ING

CREATE DATABASE ing;
USE ing;
CREATE TABLE ing (date DATE, fund CHAR(31), price DECIMAL(7,5), PRIMARY KEY (date, fund));
USE mysql;
GRANT SELECT,INSERT ON ing.* TO ing@'localhost' IDENTIFIED BY 'ing';
GRANT FILE ON *.* TO ing@'localhost' IDENTIFIED BY 'ing';

curl -s http://www.ing.nl/particulier/pensioenen/koersinformatie-beleggingsfondsen/index.aspx | grep -A 3 "Actuele koersen" | tail -1 | sed -e 's//\n/g' -e 's/,/./g' -e 's/<\/td>/,/g' -e 's/ /_/g' -e 's/<[^>]*>//g' | grep --color=never ING > /tmp/ing.txt 

mysql -uing -ping -Ding -Be "LOAD DATA INFILE '/tmp/ing.txt' INTO TABLE ing FIELDS TERMINATED BY ',' (fund, price) SET date = CURRENT_DATE";
java -cp jcommon-1.0.15.jar:jfreechart-1.0.12.jar:mysql-connector-java-5.1.7-bin.jar:poi-3.5-FINAL-20090928.jar:util:. INGGraph

SDR

Special Drawing Right
https://philihp.com/2008/how-to-get-an-xdr-exchange-rate-xml-feed-from-the-imf.html

USE mysql;
GRANT SELECT,INSERT ON xrate.xrate TO xrate@'localhost' IDENTIFIED BY 'xrate';

http://www.imf.org/external/np/fin/ert/GUI/Pages/Report.aspx?CU=%27EUR%27,%27SEK%27,%27GBP%27,%27USD%27&EX=SDRC&P=DateRange&Fr=630822816000000000&To=634638240000000000&CF=Compressed&CUF=Period&DS=Ascending&DT=NA

http://www.imf.org/external/np/fin/ert/GUI/Pages/Report.aspx?CU=%27EUR%27,%27SEK%27,%27GBP%27,%27USD%27&EX=SDRC&P=DateRange&Fr=634638240000000000&To=634638240000000000&CF=Compressed&CUF=Period&DS=Ascending&DT=NA

http://www.imf.org/external/np/fin/ert/GUI/Pages/Report.aspx?CU=%27EUR%27,%27SEK%27,%27GBP%27,%27USD%27&EX=SDRC&P=DateRange&Fr=634637376000000000&To=634637376000000000&CF=Compressed&CUF=Period&DS=Ascending&DT=NA 

(TODO, FIELDS MARKED AS N/A can be marked as empty!)
CREATE TABLE xrate (date DATE KEY, eur DECIMAL(8,7), sek DECIMAL(8,7), gbp DECIMAL(8,7), usd DECIMAL(8,7));

LOAD DATA INFILE '/home/public/xdr-exchangerate.txt' INTO TABLE xrate FIELDS TERMINATED BY '\t' (date,eur,sek,gbp,usd)"
LOAD DATA INFILE '/home/public/xdr-exchangerate.txt' INTO TABLE xrate FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (@date,eur,sek,gbp,usd) set date = str_to_date(@date,'%d-%M-%Y');

#!/bin/bash
T="$(date +%s)"
J=`expr $T + 62135596800`
TIME="${J}0000000"
URL="http://www.imf.org/external/np/fin/ert/GUI/Pages/Report.aspx?CU=%27EUR%27,%27SEK%27,%27GBP%27,%27USD%27&EX=SDRC&P=Last30Days&CF=Compressed&CUF=Period&DS=Ascending&DT=NA"
curl -s -L -c /tmp/cookie.txt $URL > /dev/null
curl -s -L -b /tmp/cookie.txt "http://www.imf.org/external/np/fin/ert/GUI/Pages/ReportData.aspx?Type=XML" -o /tmp/sdr.xml
xsltproc /tmp/sdr.xsl /tmp/sdr.xml | grep INSERT | mysql -X -uxrate -pxrate -Dxdr

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:for-each select="EXCHANGE_RATE_REPORT/EFFECTIVE_DATE">INSERT IGNORE INTO xrate VALUES (STR_TO_DATE("<xsl:value-of select="@VALUE" />", '%d-%b-%Y')<xsl:for-each select="RATE_VALUE">,"<xsl:value-of select="."/>"</xsl:for-each>);
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

<xsl:value-of select="@ISO_CHAR_CODE" />

move table to different database 

RENAME TABLE xrate.xrate TO ericb.xrate;

Epguides

CREATE database epguides;
USE epguides;
CREATE TABLE epguides (num DECIMAL(3), productid char(14), date DATE, title char(64), season char(2), episode char(2), epititle char(255), PRIMARY KEY (title,season,episode));

USE mysql;
GRANT SELECT,INSERT ON epguides.epguides TO epguides@'%' IDENTIFIED BY 'epguides';

SELECT * FROM epguides WHERE date > '2010-02-01' ORDER BY date;
SELECT COUNT(date) FROM epguides;
PreparedStatement ps = con.prepareStatement("INSERT INTO epguides VALUES(?, ?, ?, ?, ?, ?, ?)");

IEEE

https://standards-oui.ieee.org/ 

CREATE TABLE ieee (mac varchar(6) NOT NULL, name varchar(255), UNIQUE(mac) );

LOAD DATA LOCAL INFILE 'oui.txt'
INTO TABLE ieee
(@row)
SET
mac = TRIM(SUBSTR(@row,1,6)),
name = TRIM(SUBSTR(@row,33));

LOAD DATA LOCAL INFILE 'oui.txt' INTO TABLE ieee FIELDS TERMINATED BY '\t\t';

Restore

restoring an old database. (mounting the old datafiles and then dumping them into an sql file and then loading them to the new database 

mysqld --innodb_log_file_size=5242880 --innodb_force_recovery=6 --datadir=/mnt/var/lib/mysql/
mysqldump -u root -p euribor > euribor.sql
mysql -u root -p euribor < /home/jan/euribor.sql

resetdb

Execute commands without needing to login 

service mysql stop
mysqld_safe --init-file=/tmp/resetroot.sql
service mysql start

Size

SELECT table_schema "Database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB", engine FROM information_schema.tables GROUP BY table_schema;

Empty DB without dropping

creates drop commands for a database 

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema = 'wiki';

Reinitialize DB

mysqld --initialize

Advanced

SHOW INNODB STATUS;
show processlist;

PostgresSQL

\l
\c kong
\dt
\d routes

sudo -u postgres createuser -DRS msf
sudo -u postgres createdb -O msf msf
sudo -u postgres psql msf
ALTER ROLE msf WITH PASSWORD 'msf';

metasploit

db_nmap -v -sV google.com

select methods, paths from routes;

Casandra

cqlsh localhost
SELECT cluster_name, listen_address FROM system.local;
SELECT * FROM system_schema.keyspaces;
DESCRIBE keyspaces;
USE kong;
DESCRIBE tables;
DESCRIBE upstreams;
SELECT * FROM schema_migrations;
SELECT * FROM apis;

Mongo

use phpipam
db.subnets.save( {description:"microsoft", subnet:"42.112.0.0", mask:"13"} )
db.subnets.find()
db.subnets.remove( {"_id": ObjectId("5b224f4a01bcaae810e15eb5")});

Elasticsearch

curl -s -XGET 'http://localhost:9200/_cat/indices?v&pretty'
curl 'http://localhost:9200/nsg-flow/ip/_search?pretty=true'
Retrieved from "https://www.janmg.com/wiki/index.php?title=Databases&oldid=924"