[MySQL O & M] upgrade MySQL5.1 to MySQ5.5

In the past, most of the company's projects were on version 5.5, and there was just a project (free trading platform) with a small amount of data. A single server ran stably online and rarely took over, to unify the version, I plan to upgrade it to MyS.

In the past, most of the company's projects were on version 5.5, and there was just a project (free trading platform) with a small amount of data. A single server ran stably online and rarely took over, to unify the version, I plan to upgrade it to MyS.

# Comment out default_table_type = MyISAM, which is not recognized in 5.5

# Comment out myisam_max_extra_sort_file_size which is not recognized in 5.5

# Innodb_file_io_threads is not recognized and changed

Innodb_read_io_threads = 8

Innodb_write_io_threads = 8

# Increase the volume of dirty pages refreshed and the number of merged inserts, improve disk I/O processing capability, and adjust the IOPS based on your own disk capacity

Innodb_io_capacity = 2000

# Change the previously built-in file format Antelope to Barracuda

Innodb_file_format = Barracuda

# Modify the dirty page from 25 to 90. The value can be selected between 70 and 90.

Innodb_max_dirty_pages_pct = 90

# Change the redo log of transaction logs from 256M to 1024 M

Innodb_log_file_size = 1024

# Using independent tablespace

Innodb_file_per_table = 1

Restart MySQL to take effect:

[Root @ db_fb05 mysql-5.1.62] # service mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL... SUCCESS!

[Root @ db_fb05 mysql-5.1.62] #

Ii. Upgrade steps

Note the following important parameters before upgrading: innodb_fast_shutdown

Disable this function before Upgrade (please refer to the Manual for reasons ):

(Root: opdba.com: Sat Dec 1 18:14:48 2012) [(none)]> show variables like '% fast % ';

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

| Variable_name | Value |

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

| Innodb_fast_shutdown | 1 |

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

1 row in set (0.00 sec)

(Root: opdba.com: Sat Dec 1 18:35:20 2012) [(none)]> set global innodb_fast_shutdown = 0;

Query OK, 0 rows affected (0.00 sec)

(Root: opdba.com: Sat Dec 1 18:35:43 2012) [(none)]> show variables like '% fast % ';

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

| Variable_name | Value |

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

| Innodb_fast_shutdown | 0 |

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

1 row in set (0.00 sec)

1. Stop the current MySQL Service

Service mysqld stop

2. Link to the new MySQL directory

Cd/opt/webserver/

Unlink mysql

Ln-sv mysql-5.5.34-linux2.6-x86_64 mysql

3. Start the service

Service mysqld start

4. Execute update program upgrade

/Opt/webserver/mysql/bin/mysql_upgrade

Looking for 'mysql' as:/opt/webserver/mysql/bin/mysql

Looking for 'mysqlcheck' as:/opt/webserver/mysql/bin/mysqlcheck

Running 'mysqlcheck' with connection arguments: '-- port = 100'' -- socket =/data/mysql. sock'

/Opt/webserver/mysql/bin/mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/data/mysql. sock (111) when trying to connect

Fatal error: Upgrade failed

The above error occurs because the root user of the current MySQL server has set a password. Add the-p parameter to mysql_upgrade to batch root user passwords:

/Opt/webserver/mysql/bin/mysql_upgrade-padmin

Looking for 'mysql' as:/opt/webserver/mysql/bin/mysql

Looking for 'mysqlcheck' as:/opt/webserver/mysql/bin/mysqlcheck

Running 'mysqlcheck' with connection arguments: '-- port = 100'' -- socket =/data/mysql. sock'

Running 'mysqlcheck' with connection arguments: '-- port = 100'' -- socket =/data/mysql. sock'

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

If the above information appears, the upgrade is OK!

View the current version:

[Root @ db_fb010 ~] #/Opt/webserver/mysql/bin/mysql-V

Logging to file '/data/mysql/logs/query. Log'

/Opt/webserver/mysql/bin/mysql Ver 14.14 Distrib 5.5.34, for linux2.6 (x86_64) using readline 5.1

The mysql_upgrade Command actually performs the following operations:

Mysqlcheck -- all-databases -- check-upgrade -- auto-repair

Mysql <fix_priv_tables

Mysqlcheck -- all-databases -- check-upgrade -- fix-db-names -- fix-table-names

5. fixed storage process problems

After upgrading from 5.1 to 5.5, if a stored procedure is unavailable, the following error is reported:

(Root: opdba.com: Sat Dec 1 18:14:48 2012) [(none)]> show procedure status;
ERROR 1548 (HY000): Cannot load from mysql. proc. The table is probably updated upted

After the above error is reported, check the proc status of the tables on both sides and find: show create table proc \ G ------>

In mysql. proc 5.1, the comment field is char (64 ):