MySQL upgrade method guide page 1/5

If all the updates are updated, refer to the following Article ,
MySQL database versions are updated quickly, and new features are constantly updated. What's more, it solves many bugs that affect our applications. In order to make our MySQL better, we need to upgrade it, even though you will say it is running well and stable, and 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 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/*. SQL | mysql-p3307-uroot mysqlsystems_com (create tables)

# Mysqlimport mysqlsystems_com/opt/mysqlsystems_bk/*. txt (load data)

4. All subsequent steps are the same as those in step 5, step 6, and step 7 of the first method.

The third method is applicable to the MyISAM storage engine, where all files are copied.

1. install.

2. Copy all. frm,. MYD, And. myi files from the old mysqlsystems_com database to the same directory of the new version.

3. The subsequent steps are the same as those of the first three steps.

The above are three methods to upgrade mysql. It seems that there are no problems. In fact, there are many problems in the actual production environment, this requires us to fully understand what new features are added to the new version before the upgrade, and further analyze whether these new features will affect our original application after the upgrade.

For the upgrade of the old version of MySQL, see the next page.