Getting Started with MySQL

getting-started-mysql.md

Getting Started with MySQL

I swap between databases all the time for various projects, and end up Googling on how to do the inital setup all the time, so the purpose of this document is to list a set of commands that anyone would end up using in getting started.

Getting Started with the MySQL Client Console

Login to the MySQL console as the root user, with your password for the root user.

mysql -u root -p
# you will be prompted for the password

A more verbose and easier to read version is this:

mysql --user <username> --host <hostname/ipaddr> --port <port> --password
# you will be prompted for the password

The commands below will require you to be in the MySQL client console.

Creating a User

CREATE USER 'newuser'@'host' IDENTIFIED BY 'password';

Create a New Database

CREATE DATABASE dbname;

Grant All Privileges for the User newuser for Database dbname

Don't this this on a production environment. This is really just helpful for development environment.

GRANT ALL PRIVILEGES ON dbname . * TO 'newuser'@'host';

Drop a Database

DROP DATABASE [IF EXISTS] dbname;

Drop a User

DROP USER 'newuser'@'host';

Get the database size

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

Get the table size inside a database

IMPORTANT: Be sure to change the database name here:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;