Master MySQL: From Basics to Advanced Operations and Essential Tools
This comprehensive guide introduces MySQL fundamentals, walks through installation on Windows and Linux, explains core commands for connecting, creating databases, managing users and privileges, performing table CRUD operations, handling data types, and provides a curated list of analysis, backup, performance, HA, and GUI tools for MySQL.
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+DDatabase Management
Show databases, create a database, and select a database:
show databases;create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf8 encodingcreate database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk encodinguse 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 tablesselect * from table_name; # view all rowsCreating 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 columnalter table table_name drop column column_name; # drop columnalter table table_name modify column column_name type; # change typealter table table_name change old_name new_name type; # rename columnalter table table_name add primary key (column_name); # add primary keyalter table table_name drop primary key; # drop primary keyalter table table_name add constraint fk_name foreign key (column) references parent_table(parent_column); # add foreign keyalter table table_name drop foreign key fk_name; # drop foreign keyChanging 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 rowsdelete 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‑64tinyint[(M)] [unsigned] [zerofill] # -128..127 or 0..255int[(M)] [unsigned] [zerofill] # -2147483648..2147483647 or 0..4294967295bigint[(M)] [unsigned] [zerofill] # -9.22e18..9.22e18 or 0..1.84e19decimal[(M,D)] [unsigned] [zerofill] # exact numeric, M≤65, D≤30float[(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 charsvarchar(M) # variable‑length string, up to 255 charstext, mediumtext, longtext # large variable‑length stringsenum('value1','value2',...) # enumeration, up to 65,535 distinct valuesset('a','b','c',...) # set, up to 64 membersdate # YYYY‑MM‑DDtime # HH:MM:SSyear # YYYYdatetime # YYYY‑MM‑DD HH:MM:SStimestamp # seconds since 1970‑01‑01MySQL 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.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
