Guide to MySQL upgrade

Guide to MySQL upgrade

Http://blog.sina.com.cn/s/blog_5a8b8eb80100ld7a.html

We recommend that you upgrade the version from one released version to another. For example, to upgrade MySQL 3.23 to MySQL 4.0, instead of directly upgrading to MySQL 4.1 or 5.0. For updates to the latest version, refer to the following article. The MySQL database version is updated quickly and new features are constantly updated, more importantly, it solves many bugs that affect our applications. To make MySQL better, we need to upgrade it, although you will say it is running well and stable, it is enough. Next, let's take a look at several common upgrade methods.

Before introduction, we should first make some declarations that MySQL uses binary packages for installation, and upgrades are performed on the same DB Server.

First, it is very simple and suitable for any storage engine.

1. download and install the new version of MySQL database, change its port to 3307 (to avoid conflict with the old version 3306), and start the service.

2. Create a database with the same name in the new version.

# Mysqldump-p3307-uroot create mysqlsystems_com

3. Back up the database in the old version.

# Mysqldump-p3306-uroot mysqlsystems_com> mysqlsystems_com.bk

Note: You can also add the-opt option, so that you can use the optimization method to export your database to reduce unknown problems.

4. Import the exported database backup to the new MySQL database.

# Mysql-p3307-uroot mysqlsystems_com <mysqlsystems_com.bk

5. overwrite all the mysql databases in the data directory of the old database to the new version.

# Cp-R/opt/mysql-5.1/data/mysql/opt/mysql-5.4/data

Note: Everyone knows the importance of this default database.

6. Execute the mysql_upgrade command in the new version. In fact, this command contains the following three commands:

# Mysqlcheck-check-upgrade-all-databases-auto-repair # mysql_fix_privilege_tables # mysqlcheck-all-databases-check-upgrade-fix-db-names-fix-table-names

Note: during each upgrade, we should execute the mysql_upgrade command. It uses the mysqlcheck command to help us check whether the database is compatible with the new version and fix it at the same time, another important role is to use the mysql_fix_privilege_tables command to upgrade the permission table.

7. Disable the old version, change the port used by the new version of the database to 3306, and restart the new version of MySQL database. At this point, the Database Upgrade in a simple environment is over.

Second, it also applies to any storage engine.

1. Install the new MySQL version first.

2. Back up the database in the old version.

# Mkdir/opt/mysqlsystems_bk; mysqldump-p3306-uroot-tab =/opt/mysqlsystems_bk mysqlsystems_com

Note: The-tab option can generate two types of files with the suffix *. SQL and *. txt under the Backup Directory mysqlsystems_bk. The SQL statement stores the SQL statement of the created table and the TXT statement stores the original data.

3. Next, update the data in the new database version.

# Mysqladmin-p3307-uroot create mysqlsystems_com

# Cat/opt/mysqlsystems_bk *. TRG starts the server and runs the following trigger: mysql> delimiter //; mysql> source/tmp/triggers. SQL // incompatible changes: MySQL 5.1.6 introduces the trigger permission mechanism. Previously, the SUPER permission was required to create a TRIGGER. Now, this operation only requires the TRIGGER permission. This improves permission security. Some MySQL 5.1 statements that are used as reserved keywords in MySQL 5.0 do not introduce the "install plugin" and "uninstall plugin" statements for operating the API plug-in. Similarly, when creating a FULLTEXT index, you can use the "with parser" clause to associate the PARSER plug-in 3. upgrade from MySQL 4.1 to MySQL 5.0.

Server:

Incompatible changes: the index order of TEXT fields ending with spaces in InnoDB and MyISAM tables has changed. Therefore, you need to run the "check table" statement to REPAIR the data TABLE. If an error occurs, run the "optimize table" or "repair table" statement to fix the problem, or even re-dump (use mysqldump) incompatible changes: Starting from MySQL 5.0.15, how to process the value filled in the BINARY field has changed. The filled value is now 0x00 instead of space, and the incompatible space at the end is not removed during the value: Starting from MySQL 5.0.3, the DECIMAL implementation method has changed, and the DECIMAL format limitation of 5.0 has changed significantly: after the DECIMAL field created in MyISAM and InnoDB tables between MySQL 5.0.3 and 5.0.5 is upgraded to 5.0.6, the system will crash and become incompatible: Starting from 5.0.3, unless there is a secondary symbolic link with the main function, the server will no longer load the User-Defined Function (UDFs) by default ), you can also use the -- allow-suspicious-udfs option to enable incompatible changes: update log is disabled in 5.0, But binary log can be used) to replace the incompatible changes: 5.0 does not support the ISAM storage engine anymore (Author: You can recompile the source code to support it, but it is not recommended to do so) incompatible changes: mySQL 5.0 does not support the RAID option of MyISAM anymore. You can use mysqldump to export the old table and re-import it back to upgrade to 5.0.6. The binary logs of the stored procedure and trigger have changed, for details, see the "17.4 Binary Logging of Stored Routines and Triggers" SQL section in the manual:

Incompatible changes: Starting from 5.0.12, the natural connections and USING connections, including derivative forms of external connections, are all handled in accordance with the SQL: 2003 standard; this change reduces the number of result fields produced by natural connections and USING connections, and displays these fields in a more reasonable order, the priorities of comma comparison operators and JOIN, incompatibility changes in left join: In the past, the lock waiting for timeout will cause InnoDB to roll back all the current transactions, starting from 5.0.13, it only rolls back the incompatible changes of the latest SQL statement: the changes of the trigger are the same as the changes mentioned earlier: Starting from 5.0.15, the CHAR () function returns the binary string, instead of strings in the format of the connected character set. The USING charset_name clause can customize the Character Set Incompatibility changes of returned results: Before 5.0.13, The results returned by NOW () and SYSDATE () are the same. However, starting from 5.0.13, SYSDATE () returns the statement execution time, which may be different from the result returned by NOW, however, the -- sysdate-is-now option can be used to make SYSDATE () incompatible with functions of the same name as NOW (): Before 5.0.13, GREATEST (x, NULL) and LEAST (x, NULL) If x is not NULL, return x. Starting from 5.0.13, if any parameter is NULL, NULL is returned, which is not compatible with Oracle: Before 4.1.13/5.0.8, DATETIME is added with 0 and converted to YYYYMMDDHHMMSS format, the format is changed to YYYYMMDDHHMMSS.000000: In 4.1.12/5.0.6, the statement load data infile and SELECT... in into outfile, when the values of fields terminated by and fields enclosed by are both null, the result is changed. Previously, fields were read and written according to the display width. Now it is written and written according to the width that is sufficient to save the field value. However, for MySQL 4.0.12/5.0.6, files that come out before them may not be able to use the load data infile statement to import some MySQL 5.0 files as reserved keywords. MySQL 4.1 does not start with 5.0.3 as reserved keywords, DECIMAL is stored in a more effective format starting from 5.0.3. When the exact DECIMAL value and rounding value are calculated using the exact math 4.1, the comparison between FLOAT and DOUBLE happens to be okay, but in 5.0, it may not work starting from 5.0.3. The spaces at the end of the VARCHAR and VARBINARY fields will not be deleted from 5.0.3. BIT is an independent data type and not TINYINT (1) mySQL 5.0.2 adds some SQL modes to control the exclusion of invalid or missing records more strictly starting from 5.0.2. The keywords SCHEMA and SCHEMAS are considered DATABASE and In DATABASES, the user variables in the same term 5.0 are not case sensitive. In DATABASES 4.1, a new startup option innodb_table_locks is added. This causes InnoDB TABLE locks to be requested during table lock. This option is enabled by default, but it may cause deadlock in the AUTOCOMMIT = 1 and lock tables applications c api section:

Incompatible changes: the DECIMAL data type Implementation Method in MySQL 5.0 has changed. Therefore, if you use a database file of a version, pay attention to this incompatible change: In 5.0.3, the ER_WARN_DATA_TRUNCATED warning symbol is changed to WARN_DATA_TRUNCATED. The reconnect mark in the MYSQL struct is set to 0 by mysql_real_connect. 4. upgrade from MySQL 4.0 to MySQL 4.1

Server:

Incompatible changes: You can use mysqldump to export the table and re-import it back.

If an InnoDB table containing the TIMESTAMP field is created in MySQL from version 4.1.0 to version 4.1.3. You need to recreate the table when upgrading to 4.1.4 and later, because the storage format has changed from 4.1.3, the InnoDB table uses the same character set comparison function to compare strings that are not latin1_swedish_ci character sets and are not BINARY. If the UTF8 field or other multi-byte fields are prefixed in MySQl from version 4.1.0 to version 4.1.5, the table must be rebuilt before the upgrade to 4.1.6 or higher. If the stress character (4.1 to 128 characters in bytes) is used in the database, table, field, and constraint name before the upgrade to 255 ), therefore, you cannot upgrade to 4.1 directly. Because 4.1 uses UTF8 to store metadata names. String comparison based on standard SQL: Compares spaces at the end of a string that are not deleted before, and extends short strings with spaces at the end. The result is 'A'> 'a \ t', which is not the same in the past. You can use mysqlcheck to check the data table MyISAM now using a better checksum Algorithm for incompatible changes: MySQL defines the length of string fields as the character length rather than the byte length. Important: MySQL 4.1 uses the UTF8 character set to store data table names and field names. If a character out of the Standard 7-byte US-ASCII range is used as the table name/field name, you need to recreate the table. Note: after upgrading to 4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1, incompatible changes because InnoDB uses multiple tablespaces: MySQL 4.1.13 allows each connection to set the time zone. Therefore, the system variable timezone is changed to all data tables and non-binary strings (CHAR, VARCHAR, and TEXT). BINARY string fields include BINARY, VARBINARY, and BLOB MySQL4.0. If the field type is char binary or varchar binary, they are treated as binary strings. If the fields in the data table store character set data that MySQL 4.1 can directly support, the value of this field can be converted to a data structure description file stored in MySQL 4.1 by the appropriate character set. the frm format is slightly improved. The new version is compatible with this new format, however, the old version does not allow the -- shared-memory option to be added when the windows Server is started to support changes in compatibility with the shared memory when the local client is connected: From MySQL 4.1.1, the user-defined function set interface is greatly improved and incompatible: Starting from 4.1.10a, unless there is a secondary link between it and the main function, otherwise, the server will no longer load user-defined functions (UDFs) by default. You can also enable the client using the -- allow-suspicious-udfs option:

Mysqldump is enabled by default -- opt and -- quote-names option SQL section:

Incompatible changes: strings are compared based on standard SQL, as mentioned in the "Server Changes" section above: TIMESTAMP returns 'yyyy-MM-DD HH: MM: ss' format string. In MySQL 4.0, you can add the option -- new to obtain the incompatible changes of this feature in MySQL 4.1: binary data such as 0 xFFDF is treated as a string rather than a number incompatible change: before MySQL 4.1.1, the statement parser is not so strict. It ignores other characters before the first number when processing string-to-time conversion. After MySQL 4.1.1, the Type field of the show table status result is renamed to Engine. When the multi-TABLE deletion statement is executed, the table to be deleted can only use its alias, but cannot return DATE, DATETIME, or the result of a TIME-type function is converted to the AUTO_INCREMENT field of the TIME type. The DEFAULT value cannot be set. LIMIT no longer accepts the negative number parameter. SERIALIZE is no longer the valid value of the SQL _mode variable, it is replaced by the set transaction isolation level serializable c api section:

The password hash algorithm in MySQL 4.1 has been improved to improve security, but it may cause compatibility problems. Problems may occur when you use MySQL 4.0 or earlier client library files.

Incompatible changes: the mysql_shutdown () function adds a parameter: SHUTDOWN-level some functions, such as mysql_real_query (), return 1 rather than-1 for password processing when an error occurs:

The password hash algorithm in MySQL 4.1 has been improved to improve security, but it may cause compatibility problems. Problems may occur when you use MySQL 4.0 or earlier client library files. Solutions:

Upgrade the client library file to 4.1 (without upgrading the server library file). Run the mysql_fix_privilege_tables script to increase the Password field value in the user table to adapt to the new hash algorithm. If you want to allow clients lower than 4.1 to connect to the server, add the parameters -- old-passwords 5 and appendix during server running.

1) Steps for upgrading MySQL on Windows:

Back up old data and stop the old server from deleting the mysql service from the windows System Service. Run the following command: C: \> C: \ mysql \ bin \ mysqld -- remove install mysql using an executable installation file, or decompress the binary compressed package that can be directly executed to install and re-register the mysql service. Run the following command: C: \> C: \ mysql \ bin \ mysqld -- install restart the server for other issues. For details, refer to the above-mentioned various upgrades. 2.) upgrade authorization table

Before upgrading the authorization table, you must back up the mysql database to use the old authorization table when the upgrade fails.

In unix or unix-like systems, run the mysql_fix_privilege_tables script to upgrade the authorization table:

Shell> mysql_fix_privilege_tables must execute this script when mysqld is running. It tries to use the root account to connect to the server. Therefore, when the root account needs a password, specify the password as follows:

Shell> mysql_fix_privilege_tables -- password = root_password before MySQL 4.1, the password is specified in the following form:

Shell> mysql_fix_privilege_tables root_password next, the mysql_fix_privilege_tables script will upgrade the authorization table. During this process, there may be some Duplicate column name warning information, so ignore them. After running it, restart the server.

On windows, it is not easy to upgrade the authorization table to 4.0.15. Starting from 4.0.15, the release contains an SQL Script: mysql_fix_privilege_tables. SQL, which is run on the mysql client to upgrade the authorization table. Run a command similar to the following:

C: \> C: \ mysql \ bin \ mysql-u root-p mysql> source c:/mysql/scripts/mysql_fix_privilege_tables. SQL change the directory mentioned above to a real directory.

3) Update Synchronization

Please refer to my translation document "6.6 upgrade synchronization"

4) Check the data table during MySQL upgrade

The mysql_upgrade script must be run during each upgrade. It checks the incompatibility of all database tables in the current version of MySQL. It checks these tables and fixes them if any problem is found. Mysql_update also upgrades the system table, so it can be compatible with the new permission mechanism and use the new permissions.

Mysql_update will mark the tables that have been checked and repaired with the current MySQL version number, so that the next time you run this script in the same MySQL version, the tables to be repaired or checked will be reported again.

It also records the MySQL version number in a file in the data file directory: mysql_upgrade.info. This file is used to identify which tables can be skipped during the current release version checklist. To ignore this file during the check, you only need to attach the -- force option.

To check and repair the data table and upgrade the system table, mysql_update runs the following command:

Mysqlcheck -- check-upgrade -- all-databases -- auto-repair mysql_fix_privilege_tables mysql_update currently only supports unix-like platforms. In windows, you must manually execute the mysqlcheck command, for the upgrade authorization table, see Appendix "upgrade authorization table ".

When executing mysql_update, the MySQL server must be running. It has the following parameters:

-- Help: displays help information and exits.

-- Basedir = path: Set the MySQL installation path.

-- Datadir = path: Set the MySQL Data File path

-- Force tells mysql_update to ignore whether the mysql_upgrade.info file exists during the check and forcibly check the MySQL DATA table of this version, whether or not it has been checked

-- User = user_name,-u user_name: username used to connect to MySQL. The default value is root.

-- Verbose redundancy mode. Print more information when a problem occurs.

Other options such as -- password [= password] are to be passed to the mysqlcheck and mysql_fix_privilege_tables scripts, and are not required.