Basic MySQL management with the command line

Connecting to the MySQL cli (database is optional)
mysql -u USERNAME -p DATABASE

Listing the databases
SHOW DATABASES;

Selecting a database
USE database_name;

Listing the tables
SHOW TABLES;

Details of a table
DESC table_name;

Creating a database
CREATE SCHEMA database_name DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Creating a table (I’m using auto increment, foreign keys and unique index)
CREATE TABLE aliases (id INT NOT NULL AUTO_INCREMENT, domain_id INT NOT NULL, src VARCHAR(250) NOT NULL, dst VARCHAR(250) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (domain_id) REFERENCES domains(id), UNIQUE KEY sources (src));

Selecting data from a table but displaying it row by row (\G does the trick)
SELECT * FROM aliases \G;

Creating a new user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'the_password';

Granting table permissions to the user, the last command updates the permissions, in this command database_name and table_name could be replaced with ‘*’ to accept all of them
GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'localhost';
FLUSH PRIVILEGES;

In order to revoke permissions use the same command as above but instead of GRANT use REVOKE

Delete a user from the system
DROP USER 'username'@'localhost';

UPDATE, INSERT and SELECT are the usual so no additional information for them

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s