ohazot_MySQL

       _                    _
  ___ | |__   __ _ _______ | |_
 / _ \| '_ \ / _` |_  / _ \| __|
| (_) | | | | (_| |/ / (_) | |_
 \___/|_| |_|\__,_/___\___/ \__|

MySQL — usage and tips

COMMANDS

show databases;
show tables;
show tables like '%pattern%'

USERS

CREATE DATABASE db_name;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE;
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
->   ON db_name.*
->   TO 'username'@'localhost';

EXECUTE

Executing commands on mysql from a file.

$ cat FILE | sudo mysql

READ

Use a SQL file to update database.

$ sudo mysql DATABASE <
  FILE.sql

BACKUP

Backup a database:

# mysqldump -p DATABASE >
  FILE.sql

The argument -p specifies a password, if not given, mysqldump prompts the user to enter a password.

Compress backup

# mysqldump DATABASE | gzip >
  FILE.sql.gz

TIMEZONES

Adding timezones to mysql:

$ mysql_tzinfo_to_sql
  /usr/share/zoneinfo | sudo mysql mysql

AUTHORS

ohazot | about | ohazot.com <admin@ohazot.com>