MySQL¶

"MySQL is an open-source relational database management system. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language. The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, and is now owned by Oracle Corporation." - https://en.wikipedia.org/wiki/MySQL

Examples¶

Show variables of the running server¶

mysqladmin variables

Enable bin logging¶

Edit /etc/my.cnf:

log-bin=/var/lib/mysql/mysql-bin

Show how a table was created¶

SHOW CREATE TABLE table_name \G

Create a table¶

CREATE TABLE photo_sizes (
  `photo_id` char(32) NOT NULL,
  `format` mediumtext,
  `width` mediumtext,
  `height` mediumtext,
  `source` mediumtext,
  `url` mediumtext,
  PRIMARY KEY(`photo_id`)
) ;

Create a table with multiple columns as the primary key¶

CREATE TABLE `photo_sizes` (
  `photo_id` char(32) NOT NULL,
  `format` char(32) NOT NULL DEFAULT '',
  `width` mediumtext,
  `height` mediumtext,
  `source` mediumtext,
  `url` mediumtext,
  PRIMARY KEY (`photo_id`,`format`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Show what processes are running¶

show processlist;

Dump databases to sql files¶

All databases

mysqldump -u root -phunter2 --all-databases | gzip -9 > ~/$(date +%F-%H%m).sql.gz

Or just a single database

mysqldump -u root -phunter2 my_favorite_db | gzip -9 > ~/my_favorite_db-$(date +%F-%H%m).sql.gz

Duplicate a database¶

sudo mysqldump -v mogilefs | sudo mysql -D mogilefs_sjc

Dump the schema of a database with no actual data¶

sudo mysqldump --no-data dbname > schema.sql

Show privileges¶

show GRANTS ;

Create a new user¶

CREATE USER 'a_new_user'@'10.0.5.%' IDENTIFIED BY 'the_user_password';
GRANT ALL PRIVILEGES ON some_database.* TO 'a_new_user'@'10.0.5.%' WITH GRANT OPTION;

Delete a user¶

DELETE from mysql.user where user = 'user_name';

Grant Privileges¶

GRANT ALL ON database.* TO 'newuser'@'localhost';

Change root password¶

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h hostname password 'new-password'

or...

UPDATE mysql.user
  SET Password=PASSWORD('hunter2')
  WHERE User='leroy_jenkins'
  AND Host='localhost' ;

Create statements¶

Create an index on table images for column rating_count¶

create index rating_count on images (rating_count) ;

Drop an index from a table¶

drop index rating_count on images ;

Table Alters¶

Add a column¶

alter table flixplor add o_width char(12);

Drop a column¶

alter table flixplor drop column o_width;

Change the type of a column¶

alter table flixplor modify o_height mediumint ;

Add a current timestamp column¶

alter table images add last_updated timestamp not null default current_timestamp on update current_timestamp;

Change the table engine to innodb¶

ALTER TABLE images ENGINE=INNODB;

Change a table's encoding¶

alter table raw_flickr_data CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

Table Inserts¶

Add a record¶

Table Updates¶

Update if key exists¶

For unique fields, you cannot insert, so you need to have an on duplicate key section in your statement.

INSERT INTO photo_sizes (
  photo_id,
  format,
  height,
  width,
  url,
  source
) values (
  '32704962848',
  'Medium 640',
  '427',
  '640',
  'https://www.flickr.com/photos/warzauwynn/32704962848/sizes/z/',
  'https://farm5.staticflickr.com/4855/32704962848_3a96b4c635_z.jpg'
) ON DUPLICATE KEY UPDATE
  height='427',
  width='640',
  url='https://www.flickr.com/photos/warzauwynn/32704962848/sizes/z/',
  source='https://farm5.staticflickr.com/4855/32704962848_3a96b4c635_z.jpg'

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Update a datetime column with a random datetime¶

UPDATE flixplor
SET last_retrieval = subtime(
  concat_ws(' ','2019-01-01' - interval rand() * 10000 day ,'00:00:00'), -- create a random YYYY-MM-DD within 10k days of given datetime
  sec_to_time(floor(0 + (rand() * 86401))) -- create a random HH:MM:SS within a 24 hour period
)
WHERE last_retrieval < '2019-01-01 00:00:00';

Table Selects¶

Select values and don't show duplicates¶

SELECT col from servers group by col ;

Select photo_id and discard duplicates (uniq)¶

SELECT photo_id from photo_sizes group by photo_id ;

Select and count unique pairs of columns¶

SELECT model, unit, count(*) as n from servers group by model, unit having n > 1 order by model asc ;

Select the count of rows in a table¶

SELECT count(*) from flixplor where o_height > 100 ;

Do some math to create a new column during a select¶

SELECT photo_id,last_retrieval,o_height,o_width,(o_height * o_width) as pixels from flixplor
where last_reposted < from_unixtime('1384268667') or last_reposted is NULL
order by (o_height * o_width) limit 10 ;

Transform datetime into a date diff¶

This selects the number of hours since the given datestamp instead of the datestamp itself.

SELECT TIMESTAMPDIFF(HOUR, date_taken, NOW()) from photos ;

Statement explanations¶

The EXPLAIN statement can give you additional info about how complex your statement is.

Explain select¶

mysql> explain SELECT *,(rating_sum / rating_count) as average from images where (rating_sum / rating_count) > 20 or rating_count=0 ORDER BY RAND() LIMIT 1 ;
+----+-------------+--------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+--------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | images | ALL  | rating_count  | NULL | NULL    | NULL | 301937 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

Misc¶

Complete statement with \G for different output format
ERROR 1045 (28000) may be caused by invalid hostname in connect command. Replace the --host token with the full hostname of the db server. Or, restart mysql and try again.