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 ):