数据库MySQL


32位安装:


http://down1.chinaunix.net/distfiles/mysql-5.0.56.tar.gz        (32位)

 


# useradd -M -s /sbin/nologin  mysql
# tar zxf mysql-5.0.56.tar.gz -C /usr/src
# cd /usr/src/mysql-5.0.56
# ./configure --prefix=/usr/local/mysql

# make
# make install
# cp support-files/my-medium.cnf /etc/my.cnf
# /usr/local/mysql/bin/mysql_install_db --user=mysql
# chown -R root.mysql /usr/local/mysql/
# chown -R mysql /usr/local/mysql/var
# echo "/usr/local/mysql/lib/mysql"  >> /etc/ld.so.conf
# ldconfig
# /usr/local/mysql/bin/mysqld_safe  --user=mysql &
# 回车(Enter键)
# netstat -ntpl | grep 3306
# cp support-files/mysql.server  /etc/init.d/mysqld
# chmod +x /etc/init.d/mysqld
# chkconfig --add mysqld
# chkconfig --level 35 mysqld on
# export PATH=$PATH:/usr/local/mysql/bin
# echo "PATH=$PATH:/usr/local/mysql/bin"   >>  /etc/profile


# mv /tmp /tmp1
# vi /etc/my.cnf
socket      = /tmp1/mysql.sock
:wq
# ln -s /tmp1/mysql.sock  /tmp/mysql.sock

# ln -s /usr/local/mysql/bin/mysql /sbin/mysql
# ln -s /usr/local/mysql/bin/mysqladmin /sbin/mysqladmin
# mysqladmin -u root password "123456"
# mysql -u root -p

密码

 

 

 

64位安装:

 


http://mysql.mirror.kangaroot.net/Downloads/MySQL-5.1/mysql-5.1.60-linux-x86_64-glibc23.tar.gz   

        (64位)


 

# tar zxf mysql-5.1.60-linux-x86_64-glibc23.tar.gz  

# mv mysql-5.1.60-linux-x86_64-glibc23   /usr/local/mysql

# cd /usr/local/mysql

# useradd -M -s /sbin/nologin mysql

# chown mysql:mysql /usr/local/mysql

# ./script/mysql_install_db  --user=mysql

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

# cp support-files/my-medium.cnf /etc/my.cnf

# chkconfig --add mysqld

# chkconfig --level 35 mysqld on

# /usr/local/mysql/bin/mysqld_safe --user=mysql &

# /usr/local/mysql/bin/mysqladmin -u root password "123456"

# /usr/local/mysql/bin/mysql -u root -p

 

 

附:mysql编译的参数

 

--without-debug 去除debug模式
--with-extra-charsets=gb2312 添加gb2312中文字符支持
--enable-assembler 使用一些字符函数的汇编版本
--without-isam 去掉isam表类型支持 现在很少用了 isam表是一种依赖平台的表
--without-innodb 去掉innodb表支持 innodb是一种支持事务处理的表,适合企业级应用
--with-pthread 强制使用pthread库(posix线程库)
--enable-thread-safe-client 以线程方式编译客户端
--with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static 以纯静态方式编译服务端和客户端

--with-raid 激活raid支持

 

 

> show databases;                    (查看当前存在的数据库)

> use mysql;                         (切换到数据库mysql)

> show tables;                       (查看数据库中的表信息)

> describe user;                     (显示user表的信息)

> create database auth;              (创建新的数据库auth)

> use auth;

> create table users (user_name char(30) not null,user_passwd char(20) not null default

'123456',primary key (user_name));       (建名为users的表)

> drop table auth.users;                        (删除数据库auth中的users表)

> drop database auth;                                  (删除数据库auth)

> insert into auth.users(user_name,user_passwd) values('zhangsan',encrypt('123456'));            

       (在表users中插入用户名zhangsan和密码123456)

> select * from auth.users;                     (查看users中的数据记录)

> update auth.users set user_passwd=encrypt('654321') where user_name='zhangsan';                

            (更改用户的密码:密文)

> update mysql.user set password=password('123456') where user='root';(明文)

> flush privileges;                (刷新用户信息)

> delete from auth.users where user_name='zhangsan'; (删数据库auth中的表users里的用户zhangsan)

> delete from mysql.user where user_name user=' ';  (删除数据库mysql中的user表里的空用户)

> grant all on auth.* to admin1@'localhost' identified by '123456';  (授权用户admin1,允许其从本

机连接到mysql服务器,对auth数据库的所有表具有完全权限)

> grant select on auth.* to admin2@'192.168.0.0/24' identified by '123456';(授权admin2,允许其从

网段192.168.0.0/24中访问mysql服务器,可以查询auth库中的所有表)

> grant select,insert on auth.* to admin3@'%.benet.com' identified by '1234';(授权用户admin3,允

许其从benet.com域内的任何主机访问mysql服务器,对auth库中的所有表具有查询、插入的权限)

> show grants for root@'localhost'; (查看用户root从本机连接到mysql服务器时的权限)

> show grants for admin3@'%.benet.com'; (查看用户admin3从benet.com域内的客户机访问mysql服务器时

的权限)

> revoke all on auth.* from admin3@'%.benet.com'; (撤销用户admin3从benet.com域内访问数据库auth的

所有权限)


> exit   或   quit            (退出)


# mysqldump - u root - p auth > mysql-auth.sql   密码  (备份整个auth数据库)

# mysqldump - u root - p mysql host user > mysql.host.user.sql 密码  (备份数据库mysql中的user表

、host表)

# mysqldump - u root - p --all-databases > mysql-all.sql  密码 (备份所有数据库的内容)

# mysql - u root - p < mysql-all.sql  (恢复备份文件mysql-all.sql)

# mysql - u root - p auth < mysql-auth.sql  (恢复单个数据库时需指定目标数据库名称)