Master MySQL: From Basics to Advanced Operations and Essential Tools

MySQL Overview

Database (DB) is a structured data repository that has evolved over the past six decades to support a wide range of applications, from simple spreadsheets to massive data‑storage systems. MySQL is an open‑source relational database management system (RDBMS) that uses SQL for data manipulation and is widely used in web applications.

Installation

To use MySQL you need to install both the server and the client, then connect the client to the server to execute commands such as create, read, update, and delete (CRUD).

Install MySQL server

Install MySQL client

Connect client to server

Send commands to the MySQL service (e.g., INSERT, SELECT, UPDATE, DELETE)

Download address: http://dev.mysql.com/downloads/mysql/

Installation guides:

Windows installation: http://www.cnblogs.com/lonelywolfmoutain/p/4547115.html

Linux installation: http://www.cnblogs.com/chenjunbiao/archive/2011/01/24/1940256.html

Basic Operations

Connecting to MySQL mysql -u user -p # example: mysql -u root -p Common error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.

Exit the connection:

QUIT   # or press Ctrl+D

Database Management

Show databases, create a database, and select a database:

show databases;

create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;   # utf8 encoding

create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;   # gbk encoding

use db1;

Show tables in the current database:

show tables;

User Management

Create a user:

create user 'username'@'IP_address' identified by 'password';

Delete a user: drop user 'username'@'IP_address'; Rename a user:

rename user 'username'@'IP_address' to 'new_username'@'IP_address';

Change a user's password:

set password for 'username'@'IP_address' = Password('new_password');

Note: user privilege data is stored in the mysql.user table, but direct manipulation is not recommended.

Privilege Management

MySQL privileges include ALL, SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, GRANT, REVOKE, etc. Examples:

grant all privileges on db1.* to 'username'@'IP_address';

grant select on db1.* to 'username'@'IP_address';

revoke select on db1.tb1 from 'username'@'IP_address';

View privileges:

show grants for 'username'@'IP_address';

Table Operations

Viewing tables

show tables;   # list all tables

select * from table_name;   # view all rows

Creating a table

create table table_name ( column1 type [NULL|NOT NULL], column2 type, ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Example:

CREATE TABLE `tab1` ( `nid` int(11) NOT NULL auto_increment, `name` varchar(255) DEFAULT 'zhangyanlin', `email` varchar(255), PRIMARY KEY (`nid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Key points: default values, auto‑increment columns (must be indexed), primary keys (unique, non‑null).

Deleting a table drop table table_name; Truncating a table truncate table table_name; Modifying a table

alter table table_name add column_name type;                # add column

alter table table_name drop column column_name;           # drop column

alter table table_name modify column column_name type;    # change type

alter table table_name change old_name new_name type;    # rename column

alter table table_name add primary key (column_name);     # add primary key

alter table table_name drop primary key;                # drop primary key

alter table table_name add constraint fk_name foreign key (column) references parent_table(parent_column);   # add foreign key

alter table table_name drop foreign key fk_name;         # drop foreign key

Changing default values

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

Data Manipulation (CRUD)

Insert

insert into table_name (col1, col2) values ('value1', 'value2');

insert into table_name (col1, col2) values ('v1','v2'),('v3','v4');

Delete

delete from table_name;   # delete all rows

delete from table_name where id=1 and name='zhangyanlin';

Update

update table_name set name='zhangyanlin' where id>1;

Select

select * from table_name;

select * from table_name where id>1;

select nid, name, gender as gg from table_name where id>1;

Common query clauses:

WHERE – filter rows (e.g., where id>1 and name!='aylin')

BETWEEN , IN , NOT IN

LIKE – pattern matching with % and _ LIMIT – restrict result set (e.g., limit 5 or limit 4,5)

ORDER BY – sort results (ASC/DESC)

GROUP BY – aggregate rows, often used with COUNT, SUM, MAX, MIN HAVING – filter groups

Basic Data Types

MySQL data types are divided into numeric, date/time, and string categories.

bit[(M)]                # binary bit, length 1‑64

tinyint[(M)] [unsigned] [zerofill]   # -128..127 or 0..255

int[(M)] [unsigned] [zerofill]       # -2147483648..2147483647 or 0..4294967295

bigint[(M)] [unsigned] [zerofill]    # -9.22e18..9.22e18 or 0..1.84e19

decimal[(M,D)] [unsigned] [zerofill] # exact numeric, M≤65, D≤30

float[(M,D)] [unsigned] [zerofill]   # single‑precision floating point (approximate)

double[(M,D)] [unsigned] [zerofill]  # double‑precision floating point (approximate)

char(M)                # fixed‑length string, up to 255 chars

varchar(M)            # variable‑length string, up to 255 chars

text, mediumtext, longtext   # large variable‑length strings

enum('value1','value2',...)   # enumeration, up to 65,535 distinct values

set('a','b','c',...)        # set, up to 64 members

date      # YYYY‑MM‑DD

time      # HH:MM:SS

year      # YYYY

datetime  # YYYY‑MM‑DD HH:MM:SS

timestamp # seconds since 1970‑01‑01

MySQL Resource Collection

Analysis Tools

Anemometer – SQL slow‑query monitor.

innodb‑ruby – InnoDB file parser for Ruby.

innotop – MySQL “top” with many features.

pstop – Top‑like tool that aggregates performance_schema data.

mysql‑statsd – Python daemon that sends MySQL metrics to StatsD/Graphite.

Backup Tools

MyDumper – Parallel logical backup/dump.

MySQLDumper – Web‑based backup tool.

mysqldump‑secure – Encrypted, compressed, logged mysqldump script.

Percona Xtrabackup – Hot backup utility.

Performance Testing

iibench‑mysql – Java‑based insert performance tester.

Sysbench – Modular, cross‑platform multi‑threaded benchmark.

High‑Availability (HA)

Galera Cluster – Synchronous multi‑master clustering.

MHA – MySQL high‑availability manager.

MySQL Fabric – Scalable framework for managing server farms.

Percona Replication Manager – Asynchronous replication manager.

Proxy Solutions

MaxScale – Open‑source database‑centric proxy.

Mixer – Go‑based MySQL sharding proxy.

MySQL Proxy – Simple program between client and server.

ProxySQL – High‑performance MySQL proxy.

Replication Tools

orchestrator – Visual replication topology manager.

Tungsten Replicator – High‑performance open‑source replication engine.

Additional Utilities

go‑mysql – Pure Go library for MySQL protocol and replication.

MySQL Utilities – Python command‑line utilities for maintenance.

Percona Toolkit – Advanced command‑line tools for complex tasks.

openark kit – Python tools for routine maintenance.

UnDROP – Recover data from dropped or corrupted InnoDB tables.