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)
Cloning a MySQL database on the same MySQL instance
mysqldump -u <user name> --password=<pwd> <original db> | mysql -u <user name> -p <new db>
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;
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
How to determine which database is selected
SELECT DATABASE();
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);
Rename table
RENAME TABLE tb1 TO tb2;
or multiple tables as:
RENAME TABLE tb1 TO tb2, tb3 TO tb4;
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"