MySQL

Administration Grant privileges syntax to allow users to access defined databases from defined IPs. After the GRANT command is executed, you have to flush privileges for the changes to be effective. The following commands should be ran in MySQL shell.

--Grant all privileges to root from localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
 
--Grant read-only privilege on mydatabasename to user.
GRANT SELECT ON mydatabasename.* TO 'user'@'192.168.0.101' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
 
--Show granted privileges of user.
SHOW GRANTS FOR 'user'@'192.168.0.101';
 
--Delete guest user from MySQL
DELETE FROM mysql.user WHERE User = 'guest';


Misc. commands

mysqladmin -uusername -ppassword create databasename
Create database
mysqladmin -uusername -ppassword -f drop databasename
Delete database
mysql -uusername -ppassword databasename < SQLcommands.sql
Execute sql commands from SQLcommands.sql.
mysql -uusername -ppassword databasename <<< "SELECT * FROM aTable;"
Execute SQL commands from string.
mysql -uusername -ppassword databasename -e "SELECT * FROM aTable;"
Execute SQL commands from string.
mysqldump -uusername -ppassword databasename > filename.sql
Export data of databasename to filename.sql.
mysqldump --opt --user=username --password=password databasename > filename.sql
--opt, it does a few things, dumps each table one at a time, tells MySQL not to load the whole database into memory during the dump, optimizes the table
Add a column called "colname" to the table
ALTER TABLE yourTableName ADD colname VARCHAR(30);
Rename a column in the table from "address" to "home_address"
ALTER TABLE p_addr CHANGE address home_address INTEGER; Note: You cannot use this method to rename a column which is a primary key.
Change column definition to VARCHAR(111)
ALTER TABLE yourTableName MODIFY colname VARCHAR(111);
Copy from original_table to new_table
CREATE TABLE new_table SELECT * FROM original_table;
Insert rows from table(t1) to another table(t2)
INSERT t1 SELECT * FROM t2;
Selectively dump tables
mysqldump -t -u[username] -p[password] [DATABASE] [TABLE] --where="[columname]='some_value'" > filename.sql
Drop all tables
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
Save results/outputs to file
\T c:\temp\filename.txt

Mysqldump: --no-create-info : Output only INSERT statements