MySQL raw notes

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)

* 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';

* 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