Mysql commands

Handy Cheat-Sheet of MySQL Commands

From your login shell

Securing MySQL

mysql_secure_installation

Creating a Database

mysqladmin create mydatabase

Dropping (Removing) a Database

mysqladmin drop mydatabase

Import Database

Populating an Existing Database from a *.sql File

mysql databasename < filename.sql

Export Database

To a Local file

Dumping Database Structure and Data to a *.sql file

mysqldump databasename > filename.sql
mysqldump --all-databases  > full-backup-$(date +%Y%m%d-%H%M).sql

mysqldump databasename table1 > table1.sql

To a Remote Server

mysqldump <-uUserName> <-pMyPassword> [DatabaseName] | ssh root@myremoteserver.mydomain.com "cat > /tmp/DatabaseName.sql"

User Management

List Users

mysql -B -N -pMyPassword -e "SELECT user, host FROM user" mysql

List User Permissions

mysql -B -N -pMyPassword -e "SHOW GRANTS FOR 'MyUserName'"

From within the MySQL interface

MySQL CLI

Starting MySQL from the Command Line

mysql -uusername -pMyPassword

You will be welcomed with the following message:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is ## to server version: #.##.##

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

The prompt changes to "mysql>", which will be shown in each example below.

Show Databases

Seeing What Databases are Available

show databases;

(be sure to use the semi-colon to terminate the command)

Select Database

Telling MySQL to Use a Specific Database

use mydatabase;

Show Tables

Seeing What Tables are Available Within a Database

show tables;

List Data from a Table

Looking at the Data in a Particular Table

select * from tablename;

Select the last 10 entries by column date

SELECT * FROM (
    SELECT * FROM tablename ORDER BY date DESC LIMIT 10
) sub
ORDER BY date ASC;

List data between a date range

select * from 3kw where date >= '2015-08-05 17:20:00' and curdate();

or

select * from 3kw where date >= '2015-08-05 10:30:00' and date <= '2015-08-05 11:00:00';
select date, PAC, ETODAY from 3kw where date >= '2015-08-05 10:30:00' and date <= '2015-08-05 11:00:00';

Indexes

Show index

show index from tablename;

Create index

create index indexname on tablename(columnname);

Delete index

drop index indexname from tablename;

Export Data to CSV

SELECT * FROM databasename WHERE calldate >= DATE(20141028) AND calldate <= DATE(20141105) into outfile 'calls.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Note: outfile must be before the FIELDS, ENCLOSED and LINES parameters.

Rename Table

Rename an existing Table

RENAME TABLE tbl_name TO new_tbl_name

Delete Table

use databasename;
drop tablename;

User Management

Privileges

List of privileges : https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

Add User

Adding a Database User with Password

grant all privileges on [databasename].* to [dbusername]@localhost identified by '[dbpassword]';
grant all privileges on mydatabase.* to joeuser@localhost identified by 'supersecretpasswd';

grant select on mydatabase.tablename to 'username'@192.168.1.3 identified by 'supersecretpasswd';
grant select on mydatabase.* to 'username'@'%' identified by 'supersecretpasswd';
grant create on mydatabase.* to 'username'@'%' identified by 'supersecretpasswd';

flush privileges;

Delete User

Removing a Database User

delete from mysql.user where user='techgeek' and host='localhost';
flush privileges;

Data Manipulation

Add Data

Add data to a table

insert into cid  (name, number) values 
('My Name', '0731234321'),
('Mr Name', '0412344321');

Delete Data

delete from cid where name like '0%';

Search & Replace

Performing Search-and-Replace Actions on a Table

update DatabaseName set TableName = REPLACE(TableName,"[String to Search For]","[String Replacement]");
update roesublist set Grades = REPLACE(Grades,"Grades: Any","K,1,2,3,4,5,6,7,8,9,10,11,12");