MySQL 5.1.73升级为MySQL 5.5.35详解

一、前言

二、概述

三、安装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源

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

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

2.同步时间

[root@node6 src]# yum
install -y ntp

[root@node6 src]# ntpdate
202.120.2.101

[root@node6 src]# hwclock –w

3.安装mysql 5.1依赖包

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

4.安装mysql5.5依赖包

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

5.安装cmake

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

三、安装MySQL 5.1.73

1.编译并安装mysql5.1

[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

上面配置内容省略……

This version of MySQL
Cluster is no longer maintained.

Please use the separate
sources 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 the
platform specific part of the reference manual

forhints about installing
MySQL on your platform.

Also have a lookat the files
inthe Docs directory.

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

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

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

2.创建数据目录并授权

[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 root
root 16384 8月 17 18:42 lost+found

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

3.为mysql提供配置文件

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

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

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

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

[client]

default-character-set= utf8

[mysqld]

default-character-set= utf8

datadir
=/data/mysql

5.提供启动脚本

[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 ~]# chkconfig
mysqld –add

[root@node6 ~]# chkconfig
mysqld on

6.初始化mysql

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

Installing MySQL system
tables…

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

140104 16:18:43
[Warning]’–skip-locking’is deprecated and will be removed ina future
release. Please use’–skip-external-locking’instead.

Filling help tables…

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

140104 16:18:43
[Warning]’–skip-locking’is deprecated and will be removed ina future
release. Please use’–skip-external-locking’instead.

To start mysqld at boot
timeyou have to copy

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

PLEASE REMEMBER TO SET A
PASSWORD 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/loc开发云主机域名al/mysql//bin/mysql_secure_installation

whichwill also give you the
option of removing thetest

databases and anonymous user
created by default. This is

strongly recommended
forproduction servers.

See the manual
formoreinstructions.

You can start the MySQL
daemon with:

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

You can testthe MySQL daemon
with mysql-test-run.pl

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

Please report any problems
with the/usr/local/mysql//scripts/mysqlbugscript!

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

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

140104 16:18:43
[Warning]’–skip-locking’is deprecated and will be removed ina future
release. Please use’–skip-external-locking’instead.

Filling help tables…

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

140104 16:18:43
[Warning]’–skip-locking’is deprecated and will be removed ina future
release. Please use’–skip-external-locking’instead.

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

7.查看一下初始化目录

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

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

8.启动一下mysql

[root@node6 ~]# service
mysqld start

Starting MySQL.. SUCCESS!

9.测试访问一下

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

Welcome to the MySQL
monitor. Commands end with ; or g.

Your MySQL connection idis 1

Server version: 5.1.73-log
Source distribution

Oracle is a registered
trademark of Oracle Corporation and/orits

affiliates. Other names may
be 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命令的查找路径

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

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

MANPATH/usr/local/mysql/man

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

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

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

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

[root@node6 mysql]# ldconfig

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

[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 MySQL
monitor. Commands end with ; or g.

Your MySQL connection idis 2

Server version: 5.1.73-log
Source distribution

Oracle is a registered
trademark of Oracle Corporation and/orits

affiliates. Other names may
be trademarks of their respective

owners.

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

mysql>

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

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

mysql> CREATE DATABASE
mydb;

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 create
database mydb;

+———-+—————————————————————+

| Database | Create
Database
|

+———-+—————————————————————+

|
mydb | CREATE DATABASE `mydb` /*!40100 DEFAULT
CHARACTER 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 table
t1;

+——-+————————————————————————————–+

| Table | Create
Table
|

+——-+————————————————————————————–+

| t1 |
CREATE TABLE `t1` (

`id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT
CHARSET=utf8 |

+——-+————————————————————————————–+

1 row inset(0.00 sec)

mysql> show tables;

+—————-+

| Tables_in_mydb |

+—————-+

|
t1 |

+—————-+

1 row inset(0.00 sec)

mysql> desc t1;

+——-+———+——+—–+———+——-+

+——-+———+——+—–+———+——-+

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

+——-+———+——+—–+———+——-+

1 row inset(0.33 sec)

15.增加数据

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

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

(2).查看一下

mysql>select* from
t1;

+——+

|id|

+——+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

+——+

10 rows inset(0.00 sec)

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

mysql> insert into
t1select* from t1;

Query OK, 10 rows affected
(0.00 sec)

Records: 10
Duplicates: 0 Warnings: 0

mysql> selectcount(*)
from t1;

+———-+

| count(*) |

+———-+

|
20 |

+———-+

1 row inset(0.33 sec)

四、升级为MySQL 5.5.35

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

(1).安装目录

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

(2).配置文件

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

(3).数据目录

[root@node6 ~]# /data/mysql

(4).启动脚本

[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.备份所有数据库

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

-l 锁定

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

4.备份安装目录

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

5.备份数据目录

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

6.备份配置文件

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

7.关闭mysql

[root@node6 ~]# service
mysqld stop

Shutting down MySQL..
SUCCESS!

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

8.升级mysql 5.5.35

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

[root@node6 mysql]# cd
mysql-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的配置文件

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

10.提供新的配置文件

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

11.修改一下配置文件

[mysqld]

datadir
=/data/mysql

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

12.尝试重新启动一下

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

Shutting down MySQL.
SUCCESS!

Starting MySQL.. SUCCESS!

13.执行更新程序并重启mysql

[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’

mydb.t1
OK

mydb.t2
OK

mysql.columns_priv
OK

mysql.db
OK

mysql.event
OK

mysql.func
OK

mysql.general_log
OK

mysql.help_category
OK

mysql.help_keyword
OK

mysql.help_relation
OK

mysql.help_topic
OK

mysql.host
OK

mysql.ndb_binlog_index
OK

mysql.plugin
OK

mysql.proc
OK

mysql.procs_priv
OK

mysql.proxies_priv
OK

mysql.servers
OK

mysql.slow_log
OK

mysql.tables_priv
OK

mysql.time_zone
OK

mysql.time_zone_leap_second
OK

mysql.time_zone_name
OK

mysql.time_zone_transition
OK

mysql.time_zone_transition_type
OK

mysql.user
OK

Running’mysql_fix_privilege_tables’…

14.查看生成的更新文件

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

[root@node6 mysql]#
ls

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

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

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

mydb
mysql-bin.000003 mysql-bin.000007 mysql_upgrade_infotest

[root@node6 mysql]# cat
mysql_upgrade_info

5.5.35

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

[root@node6 mysql]#
mysql

Welcome to the MySQL
monitor. Commands end with ; or g.

Your MySQL
connectionidis 9

Server version: 5.5.35-log
Source distribution

Oracle is a registered
trademark of Oracle Corporation and/orits

affiliates. Other names may
be 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.查看一下库和表

mysql> show
databases;

+——————–+

|
Database
|

+——————–+

| information_schema
|

|
mydb
|

|
mysql
|

| performance_schema
|

|test|

+——————–+

5 rowsinset(0.00 sec)

mysql> use mydb;

Database changed

mysql> show
tables;

+—————-+

| Tables_in_mydb
|

+—————-+

|
t1
|

|
t2
|

+—————-+

2 rowsinset(0.00 sec)

mysql>selectcount(*) from t1;

+———-+

| count(*) |

+———-+

| 20971520 |

+———-+

1 rowinset(0.00 sec)

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

五、总结

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

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

相关推荐: 数据库之锁模块

MyISAM与InnoDB关于锁方面的区别：注：实际上在不走索引的时候，InnoDB的实现方式和MyIsam的表锁方式不同，单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的…