Upgrade with Mysql_upgrade upgrade Authorization form

1.7.1 upgrade with Mysql_upgrade upgrade Authorization form (1)

This upgrade method is more convenient, through the Mysql_upgrade command can be completed. Here's a demonstration of the entire upgrade process.

1) Modify the MY.CNF configuration file because the parameters in MySQL5.1 are not recognized in the 5.5 version.

2. #skip-locking in 5.5, changed to
4. Skip-external-locking
8. #log-long-format in 5.5, changed to
10. Log-short-format
14. #log_slow_queries have not been recognized in 5.5, changed to
16. Slow-query-log
20. #Default-character-set = UTF8 has not been recognized in 5.5, changed to
22. Character-set-server = UTF8
26. #注释掉default_table_type = MyISAM? It's not recognized in 5.5 .
30. #注释掉MyISAM_max_extra_sort_file_size's not been recognized in 5.5.
34. #innodb_file_io_threads已经不识别了, change into
36. Innodb_read_io_threads = 8
38. Innodb_write_io_threads = 8
42. # Change the previous built-in file format to Barracuda
44. Innodb_file_format = Barracuda
46. #注释掉同步的信息, otherwise start the times wrong
48. #master-host = 192.168.110.20
50. #Master-user = repl
52. #Master-password = repl
54. #master-connect-retry=
56. #另如果你采用了InnoDB-plugin, write it off.
58. #ignore_builtin_innodb
60. #plugin-load=innodb=ha_innodb_plugin.so; innodb_trx=ha_innodb_plugin.so; innodb_locks=ha_innodb_plugin.so; innodb_lock_waits=ha_i
62. nnodb_plugin.so; innodb_cmp=ha_innodb_plugin.so; innodb_cmp_reset=ha_innodb_plugin.so; innodb_cmpmem=ha_innodb_plugin.so;innodb_c
64. mpmem_reset=ha_innodb_plugin.so

2) Modify Innodb_fast_shutdown equals 0. Let's take a look at what this parameter does.

Explanation: When MySQL is turned off, it is set to 1, does not erase dirty pages and insert buffer merge operations, and does not brush dirty pages into disk. Set to 0 to clean up dirty pages and insert buffer merge operations, and to brush dirty pages into the disk, so set the shutdown speed is the slowest. Set to 2, do not clean the dirty page and insert buffer merge operation, do not brush the dirty page into the disk, but instead of brush into the redo log transaction log, the next time it starts MySQL resume, the shutdown speed is the fastest. When you do an upgrade or downgrade operation, you should set it to 0 to ensure data integrity and avoid errors. This parameter supports dynamic modification.

The command to set this parameter is as follows:

1. Set global innodb_fast_shutdown=0;

For an introduction to the Innodb_fast_shutdown parameters, see the MySQL5.5 Manual:

3) Close the MySQL service with the following command:

1. /etc/init.d/mysql stop or mysqladmin-uroot-p123456 shutdown

4) Skip the MySQL authorization form to start the MySQL service with the following command:

1. MySQLd --defaults-file=/etc/my.cnf--skip-grant-tables &

5) Perform the update as follows:

8. [Email protected] mysql]#/usr/local/mysql/bin/mysql_upgrade
10. Looking for ' MySQL ' As:bin/mysql
12. Looking for ' Mysqlcheck ' As:bin/mysqlcheck
14. Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/tmp/mysql.sock '
16. Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/tmp/mysql.sock '
18. Mysql.columns_priv OK
20. Mysql.db OK
22. Mysql.event OK
24. Mysql.func OK
26. Mysql.general_log OK
28. Mysql.help_category OK
30. Mysql.help_keyword OK
32. Mysql.help_relation OK
34. Mysql.help_topic OK
36. Mysql.host OK
38. Mysql.ndb_binlog_index OK
40. Mysql.plugin OK
42. Mysql.proc OK
44. Mysql.procs_priv OK
46. Mysql.servers OK
48. Mysql.slow_log OK
50. Mysql.tables_priv OK
52. Mysql.time_zone OK
54. Mysql.time_zone_leap_second OK
56. Mysql.time_zone_name OK
58. Mysql.time_zone_transition OK
60. Mysql.time_zone_transition_type OK
62. Mysql.user OK
64. Test.heartbeat OK
66. test.log20111127 OK
68. test.log20111128 OK
70. test.log20111129 OK
72. test.log20111130 OK
74. test.log20111201 OK
76. test.log20111202 OK
78. test.log20111203 OK
80. test.log20111204 OK
82. test.log20111205 OK
84. test.log20111206 OK
86. test.log20111207 OK
88. Test.t1 OK
90. Test.t2 OK
92. Running ' Mysql_fix_privilege_tables ' ...
94. Ok

95. .7.1 Upgrade with Mysql_upgrade upgrade Authorization form (2)

    6) Close the MySQL service again with the following command:
    

    1. /etc/init.d/mysql stop or mysqladmin-uroot-p123456 shutdown

    7) normal start MySQL service, command as follows:
    

    1. Mysqld_safe --defaults-file=/etc/my.cnf --user=MySQL &

    8) To view the version as follows:
    

    2. MySQL> select version ();
    4. +-----------------------------------------------------------+
    6. | Version () |
    8. +-----------------------------------------------------------+
    10. | 5.5.20-enterprise-commercial-advanced-log |
    12. +-----------------------------------------------------------+
    14. 1 row in Set (0.00 sec)
    16. MySQL> select @ @innodb_version;
    18. +----------------------------+
    20. | @ @innodb_version |
    22. +----------------------------+
    24. | 1.1.8 |
    26. +----------------------------+
    28. 1 row in Set (0.00 sec)

    This completes the upgrade process.

    During the upgrade process, there is an issue to be aware of, which was encountered by the author prior to installation. RHEL5.3 a system crash occurred while installing MySQL5.5 (1-24), when the environment was: the system is rhel5.3,64 bit, XFS partition, MySQL5.5.20 binary version.

    When the system crashes, the author uses Delldrac remote management card capture screenshot 1-25.

    As you can see, the XFS file system has errors when reading and writing. This problem occurs because the RHEL5.3 system kernel does not bring the XFS file system, it is supported by patching to the kernel, and later the system is upgraded to RHEL5.5, the system is normal, and no freezing situation occurs.

    Attention

    RHEL5.5 version, the kernel supports XFS file systems.

Upgrade with Mysql_upgrade upgrade Authorization form