Ubuntu10下Mysql-5.1.56安装

Ubuntu10下Mysql-5.1.56安装

创建mysql用户和组

sudo groupadd mysql

sudo useradd -r -g mysql mysql

Note:

-r option to create a user that does not have login permissions to your server host. Omit this option to permit logins for the user。

-g：指定用户所属的群组。

 

解压和移动到应用目录

tar –zxvf mysql-5.1.56-linux-i686-glibc23.tar.gz

sudo mv mysql-5.1.56-linux-i686-glibc23 /usr/local/mysql51

 

建立软连接

为mysql51目录建立软连接mysql，以便快速访问mysql51目录。

 

#相当于cd /usr/local/mysql51 和cd /usr/local/mysql等效

sudo ln -s  /usr/local/mysql51 mysql

cd /usr/local/mysql

 

访问权限配置

cd /usr/local/mysql

//注意后面有小数点

sudo chown -R mysql .

//将组属性改为mysql组。

sudo chgrp -R mysql .

 

There are four symbolic link files whose ownership is not changed(/usr/mysql/lib).

lrwxrwxrwx 1 7155 staff 24 Mar 9 06:28 libmysqlclient.so.16 -> libmysqlclient.so.16.0.0
lrwxrwxrwx 1 7155 staff 24 Mar 9 06:28 libmysqlclient.so -> libmysqlclient.so.16.0.0
lrwxrwxrwx 1 7155 staff 26 Mar 9 06:28 libmysqlclient_r.so.16 -> libmysqlclient_r.so.16.0.0
lrwxrwxrwx 1 7155 staff 26 Mar 9 06:28 libmysqlclient_r.so -> libmysqlclient_r.so.16.0.0

//为了更改以上几个文件的权限，需要执行下面的命令。
guangbo@guangbo-laptop:/usr/local/mysql/lib$ sudo chown -h mysql:mysql libmysqlclient.so.16 libmysqlclient.so libmysqlclient_r.so.16 libmysqlclient_r.so

 

创建MySQL授权表初始化

sudo scripts/mysql_install_db --user=mysql

 

//将文件的所有属性改为root用户。

sudo chown -R root .

//将数据目录的所有属性改为mysql用户。

sudo chown -R mysql data

//下面这条给所有人弄data的权限，仅为了方便调试,不需要

sudo chmod a+rwx -R data

 

//下面的命令可选。copy配置文件到相关目录

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

启动mysql

sudo bin/mysqld_safe --user=mysql &

//下面的命令可选。开机自启动mysql.server服务.

//To start mysqld at boot time you have to copy

//support-files/mysql.server to the right place for your system

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

service mysql.server start

//查看启动是否正常

./bin/mysqladmin version

./bin/mysqladmin  Ver 8.42 Distrib 5.1.56, for pc-linux-gnu on i686

Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license

 

Server version        5.1.56-log

Protocol version  10

Connection        Localhost via UNIX socket

UNIX socket       /tmp/mysql.sock

Uptime:             12 min 41 sec

 

修改mysql密码

guangbo@guangbo-laptop:/usr/local/mysql$ ./bin/mysqladmin -u root -p password ='root'

授权GRANT充许各ip访问

当重置密码后，还是无法访问，如通过ip，另外的机器等，请用受权，权限自行配置

 

//不能通过localhost和127.0.0.1。只能是服务器名。

mysql -h guangbo-laptop -u root –p

//添加新ip

mysql>INSERT INTO user (Host,User,Password) VALUES('192.168.1.2','root',PASSWORD('root'));

mysql>FLUSH PRIVILEGES;

//给某ip授权

mysql>GRANT ALL ON *.* TO root@'192.168.1.2' IDENTIFIED BY 'root' WITH GRANT OPTION;

//root用户可以在所有ip访问

mysql>GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

mysql> select host,user,password from user;

对 GRANT 语句的简单说明：
GRANT
ALL
所有权限
ON *.*
在所有的数据库上
TO root@’%’
对 root 用户，在所有其他 IP 上的 root 用户（注： mysql 按照用户名和所在 IP 区分用户，所以 root 和 root@% 不是同一个用户）
IDENTIFIED BY ‘root’
口令为 root

常见错误

一般可以查看/usr/local/mysql/data/guangbo-laptop.err文件定位错误问题。

访问拒绝-登录及修改密码

./bin/mysqladmin -u root -p password 'root'

./bin/mysql -u root

Enter password:

./bin/mysqladmin: connect to server at 'localhost' failed

error: 'Access denied for user 'root'@'localhost' (using password: YES)'

//添加机器名,还是不行的话，重设密码

mysql -u root -h guangbo-laptop

方法:重设密码

//先关闭mysql

sudo /etc/init.d/mysql.server stop

//或者

sudo service mysql.server stop

//忽略受权和网络启动

sudo ./bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

//登录

./bin/mysql -u root

//更新密码

mysql>UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';

mysql>FLUSH PRIVILEGES;

mysql>quit;

//重启

sudo service mysql.server stop

sudo service mysql.server start

//or

sudo /etc/init.d/mysql.server stop

sudo /etc/init.d/mysql.server start

 

unrecognized service的问题

这个命令的作用是把mysql添加为系统服务，服务名为mysql.server FA6平坦软件园

chmod 700 /etc/init.d/mysql.server

chkconfig --add mysql.server

server PID file找不到

MySQL manager or server PID file could not be found!

[ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip federated'

Step1：修改/etc/my.cnf文件

sudo gedit /etc/my.cnf

//添加如下内容

datadir=/usr/local/mysql/data

//注释掉skip-federated

Step2:修改/etc/init.d/mysql.server文件

sudo gedit /etc/init.d/mysql.server

//find
basedir=
datadir=

//change with

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

mysql启动一半锁死的问题

//查看占用的pid

ps aux|grep mysq*

root      1932  7.9  0.0   1828   532 ?        S    01:35   0:26 /bin/sh ./bin/mysql_secure_installation

guangbo  31050  0.0  0.0   3320   792 pts/1    S+   01:40   0:00 grep --color=auto mysq*

//杀死锁死的进程

sudo kill -9 1932

unknown variable 'datadir=

guangbo@guangbo-laptop:/usr/local/mysql$ ./bin/mysqladmin -u root -p password 'root'

./bin/mysqladmin: unknown variable 'datadir=/usr/local/mysql/data'

修改my.cnf  配置问题
[client]
#password        = your_password
port                = 3306
socket                = /tmp/mysql.sock
datadir=/usr/local/mysql/data  #这个不能加在上面

[mysqld]
port                = 3306
socket                = /tmp/mysql.sock
datadir=/usr/local/mysql/data #只要这里加就可以了，

tmp/mysql.sock问题

./bin/mysqladmin: connect to server at 'localhost' failed

error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'

Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!

没有权限,非root用户命令前面加上sudo.

 

常用mysql命令

./bin/mysql –h peter-host -u root -p

./bin/mysqladmin version

./bin/mysqladmin -u root shutdown

./bin/mysqladmin variables

./bin/mysqlshow

//查看有哪些数据库

mysql> show databases;

//查看某数据库存有哪些表:
mysql> use mysql;
mysql> show tables;