MySQL examples

I’ve always wanted to create my own cheat sheet that lists simple and common examples of MySQL commands that I collected.

Create a new database
create database unfortunate_database;

Delete a database
drop database unfortunate_database;

Create a new user with specific privileges on the database
grant select,update,insert,delete,create on unfortunate_database.* to 'jack'@'localhost' identified by 'H0lyFreak1ng@!';

The example above allows the username jack, password H0lyFreak1ng@! access to unfortunate_database but does not have permissions to drop a table or database. Granting specific privileges is particularly useful if you want to provide read only access. Ideally, you would have a user with all privileges and a second user with read-only privileges (grant select on unfortunate_database.*…)

To Backup a MySQL database
mysqldump -u root -proot_password unfortunate_database > unfortunate_database.sql
or
mysqldump -u root -p unfortunate_database > unfortunate_database.sql

The mysqldump command is done outside of the MySQL server console.  You don’t necessarily have to be root in order to issue the command nor specify as the user root to perform a backup of the database. As long as the user has privileges in the shell and the specified MySQL user has permissions (on the database), you should be fine. Also, the new MySQL dump file will be stored in the present working directory you are in. Which means, if you are on /home/jack on some Linux server, unfortunate_database.sql will in that directory.  The dump file, unfortunate_users.sql is just a text file that includes commands that will restore the data to the database.  With that, the file doesn’t really need to end in .sql extension but is considered good practice.

To create a table

CREATE TABLE `unfortunate_users` (
`id` int(8) NOT NULL auto_increment,
`username` varchar(64) NOT NULL,
`password` varchar(64) NOT NULL,
`active` tinyint(1) NOT NULL default '0', 
PRIMARY KEY (`id`));

To empty out data from a table without wiping out or altering the table structure
truncate table unfortunate_users

In case of you run into a scenario where someone sends you a CSV file and you need to load it unto an empty or truncated table

load data local infile 'more_unfortunate_bastards.csv' into table unfortunate_ fields terminated by ';'
enclosed by ''
lines terminated by '\n'
(column1, column2, column3, column4);

What good is a backup if you can’t restore it?
To restore a MySQL backup…
mysql -u root -p database_name < dumpfile.sql

Add a column to an existing MySQL database table
ALTER TABLE table_name ADD sometext tinyint(1);

I made an error with the above example, I did not intend sometext to be a storage for a boolean character.

To delete a MySQL database column
ALTER TABLE table_name DROP sometext;

Not enough commands?  This guy has more.  http://www.nparikh.org/notes/mysql.php