MySQL Upgrade method Guide Daquan

Original: MySQL Upgrade method Guide Daquan

In general, when upgrading from one release version to another, we recommend that you upgrade the version in order. For example, to upgrade MySQL 3.23, upgrade to MySQL 4.0 instead of directly upgrading to MySQL 4.1 or MySQL 5.0.
If you are comparing the newer version of the upgrade you can refer to the following article,
MySQL database version updated quickly, the new features are constantly updated, but also to solve a lot of bugs that affect our application, in order to make our MySQL better, we need to upgrade it, although you will say that it is now running very well and stable fully enough. Let's take a look at several common upgrade methods.

Before we do this, let's make a statement that MySQL uses a binary package to install, and the upgrade is done on the same DB server.

The first, very simple, applies to any storage engine.

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

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

# Mysqldump-p3307-uroot Create mysqlsystems_com

3. Back up the database under the old version.

# mysqldump-p3306-uroot Mysqlsystems_com >MYSQLSYSTEMS_COM.BK

Note: You can also add the –opt option so that you can use an optimized way to export your database to reduce unknown issues.

4. Import the exported database backup into the new version of the MySQL database.

# mysql-p3307-uroot Mysqlsystems_com <MYSQLSYSTEMS_COM.BK

5. Overwrite the MySQL database in the data directory in the old version database with the new version.

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

Note: Everyone also knows the importance of this default database.

6. In the new version of the implementation of the Mysql_upgrade command, in fact, this command contains a few 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: In each upgrade process, Mysql_upgrade This command we should go to execute, it through the Mysqlcheck command to help us to check whether the table is compatible with the new version of the database at the same time to fix, but also a very important role is to use the Mysql_fix_ Privilege_tables command to upgrade the permissions table.

7. Close the old version, change the new version of the database to 3306, and restart the MySQL database. In this case, the database upgrade is over in a simple environment.

Second, the same applies to any storage engine.

1. Install the new version of MySQL as well.

2. In the previous version, back up the database.

# MKDIR/OPT/MYSQLSYSTEMS_BK; MYSQLDUMP-P3306-UROOT–TAB=/OPT/MYSQLSYSTEMS_BK mysqlsystems_com

The Note:–tab option can generate two types of files with the suffix *.sql and *.txt under the backup directory MYSQLSYSTEMS_BK, where. SQL saves the SQL statement that created the table and. TXT holds the original data.

3. Next, update the data under the new version of the database.

# Mysqladmin-p3307-uroot Create mysqlsystems_com

# cat/opt/mysqlsystems_bk*. TRG
Start the server and pour the trigger:
Mysql> delimiter//;
Mysql> Source/tmp/triggers.sql//
Incompatible changes: MySQL 5.1.6 introduced a trigger permission mechanism. Previously, creating a trigger requires SUPER privileges, and now this operation requires only trigger permissions. This improves the security posture of permissions
Some MySQL 5.1 are reserved as keywords in MySQL 5.0 and are not reserved as keywords
The new "INSTALL PLUGIN" and "UNINSTALL PLUGIN" statements are introduced to manipulate API plug-ins. Similarly, when you create an fulltext index, you can associate the parser plug-in with the "with PARSER" clause
3. Upgrade from MySQL 4.1 to MySQL 5.0

Server section:

Incompatible changes: The index order of the TEXT field at the end of the hollow lattice of the InnoDB and MyISAM tables has changed. Therefore, you need to run the "checktable" statement to repair the data table, and if an error occurs, run the "OPTIMIZE table" or "REPAIR table" statement to fix or even re-dump (with mysqldump)
Incompatible changes: Starting with MySQL 5.0.15, how to handle the values populated in binary fields has changed. The padding value is now 0x00, not a space, and the trailing space is not removed when the value is taken
Incompatible changes: Starting with MySQL 5.0.3, the implementation of decimal has changed, and 5.0 has a much more restrictive format for decimal.
Incompatible changes: In MySQL 5.0.3 to 5.0.5 The Decimal field created in the version MyISAM and InnoDB tables has been upgraded to 5.0.6 crashes after
Incompatible changes: Starting with 5.0.3, unless there is an auxiliary symbolic link between the main function, the server will no longer load the user custom function (UDFs) By default, or it can be enabled by the--ALLOW-SUSPICIOUS-UDFS option
Incompatible changes: The update log is disabled in 5.0, but it can be replaced with binary logs (binary log)
Incompatible changes: The ISAM type storage engine is no longer supported in 5.0 (it is not recommended to do so by recompiling source code support)
Incompatible changes: The MyISAM RAID option is no longer supported in 5.0, you can export the old table with mysqldump and then re-return it for upgrade
In 5.0.6, there have been some changes to the binary log of the record stored procedures and triggers, as described in the manual "17.4 binary Logging ofstored Routines and Triggers"
SQL section:

Incompatible changes: Starting from 5.0.12, the natural connection and use of the connection, including the derivative form of the external connection, are handled according to the SQL:2003 standard; This change leads to a reduction in the number of result fields resulting from natural connections and the use of using connections, These fields are also displayed in a more logical order, with the comma-comparer precedence and join, as in the left join.
Incompatible changes: In the past, a lock that waits for a timeout causes InnoDB to roll back all the current transactions, starting with 5.0.13 and rolling back only the most recent SQL statements.
Incompatible changes: Trigger changes, as mentioned earlier
Incompatible changes: Starting with 5.0.15, the CHAR () function returns a binary string instead of a string in the concatenated character set format. Clause usingcharset_name can customize the character set of the returned result
Incompatible changes: Before 5.0.13, now () and Sysdate () return the same results. But starting with 5.0.13, Sysdate () returns the time of the statement execution point, which may be different from the result returned by now (), but you can use the--sysdate-is-now option to have Sysdate () as the function with the same name as now ()
Incompatible changes: Before 5.0.13, Greatest (X,null) and LEAST (X,null) return x if X is not a NULL value. Starting with 5.0.13, NULL is returned as long as any parameter is NULL, just like Oracle
Incompatible changes: Before 4.1.13/5.0.8, DATETIME Plus 0 is converted to YYYYMMDDHHMMSS format and now becomes yyyymmddhhmmss.000000 format.
Incompatible changes: In 4.1.12/5.0.6, the statement LOAD DATA INFILE and SELECT ... into outfile, the result is changed when the value of fields TERMINATED by and fields enclosed is empty. Previously, the field was read and written according to the width it displayed. Now it's going to read and write to the width that is sufficient to save the field value. However, for mysql4.0.12/5.0.6, the files that were produced before them may not be properly imported with the LOAD DATA INFILE statement
Some MySQL 5.0 are reserved as keywords in MySQL 4.1 and are not reserved as keywords
Starting with 5.0.3, DECIMAL is stored in a more efficient format
5.0.3 starts by using exact math when calculating DECIMAL values and rounding exact values
4.1, the comparison between FLOAT or DOUBLE happens to be fine, but in 5.0 it's probably not.
At the beginning of 5.0.3, the spaces at the end of the VARCHAR and VARBINARY fields are no longer deleted
Starting with 5.0.3, BIT is a separate data type, no longer the same name as TINYINT (1).
MySQL 5.0.2 adds some SQL schemas to make it more tightly controlled to exclude illegal or missing records
Starting with 5.0.2, the keyword SCHEMA and SCHEMAS are considered to be the names of the DATABASE and databases respectively.
5.0 the user variable is not case sensitive, while 4.1 is not
Added a new boot option innodb_table_locks, which causes lock table to request InnoDB table locks as well. This option is turned on by default, but may cause deadlocks in autocommit=1 and lock TABLES applications
C API section:

Incompatible changes: Since the implementation of the decimal data type in 5.0 has changed, you need to be aware of this issue if you are using a version of the library file
Incompatible changes: In 5.0.3, the er_warn_data_truncated warning sign is renamed warn_data_truncated
The reconnect flag in the MYSQL structure is set to 0 by Mysql_real_connect ().
4. Upgrade from MySQL 4.0 to MySQL 4.1

Server section:

Incompatible changes: Several of the following are required to rebuild the data table, you can use mysqldump to export the table and re-lead back

If you created a InnoDB table with the TIMESTAMP field in 4.1.0 to 4.1.3 MySQL version. The table needs to be rebuilt when upgrading to 4.1.4 and higher because the storage format has changed
Starting with 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 multibyte fields are prefixed with the 4.1.0 to 4.1.5 version of MySQL, the table must be rebuilt at the time of upgrade to 4.1.6 and higher
If the accented characters are used in the database, table, field, and constraint names before 4.1 (the byte value is 128 to 255 characters), you cannot upgrade directly to 4.1. Because 4.1 uses UTF8 to store metadata names.
Strings are compared against standard SQL: The trailing spaces are not deleted before the comparison, and shorter strings are previously expanded with trailing spaces. Now the result is ' a ' > ' a\t ', not in the past. You can use Mysqlcheck to check the data sheet.
MyISAM now uses a better checksum algorithm.
Incompatible changes: MySQL understands the length definition of a String type field as a character length instead of a byte length.
Important: MySQL 4.1 stores data table names and field names with the UTF8 character set. If you use a character other than the standard 7-byte Us-ascii range as the table name/field name, you need to rebuild the table
Important: After upgrading to 4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1 because InnoDB uses more than one table space
Incompatible changes: MySQL 4.1.13 supports setting the time zone for each connection, so the system variable timezone is changed to System_time_zone
All data tables and fields of non-binary strings (CHAR, VARCHAR, and TEXT) have character sets, binary string fields include Binary,varbinary, and blobs
MySQL4.0, if there are field types CHAR binary or varcharbinary, they are treated as binary string types
If the data table field stores the character set character data that MySQL 4.1 can support directly, you can convert the value of this field to be stored by the appropriate character set
MySQL 4.1 Describes the data structure description file. frm format slightly improved, the new version of the instinctive compatibility of the latest format, but the older versions can not
Add--shared-memory option on Server startup under Windows to support shared memory when connecting from local clients
Incompatible changes: Starting with MySQL 4.1.1, a great improvement has been made to the user-defined function collection interface.
Incompatible changes: Starting with 4.1.10a, unless there is a secondary link between the main function, the server will no longer load the user custom function (UDFs) By default, or it can be enabled by the--ALLOW-SUSPICIOUS-UDFS option
Client section:

Mysqldump--opt and--quote-names options are enabled by default
SQL section:

Incompatible changes: strings are compared against standard SQL, as mentioned in the "Server Changes" section above
Incompatible change: TIMESTAMP returns a string in ' Yyyy-mm-dd HH:MM:SS ' format. In MySQL 4.0, you can add the option--new to get the features in MySQL 4.1
Incompatible changes: Binary data such as 0xFFDF is treated as a string rather than as a number
Incompatible changes: Before MySQL4.1.1, the statement parser is not so strict that it ignores the other characters in front of the first number when processing a string-to-time conversion. After the 4.1.1, it was more rigorous.
Incompatible changes: The Type field of the MySQL 4.1.2,show TABLE STATUS result is renamed Engine
When you execute a multiple-table DELETE statement, the table you want to delete can only use its alias, not the real table name
The result of a function that returns a date, DATETIME, or time type is converted to a temporal type
Auto_increment field Cannot set default value
LIMIT no longer accepts a negative parameter
SERIALIZE is no longer a valid value for the Sql_mode variable, its substitution value is SET TRANSACTION isolationlevel SERIALIZABLE
C API section:

The password hashing algorithm in MySQL 4.1 has been improved to improve security, but can cause compatibility issues. A problem occurs with MySQL4.0 and earlier client library files.

Incompatible changes: the Mysql_shutdown () function adds a parameter: Shutdown-level
Some functions such as Mysql_real_query () return 1 instead of 1 when an error occurs
Password Processing Section:

The password hashing algorithm in MySQL 4.1 has been improved to improve security, but can cause compatibility issues. A problem occurs with MySQL4.0 and earlier client library files. The solutions are:

Upgrade client library files to 4.1 (do not upgrade server side library files)
Run the Mysql_fix_privilege_tables script to widen the value of the password field in the user table to accommodate the new hashing algorithm. If you want to allow clients under 4.1 to be able to connect to the server, the server is running with additional parameters--old-passwords
5. Appendix

1.) Upgrade the MySQL steps on the Windows platform:

Back up old data
Stop the old server
Delete the MySQL service from the Windows System service, using the following command:
c:\> C:\mysql\bin\mysqld--remove install MySQL with executable installation files, or unzip a binary compressed package that can be executed directly to install
To re-register the MySQL service, use the following command:
c:\> C:\mysql\bin\mysqld--install Restart the server
Other questions can be seen in the various upgrades described above.
2.) Upgrade Authorization Form

Be sure to back up the MySQL database before upgrading the authorization form, in case the upgrade fails with the old authorization table.

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 while the mysqld is running, trying to connect to the server using the root account, so when root requires 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_tablesroot_password
Next the Mysql_fix_privilege_tables script upgrades the authorization table, and there may be some duplicatecolumn name warnings in the process, regardless of them. Once it has finished running, reboot the server.

On the Windows platform, authorization tables are not easy to upgrade to 4.0.15. Starting with 4.0.15, the release contains a SQL script: Mysql_fix_privilege_tables.sql, which runs it with the MySQL client to upgrade the authorization table, running a command similar to the following:

c:\> C:\mysql\bin\mysql-u root-p MySQL
Mysql> Sourcec:/mysql/scripts/mysql_fix_privilege_tables.sql
Change the directory mentioned above into a real directory.

3.) Upgrade Sync

Please see my translated document "6.6 Upgrade Sync"

4.) mysql_update MySQL upgrade check data sheet

You must run the Mysql_upgrade script every time you upgrade. It examines the incompatibility of all database tables under the current version of MySQL, checks the tables, and fixes the tables if they are found to be problematic. Mysql_update also upgrades the system tables, so it can be compatible with the new permissions mechanism and use new permissions.

Since Mysql_update will flag the checked and repaired tables with the current MySQL version number, it is guaranteed that the next time the script is run under the same MySQL version, it will again report which tables need to be repaired or checked.

It also logs 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 when checking the current release version, and to omit the file when checked, just attach the--force option.

In order to be able to inspect and repair the data table, and upgrade the system table, mysql_update executed the command:

Mysqlcheck--check-upgrade--all-databases--auto-repair
Mysql_fix_privilege_tables
Mysql_update currently only supports UNIX-like platforms; Under Windows, you need to manually execute the Mysqlcheck command, and upgrade the authorization form to see the Appendix "Upgrade Authorization Form".

When executing mysql_update, the MySQL server must be running, and it has the following parameters:

--help
Display Help information and exit

--basedir=path
Set the installation path for MySQL

--datadir=path
Set the MySQL data file path

--force
Tell Mysql_update to check if the Mysql_upgrade.info file is present and force check the version of the MySQL data sheet, whether or not it has been checked.

--user=user_name,-u user_name
User name connected to MySQL, default is root

--verbose
Redundancy mode. Print more information when a problem occurs

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