Shulou(Shulou.com)06/01 Report--

The following is mainly to bring you'In Place' to help MySQL5.0 upgrade directly to 5.7.I hope these contents can bring you practical use, which is also the main purpose of my editor'In Place' to help MySQL5.0 upgrade directly to 5.7.' All right, don't talk too much nonsense, let's just read the following.

In Place' upgrades are faster than Dump' upgrades because you skip the step of importing data. This saves time, but the backup before the upgrade is even more important, because you are performing the upgrade on the original directory. If you use the same data file, you can't use some new features that need to rebuild the database to use, such as creating undo tablespaces (introduced in version 5.6.3) or changing existing tables to separate tablespaces (innodb_file_per_table has been supported since 5.6.6).

I use the following steps to upgrade:

Start the instance of version 5.0.96 and import sakila schema data. For simplicity, use-- no-defaults.

Cd $. / scripts/mysql_install_db-- no-defaults-- datadir=-- basedir=.$. / bin/mysqld_safe-- no-defaults-- datadir=-- basedir=. -- port=-- socket= & $. / bin/mysql-uroot-- socket=-- execute= "create database sakila;" $. / bin/mysql-uroot-- socket=-- execute= "source sakila-schema.sql"-- database=sakila$. / bin/mysql-uroot-- socket=-- execute= "source sakila-data.sql"-- database=sakila

Shut down the database service. This is a good time for you to back up your database. Then, go to the folder of the new version of MySQL and start the database. Use the same data directory, so all your data is in

$cd $. / bin/mysqladmin-uroot-socket= shutdown$ cd $. / bin/mysqld_safe-- no-defaults-- datadir=-- basedir=. -- port=

-socket=-skip-grant-tables &

Note that-- skip-grant-tables must be used when starting the version 5.7 database for the first time.

Run mysql_upgrade. This will upgrade all system tables and check the user library.

$. / bin/mysql_upgrade-uroot-socket=

Import help sheet (optional)

$. / bin/mysql-uroot-socket=-execute= "source. / share/fill_help_tables.sql" mysql

Restart the MySQL service

$. / bin/mysqladmin-uroot-socket= shutdown$. / bin/mysqld_safe-- no-defaults-- datadir=-- basedir=. -port=-socket= &

Run mysqlcheck to check the database status

$. / bin/mysqlcheck-uroot-socket=-all-databases

If upgrading from 5.0.96, this upgrade method may require some tables or triggers to be rebuilt. Under the sakila conditions used in this article, I received the following warning message:

Warning: Triggers for table `sakila`.`customer` have no creation contextWarning: Triggers for table `sakila`.`customer`have no creation contextWarning: Triggers for table `sakila`.`payment`have no creation contextWarning: Triggers for table `sakila`.`rental` have no creation context

It needs to be overloaded due to changes in the metadata stored by the trigger. I use the following steps to rebuild the trigger:

Export triggers using mysqldump

/ bin/mysqldump-- socket=-uroot-- triggers-- no-create-db-- no-data-- no-create-info-- all-databases > addtriggers.sql

Generate a sql to delete existing triggers. Exclude the sys library because the above mysqldump does not export triggers in the sys library.

Mysql > SELECT CONCAT ('DROP TRIGGER', TRIGGER_SCHEMA,'., TRIGGER_NAME,';) FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema not in ('sys') INTO OUTFILE' droptriggers.sql'

Delete existing triggers

$. / bin/mysql-socket=-uroot-execute= "source droptriggers.sql"

Heavy-duty trigger

$. / bin/mysql-socket=-uroot-execute= "source addtriggers.sql"

Run mysqlcheck

$. / bin/mysqlcheck-uroot-socket=-all-databases

Using the above steps to upgrade from 5.0.96, 5.1.73, 5.5.46, and 5.6.27 to 5.7.9 was successful. Using mysqlcheck, count the number of tables, columns, and programs in all libraries, run basic select/insert/update/delete statements and execute and call user functions and stored procedures for verification.

(note: at the time of this writing, replication of versions 5.5 to 5.7 is not supported. In version 5.7.13, the bug was fixed.)

For the above about'In Place' to help MySQL5.0 upgrade directly to 5. 7, do you find it very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.