Shulou(Shulou.com)06/01 Report--

I. Preface

II. Overview

Third, install MySQL 5.1.73

Upgrade to MySQL 5.5.35

V. Summary

Note: the latest version of the test environment CentOS 6.4x86m64 MySQL (5.1.73,5.5.35) is currently available. Download address: http://dev.mysql.com/downloads/mysql/5.1.html#downloads.

I. Preface

In previous blogs, we talked about the performance differences between MySQL5.1 and MySQL5.5, and the performance of MySQL5.5 has been significantly improved, especially the support for multicore CPU and the improvement of TPS performance. In this blog we will focus on upgrading MySQL 5.1.73 to MySQL 5.5.35. Let's move on to.

II. Overview

1. Install the yum source

one

two

[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

two。 Synchronization time

one

two

three

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

[root@node6 src] # ntpdate 202.120.2.101

[root@node6 src] # hwclock-w

3. Install the mysql 5.1 dependency package

one

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

4. Install the mysql5.5 dependency package

one

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

5. Install cmake

one

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

Third, install MySQL 5.1.73

1. Compile and install mysql5.1

one

two

three

[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

The above configuration content is omitted.

one

two

three

four

five

six

seven

eight

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.

Here we compile the configuration is complete, let's compile and install.

one

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

Note, the compilation and installation time is relatively long, please wait patiently, of course, you will see the configuration of the blog machine, relatively speaking, the better the configuration, the less relative compilation and installation time.

two。 Create a data directory and authorize

one

two

three

four

five

six

seven

eight

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

Mkdir: directory "/ data/mysql" has been created

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

The total dosage is 20

Drwx-. 2 root root 16384 August 17 18:42 lost+found

Drwxr-xr-x. 2 mysql mysql 4096 January 4 16:10 mysql

3. Provide configuration files for mysql

one

two

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

Cp: overwrite "/ etc/my.cnf"? Y

4. Simply modify the configuration file

one

two

three

four

five

six

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

[client]

Default-character-set= utf8

[mysqld]

Default-character-set= utf8

Datadir = / data/mysql

5. Provide startup script

one

two

three

four

[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. Initialize mysql

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

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

OK

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.

OK

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-u root password' new-password'

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

Alternatively you can run:

/ usr/local/mysql//bin/mysql_secure_installation

Whichwill also give you the option of removing the test

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!

Note, we have seen several warnings from the above, let's check it out.

one

two

three

four

five

six

seven

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.

OK

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.

As you can see from the above warning, the-- default-character-set,-- skip-locking options are out of date, and-- character-set-server,-- skip-external-locking is recommended.

7. Check the initialization directory

one

two

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

Mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index test

8. Start mysql.

one

two

[root@node6 ~] # service mysqld start

Starting MySQL.. SUCCESS!

9. Test and visit.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

[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

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

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 >

Well, our mysql has basically been installed here, but we also mentioned to do some optimization work.

10. Output the man manual of mysql to the lookup path of the man command

one

two

three

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

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

MANPATH / usr/local/mysql/man

11. Output the header file of mysql to the system header file path / usr/include

one

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

twelve。 Output the library file of mysql to the system library to find the path

one

two

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

[root@node6 mysql] # ldconfig

13. Modify the PATH environment variable so that the system can directly use the relevant commands of mysql

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

[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

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

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 >

All right, at this point, all of our mysql has been installed, so let's prepare the test environment.

14. New test database and test table

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

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

Empty set (0.01sec)

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

+-+ +

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

+-+ +

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

+-+ +

1 row inset (0.33 sec)

15. Increase data

(1)。 Simply insert 10 rows of data first

one

Mysql > insert into T1 value (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

(2)。 Check it out

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

Mysql > select* from T1

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

| | 6 |

| | 7 |

| | 8 |

| | 9 |

| | 10 |

+-+

10 rows inset (0.00 sec)

(3)。 Insert multiple rows of data method

one

two

three

four

five

six

seven

eight

nine

ten

Mysql > insert into T1 select* 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)

Upgrade to MySQL 5.5.35

1. Prepare before upgrade (check the relevant parameters of MySQL 5.1, as follows)

(1)。 Installation directory

one

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

(2)。 Configuration file

one

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

(3)。 Data catalog

one

[root@node6 ~] # / data/mysql

(4)。 Startup script

one

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

(5)。 Other parameters

Output the man manual of mysql to the lookup path of the man command

Output the header file of mysql to the system header file path / usr/include

Output the library file of mysql to the system library to find the path

Modify the PATH environment variable so that the system can directly use the relevant commands of mysql

two。 Upgrade mode

Directly overwrite the MySQL5.5 installation directory over the running Mysql directory (the way we demonstrate it here)

Install MySQL5.5 to another directory, such as / usr/local/mysql5

3. Back up all databases

one

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

-l lock

-F is flush logs, and you can regenerate new log files, including log-bin logs, of course.

4. Back up the installation directory

one

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

5. Backup data directory

one

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

6. Backup configuration fil

one

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

7. Close mysql

one

two

[root@node6 ~] # service mysqld stop

Shutting down MySQL.. SUCCESS!

All right, here we are, and the preparatory work is done. Let's upgrade to MySQL 5.5.35.

8. Upgrade mysql 5.5.35

one

two

three

four

[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. Delete the configuration file of the original mysql5.1

one

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

10. Provide a new profile

one

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

11. Modify the configuration file

one

two

[mysqld]

Datadir = / data/mysql

Note, only add one row to the specified data data / data/mysql.

twelve。 Try to restart it.

one

two

three

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

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

13. Execute the update and restart mysql

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

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

Looking for 'mysql' as: / usr/local/mysql/bin/mysql

Looking for '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.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'...

OK

14. View the generated update file

one

two

three

four

five

six

seven

eight

[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_info test

[root@node6 mysql] # cat mysql_upgrade_info

5.5.35

15. Try to log in and check the version

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

[root@node6 mysql] # mysql

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

Your MySQL connection id is 9

Server version: 5.5.35-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.5.35-log |

+-+

1 row in set (0.00 sec)

16. Check the library and table

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mydb |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > use mydb

Database changed

Mysql > show tables

+-+

| | Tables_in_mydb |

+-+

| | T1 |

| | T2 |

+-+

2 rows in set (0.00 sec)

Mysql > select count (*) from T1

+-+

| | count (*) |

+-+

| | 20971520 |

+-+

1 row in set (0.00 sec)

All right, at this point, we have successfully upgraded.

V. Summary

According to the above demonstration, we find that it is not very difficult to upgrade the MySQL database, we should mainly pay attention to data backup to prevent data and accidental loss. All right, that's all for this blog.

Finally, I hope you can get something from it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.