Mysql数据库理论基础一：编译安装
一、简介
由MySQL AB公司开发，是最流行的开放源码SQL数据库管理系统，主要特点：
1、是一种数据库管理系统
2、是一种关联数据库管理系统
3、是一种开放源码软件，且有大量可用的共享MySQL软件
4、MySQL数据库服务器具有快速、可靠和易于使用的特点
5、MySQL服务器工作在客户端/服务器模式下，或嵌入式系统中
InnoDB存储引擎将InnoDB表保存在一个表空间内，该表空间可由数个文件创建。这样，表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区，从而使得很大的表成为可能。表空间的最大容量为64TB。
二、安装MySQL ( 依赖gcc gcc-c++ ncurses-devel openssl )
2.1、编译安装前准备
确认系统环境
[root@lamp mysql]# uname -a
Linux lamp 2.6.32-71.el6.x86_64 #1 SMP Wed Sep 1 01:33:01 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[root@lamp ~]# ll
-rw-r--r--. 1 root root 5691656 Mar 6 08:54 cmake-2.8.8.tar.gz
-rw-r--r--. 1 root root 24739429 Mar 6 09:04 mysql-5.5.28.tar.gz
[root@lamp ~]# rpm -qa gcc gcc-c++ openssl ncurses-devel
gcc-4.4.4-13.el6.x86_64
gcc-c++-4.4.4-13.el6.x86_64
ncurses-devel-5.7-3.20090208.el6.x86_64
openssl-1.0.0-4.el6.x86_64
安装前需确认开发环境组是否安装：Development Tools（开发工具）和Development Libraries（开发库）
[root@lamp ~]# yum groupinstall "Development Tools"
[root@lamp ~]# yum groupinstall "Development Libraries"
** mysql数据库随着时间的增长，数据会越来越大，所以应该把数据库数据放置在一个单独的可扩展的分区卷上，以便后期管理和备份，挂载在逻辑卷,方法如下：
[root@lamp ~]# fdisk /dev/sda #新建分区
aCommand (m for help): n
p
Partition number (1-4): 1
Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +10G
Command (m for help): T
Hex code (type L to list codes): 8e
Command (m for help): p
Device Boot Start End Blocks Id System
/dev/sdb1 1 1306 10490413+ 8e Linux LVM
Command (m for help): w
[root@lamp ~]# partprobe /dev/sda #通知内核重读sda分区
[root@lamp ~]# fdisk -l #查看分区
Device Boot Start End Blocks Id System
/dev/sdb1 1 2612 20980858+ 8e Linux LVM
[root@lamp ~]# pvcreate /dev/sda1 #先把/dev/sda1分区建立一个pv物理卷
Physical volume "/dev/sda1" successfully created
[root@lamp ~]# pvs #查看物理卷
PV VG Fmt Attr PSize PFree
/dev/sdb1 lvm2 a- 10.00g 10.00g
[root@lamp ~]# vgcreate myvg /dev/sda1 #以/dev/sda1分区创建myvg卷组
Volume group "myvg" successfully created
[root@lamp ~]# vgs #查看卷组
VG #PV #LV #SN Attr VSize VFree
myvg 1 0 0 wz--n- 10.00g 10.00g
[root@lamp ~]# lvcreate -n mydata -L 10G myvg #在卷组myvg中建立一个大小为10G，
名称为mydata 的逻辑卷
Logical volume "mydata" created
[root@lamp ~]# lvs #查看逻辑卷
LV VG Attr LSize Origin Snap% Move Log Copy% Convert
mydata myvg -wi-a- 10.00g
[root@lamp ~]# mke2fs -t ext4 /dev/myvg/mydata #格式化mydata逻辑卷
[root@lamp ~]# mkdir /mydata #建立一个目录作为挂载点
[root@lamp ~]# vim /etc/fstab #设定逻辑卷开机自动挂载
# /etc/fstab
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
/dev/myvg/mydata /mydata ext4 defaults 0 0 #新增该行内容
[root@lamp ~]# mount -a #重读/etc/fstab硬盘挂载文件，使得新增的分区挂载成功
[root@lamp ~]# mount #查看已经挂载的分区
...
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
/dev/mapper/myvg-mydata on /mydata type ext4 (rw) #逻辑卷mydata已经挂载到/mydata目录
[root@lamp ~]# mkdir /mydata/data
[root@lamp ~]# ll /mydata
总用量 20
drwxr-xr-x. 2 root root 4096 2月 17 17:30 data
drwx------. 2 root root 16384 2月 17 15:11 lost+found
[root@lamp ~]# groupadd -r mysql #-r建立一个系统组
groupadd: group 'mysql' already exists
[root@lamp ~]# useradd -r -g mysql -s /sbin/nologin mysql #建立一个系统用户不能登录
useradd: user 'mysql' already exists
[root@lamp ~]# id mysql #查看用户信息
uid=27(mysql) gid=27(mysql) groups=27(mysql)
[root@lamp ~]# chown -R mysql.mysql /mydata/data/ #更改文件夹属主和属组-R递归
[root@lamp ~]# ll /mydata
总用量 20
drwxr-xr-x. 2 mysql mysql 4096 2月 17 17:30 data
drwx------. 2 root root 16384 2月 17 15:11 lost+found
[root@lamp ~]# chmod o-rx /mydata/data/ #删除/data组其他人的读和执行权限
[root@lamp ~]# ll /mydata
总用量 20
drwxr-x---. 2 mysql mysql 4096 2月 17 17:30 data
drwx------. 2 root root 16384 2月 17 15:11 lost+found
2.2、编译安装cmake-2.8.8.tar.gz:
[root@lamp ~]# tar vxf cmake-2.8.8.tar.gz
[root@lamp ~]# cd cmake-2.8.8
[root@lamp cmake-2.8.8]# ./configure
[root@lamp cmake-2.8.8]# make && make install
...........
-- Installing: /usr/local/doc/cmake-2.8/ccmake.docbook
-- Installing: /usr/local/share/aclocal/cmake.m4 #编译安装cmake完成
2.3、使用cmake编译安装mysql5.5.28:
清理此前的编译所生成的文件，则需使用如下命令：make clean
[root@lamp ~]# tar vxf mysql-5.5.28.tar.gz
[root@lamp ~]# cd mysql-5.5.28
[root@lamp mysql-5.5.28]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
# -DCMKE_INSTALL_PREFIX=安装路径，-DMYSQL_DATADIR=数据存放路径，-DSYSCONFDIR=配置文件路径，
-DWITH_INNOBASE_STORAGE_ENGINE=1 (是否包含INNOBASE引擎1表示包含0表示不包含)，
-DWITH_ARCHIVE_STORAGE_ENGINE=1 (是否包含ARCHIVE引擎1表示包含0表示不包含)，
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 (是否包含BLACKHOLE空洞引擎1表示包含0表示不包含)，
.........
-- Performing Test HAVE_PEERCRED - Success
-- Configuring done
-- Generating done
-- Build files have been written to: /root/mysql-5.5.28 #编译完成
[root@lamp mysql-5.5.28]# make && make install
.......
-- Installing: /usr/local/mysql/man/man1/mysql.1
-- Installing: /usr/local/mysql/man/man1/mysql-test-run.pl.1
-- Installing: /usr/local/mysql/man/man8/mysqld.8 #安装mysql完成
2.4、配置mysql脚本让其开机自动启动及运行：
[root@lamp mysql-5.5.28]# chown -R :mysql /usr/local/mysql #更改组为mysql
[root@lamp mysql-5.5.28]# cd /usr/local/mysql
[root@lamp mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data #初始化mysql,指定用户和数据目录
Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h lamp password 'new-password'
Alternatively you can run:
./bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl
Please report any problems with the ./bin/mysqlbug script! #初始化完成
[root@lamp mysql]# mv /etc/my.cnf /etc/my.cnf.back #把原系统中配置文档改名
[root@lamp mysql]# cp support-files/my-large.cnf /etc/my.cnf #复制配置文档至/etc目录
[root@lamp mysql]# cp support-files/mysql.server /etc/init.d/mysqld #复制启动脚本至/etc/init.d目录中
[root@lamp mysql]# chkconfig --add mysqld #把mysqld启动脚本加到开机启动列表
[root@lamp mysql]# chkconfig --list mysqld #查看mysqld脚本是否开机启动
mysqld 0:off1:off2:on3:on4:on5:on6:off
[root@lamp mysql]# service mysqld start #启动mysql脚本
Starting MySQL... [ OK ]
[root@lamp mysql]# vim /etc/profile.d/mysql.sh #编辑环境变量使得mysql命令在PATH变量中
export PATH=$PATH:/usr/local/mysql/bin #新增该行内容
[root@lamp mysql]# . /etc/profile.d/mysql.sh #读取mysql.sh脚本的内容
[root@lamp mysql]# echo $PATH #输出PATH变量内容
/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin #PATH变量中的路径已生效
三、MySQL 启动及簡單应用：
[root@lamp mysql]# mysql #启动mysql客户端程序
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-log Source distribution
mysql> SHOW DATABASES; #查看基本数据
mysql> SHOW ENGINES; #查看支持的引擎
| Engine | Support | Comment | Transactions | XA | Savepoints |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys ...
| BLACKHOLE|YES|/dev/null storage engine (anything you write to it disappears)|YES|YES| YES|
| CSV | YES | CSV storage engine| MRG_MYISAM | ...
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |...
+----------+---------+--------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
mysql> \q #退出mysql
Bye
[root@lamp mysql]# vim /etc/my.cnf #修改mysql的配置文档
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8 #线程数量需要与实际一样
datadir = /mydata/data #新增此行，指定mysql数据路径
[root@lamp mysql]# mysql
mysql> USE mysql; #设定mysql数据库为默认database
Database changed
mysql> SELECT User,Host,Password FROM user; #查询user表中关于User,Host,密码三个字段的信息
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | lamp | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | lamp | |
+------+-----------+----------+
6 rows in set (0.00 sec)
mysql> DROP USER ''@localhost; #删除初始的匿名登录用户
Query OK, 0 rows affected (0.00 sec)
mysql> DROP USER ''@lamp; #删除初始的匿名登录用户
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT User,Host,Password FROM user; #再次查看，匿名用户已经删除成功
+------+-----------+----------+
| User | Host | Password |
+------+-----------+----------+
| root | localhost | |
| root | lamp | |
| root | 127.0.0.1 | |
| root | ::1 | |
+------+-----------+----------+
4 rows in set (0.00 sec)
mysql> UPDATE user SET Password=PASSWORD('123456') WHERE user='root'; #设定root用户的密码
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT User,Host,Password FROM user; #再次查看，所有root用户已经设置好密码
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | lamp | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | ::1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> FLUSH PRIVILEGES; #使设置立即生效
Query OK, 0 rows affected (0.00 sec)
[root@lamp mysql]# mysql #再次登录mysql，提示错误，因为已经设定了密码。
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@lamp mysql]# mysql -uroot -p #从本机登录mysql,输入刚设定的密码
Enter password: 123456
mysql> #正常登录mysql
3.2. 如何设定本机本地登录mysql不用输入密码：
在家目录下，新建一个.my.cnf文档，里面添加以下内容：
[root@lamp ~]# vim .my.cnf
[client]
user=root
host=localhost
password=123456
#即使root用户设定了密码，也可以免密码直接读取家目录下面的.my.cnf的隐藏文件进行登录认证。
3.3. 为innodb引擎设置了每表都单独为一个空间
[root@lamp ~]# cd /mydata/data/mysql
[root@lamp mysql]# ll
total 1016
-rw-rw----. 1 mysql mysql 8820 May 11 16:12 columns_priv.frm
-rw-rw----. 1 mysql mysql 0 May 11 16:12 columns_priv.MYD
-rw-rw----. 1 mysql mysql 4096 May 11 16:12 columns_priv.MYI
-rw-rw----. 1 mysql mysql 9582 May 11 16:12 db.frm #MyISAM引擎 表的结构文件
-rw-rw----. 1 mysql mysql 880 May 11 16:12 db.MYD #MyISAM引擎 存放数据的文件
-rw-rw----. 1 mysql mysql 5120 May 11 16:12 db.MYI #MyISAM引擎 数据索引文件
-rw-rw----. 1 mysql mysql 10223 May 11 16:12 event.frm
-rw-rw----. 1 mysql mysql 0 May 11 16:12 event.MYD
-rw-rw----. 1 mysql mysql 2048 May 11 16:12 event.MYI
...
mysql> mysql
mysql> SHOW VARIABLES LIKE '%innodb%'; #查看关于innodb引擎的相关变量参数
+----------------------------+------------------------+
| Variable_name | Value |
+----------------------------+------------------------+
| have_innodb | YES |
| ignore_builtin_innodb | OFF |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | OFF | #该项表示innodb引擎是否每个表都开启独立空间
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method |
[root@lamp mysql]# vim /etc/my.cnf #编辑mysql配置文件，开启innodb引擎对于每个表的独立空间
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_file_per_table = 1 # 增加此行，1为启用，0为禁用
[root@lamp mysql]# service mysqld restart #重启mysql服务
Shutting down MySQL. [ OK ]
Starting MySQL.. [ OK ]
[root@lamp mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql> SHOW VARIABLES LIKE '%innodb%';
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
...
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON | #已经为innodb引擎设置了每表都单独为一个空间
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
mysql> CREATE DATABASE mydb; #创建新的数据库
Query OK, 1 row affected (0.00 sec)
mysql> USE mydb; #设定mydb为默认数据库
Database changed
mysql> CREATE TABLE testdb(id INT NOT NULL,name CHAR(30)); #建立testdb表2行
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> SHOW TABLES FROM mydb; #mydb数据库中包含的表
+----------------+
| Tables_in_mydb |
+----------------+
| testdb |
+----------------+
1 row in set (0.00 sec)
[root@lamp ~]# cd /mydata/data/mydb
[root@lamp mydb]# ll -h
total 112K
-rw-rw----. 1 mysql mysql 61 Apr 18 15:25 db.opt
-rw-rw----. 1 mysql mysql 8.4K Apr 18 15:27 testdb.frm #test表的结构文件存放
-rw-rw----. 1 mysql mysql 96K Apr 18 15:27 testdb.ibd #test表的数据和索引文件
[root@lamp ~]# vim test.sql
CREATE DATABASE testdb; #创建一个testdb数据库
CREATE TABLE testdb.tb1(id INT,name CHAR(20)); #在testdb库中建立tb1表。
mysql> \. /root/test.sql #把test.sql中的内容导入到mysql内执行，
或者不进入数据，直接执行：mysql < /root/test.sql 输入重定向一样。
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
mysql> USE testdb
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| tb1 |
+------------------+
1 row in set (0.00 sec)
mysql> DROP DATABASE testdb; #删除testdb库
Query OK, 1 row affected (0.01 sec)
附1：
编译mysql出现CMake Error atcmake/readlineNaNake:83
-- Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH)
CMake Error at cmake/readlineNaNake:83 (MESSAGE):
Curses library not found. Please installappropriate package,
remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu,package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
cmake/readlineNaNake:127 (FIND_CURSES)
cmake/readlineNaNake:217(MYSQL_USE_BUNDLED_LIBEDIT)
CMakeLists.txt:257 (MYSQL_CHECK_READLINE)
-- Configuring incomplete, errors occurred!
该报错原因是未安装ncurses-devel，运行下面命令
第一步：安装
#yum -y install ncurses-devel
第二步：删除CMakeCache.txt
这里是因为，我们在多次运行cmake有个文件我们需要删除，删除当前目录下CMakeCache.txt文件并重新编译，再次运行cmake命令就会正常！
[root@mysql mysql-5.6.12]# rm -rf CMakeCache.txt
或者通过find命令找到所有CMakeCache.txt文档的位置
#find / -name CMakeCache.txt
然后全部删除：
# rm -rf/usr/local/src/cmake-2.8.6/Tests/Complex/Cache/CMakeCache.txt
# rm -rf/usr/local/src/cmake-2.8.6/Tests/ComplexOneConfig/Cache/CMakeCache.txt
# rm -rf /usr/local/src/cmake-2.8.6/Tests/ComplexRelativePaths/Cache/CMakeCache.txt
# rm -rf /usr/local/src/mysql-5.5.18/CMakeCache.txt
全部删除后再重新cmake就OK了。
附2：
mysql5.5.28.tar.gz下载地址：http://down.51cto.com/data/700556
cmake-2.8.8.tar.gz下载地址：http://vdisk.weibo.com/s/usonnBN1894A3
---end---
