Master MySQL: Installation, Configuration, Charset & Engine Guide

Overview

MySQL is the most popular open‑source SQL database management system, known for its speed, reliability, client/server architecture, and extensive community support.

Table Size Limit

InnoDB stores tables in a tablespace that can span multiple files, allowing a maximum tablespace size of 64TB.

Installation on Linux

CentOS : yum install mysql-server Ubuntu :

sudo apt-get install mysql-server

sudo apt-get install mysql-client

sudo apt-get install libmysqlclient-dev

Verify installation:

sudo netstat -tap | grep mysql

Compiling MySQL 5.5.32 from Source

Install build dependencies: yum install ncurses-devel gcc gcc-c++ -y Create working directory: mkdir -p /home/oldsuo/tools Install CMake and compile:

cd /home/oldsuo/tools/

tar xf cmake-2.8.8.tar.gz

cd cmake-2.8.8

./configure

gmake

gmake install

Prepare MySQL user and group:

groupadd mysql

useradd mysql -s /sbin/nologin -M -g mysql

Extract source and configure with extensive options:

tar zxf mysql-5.5.32.tar.gz
cd mysql-5.5.32
cmake . \
  -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
  -DMYSQL_DATADIR=/application/mysql-5.5.32/data \
  -DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
  -DDEFAULT_CHARSET=utf8 \
  -DDEFAULT_COLLATION=utf8_general_ci \
  -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
  -DENABLED_LOCAL_INFILE=ON \
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \
  -DWITH_FEDERATED_STORAGE_ENGINE=1 \
  -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
  -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
  -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
  -DWITH_FAST_MUTEXES=1 \
  -DWITH_ZLIB=bundled \
  -DWITH_READLINE=1 \
  -DWITH_EMBEDDED_SERVER=1 \
  -DWITH_DEBUG=0

Build and install:

make

make install

ln -s /application/mysql-5.5.32/ /application/mysql

Copy configuration and set environment:

cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf

echo 'export PATH=/application/mysql/bin:$PATH' >> /etc/profile

source /etc/profile

Set ownership and permissions:

chown -R mysql.mysql /application/mysql/data/

chmod -R 1777 /tmp/

Initialize the database:

cd /application/mysql/scripts/

./mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql

Start MySQL and enable at boot:

cp support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

/etc/init.d/mysqld start

chkconfig mysqld on

Post‑Installation Security

Remove unnecessary users and the default test database:

select user,host from mysql.user;
delete from mysql.user where user='';
delete from mysql.user where host='localhost.localdomain';
delete from mysql.user where host='::1';
drop database test;

Add an administrative user:

grant all privileges on *.* to system@'localhost' identified by 'oldsuo' with grant option;
flush privileges;

Set root password and enable automatic start:

/usr/local/mysql/bin/mysqladmin -u root password 'oldsuo'
chkconfig mysqld on

Character Set Management

MySQL character sets define how strings are stored; collations define comparison rules. It is recommended to use utf8 for both Chinese and English environments.

Common commands:

show variables like 'character_set%';

show create database db;

show create table db_tb\G;

show collation;

set tables utf8;

Converting a Database from UTF‑8 to GBK

Export table definitions:

mysqldump -uroot -p --default-character-set=utf8 -d nick_defailt > alltable.sql

Edit alltable.sql to replace utf8 with gbk.

Export data:

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 nick_defailt > alldata.sql

Replace set names utf8 with set names gbk in alldata.sql.

Create a new GBK database: create database oldsuo default charset gbk; Import schema: mysql -uroot -p oldsuo < alltable.sql Import data:

mysql -uroot -p oldsuo < alldata.sql

Storage Engines

MySQL supports multiple storage engines; the default since 5.5.5 is InnoDB, which provides transactions and crash recovery. MyISAM stores data in .MYD and index files .MYI, while InnoDB uses ibdata1 and related files.

Change engine of an existing table:

ALTER TABLE student ENGINE = INNODB;
ALTER TABLE student ENGINE = MyISAM;

Create a table with a specific engine:

CREATE TABLE mess (
  id int(4) NOT NULL,
  name char(20) NOT NULL,
  age tinyint(2) NOT NULL DEFAULT '0',
  dept varchar(16) DEFAULT NULL
) ENGINE=MyISAM CHARSET=utf8;

Essential MySQL Commands

Start service: /etc/init.d/mysqld start Stop service: /etc/init.d/mysqld stop Check listening port: ss -lntup|grep 3306 View process: ps -ef|grep mysql|grep -v grep Set root password: /usr/local/mysql/bin/mysqladmin -u root password 'oldsuo' Change password: mysqladmin -uroot -pnewpass List databases: show databases; Drop database: drop database test; Show tables: show tables; Query version:

select version();

Recovering a Forgotten MySQL Root Password

Method 1 – Single instance:

service mysqld stop
mysqld_safe --skip-grant-tables &
mysql -uroot -p
use mysql;
update user set password=PASSWORD('newpass') where user='root';
flush privileges;
service mysqld restart
mysql -uroot -pnewpass

Method 2 – Shortcut:

service mysqld stop
mysqld_safe --skip-grant-tables --user=mysql &
mysql
update mysql.user set password=PASSWORD('newpass') where user='root' and host='localhost';
flush privileges;
mysqladmin -uroot -pnewpass shutdown
/etc/init.d/mysqld start
mysql -uroot -pnewpass

Method 3 – Multi‑instance:

killall mysqld
mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables &
mysql -u root -S /data/3306/mysql.sock
update mysql.user set password=PASSWORD('newpass') where user='root';
flush privileges;
mysqladmin -uroot -pnewpass shutdown
/etc/init.d/mysqld start
mysql -uroot -pnewpass