What is MySQL?
MySQL is the world's most used open source relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases.
Daily Operations with MySQL
Log into MySQL SSH
mysql -p
- This will prompt you to enter the MySQL password for the root user.
Create a database
CREATE DATABASE databasename;
- This allows you to create a database of any name.
Create a User
CREATE USER username IDENTIFIED BY 'somepass';
- Adds a user to the MySQL server with a password.
Change User Password
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpass');
Grant Privileges
GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost' IDENTIFIED BY 'somepass';
List of privileges
- ALL [PRIVILEGES]
Grant all privileges at specified access level except GRANT OPTION
- ALTER
Enable use of ALTER TABLE
- ALTER ROUTINE
Enable stored routines to be altered or dropped
- CREATE
Enable database and table creation
- CREATE ROUTINE
Enable stored routine creation
- CREATE TABLESPACE
Enable tablespaces and log file groups to be created, altered, or dropped
- CREATE TEMPORARY TABLES
Enable use of CREATE TEMPORARY TABLE
- CREATE USER
Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
- CREATE VIEW
Enable views to be created or altered
- DELETE
Enable use of DELETE
- DROP
Enable databases, tables, and views to be dropped
- EVENT
Enable use of events for the Event Scheduler
- EXECUTE
Enable the user to execute stored routines
- FILE
Enable the user to cause the server to read or write files
- GRANT OPTION
Enable privileges to be granted to or removed from other accounts
- INDEX
Enable indexes to be created or dropped
- INSERT
Enable use of INSERT
- LOCK TABLES
Enable use of LOCK TABLES on tables for which you have the SELECT privilege
- PROCESS
Enable the user to see all processes with SHOW PROCESSLIST
- PROXY
Enable user proxying
- REFERENCES
Not implemented
- RELOAD
Enable use of FLUSH operations
- REPLICATION CLIENT
Enable the user to ask where master or slave servers are
- REPLICATION SLAVE
Enable replication slaves to read binary log events from the master
- SELECT
Enable use of SELECT
- SHOW DATABASES
Enable SHOW DATABASES to show all databases
- SHOW VIEW
Enable use of SHOW CREATE VIEW
- SHUTDOWN
Enable use of mysqladmin shutdown
- SUPER
Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command
- TRIGGER
Enable trigger operations
- UPDATE
Enable use of UPDATE
- USAGE
Synonym for “no privileges”
List Databases
SHOW DATABASES;
Import .sql file
- Do not connect with the mysql -p command first
mysql -p -u username database_name < file.sql
Importing a .sql to a remote host
- Do not connect with the mysql -p command first
mysql -h hostname-p -u username database_name < file.sql
Exporting .sql file
- Do not connect with the mysql -p command first
mysqldump -u username -p database_name > dbname.sql
Quitting MySQL CLI
\q