Here is a list of helpful Postgres commands
Drop a Role
DROP ROLE role_name;
Connect to database
\c database_name;
List tables
\l
List tables in current database
\dt
List users
\du
A better Cheat Sheet
PostgreSQL Commands Cheat Sheet
Reply
Tag Archives: database
LibreNMS dump devices in MySQL database
All the devices are stored in the librenms datebase devices table.
You can dump all of them with the following command from a shell prompt.
sudo mysql -u librenms -p librenms -e "select hostname,sysName from devices"
Example output
[jim@localhost ~]$ sudo mysql -p librenms -e "select hostname,sysName from devices" Enter password: +----------------------------------------+--------------------------------+ | hostname | sysName | +----------------------------------------+--------------------------------+ | 192.168.1.1 | linksys | | 192.168.1.9 | wifi-unifi | +----------------------------------------+--------------------------------+
Basic MongoDB commands
Connect to Mongo database
mongo
Connect to Mongo on a different port (May be needed. Port is for UniFi server)
mongo -port 27117
Show Databases
show dbs
Use database
use dbname
Show tables/collections
show collections
or
show tables
List contents of table/collection
db.collection.find()
Example: (This example prints everything in the “admin” collection)
db.admin.find()
Find info that line that whose name is admin
db.admin.find({name : "admin" })
Find everything in a table, but only print columns that are named “name, email, and x_shadow”
db.admin.find({ }, { name : "", email : "", "x_shadow" : "" })
List users
show users
Authenticate
db.auth("username","password")
Insert into table/collection
d = {"data":"data"}
db.collection.insert(d)
Update a line, for example a users password. Swap out the ObjectId for the ID that mongo gives you when you list the admins or users. You will need to swap out the hash for the hash of the password you want.
db.admin.update({"_id" : ObjectId("a328bf90547ehc429a03ed85")}, {$set: { "x_shadow" : "$6$XB32GMXr$8dUt9huJzzL6O.gGZbs7QH1npldbzBzNDt/uUO1bI3b7Ij3YipgubtVHwincUUZjnDLh.KDI36uh2gUCID9yb1"}});
Example:
d = {"name":"admin","lang":"en_US","x_password":"password","time_created":"","last_site_name":"default"}
db.admin.insert(d)
Delete line from table/collection
db.collection.remove()
Example: (This will look for all the “rows” where the “name” contains “admin” and remove it)
db.admin.remove({ name: "admin"})
UniFI Specific
The following commands are specifically for a UniFi server.
Show UniFi Sites
db.site.find().forEach(printjson);
Or
mongo --port 27117 ace --eval "db.site.find().forEach(printjson);"
Show UniFi admins
db.admin.find().forEach(printjson);
Or
mongo --port 27117 ace --eval "db.admin.find().forEach(printjson);"
Some Basic MySQL Commands
Enter Mysql
mysql -u root -p
Create Database
create DATABASE testdb;
Select Database
use testdb;
Delete Database
drop DATABASE testdb;
Drop Table
drop table table_name;
Show Tables
show tables;
Show data in table
SELECT * FROM table_name;
Create Table
CREATE TABLE contacts_table (id INT, name VARCHAR(20), email VARCHAR(20));
Insert data into table
INSERT INTO contacts_table (id,name,email) VALUES(2,"John","John83@incredigeek.com");
List one row in table
SELECT * FROM table_name LIMIT 1;
Delete Row in Table
DELETE FROM table_name WHERE row_name=data_to_delete;
Delete all “users” from a WordPress database that do not contain admin in the username.
DELETE FROM `wp-users` WHERE user_login NOT LIKE "%admin%"
Add Auto Increment to Table
ALTER TABLE `table_name` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;Create table with Date and Time timestamp
CREATE TABLE table_name (id INT, timeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Show MySQL Users
select * from mysql.users;
Create MySQL User
GRANT ALL ON mysqldb.* TO username@localhost IDENTIFIED BY 'password';
Delete MySQL user
DROP USER 'username'@'localhost';
Add Column to the end of MySQL Table
ALTER TABLE mysqltable ADD email VARCHAR(60);
Add Column to the begging of MySQL Table
ALTER TABLE mysqltable ADD email VARCHAR(60) FIRST;
Insert Column after Specific Column in MySQL Table
ALTER TABLE mysqltable ADD email VARCHAR(60) AFTER columnname;