MySQL Admin

[ Setup ]

Install: apt-get install mysql-server mysql-client

Config Files: /etc/mysql

Database Dir: /var/lib/mysql

Tmp Dir: /tmp

Log Dir: /var/log/mysql/

Process: /usr/sbin/mysqld (pid file /var/run/mysqld/mysqld.pid)

TCP Socket:  tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 859/mysqld

Unix Socket: [ ACC ] STREAM LISTENING 17989 859/mysqld /var/run/mysqld/mysqld.sock

[ /etc/mysql/my.cnf ]

[mysqld]  #settings for the mysql server.

basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

socket = /var/run/mysqld/mysqld.sock　　#support local socket connection
port = 3306　　#support TCP/IP connection

bind-address = 127.0.0.1  # can be removed to bind to all the interfaces

default-storage-engine=INNODB　　#default table type

character_set_server = utf8 

collation_server = utf8_general_ci

[ ~/.my.cnf (chmod 600) ]

Note: for convenient only

[client]

user=username

password=xxx

[mysql]

database=mydatabase 

[ CLI - mysql ]

Default: mysql -u root -p

CLI Options

example: > mysql -u root -p -h 127.0.0.1 --default-character-set=utf8 decloud < backupfile.sql

-u name (or --user=name): to specify the user name.

-p (or password="xxx"): to give password.

-h <ip/hostname> (or -host=ip/hostname):  to specificy the ip of the mysql db server.

--default-character-set=<name>: utf8 or latin1 or latin2 or cp850

<default database>: e.g decloud

< command-file: automatically execute sql commands in a command-file, like a backup file by mysqldump (e.g. backupfile.sql).

CLI Commands

status : show status info (of the sql server).

show engines; : show info about supported engines (table types) 

show engine <engin_name like innodb> <status/logs/...> : show detailed info about an engine.

show charset; show collation; : show supported char set and collation.

show privileges; show grants;  : show priviledge related info.

show [global|session] variables [like '%keyword%']; : show system variables. e.g. show variables like '%char%';

important variable: back_log, %char%, connect_timeout,  join_buffer_size, key_buffer_size, log_bin, log_update, long_query_time, etc...

set [global|session] <variable>=<value>; : set a value to a system variable;

set names <utf8/cp850>: set system variables character_set_client/character_set_results/character_set_connection to utf8 (for linux) or cp850 (for windows)

charset <utf8/latin1/...>: change the charset.

show open tables; : show the table opened in cache;

show processlist; : show running processes.

show errors; show warnings; : show errors / warnings;

show status; : show detailed status info.

show databases; : show all the data bases.

show create databse <dbname>; : show the SQL command used to create a DB.

use <database> : change the current database.

show tables; : show all the tables in current database.

show create table <tablename>; : show the SQL command used to create a table.

show columns from <tablename>; : show column information.

show index from <tablename>; : show index information.

show table status; : 

show triggers; : show all the triggers

tee [filename] / notee: start/stop logging all the input and output into a specified file.

source <filename>: execute a batch of commands in a script file.

system <shell-cmd>: execute a shell command.

help: give a list of available commands.

exit/quit: close the mysql.

↑ / ↓ : command history.

<Ctrl-c> + RETURN : terminate the current line (of input).

<Ctrl+k> : deleting a line from the cursor location to the end

<Ctrl+y> : restoring the most recently deleted text 

[ CLI - mysqladmin ]

CLI Options: most the same as for mysql, like -u -h -p, etc.

CLI Commands:

mysqladmin -u root -p password "new password": to change the the password for 'root.

mysqladmin -u root -p create decloud: to create a new db named decloud.　　

mysqladmin -u root -p drop decloud: to drop (delete) a db name decloud.

mysqladmin -i 5 ping: to check if mysql is still alive.

mysqladmin status: to check the status of mysql.

mysqladmin -u root -p processlist 

[ CLI - mysqldump ]

CLI options: most the same as for mysql, like -u -h -p, etc.

CLI Commands:

mysqldump -u root -p decloud > backupfile.sql: to backup the decloud (including table structres and all the data).

[ GUI Tools ]

phpMyAdmin

MySQL Administrator, MySQL Query Browser, etc.

[ DB Management ]

DB Storage

normally in /var/lib/mysql

DB Backup

mysqldump, mysqlcopy, replication

e.g. mysqldump -u root --password=xxx dbname | mysql -u root --password=yyy -h destinationhost dbname -> migrate data from one server to another.

Logging

[general]

Logging slows down the operation of MySQL considerably. According to the MySQL documentation, binary logging slows operation of the MySQL server by only one percent. Really?

[log files]

By default, the logging files are stored in the same directory in which the directories of the various MySQL databases are located.

If you value maximum speed and security, the logging files should reside on a different hard drive from that on which the database files are located.

config-file - mysqld - log_bin: for the update (binary) logging. 

The update (binary) log can be viewed with the auxiliary program 'mysqlbinlog'.

config-file - mysqld - log_error: for the server errors (and other information server events).

config-file - mysqld - log: general query log (to log every login, connection and command).

config-file - mysqld - log_slow_queries, long_query_time, log-queries-not-using-indexes: to log performance issue (long query time).

mysqladmin flush-logs (or SQL command FLUSH LOGS): close the currently active logging file and begin a new one.

To delete logs: 'PURGE MASTER LOGS TO' or 'RESET MASTER'.

Halting Logging Temporarily: SET SQL_LOG_BIN=0 / 1. 

Replication

For purpose of security, speed and backup.

At present only support master/slave replication. And not support fail-safe replication yet.

Setup Replication Master:

Set up a user (in master system), with replication slave privilege (so can access the binary log).

...

[ DB Design ]

Main Table Types

MyISAM, InnoDB, HEAP.

HEAP: only in RAM (not on hard disk), with quite some constraint, mainly used as temporary table.

Note Eric: It seems like InnoDB performances better only in the situation of mass concurrent write operations. And MyISAM does not support transaction.

Other Tale Types: Compressed (ARCHIVE), etc.

Data Types

TINYINT/BOOL(m) (8 bits), SMALLINT(m) (16 bits), INT/INTEGER(m) (32 bits), BIGINT(m) (64 bits), SERIAL(bigint auto_increment not null primary key), BIT(n)

m: desired column width (in selection results).

FLOAT(m, d) (32 bits), DOUBLE/REAL (64 bits), DECIMAL/NUMBER/DEC(p,s) (fixed point number)

DATE (24 bits), TIME (24 bits), DATETIME (64 bits), YEAR (8 bits), TIMESTAMP

CHAR(n), VARCHAR(n) (n<65535), TINYTEXT(<=255 byts), MEDIUMTEXT, LONGTEXT

BIT(n) (n<64), TINYBLOB (<255 bytes), BLOB, MEDIUMBLOB, LONGBLOB

Attributes: NULL, NOT NULL, DEFAULT xxx, DEFAULT CURRENT_TIMESTAMP, ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY, AUTO_INCREMENT, UNSIGNED, CHARACTER SET name [COLLATE sort]

Rules for Good DB Design

Tables should not contain redundant (repetitive) data.

Tables should not have columns like order1, order2, order3.

The storage requirements for all your tables should be as small as possible.

Frequently required database queries should be able to be executed simply and efficiently. 

Always consider: What types of queries will occur most frequently? Will data be frequently changed? Optimizing read or write operations?

Normalization Rules

Columns with similar content must be eliminated.

A table must be created for each group of associated data.

Each data record must be identifiable by means of a primary key.

Whenever the contents of columns repeat themselves, this means that the table must be divided into several subtables. And these tables must be linked by foreign keys.

Columns that are not directly related to the primary key must be eliminated (that is, trans-planted into a table of their own).

Pros: nice data consistency, good storage usage, can support flexible queries.

Cons: Query efficiency may be not very high (sometimes data redundancy is good for query speed).

Relations

1:1 : Pros query speed and security; Cons data consistency

1:n : The linkage takes place via key fields.

n:m : it is necessary to add an auxiliary table to the two original tables so that the n:m relation can be reduced to two 1:n relations.

Primary and Foreign Keys

The job of the primary key is to locate, as fast as possible, a particular data record in a table. (MySQL creates an index to enable rapid search?)

 CREATE TABLE publishers (publID INT NOT NULL AUTO_INCREMENT, othercolumns ..., PRIMARY KEY (publID)) 

The primary key must be unique. The primary key should be compact.

With most database systems it has ecome standard practice to use a 32- or 64-bit integer as primary key field, generated automatically in sequence (1, 2, 3, ...) by the database system.

The task of the foreign key field is to refer to a record in the detail table.

FOREIGN KEY [name] (column1) REFERENCES table2 (column2)
  [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
  [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

 ALTER TABLE tablename DROP FOREIGN KEY foreign_key_id 

Foreign key constraints: to keep data integrity. 

To disable/enable the auto-checking of integrity rules: SET foreign_key_checks=0/1

Index

If you are searching for a particular record in a table or would like to create a series of data records for an ordered table, MySQL must load all the records of the table. With large tables, performance will suffer under such everyday queries. Fortunately, there is a simple solution to cure our table’s performance anxiety: Simply use an index for the affected column.

Indexes are not a panacea! They speed up access to data, but they slow down each alteration in the database.

Ordinary Index, Unique Index, Primary Index, Foreign Key Index, Combined Indexes, 

Limits on Index Length, Full Text Index

Views

Views act like virtual tables containing the result of a SELECT query.

CREATE VIEW v2 AS
SELECT title, publname, catname FROM titles, publishers, categories
WHERE titles.publid=publishers.publid
AND titles.catID = categories.catID
AND langID=2;

Whether the commands INSERT, UPDATE, and DELETE can be used with a view (that is, whether the view is updatable) depends on the underlying SELECT command. E.g Views that process data from more than one table are almost always unchangeable; and etc...

Note Eric: mostly used for convenient.

Stored Procedures

Stored procedures are a collection of SQL commands that are stored and executed in the MySQL server.

Pros: possible Greater speed; Avoidance of code redundancy, better maintenance;  Increase in database security.

Cons: porting to another RDBMS, 

Triggers

Triggers make it possible to execute a set of SQL commands or a stored procedure automatically after or before INSERT, UPDATE, or DELETE commands.

[ DB Performance & Optimization ]

Using EXPLAIN SELECT command. 

[Reference]

MySQL常用命令

MySQL快速教程