Add a column to an existing table

ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

(using the keywords FIRST and AFTER we can place the new column in any position in the table)

source

Cloning a MySQL database on the same MySQL instance

mysqldump -u <user name> --password=<pwd> <original db> | mysql -u <user name> -p <new db>

source

Create read-only user in MySQL

CREATE USER 'youruser'@'localhost' IDENTIFIED BY 'yourpassword';

You can change 'localhost' whith the ip address of the connection source or '%' to open to all sources.

GRANT SELECT ON yourdatabase . * TO 'youruser'@'localhost';

Be sure you grant the SELECT privileges to all sources of connection you want.

Finally apply the new user and privileges.

FLUSH PRIVILEGES;

source

Executing SQL Statements from a Text File

from the terminal

$ mysql -u username -p database_name < file.sql

from a MySQL session

mysql> source file_name
mysql> \. file_name

source

How to determine which database is selected

SELECT DATABASE();

source

Remove a Not Null Constraint in MySQL

if you have a table like this,

CREATE TABLE something (
  id int(11) AUTO_INCREMENT PRIMARY KEY,
  name varchar(100) NOT NULL
);

To remove the NOT NULL constraint to name you must write:

ALTER TABLE something MODIFY name VARCHAR(100);

source

Rename table

RENAME TABLE tb1 TO tb2;

or multiple tables as:

RENAME TABLE tb1 TO tb2, tb3 TO tb4;

source

script to backup a mysql database

#!/usr/bin/env bash
now=$(date +"%Y%m%d-%H%M")
mysqldump -u [your-user] -d [your-database] --password=******** > "[your-path]/backup-$now.sql"
echo "filename: backup-$now.sql"