MySQL crash course in 10 easy steps.

December 17th, 2016 by Andrea Matesi

 

 

If you ever had to deal with MySQL in the past, I'm sure you had to spend lots of time searching on Google (as I did..).

To save you some searching, I'll post some commands I "learned while doing".

  • I'll assume all you know about MySQL is "mysql -u root -p"

The above means:

  1. You are logged-in to a MySQL Box as "root".
  2. You know the MySQL root's password (which might be different from the root local user...).
  3. There is at least 1 database with a couple of tables you can safely play with.

Let's get dirty, let's say you have one MySQL table as follows:

  • Table name "login" - Table Structure (aka schema) as follows:
Field Type Null Key Default Extra
id tinyint(3) NO PRI NULL auto_increment
password varchar(150) YES NULL
username varchar(150) YES NULL
  • Table name "login" - Table Content as follows:
id password username
1 pantera phil

 

 

select * from TABLE-NAME;

Select lists a table content.

On a MySQL prompt, type:

mysql> select * from login;

 

delete from TABLE-NAME where id=1;

delete wipes rows based on some condition (always pay attention b/c there's no such thing as a "Recycle Bin").

Example:

mysql> delete from login where id=1;

 

insert into TABLE-NAME (id, password, username) VALUES (1, "usr", "pwd");

insert adds data into rows.

Example:

mysql> insert into login (id, password, username) VALUES (1, "metallica", "lars");

 

describe TABLE-NAME;

describe shows you the schema (the columns definitions).

Example:

mysql> describe login;

Output:

Field Type Null Key Default Extra
id tinyint(3) NO PRI NULL auto_increment
password varchar(150) YES NULL
username varchar(150) YES NULL

 

ALTER TABLE TABLE-NAME ADD COLUMN new-col-name bool;

Alter allows you to add (& remove) new column(s):

Example:

mysql> ALTER TABLE login ADD COLUMN status bool;

 

select * from mysql.user;

When you run a select against the mysql.user, you will get a list of all the authorised MySQL Users.

Example:

select * from mysql.user;

 

SHOW GRANTS;

show grants ;-)

not THAT Grant's...

Show GRANTS reports your [logged-in] user's permissions.

Example:

mysql> SHOW GRANTS;

Output:

+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost|
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '1e5515b01d57bf15' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

SHOW GRANTS FOR phil;

When you wish to know a PARTICULAR User Permissions.

mysql> SHOW GRANTS FOR phil;
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for phil@%|
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, FILE, SHOW VIEW ON *.* TO 'phil'@'%' IDENTIFIED BY PASSWORD '794c6f050dff6713' |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

GRANT ALTER ON  `db2\_db`.* TO 'lars'@'%';

Grant can also be used by itself To assign (additional) permissions to a particular user.

Example:

mysql> GRANT ALTER ON  `db2\_db`.* TO 'lars'@'%';
Query OK, 0 rows affected (0.03 sec)

 

REVOKE ALTER ON  `db2\_db`.* TO 'lars'@'%';

Revoke is the opposite of Grant and it is used to deny permissions to a user.

Example:

mysql> REVOKE ALTER ON  `db2\_db`.* TO 'lars'@'%';
Query OK, 0 rows affected (0.00 sec)

Senior Professional Network and Computer Systems Engineer during work hours and father when home.

Andrea strives to deliver outstanding customer service and heaps of love towards his family.

In this Ad-sponsored space, Andrea shares his quest for "ultimate" IT knowledge, meticulously brought to you in an easy to read format.

Posted in DBs, DEV | No Comments »

my favourite "mysqldump" options.

November 15th, 2015 by Andrea Matesi
mysqldump

mysqldump

MySQL Tools & Co. are very nice and smart toys, 'though sometimes DB Admins like to overcomplicate things that should be easy and simple.

Over time and experience, I developed a specific taste for the following mysqldump options:

mysqldump -u root -p - -add-drop-databases - -add-drop-table - -databases DB_NAME > filename.sql

Beware of the dreaded "- -".

This command simply dumps ALL the DB data into a single file, but the best part is the "--databases" option, that enables (even if dumping a single DB), the insertion of the “CREATE DATABASE DB_NAME” statements (very useful indeed!).

A pwrusr's most common operation: I want to dump a "whole db" (why?) -> I want to import it as a whole into another place, and that's it!

 

[BONUS] copy database from one server to another:

mysqldump –user=root –password=P@ssw0rd - -add-drop-databases - -add-drop-table - -databases DB_NAME | mysql -h my-remote-host-3 –user=root –password=remote-host-3-mysql-password DB_NAME

Automate backups to a specific user for cron-enabled dumps.

1) Create the bck-usr on mysql.

2) Grant some permissions to the user needed to perform the automated backups.

GRANT SELECT,LOCK TABLES ON mydb.* TO bck-usr@pwrusr.com
flush privileges;

3) Put your script into crontab!

Senior Professional Network and Computer Systems Engineer during work hours and father when home.

Andrea strives to deliver outstanding customer service and heaps of love towards his family.

In this Ad-sponsored space, Andrea shares his quest for "ultimate" IT knowledge, meticulously brought to you in an easy to read format.

Posted in DBs, DEV | No Comments »