/ _ \ \_\(_)/_/ _//"\\_ 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;