yum install mysql-server

1. sudo apt-get install mysql-server

2. sudo apt-get isntall mysql-client

3. sudo apt-get install libmysqlclient-dev

# 检测是否安装成功（是否为LISTEN状态）

sudo netstat -tap | grep mysql

# 安装依赖包

yum install ncurses-devel gcc gcc-c++ -y

# 创建目录

mkdir -p /home/oldsuo/tools

# 安装cmake软件，gmake编译安装

cd /home/oldsuo/tools/

tar xf cmake-2.8.8.tar.gz

cd cmake-2.8.8

./configure

#CMake has bootstrapped.  Now run gmake.

gmake

gmake install

cd ../

# 开始安装mysql

# 创建用户和组

groupadd mysql

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

# 解压编译MySQL

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 \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_READLINE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DWITH_DEBUG=0

#-- Build files have been written to: /home/oldsuo/tools/mysql-5.5.32

提示: 编译时可配置的选项很多，具体可参考结尾附录或官方文档：

make

#[100%] Built target my_safe_process

make install

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

如果上述操作未出现错误，则MySQL5.5.32软件cmake方式的安装就算成功了。

#拷贝配置文件

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

echo $PATH

#授权用户及/tmp/临时文件目录

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

chmod -R 1777 /tmp/

#初始化数据库

cd /application/mysql/scripts/

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

cd ../

#启动数据库

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

chmod +x /etc/init.d/mysqld

/etc/init.d/mysqld  start

#检查端口

netstat -lntup|grep 3306

#查看用户和主机列，从mysql.user里查看

select user,host from mysql.user;

#删除用户名为空的库，并检查

delete from mysql.user where user='';

select user,host from mysql.user;

#删除主机名为localhost.localdomain的库，并检查

delete from mysql.user where host='localhost.localdomain';

select user,host from mysql.user;

#删除主机名为::1的库，并检查。::1库的作用为IPV6

delete from mysql.user where host='::1';

#删除test库

drop database test;

# 添加额外管理员，system作为管理员，oldsuo为密码

mysql> delete from mysql.user;

Query OK, 2 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

# 刷新MySQL的系统权限相关表，使配置生效

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;

+--------+-----------+

| user   | host      |

+--------+-----------+

| system | localhost |

+--------+-----------+

1 row in set (0.00 sec)

mysql>

#设置密码，并登陆

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

mysql -usystem -p

#开机启动mysqld，并检查

chkconfig mysqld on

chkconfig --list mysqld

#安装依赖包

yum –y install ncurses ncurses-devel gcc gcc-c++

#添加mysql用户及组

groupadd mysql

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

#mysql5.1.62编译参数:

./configure \

--prefix=/usr/local/mysql \

--with-unix-soket-path=/usr/local/tmp/mysql.sock \

--localstatedir=/usr/local/mysql/data \

--enable-assembler \

--enable-thread-safe-client \

--with-mysqld-user=mysql \

--with-big-tables \

--without-debug \

--with-pthread \

--enable-assembler \

--with-extra-charsets=complex \

--with-readline \

--with-ssl \

--with-embedded-server \

--enable-local-infile \

--with-plugins=partition,innobase \

--with-mysqld-ldflags=-all-static \

--with-client-ldflags=-all-static

make && make install

#初始化mysql

mkdir -p /usr/local/mysql/data    #建立mysql数据文件目录

chown -R mysql.mysql /usr/local/mysql/        #授权mysql用户访问mysql安装目录

/usr/local/mysql/bin/mysql_install_db --user=mysql        #初始化

#拷贝mysql启动脚本

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

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

chmod 700 /etc/init.d/mysqld

#配置mysql使用全局路径

echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile        #添加变量到profile

source /etc/profile        #使变量生效

echo $PATH            #检查

#启动mysqld

/etc/init.d/mysqld start

#登陆报错，做软链接

#ln -s /usr/local/mysql/bin/mysql /usr/bin/

#启动报错日志：  Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't #exist

#解决方法：  /usr/local/mysql/bin/mysql_install_db --user=mysql   #初始化数据库即可

#登陆报错： mysql: unknown variable 'datadir=/usr/local/mysql/data'

#解决方法： my.cnf 配置问题，vim /etc/my.cnf

[client]

#password        = your_password

port                = 3306

socket             = /tmp/mysql.sock

#datadir             = /data1/mysql/var/          #这个不能加在上面，去掉

[mysqld]

port                = 3306

socket             = /tmp/mysql.sock

datadir            = /data1/mysql/var/          #加在这里就可以了

#设置mysql用户root 的密码为oldsuo

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

# 查看MySQL字符集设置情况

show variables like 'character_set%';

# 查看库的字符集

show create database db;

# 查看表的字符集

show create table db_tb\G

# 查询所有

show collation;

# 设置表的字符集

set tables utf8;

show create database nick_defailt\G            #查看nick_defailt库字符集

mysql -uroot -p -e "SHOW CHARACTER SET;"

show variables like 'character_set%';

mysql> show variables like 'character_set%';

+------------------------------------+----------------------------------+

| Variable_name            | Value                                |

+------------------------------------+----------------------------------+

| character_set_client      | utf8                                   |

| character_set_connection  | utf8                                   |

| character_set_database   | utf8                                   |

| character_set_filesystem   | binary                                 |

| character_set_results     | utf8                                   |

| character_set_server      | utf8                                   |

| character_set_system     | utf8                                   |

| character_sets_dir        | /usr/local/mysql/share/mysql/charsets/     |

+-----------------------------------+----------------------------------+

8 rows in set (0.00 sec)

mysql> show create database nick_defailt \G           

*************************** 1. row ***************************

       Database: data

Create Database: CREATE DATABASE `data` /*!40100 DEFAULT CHARACTER SET utf8 */

1 row in set (0.00 sec)

1>系统方面

cat /etc/sysconfig/i18n

LANG="zh_CN.UTF-8"

2>客户端（程序），调整字符集为latin1。

mysql> set names latin1;        #临时生效

Query OK, 0 rows affected (0.00 sec)

    #更改my.cnf客户端模块的参数，实现set name latin1 的效果，并且永久生效。

    [client]

default-character-set=latin1

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

3>服务端，更改my.cnf参数

[mysqld]

default-character-set=latin1        #适合5.1及以前版本

character-set-server=latin1         #适合5.5

4>库、表、程序

#建表指定utf8字符集

mysql> create database nick_defailtsss DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

1>导出表结构

#以utf8格式导出

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

--default-character-set=gbk        #表示已GBK字符集连接 –d 只表示表结构

2>编辑alltable.sql 将utf8改成gbk。

3>确保数据库不在更新，导出所有数据

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

4>打开alldata.sql将set name utf8 修改成 set names gbk(或者修改系统的服务端和客户端)

5>建库

create database oldsuo default charset gbk;

6>创建表，执行alltable.sql

mysql -uroot -p oldsuo

7>导入数据

mysql -uroot -p oldsuo

1）MyISAM引擎系统库表对应文件

[root@mysql 3306]# ll /data/3306/data/mysql/

-rw-rw----. 1 mysql mysql  10630 10月 31 16:05 user.frm            #保存表的定义

-rw-rw----. 1 mysql mysql   1140 10月 31 18:40 user.MYD            #数据文件

-rw-rw----. 1 mysql mysql   2048 10月 31 18:40 user.MYI            #索引文件

[root@mysql 3306]# file data/mysql/user.frm

data/mysql/user.frm: MySQL table definition file Version 9

[root@mysql 3306]# file data/mysql/user.MYD

data/mysql/user.MYD: DBase 3 data file (167514107 records)

[root@mysql 3306]# file data/mysql/user.MYI

data/mysql/user.MYI: MySQL MISAM compressed data file Version 1

2）InnoDB引擎

[root@mysql 3306]# ll data/

-rw-rw----. 1 mysql mysql 134217728 10月 31 20:05 ibdata1

ALTER TABLE student ENGINE = INNODB;

ALTER TABLE student ENGINE = MyISAM;

mysql> use teacher;

Database changed

mysql> show create table student;

+---------+-------------------------------------------------------------------+

| Table   | Create Table                                                                                                 |

+---------+-------------------------------------------------------------------+

| student | CREATE TABLE `student` (

  `id` int(4) NOT NULL AUTO_INCREMENT,

  `name` char(20) NOT NULL,

  `age` tinyint(2) NOT NULL DEFAULT '0',

  `dept` varchar(16) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `index_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

+---------+------------------------------------------------------------------+

1 row in set (0.01 sec)

mysql> ALTER TABLE student ENGINE = MyISAM;

Query OK, 3 rows affected (0.05 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table student;         

+---------+------------------------------------------------------------------+

| Table   | Create Table                                                                                                 |

+---------+------------------------------------------------------------------+

| student | CREATE TABLE `student` (

  `id` int(4) NOT NULL AUTO_INCREMENT,

  `name` char(20) NOT NULL,

  `age` tinyint(2) NOT NULL DEFAULT '0',

  `dept` varchar(16) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `index_name` (`name`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

+---------+------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> 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;

Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# /etc/init.d/mysqld start

Starting MySQL                                             [确定]

#mysqld_safe –user=mysql &

[root@localhost ~]# ss -lntup|grep 3306

tcp  LISTEN 0 50  *:3306 *:* users:(("mysqld",19651,10))

[root@localhost ~]# ps -ef|grep mysql|grep -v grep

root     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

mysql    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

/etc/init.d/mysqld 是一个shell启动脚本，启动后最终会调用mysqld_safe脚本，最后调用mysqld服务启动mysql。

        "$manager" \

        --mysqld-safe-compatible \

        --user="$user" \

        --pid-file="$pid_file" >/dev/null 2>&1 &

[root@localhost ~]# /etc/init.d/mysqld stop

Shutting down MySQL....                                    [确定]

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

mysqladmin -uroot -passwd password 'oldsuo'

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

#登陆mysql数据库

mysql -uroot –p

#查看有哪些库

show databases;

#删除test库

drop database test;

#使用test库

use test;

#查看有哪些表

show tables;

#查看suoning表的所有内容

select * from suoning;

#查看当前版本

select version();

#查看当前用户

select user();

#查看用户和主机列，从mysql.user里查看

select user,host from mysql.user;

#删除前为空，后为localhost的库

drop user ""@localhost；

#刷新权限

flush privileges;

#跳出数据库执行命令

system ls;

1> 普通方式

#> service mysqld stop

#>mysqld_safe --skip-grant-tables &

输入 mysql -uroot -p 回车进入

>use mysql;

> update user set password=PASSWORD("newpass")where user="root";

更改密码为 newpassord

> flush privileges; 更新权限

> quit 退出

service mysqld restart

mysql -uroot -p新密码进入
 

2> 普通方式的简写

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        #登陆

3>多实例方式

killall mysqld

mysqld_safe –defaults-file=/data/3306/my.cnf –skip-grant-table &

mysql –u root –p –S /data/3306/mysql.sock        #指定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        #登陆