MySQL Handbook

-- Shell Commands --
-- -----------------------------
-- Shutdown process
mysqladmin -u root -p shutdown
-- Change admin password
mysqladmin -u root -p'oldpassword' password newpass

-- User Commands --
-- -----------------------------
-- Create a user to access from any host to a database
CREATE USER 'user1'@'%' IDENTIFIED BY 'user1';
GRANT ALL PRIVILEGES ON MyDatabase.* TO user1@'%' IDENTIFIED BY 'user1' WITH GRANT OPTION;
SET PASSWORD FOR 'user1'@'%' = PASSWORD('123456');

DROP USER user@'%';
-- Specific host
CREATE USER 'user1'@'192.168.1.155' IDENTIFIED BY 'user1';
GRANT ALL PRIVILEGES ON MyDatabase.* TO user1@'192.168.1.155' IDENTIFIED BY 'user1' WITH GRANT OPTION;
SET PASSWORD FOR 'user1'@'192.168.1.155' = PASSWORD('123456');

SHOW GRANTS FOR 'user1'@'%'

FLUSH PRIVILEGES;

DROP USER user@'192.168.1.155';

-- My Notes --
-- -----------------------------
-- Text Field, instead of varchar
-- for unlimited text field use TEXT var instead of VARCHAR(number)
-- Not recommended for large databases

-- Databases --
-- -----------------------------
-- Rename database
RENAME DATABASE Db_name TO new_db_name;

-- Export Data
-- -----------------------------
-- Dump database
mysqldump -u'user' -p'password' name_database > /var/www/File-$(date +%F_%R).sql

-- Export Rows Into A SQL File
mysqldump --user root -p --databases mydatabase --tables 'mytable' --where 'id != 10' > fileUploads3.sql

--Export Rows Into A CSV File
SELECT *
 INTO OUTFILE '/Users/nacho/file1.csv'
 FROM mytable;
SELECT *
 INTO OUTFILE '/Users/nacho/file2.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 FROM mytable
 [...];

-- Import Data --
-- -----------------------------
/*
admin1Codes.txt
ES.00	Ceuta
ES.07	Balearic Islands
ES.27	La Rioja
[...]
Relative Path
*/
LOAD DATA LOCAL INFILE './admin1Codes.txt' INTO TABLE admin1Codes (code, name);
-- Absolute Path
LOAD DATA INFILE '/Users/nacho/Documents/admin1Codes.txt' INTO TABLE admin1Codes (code, name); 

-- Run Commands From Shell --
-- -----------------------------
mysql -u'root' -p'root' -e'SHOW DATABASES'

mysql -u'root' -p'root' database < script.sql

-- Supervising server --
-- -----------------------------
-- Check the current query
mysqladmin processlist -u root -p
-- Check every 2 seconds the active queries
watch mysqladmin pr -u root -p

Tags: load data, mysql, rename