MySQL Upgrade Method Guide Daquan

MySQL Upgrade Method Guide Daquan

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

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 all is a newer version of the upgrade can refer to the following article, MySQL database version updated quickly, the new features are constantly updated, but also mainly to solve a lot of bugs that affect our application, in order to make our MySQL better, we need to upgrade it, Although you would say that it has run very well now and is stable 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, pour the trigger: mysql> delimiter//; Mysql> Source/tmp/triggers.sql//Incompatible changes: MySQL 5.1.6 introduces the trigger permission mechanism. Previously, creating a trigger requires SUPER privileges, and now this operation requires only TRIGGER permissions. This improves the security of permissions some MySQL 5.1 as a reserved keyword in MySQL 5.0 does not have a new introduction to the "INSTALL PLUGIN" and "UNINSTALL PLUGIN" statements for manipulating API plugins as reserved keywords. Similarly, when creating an 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 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 "CHECK table" statement to repair the data table, if an error occurs, run the "OPTIMIZE table" or "REPAIR table" statement repair, or even re-dump (with mysqldump) incompatible changes: Starting with the MySQL 5.0.15, How to handle the value of the padding in a BINARY field has changed. The padding value is now 0x00, not a space, and will not be removed at the end of the space incompatibility changes: Starting with MySQL 5.0.3, the implementation of the decimal has changed, the format of the 5.0 pairs of decimal strictly more 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: Update log is disabled in 5.0, but it can be replaced with binary log (binary logs): 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-enable the upgrade in 5.0.6, the binary log of the record stored procedures and triggers has changed, as described in the manual "17.4 binary Logging of Stored 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 reasonable order, with the comma-comparer precedence and the join, which is the same as the incompatible change in the left join: 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 incompatible as previously mentioned: starting with 5.0.15, the CHAR () function returns a binary string instead of a string in the concatenated character set format. Clause USING Charset_name can customize the change in character set incompatibility that returns results: 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, as incompatible with Oracle: Before 4.1.13/5.0.8, the 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 MySQL 4.0.12/5.0.6, the files that were introduced before them may not be correctly imported with the LOAD DATA INFILE statement in some MySQL 5.0 as reserved keywords in MySQL 4.1 and not as reserved keywords Starting with 5.0.3, DECIMAL is used in a more efficient format to store 5.0.3, starting with the calculation DECIMAL values and rounding exact values are used in exact math 4.1, the comparison between FLOAT or DOUBLE happens to be fine, but in 5.0 it may not be possible to start from 5.0.3, the space at the end of the VARCHAR and VARBINARY fields is no longer removed from 5.0.3, BIT is a separate data type, no longer the same name as TINYINT (1). MySQL 5.0.2 adds some SQL patterns to make it more restrictive to exclude illegal or missing records with more stringent controls starting with 5.0.2, the keyword SCHEMA and SCHEMAS are considered respectively DATABASE and DATABASES have the same name as the 5.0 user variable is case insensitive, while 4.1 does not add a new startup option Innodb_table_locks, which causes lock table can also request InnoDB table lock. This option is turned on by default, but may cause a deadlock in the C API section in the Autocommit=1 and lock TABLES apps:

Incompatible changes: Since the implementation of the DECIMAL data type in 5.0 has changed, it is important to note the incompatibility of this issue if you are using a version of the library file: 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 since 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 you prefix the UTF8 field or other multibyte field in the 4.1.0 to 4.1.5 version of MySQL, you must rebuild the table if you upgrade to 4.1.6 and higher if you have used accented characters in database, table, field, and constraint names before 4.1 (byte values are 128 to 255 characters) , then 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 datasheet MyISAM now uses a better checksum algorithm for 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 it is useful to use a character other than the standard 7-byte Us-ascii range as the table name/field name, it is important to rebuild the table: After upgrading to 4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1 because InnoDB uses multiple tablespaces for incompatible changes: MySQL 4.1.13 supports setting the time zone for each connection, so the system variable timezone changes 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 BLOB MySQL4.0, if there is a field type of CHAR binary or VARCHAR binary, 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 a data structure description file stored in MySQL 4.1 by the appropriate character set. frm's format is slightly improved, the new version is compatible with this newer format, but older versions do not add--shared-memory when the server starts under Windows option to support changes that use shared memory incompatibility when connecting from a local client: from MySQL 4.1.1 Beginning, the user Custom Function collection interface has undergone a great improvement in incompatible changes: starting from 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 you can pass-- The ALLOW-SUSPICIOUS-UDFS option to enable the client section:

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

Incompatible changes: strings are compared against standard SQL, such as the incompatible changes mentioned in the "Server Changes" section above: TIMESTAMP returns a string in ' Yyyy-mm-dd HH:MM:SS ' format. In MySQL 4.0, you can add the option--new to get a change in the incompatibility of this feature in MySQL 4.1: binary data such as 0xFFDF is treated as a string instead of a number of incompatible changes: Before MySQL 4.1.1, the statement parser is not so strict, It ignores other characters in front of the first number when it handles string-to-time conversions. After 4.1.1, the incompatible changes are more stringent: 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 actual table name, and the result is a DATE , the result of a DATETIME, or time-type function is converted to a auto_increment field that cannot be set to the default value, the limit no longer accepts a negative parameter, SERIALIZE is no longer a valid value for the Sql_mode variable, and its The substitution value is SET TRANSACTION isolation level 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 MySQL 4.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 in the Password Processing section if an error occurs:

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

Upgrade the client library file to 4.1 (without upgrading the server side library file) run the Mysql_fix_privilege_tables script to widen the Password field value 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, add the parameter--old-passwords 5 to the server runtime, appendix

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

Back up old data stop the old server from deleting the MySQL service from the Windows system service, use the following command: c:\> C:\mysql\bin\mysqld--remove install MySQL with executable installation files, or unzip the binary package that can be executed directly to install the re-registration MySQL service, with the following command: c:\> C:\mysql\bin\mysqld--install Restart the server other issues see the above mentioned in the various upgrades encountered in the situation 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_tables Root_password The next mysql_fix_privilege_tables script will upgrade the authorization table, there may be some Duplicate in the process Column name warning messages, 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> SOURCE c:/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 Setting the installation path for MySQL

--datadir=path setting the MySQL data file path

--force tells Mysql_update to ignore the existence of the Mysql_upgrade.info file when checking, and forcibly 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.