/ _ \
\_\(_)/_/
 _//"\\_     more on JOHLEM.net
  /   \	                 
	
0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0o0






--- CHEATSHEET MYSQL



# To connect to a database
mysql -h localhost -u root -p

# To backup all databases
mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql

# To restore all databases
mysql -u root -p  < ~/fulldump.sql

# To create a database in utf8 charset
CREATE DATABASE owa CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Types of user permissions:

# ALL PRIVILEGES - gives user full unrestricted access
# CREATE - allows user to create new tables or databases
# DROP - allows user to delete tables or databases
# DELETE - allows user to delete rows from tables
# INSERT- allows user to insert rows into tables
# SELECT- allows user to use the Select command to read through databases
# UPDATE- allow user to update table rows
# GRANT OPTION- allows user to grant or remove other users' privileges

# To grant specific permissions to a particular user
GRANT permission_type ON database_name.table_name TO 'username'@'hostname';

# To add a user and give rights on the given database
GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'IDENTIFIED BY 'password' WITH GRANT OPTION;

# To change the root password
SET PASSWORD FOR root@localhost=PASSWORD('new_password');

# To delete a database
DROP DATABASE database_name;

# To reload privileges from MySQL grant table
FLUSH PRIVILEGES;

# Show permissions for a particular user
SHOW GRANTS FOR 'username'@'hostname';

# Find out who the current user is
SELECT CURRENT_USER();

# To delete a table in the database
DROP TABLE table_name;

#To return all records from a particular table
SELECT * FROM table_name;

# To create a table (Users table used as example)
# Note: Since username is a primary key, it is NOT NULL by default. Email is optional in this example.
CREATE TABLE Users (
	username VARCHAR(80),
	password VARCHAR(80) NOT NULL,
	email VARCHAR(80),
	PRIMARY KEY (username)
);

# To disable general logging
set global general_log=0;

# Export database
mysqldump -h hostname -u username -p database_name -P port > file.sql

# Import database
mysql -u username -p database_name < file.sql

# Show you any queries that are currently running or in the queue to run
SHOW PROCESSLIST;

# Grant all privileges on database
GRANT ALL PRIVILEGES ON prospectwith.* TO 'power'@'localhost' WITH GRANT OPTION;