install_Mysql

编译安装MySQL

MySQL概述

MySQL是一种关系型数据库管理系统，所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

社区版：免费，但是不提供任何技术支持
商业版：收费，可以试用30天，官方提供技术支持

# 上传源码包 
cmake-2.8.7.tar.gz
mysql-5.5.22.tar.gz
# cmake-2.8.7.tar.gz  mysql-5.5.22.tar.gz
# 安装依赖包
yum -y install ncurses-devel
yum -y install perl-Data-Dumper gcc gcc-c++ openssl-devel
# 解压软件包
tar zxvf cmake-2.8.7.tar.gz -C /usr/src
tar zxvf mysql-5.5.22.tar.gz -C /usr/src
# 编译安装cmake
cd /usr/src
cd cmake-2.8.7/
./configure && gmake && gmake install
# 编译安装MySQL
cd /usr/src/mysql-5.5.22/
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all && make && make install
# 创建用户
useradd mysql
chown mysql:mysql -R /usr/local/mysql/
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
cp support-files/mysql.server /etc/init.d/mysqld
chmod a+x /etc/init.d/mysqld 
cp -p support-files/my-medium.cnf /etc/my.cnf
service mysqld start
ln -s /usr/local/mysql/bin/* /usr/local/bin/
# 配置密码
mysqladmin -u root password

忘记密码解决方法

# 停服务
service mysqld stop
# 改配置文件
vim /etc/my.cnf
# 在[mysql]下添加 skip-grant-tables
# 输入mysql 可以直接跳过密码进入
update mysql.user set authentication_string=password('NzSB250..') where user='root';
flush privileges;

基础命令

# 创建库
mysql> create database test01;
Query OK, 1 row affected (0.00 sec)
# 查看库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test01             |
+--------------------+
5 rows in set (0.00 sec)
# 查看当前登录的账户
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
# 切换库
mysql> use test01
Database changed
# 查看库里的表
mysql> show tables;
Empty set (0.00 sec)
# 查看test01数据库的编码类型 
mysql> show create database test01;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test01   | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
# 创建表
mysql> create table students (id int unsigned not null primary key,name varchar (20) not null ,age tinyint unsigned );
Query OK, 0 rows affected (0.01 sec)
# 插入数据
mysql> insert into students (id,name,age) values(1,'xiaoming',20);
Query OK, 1 row affected (0.01 sec)
# 改数据
mysql> update students set name='lishi',id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 查数据
mysql> select id,name,age from students;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | lishi |   20 |
+----+-------+------+
1 row in set (0.00 sec)
# 别名
select 表达式 as 别名 from 表名;
mysql> select id,name,age as 年龄 from students;
+----+-------+--------+
| id | name  | 年龄   |
+----+-------+--------+
|  2 | lishi |     20 |
+----+-------+--------+
1 row in set (0.00 sec)
# 查数据
mysql> select id,name,age from students;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   20 |
|  2 | lishi    |   20 |
|  3 | xiaoming |   20 |
|  4 | wangwu   |   31 |
+----+----------+------+
4 rows in set (0.00 sec)
# 去重
select distinct 列名 from 表名;
mysql> select distinct name from students;
+----------+
| name     |
+----------+
| xiaoming |
| lishi    |
| wangwu   |
+----------+
3 rows in set (0.00 sec)
# 排序
mysql> select name,age from students order by age desc;
+----------+------+
| name     | age  |
+----------+------+
| wangwu   |   31 |
| xiaoming |   20 |
| lishi    |   20 |
| xiaoming |   20 |
+----------+------+
4 rows in set (0.00 sec)
# 删除
mysql> delete from students where name='xiaoming';
Query OK, 2 rows affected (0.00 sec)