MySQL

A First Glance at MySQL

A 安装MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

##wget http://192.168.2.2:8080/download/MySQL-client-5.6.19-1.el6.x86_64.rpm
##wget http://192.168.2.2:8080/download/MySQL-server-5.6.19-1.el6.x86_64.rpm
wget -c http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-*-5.6.19-1.el6.x86_64.rpm/from/http://mysql.spd.co.il/

rpm -ivg MySQL-server-5.6.19-1.el6.x86_64.rpm
## 报错
## file /usr/share/mysql/czech/errmsg.sys from install of MySQL-server-5.6.19-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.71-1.el6.x86_64

yum remove mysql-libs-5.1.*
rpm -ivh MySQL-server-5.6.19-1.el6.x86_64.rpm
rpm -ivg MySQL-client-5.6.19-1.el6.x86_64.rpm
rpm -ivh MySQL-devle-5.6.19-1.el6.x86_64.rpm

## check and start MySQL
chkconfig | grep mysql
service mysql start

## 数据目录
lsof|grep mysql
ll -rt /var/lib/mysql/

## mysql 配置文件
ll -rt /usr/share/mysql/

## 相关程序命令
which mysql
/usr/bin/mysql

## 启动脚本
ll -rt /etc/rc.d/init.d/

B 切换mysql目录到 /home/data

参见 http://database.51cto.com/art/200709/56715.htm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

#### 1 关掉mysql服务并创建备份目录
mysqladmin -u root -p shutdown
mkdir /home/data
cd /home/data

lsof|grep mysql
# mysql 数据目录
ll -rt /var/lib/mysql
mv /var/lib/mysql ./

#### 2 编辑MySQL的配置文件/etc/my.cnf
find / -name *.cnf -print
    /home/data/mysql/auto.cnf
    /usr/share/doc/MySQL-server-5.6.19/my-default.cnf
    /usr/share/mysql/my-default.cnf
    /usr/my.cnf
    /etc/pki/tls/openssl.cnf
cp  /usr/my.cnf /etc/my.cnf
vi   /etc/my.cnf
    #port    =   3306
    socket  =   /home/data/mysql/mysql.sock
    # 注释掉最后一行

#### 3 修改MySQL启动脚本/etc/init.d/mysql
vi　/etc/init.d/mysql
    #datadir=/var/lib/mysql(注释此行)
    datadir=/home/data/mysql (加上此行)

#### 4 reboot
/etc/init.d/mysql restart

#### 5 socket error 解决
ln -s  /home/data/mysql/mysql.sock /var/lib/mysql/

C mysql 常见报错 root@localhost 不能启动的问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

#### 1 localhost 问题
mysql -u root -p
Enter password:
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

#### 2 safe 模式重启
service mysql stop
chkconfig
mysqld_safe --skip-grant-table

#####
# 参见 http://wenku.baidu.com/link?url=ARPOWYE5qWssO6toplKyOFWoaVP57UeA3Lj3IUU00wk3tcP3EGpfEp52uJE8M_Ckio7sieL-8x0gOQ4Epwn8d4eUw_xpMNlGZZeswiPsg93
#####

/etc/init.d/mysql stop
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
mysql -u root mysql
#mysql> UPDATE user SET Password='tanhao'  where USER='root';
mysql>  update mysql.user set password=password('tanhao') where user='root';
#mysql> select * FROM user WHERE USER='root'\G
mysql>SELECT host,user,password FROM user;
mysql> FLUSH PRIVILEGES;
mysql> quit
/etc/init.d/mysql restart
mysql -uroot -p
# You must SET PASSWORD before executing this statement解决
mysql> SET PASSWORD = PASSWORD('123456'); # new root key will be 123456

D MySQL testing

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE DATABASE students;
USE students;

CREATE TABLE transcripts(
stu_id int NOT NULL AUTO_INCREMENT,
stu_name    char(50)    NOT NULL,
stu_class     char(50)    NULL,
stu_grade    int    NULL,
PRIMARY KEY(stu_id)
) ENGIN=InnoDB;

SHOW TABLE STATUS LIKE 'transcripts';
SHOW CREATE TABLE transcripts;
ALTER TABLE transcripts ADD stu_email CHAR(50);

INSERT INTO transcripts(stu_id,stu_name,stu_class,stu_grade,stu_email)
VALUES
(1, 'Jim','A',90,NULL),
(2, 'Tom','B',95,NULL),
(3, 'Jhon','A',70,NULL),
(4, 'Adam','B',65,NULL),
(5, 'Grace','A',80,NULL),
(6, 'Ana','B',95,NULL),
(7, 'Babe','A',80,NULL),
(8, 'KK','B',85,NULL),
(9, 'Han','A',70,NULL),
(10, 'Sun','B',75,NULL),
(11, 'GG','A',80,NULL),
(12, 'Dog','B',95,NULL),
(13, 'Cao','A',60,NULL),
(14, 'Sky','B',85,NULL),
(15, 'FF','A',90,NULL),
(16, 'Dom','B',85,NULL);

SELECT * FROM transcripts;

欢迎参见Details