一、前言

二、概述

三、安装MySQL 5.1.73

四、升级为MySQL 5.5.35

五、总结

注，测试环境 CentOS 6.4 x86_64，MySQL 版本（5.1.73、5.5.35）目前最新版。下载地址：http://dev.mysql.com/downloads/mysql/5.1.html#downloads。

一、前言

前几篇博客中我们讲解了MySQL5.1与MySQL5.5之间的性能差异，MySQL5.5的性能有明显的提升，特别是对多核CPU的支持与TPS性能的提升。在这篇博客中我们将主要讲解MySQL 5.1.73升级为MySQL 5.5.35。下面我们继续……

二、概述

1.安装yum源

1

2

[root@node6 src]# wgethttp://mirrors.hustunique.com/epel/6/x86_64/epel-release-6-8.noarch.rpm

[root@node6 src]# rpm -ivhepel-release-6-8.noarch.rpm

2.同步时间

1

2

3

[root@node6 src]# yuminstall -y ntp

[root@node6 src]# ntpdate202.120.2.101

[root@node6 src]# hwclock –w

3.安装mysql 5.1依赖包

1

[root@node6 mysql-5.1.73]#yum -y install ncurses ncurses-devel

4.安装mysql5.5依赖包

1

[root@node6 ~]# yum install-y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool*openssl*

5.安装cmake

1

[root@node6 ~]# yum install-y cmake

三、安装MySQL 5.1.73

1.编译并安装mysql5.1

1

2

3

[root@node6 mysql-5.1.73]#tar xf mysql-5.1.73.tar.gz

[root@node6 mysql-5.1.73]#cd mysql-5.1.73

[root@node6 mysql-5.1.73]#./configure --prefix=/usr/local/mysql --localstatedir=/data/mysql--enable-assembler --with-client-ldflags=-all-static--with-mysqld-ldflags=-all-static --with-pthread --enable-static--with-big-tables --without-ndb-debug --with-charset=utf8--with-extra-charsets=all --without-debug --enable-thread-safe-client--enable-local-infile --with-plugins=max

上面配置内容省略……

1

2

3

4

5

6

7

8

This version of MySQLCluster is no longer maintained.

Please use the separatesources provided forMySQL Cluster instead.

See http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html

formoredetails.

Thank you forchoosing MySQL!

Remember to check theplatform specific part of the reference manual

forhints about installingMySQL on your platform.

Also have a lookat the filesinthe Docs directory.

到这里我们编译配置就完成了，下面我们编译并安装。

1

[root@node6 mysql-5.1.73]#make && make install

注，编译与安装时间比较长请大家耐心等待，当然会看各位博友机器的配置，相对来说配置越好，相对的编译与安装时间相对就少。

2.创建数据目录并授权

1

2

3

4

5

6

7

8

[root@node6 mysql-5.1.73]#mkdir -pv /data/mysql

mkdir: 已创建目录"/data/mysql"

[root@node6 mysql-5.1.73]#useradd mysql

[root@node6 mysql-5.1.73]#chown mysql.mysql /data/mysql/

[root@node6 mysql-5.1.73]#ll /data/

总用量 20

drwx------. 2 rootroot 16384 8月 17 18:42 lost+found

drwxr-xr-x. 2 mysqlmysql 4096 1月 4 16:10 mysql

3.为mysql提供配置文件

1

2

[root@node6 mysql-5.1.73]#cp support-files/my-huge.cnf /etc/my.cnf

cp：是否覆盖"/etc/my.cnf"？ y

4.简单修改一下配置文件

1

2

3

4

5

6

[root@node6 mysql-5.1.73]#vim /etc/my.cnf

[client]

default-character-set= utf8

[mysqld]

default-character-set= utf8

datadir=/data/mysql

5.提供启动脚本

1

2

3

4

[root@node6 mysql-5.1.73]#cp support-files/mysql.server /etc/init.d/mysqld

[root@node6 mysql-5.1.73]#chmod +x /etc/init.d/mysqld

[root@node6 ~]# chkconfigmysqld --add

[root@node6 ~]# chkconfigmysqld on

6.初始化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

[root@node6 mysql-5.1.73]#/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql/--datadir=/data/mysql/ --user=mysql

Installing MySQL systemtables...

140104 16:18:43[Warning]'--default-character-set'is deprecated and will be removed inafuture release. Please use'--character-set-server'instead.

140104 16:18:43[Warning]'--skip-locking'is deprecated and will be removed ina futurerelease. Please use'--skip-external-locking'instead.

OK

Filling help tables...

140104 16:18:43[Warning]'--default-character-set'is deprecated and will be removed inafuture release. Please use'--character-set-server'instead.

140104 16:18:43[Warning]'--skip-locking'is deprecated and will be removed ina futurerelease. Please use'--skip-external-locking'instead.

OK

To start mysqld at boottimeyou have to copy

support-files/mysql.serverto the right place foryour system

PLEASE REMEMBER TO SET APASSWORD FOR THE MySQL root USER !

To doso, start the server,thenissue the following commands:

/usr/local/mysql//bin/mysqladmin-uroot password'new-password'

/usr/local/mysql//bin/mysqladmin-uroot -h node6.test.com password'new-password'

Alternatively you can run:

/usr/local/mysql//bin/mysql_secure_installation

whichwill also give you theoption of removing thetest

databases and anonymous usercreated by default. This is

strongly recommendedforproduction servers.

See the manualformoreinstructions.

You can start the MySQLdaemon with:

cd/usr/local/mysql/;/usr/local/mysql//bin/mysqld_safe&

You can testthe MySQL daemonwith mysql-test-run.pl

cd/usr/local/mysql//mysql-test;perl mysql-test-run.pl

Please report any problemswith the/usr/local/mysql//scripts/mysqlbugscript!

注，从上面的内容中我们看到了几个警告，我们查看一下。

1

2

3

4

5

6

7

140104 16:18:43[Warning]'--default-character-set'is deprecated and will be removed inafuture release. Please use'--character-set-server'instead.

140104 16:18:43[Warning]'--skip-locking'is deprecated and will be removed ina futurerelease. Please use'--skip-external-locking'instead.

OK

Filling help tables...

140104 16:18:43[Warning]'--default-character-set'is deprecated and will be removed inafuture release. Please use'--character-set-server'instead.

140104 16:18:43[Warning]'--skip-locking'is deprecated and will be removed ina futurerelease. Please use'--skip-external-locking'instead.

从上面的警告可以看到，--default-character-set、--skip-locking选项已经过时，建议使用--character-set-server、--skip-external-locking。

7.查看一下初始化目录

1

2

[root@node6 data]# ls/data/mysql/

mysqlmysql-bin.000001 mysql-bin.000002 mysql-bin.indextest

8.启动一下mysql

1

2

[root@node6 ~]# servicemysqld start

Starting MySQL.. SUCCESS!

9.测试访问一下

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

[root@node6 ~]#/usr/local/mysql/bin/mysql

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 1

Server version: 5.1.73-logSource distribution

Copyright (c) 2000, 2013,Oracle and/oritsaffiliates. All rightsreserved.

Oracle is a registeredtrademark of Oracle Corporation and/orits

affiliates. Other names maybe trademarks of their respective

owners.

Type'help;'or'\h'forhelp.Type'\c'to clearthe current input statement.

mysql> show databases;

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

|Database |

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

| information_schema |

|mysql|

|test|

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

3 rows inset(0.00 sec)

mysql>

好了，到这里我们的mysql基本就安装完成，但我们还提做上些优化工作。

10.输出mysql的man手册至man命令的查找路径

1

2

3

[root@node6 ~]# yum install-y man

[root@node6 ~]# vim/etc/man.config

MANPATH/usr/local/mysql/man

11.输出mysql的头文件至系统头文件路径/usr/include

1

[root@node6 mysql]# ln -sv/usr/local/mysql/include /usr/include/mysql

12.输出mysql的库文件给系统库查找路径

1

2

[root@node6 mysql]# echo'/usr/local/mysql/lib'> /etc/ld.so.conf.d/mysql.conf

[root@node6 mysql]# ldconfig

13.修改PATH环境变量，让系统可以直接使用mysql的相关命令

1

2

3

4

5

6

7

8

9

10

11

12

13

[root@node6 mysql]# vim/etc/profile.d/mysql.sh

exportPATH=$PATH:/usr/local/mysql/bin/

[root@node6 mysql]# source/etc/profile

[root@node6 mysql]# mysql

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 2

Server version: 5.1.73-logSource distribution

Copyright (c) 2000, 2013,Oracle and/oritsaffiliates. All rightsreserved.

Oracle is a registeredtrademark of Oracle Corporation and/orits

affiliates. Other names maybe trademarks of their respective

owners.

Type'help;'or'\h'forhelp.Type'\c'to clearthe current input statement.

mysql>

好了，到这里我们的mysql就全部安装完成了，下面我们来准备一下测试环境。

14.新建测试数据库与测试表

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

36

37

38

39

40

41

42

43

44

45

46

47

48

mysql> CREATE DATABASEmydb;

Query OK, 1 row affected(0.33 sec)

mysql> SHOW DATABASES;

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

| Database|

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

| information_schema |

|mydb|

|mysql|

|test|

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

4 rows inset(0.00 sec)

mysql> use mydb;

Database changed

mysql> show createdatabase mydb;

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

| Database | CreateDatabase|

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

|mydb | CREATE DATABASE `mydb` /*!40100 DEFAULTCHARACTER SET utf8 */ |

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

1 row inset(0.00 sec)

mysql> show tables;

Emptyset(0.01 sec)

mysql> CREATE TABLE `t1`(idint(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

Query OK, 0 rows affected(0.03 sec)

mysql> show create tablet1;

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

| Table | CreateTable|

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

| t1 |CREATE TABLE `t1` (

`id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULTCHARSET=utf8 |

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

1 row inset(0.00 sec)

mysql> show tables;

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

| Tables_in_mydb |

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

|t1 |

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

1 row inset(0.00 sec)

mysql> desc t1;

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

| Field |Type | Null | Key | Default | Extra |

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

|id| int(11) |YES | | NULL| |

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

1 row inset(0.33 sec)

15.增加数据

(1).先简单插入10行数据

1

mysql> insert intot1 value (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

(2).查看一下

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

mysql>select* fromt1;

+------+

|id|

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

+------+

10 rows inset(0.00 sec)

(3).插入多行数据方法

1

2

3

4

5

6

7

8

9

10

mysql> insert intot1select* from t1;

Query OK, 10 rows affected(0.00 sec)

Records: 10Duplicates: 0 Warnings: 0

mysql> selectcount(*)from t1;

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

| count(*) |

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

|20 |

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

1 row inset(0.33 sec)

四、升级为MySQL 5.5.35

1.升级前准备（查看MySQL 5.1相关参数，具体如下）

(1).安装目录

1

[root@node6 ~]#/usr/local/mysql

(2).配置文件

1

[root@node6 ~]# /etc/my.cnf

(3).数据目录

1

[root@node6 ~]# /data/mysql

(4).启动脚本

1

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

(5).其它参数

·输出mysql的man手册至man命令的查找路径

·输出mysql的头文件至系统头文件路径/usr/include

·输出mysql的库文件给系统库查找路径

·修改PATH环境变量，让系统可以直接使用mysql的相关命令

2.升级方式

·直接将MySQL5.5安装目录覆盖正在运行的Mysql目录（我们这里演示使用的方式）

·将MySQL5.5安装到其它目录中，如/usr/local/mysql5

3.备份所有数据库

1

[root@node6 ~]# mysqldump-uroot -p123456 test -l -F '/tmp/test.sql'

·-l 锁定

·-F 即flush logs，可以重新生成新的日志文件，当然包括log-bin日志

4.备份安装目录

1

[root@node6 ~]# tar czvfmysql_5.1.73_full.tar.gz /usr/local/mysql

5.备份数据目录

1

[root@node6 ~]# tar czvfmysql_5.1.73_data_full.tar.gz /data/mysql

6.备份配置文件

1

[root@node6 ~]# cp/etc/my.cnf ./

7.关闭mysql

1

2

[root@node6 ~]# servicemysqld stop

Shutting down MySQL..SUCCESS!

好了，到这里我们准备工作就完成了。下面我们来升级到MySQL 5.5.35……

8.升级mysql 5.5.35

1

2

3

4

[root@node6 mysql]# tar xfmysql-5.5.35.tar.gz

[root@node6 mysql]# cdmysql-5.5.35

[root@node6 mysql-5.5.35]#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc-DMYSQL_DATADIR=/data/mysql -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock-DMYSQL_USER=mysql -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_SSL=system-DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1-DWITH_INNOBASE_STORAGE_ENGINE=1

[root@node6 mysql-5.5.35]#make && make install

9.删除原有的mysql5.1的配置文件

1

[root@node6 mysql-5.5.35]#rm -rf /etc/my.cnf

10.提供新的配置文件

1

[root@node6 mysql-5.5.35]#cp support-files/my-huge.cnf /etc/my.cnf

11.修改一下配置文件

1

2

[mysqld]

datadir=/data/mysql

注，只增加一行指定数据数据/data/mysql。

12.尝试重新启动一下

1

2

3

[root@node6 mysql-5.5.35]#service mysqld restart

Shutting down MySQL.SUCCESS!

Starting MySQL.. SUCCESS!

13.执行更新程序并重启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

31

32

33

[root@node6 mysql-5.5.35]#/usr/local/mysql/bin/mysql_upgrade

Lookingfor'mysql'as:/usr/local/mysql/bin/mysql

Lookingfor'mysqlcheck'as:/usr/local/mysql/bin/mysqlcheck

Running'mysqlcheck'with connection arguments:'--port=3306''--socket=/tmp/mysqld.sock'

Running'mysqlcheck'with connection arguments:'--port=3306''--socket=/tmp/mysqld.sock'

mydb.t1OK

mydb.t2OK

mysql.columns_privOK

mysql.dbOK

mysql.eventOK

mysql.funcOK

mysql.general_logOK

mysql.help_categoryOK

mysql.help_keywordOK

mysql.help_relationOK

mysql.help_topicOK

mysql.hostOK

mysql.ndb_binlog_indexOK

mysql.pluginOK

mysql.procOK

mysql.procs_privOK

mysql.proxies_privOK

mysql.serversOK

mysql.slow_logOK

mysql.tables_privOK

mysql.time_zoneOK

mysql.time_zone_leap_secondOK

mysql.time_zone_nameOK

mysql.time_zone_transitionOK

mysql.time_zone_transition_typeOK

mysql.userOK

Running'mysql_fix_privilege_tables'...

OK

14.查看生成的更新文件

1

2

3

4

5

6

7

8

[root@node6 ~]# cd/data/mysql/

[root@node6 mysql]#ls

ibdata1mysqlmysql-bin.000004 mysql-bin.000008 node6.test.com.err

ib_logfile0mysql-bin.000001 mysql-bin.000005mysql-bin.000009 node6.test.com.pid

ib_logfile1mysql-bin.000002 mysql-bin.000006mysql-bin.index performance_schema

mydbmysql-bin.000003 mysql-bin.000007 mysql_upgrade_infotest

[root@node6 mysql]# catmysql_upgrade_info

5.5.35

15.尝试登录一下并查看一下版本

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

[root@node6 mysql]#mysql

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQLconnectionidis 9

Server version: 5.5.35-logSource distribution

Copyright (c) 2000, 2013,Oracle and/orits affiliates. All rightsreserved.

Oracle is a registeredtrademark of Oracle Corporation and/orits

affiliates. Other names maybe trademarks of their respective

owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthe current input statement.

mysql>selectversion();

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

| version()|

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

| 5.5.35-log |

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

1 rowinset(0.00 sec)

16.查看一下库和表

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

mysql> showdatabases;

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

|Database|

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

| information_schema|

|mydb|

|mysql|

| performance_schema|

|test|

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

5 rowsinset(0.00 sec)

mysql> use mydb;

Database changed

mysql> showtables;

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

| Tables_in_mydb|

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

|t1|

|t2|

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

2 rowsinset(0.00 sec)

mysql>selectcount(*) from t1;

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

| count(*) |

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

| 20971520 |

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

1 rowinset(0.00 sec)

好了，到这里我们就升级成功了。

五、总结

根据上面的演示，我们发现MySQL数据库升级并不是很难，主要得注意数据备份，防止数据和意外丢失。好了，这篇博客就到这里吧。

最后，希望大家有所收获吧^_^……