[TOC]
AWS RDS Connection Limits
-
The predefined AWS RDS settings for MySQL max. connections are:
- Small Instance: 150 connections (1.7GB of RAM)
- Large instance: 640 connections (7.5GB of RAM)
Basics
mysql -hSERVERNAME....rds.amazonaws.com -uUSER -pPASSWORD
show databases;
use DATABASENAME
mysql -hSERVERNAME....rds.amazonaws.com -uUSER -pPASSWORD DATABASENAME (will start you in the database)
show tables;
desc TABLENAME;
SELECT COUNT(*) FROM TABLENAME;
SELECT * FROM TABLENAME limit 2;
SELECT * FROM TABLENAME where name = 'SPECIFIC NAME';
SELECT * FROM TABLENAME where oid like 'fe7%';
mysql -hSERVERNAME...rds.amazonaws.com -uUSER -pPASSWORD DATABASENAME -e "show tables" (non interactive commands or queries)
mysql -hSERVERNAME...rds.amazonaws.com -uUSER -pPASSWORD DATABASENAME -e "show tables" > outputfile.txt
# find rows where a value is NULL
SELECT * FROM TABLENAME where group_id IS NULL limit 2;
# add a row
INSERT INTO TABLENAME (username,imagename,width,height) VALUES ('john','goal.png',20,25);
# IF A COLUMN EXISTS IN THE TABLE BUT IS NOT LISTED IN THE COLUMNS/VALUES THEN IT WILL RECEIVE NULL (OR DEFAULT)
USERS
* CREATE USER 'adminuser'@ '%' IDENTIFIED BY 'passwordhere';
* GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
* SHOW GRANTS FOR 'username' <em> SHOW GRANTS FOR 'admin'@'localhost'; </em>
<em> RESULT = GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO </em>
= Storage Gateways =
== View and Update a StorageGateway address ==
* USE storagegatewaymanager;
* DESC PstCloudGateway;
* SELECT * FROM PstCloudGateway;
* SELECT * FROM PstCloudGateway where oid like 'fe7%';
* UPDATE PstCloudGateway SET address='sandboxsgw.mydomain.com:storagegateway:0:true:443' WHERE oid like 'fe7%';
= AD/LDAP Gateways =
== View and Update a StorageGateway address ==
* USE storagegatewaymanager;
* DESC PstCloudGateway;
* SELECT * FROM PstCloudGateway;
* SELECT * FROM PstCloudGateway where oid like 'fe7%';
* UPDATE PstCloudGateway SET address='sandboxsgw.mydomain.com:storagegateway:0:true:443' WHERE oid like 'fe7%';
<pre>
| adminconsole |
| billing |
| directory |
| networkid |
| provisioning |
| repositorymanager |
| repositoryregistry |
| storagegatewaymanager |
| storagemanager |
| systemconsole |
</pre>
= Accounts & Users =
# use networkid;
# select * from PstNetworkAccount where name like '%myname%';
# select * from PstNetworkAccount where accountId like '%pfeiffer%';
=== Number of Current Users ===
# use networkid
# show tables;
# desc PstNetworkUser;
# select count(oid) from PstNetworkUser;
# select count(*) from PstNetworkUser limit 10;
=== Finding a Specific User by Name ===
* select * from PstNetworkUser where lastName = 'pfeiffer';
* select * from PstNetworkUser where oid = 'ff8080812d0c92f0012d393684f90c6b'
== Research a Deleted File ==
USE repositorynode;
DESC PstDeletedFile;
SELECT * FROM PstDeletedFile WHERE processed=0;
DESC PstFile;
SELECT * FROM PstFile LIMIT 2;
SELECT * FROM PstFile WHERE pstRepositoryOid LIKE 'fe7%';
USE storagecloud;
DESC PstCloud;
SELECT * FROM PstCloud WHERE name LIKE '%atmos%';
select * from PstStorage where storageGatewayType="Atmos";
select creatorOid, creatorName, pstRepositoryOid, deleted from PstFolder where oid = 'ff8080812f9a9f3c012faa3d3d356fb8';
user repositorynode;
desc PstFolder;
select * from PstFolder where name='LF LDAP materials';
MIGRATING A DATABASE
* Move database to cloud from Standard
<strong> mysql -u cloud_db_user -p -h <server ip> cloud_db_name < backup.sql </strong>
* Copy DB from remote server
<strong> mysqldump -P <port#> -h <server ip>-u mysql_user -p database_name table_name > backup.sql </strong>
mysqldump -P 3306 -hHOSTrds.amazonaws.com -uUSER-pPASSWORD DATABASENAME > backup-DATABASENAME.sql
mysqldump -P 3306 -htHOST.rds.amazonaws.com -uUSER-pPASSWORD DATABASENAME TABLENAME > backup-DATABASENAME-TABLENAME.sql
mysqldump --all-databases -htestoxygen.cgc8wgi5w1kh.us-east-1.rds.amazonaws.com -uUSER-pPASSWORD > date-backup.sql