Browse
 
Tools Add
Rss Categories
-->

MySQL Basics

Reference Number: AA-04896 Created: 2013-05-03 20:25 Last Updated: 2013-05-03 20:25 0 Rating/ Voters

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

Rss Comments
  • There are no comments for this article.
Info Add Comment
Nickname: Your Email: Subject: Comment:
Enter the code below: