Home >> MySQL Basic Command Cheat Sheet

Tag: shell

MySQL Basic Command Cheat Sheet

# Create user
CREATE USER 'username'@'hostname' IDENTIFIED BY 'mypassword';

# Show user
SELECT username FROM mysql.user;

# Grant privilege
GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname';
Or
GRANT SELECT, INSERT, UPDATE, DELETE ON database.table TO 'username'@'hostname'

# Revoke / remove privilege
REVOKE ALL PRIVILEGES ON object FROM username

# Dump data
mysqldump -h hostname -u root -p databasename > filename.txt

# Import data
mysql -u username -p databasename < sqlfile.sql

# Alter to fulltext
ALTER TABLE tablename ADD FULLTEXT(fieldname);

# Alter character set and collation, CONVERT change all columns
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Show character set of a table
SHOW FULL COLUMNS FROM tablename

# Show table option when creating that table
SHOW CREATE TABLE tablename;

# Show all table status
SHOW TABLE STATUS;