MySQL系列（一）

一、简介

MySQL是最流行的开放源码SQL数据库管理系统，它是由MySQL AB公司开发、发布并支持的。有以下特点：

MySQL是一种数据库管理系统。

MySQL是一种关联数据库管理系统。

MySQL软件是一种开放源码软件。

MySQL数据库服务器具有快速、可靠和易于使用的特点。

MySQL服务器工作在客户端/服务器模式下，或嵌入式系统中。

有大量可用的共享MySQL软件。

MySQL表最大能达到多少？

InnoDB存储引擎将InnoDB表保存在一个表空间内，该表空间可由数个文件创建。这样，表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区，从而使得很大的表成为可能。表空间的最大容量为64TB。

二、安装MySQL

下载MySQL地址：http://dev.mysql.com/downloads/mysql/

**2.1.**CentOS 安装：

yum install mysql-server

2.2.Ubuntu 安装：

1sudo apt-get install mysql-server
2
3sudo apt-get isntall mysql-client
4
5sudo apt-get install libmysqlclient-dev
6
7# 检测是否安装成功（是否为LISTEN状态）
8
9sudo netstat -tap | grep mysql

2.3.编译安装MySQL-5.5.32：

1# 安装依赖包
2yum install ncurses-devel gcc gcc-c++ -y
3
4# 创建目录
5mkdir -p /home/oldsuo/tools
6# 安装cmake软件，gmake编译安装
7cd /home/oldsuo/tools/
8tar xf cmake-2.8.8.tar.gz
9cd cmake-2.8.8
10./configure
11#CMake has bootstrapped.  Now run gmake.
12gmake
13gmake install
14cd ../
15# 开始安装mysql
16# 创建用户和组
17groupadd mysql
18useradd mysql -s /sbin/nologin -M -g mysql
19
20# 解压编译MySQL
21tar zxf mysql-5.5.32.tar.gz 
22cd mysql-5.5.32
23cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
24-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
25-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
26-DDEFAULT_CHARSET=utf8 \
27-DDEFAULT_COLLATION=utf8_general_ci \
28-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
29-DENABLED_LOCAL_INFILE=ON \
30-DWITH_INNOBASE_STORAGE_ENGINE=1 \
31-DWITH_FEDERATED_STORAGE_ENGINE=1 \
32-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
33-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
34-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
35-DWITH_FAST_MUTEXES=1 \
36-DWITH_ZLIB=bundled \
37-DENABLED_LOCAL_INFILE=1 \
38-DWITH_READLINE=1 \
39-DWITH_EMBEDDED_SERVER=1 \
40-DWITH_DEBUG=0
41
42#-- Build files have been written to: /home/oldsuo/tools/mysql-5.5.32
43提示: 编译时可配置的选项很多，具体可参考结尾附录或官方文档：
44make
45#[100%] Built target my_safe_process
46make install
47ln -s /application/mysql-5.5.32/ /application/mysql
48如果上述操作未出现错误，则MySQL5.5.32软件cmake方式的安装就算成功了。
49#拷贝配置文件
50cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf
51#添加变量，并使之生效
52echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
53source /etc/profile
54echo $PATH
55#授权用户及/tmp/临时文件目录
56chown -R mysql.mysql /application/mysql/data/
57chmod -R 1777 /tmp/
58
59#初始化数据库
60cd /application/mysql/scripts/
61./mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql
62cd ../
63
64#启动数据库
65cp support-files/mysql.server /etc/init.d/mysqld
66chmod +x /etc/init.d/mysqld
67/etc/init.d/mysqld  start
68
69#检查端口
70netstat -lntup|grep 3306

2.4编译安装完后一般安全操作：

2.4.1.删除不必要的用户和库：

1#查看用户和主机列，从mysql.user里查看
2select user,host from mysql.user;
3#删除用户名为空的库，并检查
4delete from mysql.user where user='';
5select user,host from mysql.user;
6
7#删除主机名为localhost.localdomain的库，并检查
8delete from mysql.user where host='localhost.localdomain';
9select user,host from mysql.user;
10
11#删除主机名为::1的库，并检查。::1库的作用为IPV6
12delete from mysql.user where host='::1';
13
14#删除test库
15drop database test;

2.4.2.添加额外管理员：

1# 添加额外管理员，system作为管理员，oldsuo为密码
2mysql> delete from mysql.user;
3Query OK, 2 rows affected (0.00 sec)
4mysql> grant all privileges on *.* to system@'localhost' identified by 'oldsuo' with grant option;
5Query OK, 0 rows affected (0.00 sec)
6# 刷新MySQL的系统权限相关表，使配置生效
7mysql> flush privileges;
8Query OK, 0 rows affected (0.00 sec)
9mysql> select user,host from mysql.user;
10+--------+-----------+
11| user   | host      |
12+--------+-----------+
13| system | localhost |
14+--------+-----------+
151 row in set (0.00 sec)
16mysql>

2.4.3.设置登录密码并开机自启：

1#设置密码，并登陆
2/usr/local/mysql/bin/mysqladmin -u root password 'oldsuo'
3mysql -usystem -p
4
5#开机启动mysqld，并检查
6chkconfig mysqld on
7chkconfig --list mysqld

三、字符集

3.1.字符集简介：

字符集，character set，就是一套表示字符的符号和这些的符号的底层编码；而校验规则，则是在字符集内用于比较字符的一套规则。简单的说，字符集就是一套文字符号及其编码、比较规则的集合，第一个计算机字符集ASC2，MySQL数据库字符集包括字符集和校对规则两个概念，字符集是定义数据库里面的内容字符串的存储方式，而校对规则是定义比较字符串的方式。

建议：中英文环境选择utf8

3.2查看设置字符集

1# 查看MySQL字符集设置情况
2show variables like 'character_set%';
3# 查看库的字符集
4show create database db;
5# 查看表的字符集
6show create table db_tb\G
7# 查询所有
8show collation;
9# 设置表的字符集
10set tables utf8;

3.3、MySQL数据乱码及解决方法

3.3.1系统方面

1cat /etc/sysconfig/i18n 
2LANG="zh_CN.UTF-8"

3.3.2客户端（程序），调整字符集为latin1

1mysql> set names latin1;        #临时生效
2Query OK, 0 rows affected (0.00 sec)
3更改my.cnf客户端模块的参数，实现set name latin1 的效果，并且永久生效。
4    [client]
5default-character-set=latin1

  #无需重启服务，退出登录就生效，相当于set name latin1。

3.3.3服务端，更改my.cnf参数

1[mysqld]
2default-character-set=latin1        #适合5.1及以前版本
3character-set-server=latin1         #适合5.5

3.3.4.库、表、程序

1#建表指定utf8字符集
2mysql> create database nick_defailtsss DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
3Query OK, 1 row affected (0.00 sec)

3.3.5.utf8字符集修改成GBK字符集的实际过程

1导出表结构
2#以utf8格式导出
3mysqldump -uroot -p --default-character-set=utf8 -d nick_defailt>alltable.sql
4--default-character-set=gbk        #表示已GBK字符集连接 –d 只表示表结构
5编辑alltable.sql 将utf8改成gbk。
6确保数据库不在更新，导出所有数据
7mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 nick_defailt>alldata.sql
8打开alldata.sql将set name utf8 修改成 set names gbk(或者修改系统的服务端和客户端)
9建库
10create database oldsuo default charset gbk;
11
12创建表，执行alltable.sql
13mysql -uroot -p oldsuo <alltable.sql
14导入数据
15mysql -uroot -p oldsuo <alltable.sql

四、存储引擎

MySQL最常用存储引擎Myisam和Innodb。mysql 5.5.5以后默认存储引擎为Innodb。

MySQL的每种引擎在MySQL里是通过插件的方式使用的，MySQL可以支持多种存储引擎。

建议：使用 Innodb引擎，因为支持回滚

4.1引擎对应系统文件

4.1.1.MyISAM引擎系统库表对应文件

1[root@mysql 3306]# ll /data/3306/data/mysql/
2-rw-rw----. 1 mysql mysql  10630 10月 31 16:05 user.frm            #保存表的定义
3-rw-rw----. 1 mysql mysql   1140 10月 31 18:40 user.MYD            #数据文件
4-rw-rw----. 1 mysql mysql   2048 10月 31 18:40 user.MYI            #索引文件
5
6[root@mysql 3306]# file data/mysql/user.frm 
7data/mysql/user.frm: MySQL table definition file Version 9
8[root@mysql 3306]# file data/mysql/user.MYD 
9data/mysql/user.MYD: DBase 3 data file (167514107 records)
10[root@mysql 3306]# file data/mysql/user.MYI
11data/mysql/user.MYI: MySQL MISAM compressed data file Version 1

4.1.2. InnoDB引擎

1[root@mysql 3306]# ll data/
2-rw-rw----. 1 mysql mysql 134217728 10月 31 20:05 ibdata1

4.2、修改引擎

1创建后引擎的修改
2语法：    ALTER TABLE student ENGINE = INNODB;
3         ALTER TABLE student ENGINE = MyISAM;
4mysql> use teacher;
5Database changed
6mysql> show create table student;
7+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8| Table   | Create Table                                                                                                                                                                                                                                                                    |
9+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10| student | CREATE TABLE `student` (
11  `id` int(4) NOT NULL AUTO_INCREMENT,
12  `name` char(20) NOT NULL,
13  `age` tinyint(2) NOT NULL DEFAULT '0',
14  `dept` varchar(16) DEFAULT NULL,
15  PRIMARY KEY (`id`),
16  KEY `index_name` (`name`)
17) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
18+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
191 row in set (0.01 sec)
20
21mysql> ALTER TABLE student ENGINE = MyISAM;
22Query OK, 3 rows affected (0.05 sec)
23Records: 3  Duplicates: 0  Warnings: 0
24
25mysql> show create table student;          
26+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
27| Table   | Create Table                                                                                                                                                                                                                                                                    |
28+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
29| student | CREATE TABLE `student` (
30  `id` int(4) NOT NULL AUTO_INCREMENT,
31  `name` char(20) NOT NULL,
32  `age` tinyint(2) NOT NULL DEFAULT '0',
33  `dept` varchar(16) DEFAULT NULL,
34  PRIMARY KEY (`id`),
35  KEY `index_name` (`name`)
36) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
37+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
381 row in set (0.00 sec)
39
404.3、建表指定引擎
41
42mysql> create table mess (                  
43    -> id int(4) not null,                 
44    -> name char(20) not null,
45    -> age tinyint(2) NOT NULL default '0',
46    -> dept varchar(16) default NULL
47    -> ) ENGINE=MyISAM CHARSET=utf8;
48Query OK, 0 rows affected (0.00 sec)

五、基本语句命令

运行相关：

5.1.0单实例mysql启动

1[root@localhost ~]# /etc/init.d/mysqld start
2Starting MySQL                                             [确定]
3#mysqld_safe –user=mysql &

5.1.1.查看MySQL端口

1[root@localhost ~]# ss -lntup|grep 3306
2tcp    LISTEN     0      50                     *:3306                  *:*      users:(("mysqld",19651,10))

5.1.3.查看MySQL进程

1[root@localhost ~]# ps -ef|grep mysql|grep -v grep
2root     19543     1  0 Oct10 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.localdomain.pid
3mysql    19651 19543  0 Oct10 ?        00:05:04 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/data/localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306

5.1.4.MySQL启动原理

1/etc/init.d/mysqld 是一个shell启动脚本，启动后最终会调用mysqld_safe脚本，最后调用mysqld服务启动mysql。
2        "$manager" \
3        --mysqld-safe-compatible \
4        --user="$user" \
5        --pid-file="$pid_file" >/dev/null 2>&1 &

5.1.5.关闭数据库

1[root@localhost ~]# /etc/init.d/mysqld stop
2Shutting down MySQL....                                    [确定]

5.1.6.查看mysql数据库里操作命令历史

cat /root/.mysql_history

5.1.7.强制linux不记录敏感历史命令

HISTCONTROL=ignorespace

5.1.8.mysql设置密码

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

5.1.9.mysql修改密码，与多实例指定sock修改密码

1mysqladmin -uroot -passwd password 'oldsuo'
2mysqladmin -uroot -passwd password 'oldsuo' -S /data/3306/mysql.sock

操作相关：

1登陆mysql数据库
2mysql -uroot –p
3查看有哪些库
4show databases;
5删除test库
6drop database test;
7使用test库
8use test;
9查看有哪些表
10show tables;
11查看suoning表的所有内容
12select * from suoning;
13查看当前版本
14select version();
15查看当前用户
16select user();
17查看用户和主机列，从mysql.user里查看
18select user,host from mysql.user;
19删除前为空，后为localhost的库
20drop user ""@localhost；
21刷新权限
22flush privileges;
23跳出数据库执行命令
24system ls;

六、破解mysql登录密码

6.1普通方式

1#> service mysqld stop
2#>mysqld_safe --skip-grant-tables &
3输入 mysql -uroot -p 回车进入
4>use mysql;
5> update user set password=PASSWORD("newpass")where user="root";
6更改密码为 newpassord
7> flush privileges; 更新权限
8> quit 退出
9service mysqld restart
10mysql -uroot -p新密码进入

6.2普通方式的简写

1service mysqld stop
2mysqld_safe --skip-grant-tables --user=mysql &
3mysql
4update mysql.user set password=PASSWORD("newpass")where user="root" and host='localhost';
5flush privileges;
6mysqladmin -uroot -pnewpass shutdown
7/etc/init.d/mysqld start
8mysql -uroot -pnewpass        #登陆

6.3多实例方式

1killall mysqld
2mysqld_safe –defaults-file=/data/3306/my.cnf –skip-grant-table &
3mysql –u root –p –S /data/3306/mysql.sock        #指定sock登陆
4update mysql.user set password=PASSWORD("newpass")where user="root";
5flush privileges;
6mysqladmin -uroot -pnewpass shutdown
7/etc/init.d/mysqld start
8mysql -uroot -pnewpass        #登陆