Page 1/5-MySQL tutorial

Source: Internet
Author: User
We recommend that you upgrade the version from one released version to another. For example, if you want to upgrade MySQL3.23 to MySQL4.0, instead of directly upgrading to MySQL4.1 or MySQL5.0.

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.

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 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/*. 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.