Shulou(Shulou.com)05/31 Report--

MySQL 5.6.27 upgrade MySQL 5.7.18 version of the upgrade steps, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Name remarks before and after upgrade

OS version CentOS release 6.5 CentOS release 6.5

MySQL version 5.6.27 Community Edition 64bit 5.7.13 Community Edition 64bit Linux Generic Binaries

MySQL installation path / usr/local/mysql / opt/app/mysql/5.7.13

My.cnf storage path / etc/my.cnf / etc/my.cnf

Mysql basedir / usr/local/mysql / opt/app/mysql/5.7.13

Mysql Datadir / database/mysql3306/data / database/mysql/data

one。 Download MySQL 5.7.18

Download mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz from the official website

Official website address: http://dev.mysql.com/downloads/mysql/

# mkdir-p / opt/app/mysql

# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz-C / opt/app/mysql

# mv / opt/app/mysql/mysql-5.7.18-linux-glibc2.5-x86_64 / opt/app/mysql/5.7.18

Chown-R mysql.mysql / opt/app/mysql/5.7.18/

two。 Shut down MySQL 5.6.27 instance

[root@chengshuidai ~] # service mysqld stop

Shutting down MySQL.. SUCCESS!

Confirm that there is no mysql process:

[root@chengshuidai ~] # ps-ef | grep mysqld | grep-v grep

three。 Back up the MySQL 5.6.27 database:

1)。 Confirm the data file path:

# cat / etc/my.cnf | grep datadir

Datadir = / database/mysql/data

# cd / database

# pwd

/ database

2)。 Start backing up data files:

# cp-r mysql mysql3306

3)。 Back up the parameter file:

# cp / etc/my.cnf / etc/5.6.27_my.cnf

four。 Formally modify my.cnf configuration file # mainly modify basedir

Replace "basedir = / usr/local/mysql" in the / etc/my.cnf file with "basedir = / opt/app/mysql/5.7.18"

Then save the changes!

1)。 Start MySQL 5.7.18

/ opt/app/mysql/5.7.18/bin/mysqld-defaults-file=/etc/my.cnf &

Start the error message:

[root@chengshuidai mysql] # / opt/app/mysql/5.7.18/bin/mysqld-- defaults-file=/etc/my.cnf &

[1] 12747

[root@chengshuidai mysql] # 2017-04-27T05:48:11.328215Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-04-27T05:48:11.328365Z 0 [Warning] 'NO_ZERO_DATE',' NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2017-04-27T05:48:11.328374Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2017-04-27T05:48:11.328430Z 0 [Note]-- secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2017-04-27T05:48:11.328483Z 0 [Note] / opt/app/mysql/5.7.18/bin/mysqld (mysqld 5.7.18-log) starting as process 12747.

2017-04-27T05:48:11.335101Z 0 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

2017-04-27T05:48:11.335153Z 0 [ERROR] Aborting

2017-04-27T05:48:11.335200Z 0 [Note] Binlog end

2017-04-27T05:48:11.335278Z 0 [Note] / opt/app/mysql/5.7.18/bin/mysqld: Shutdown complete

After adding-- user=root, the startup still reports an error:

[root@chengshuidai mysql] # / opt/app/mysql/5.7.18/bin/mysqld-defaults-file=/etc/my.cnf-user=root &

[1] 12754

[root@chengshuidai mysql] # 2017-04-27T05:50:00.048115Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-04-27T05:50:00.048278Z 0 [Warning] 'NO_ZERO_DATE',' NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2017-04-27T05:50:00.048288Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2017-04-27T05:50:00.048359Z 0 [Note]-- secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2017-04-27T05:50:00.048413Z 0 [Note] / opt/app/mysql/5.7.18/bin/mysqld (mysqld 5.7.18-log) starting as process 12754.

2017-04-27T05:50:00.054466Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation

2017-04-27T05:50:00.054518Z 0 [ERROR] Aborting

Solution:

# vi / etc/my.cnf

Log_bin=mysql-bin

Server_id=58-add server_id content

Start again, successful:

[root@chengshuidai mysql] # / opt/app/mysql/5.7.18/bin/mysqld-defaults-file=/etc/my.cnf-user=root &

[1] 12768

[root@chengshuidai mysql] # 2017-04-27T05:54:10.228092Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-04-27T05:54:10.228188Z 0 [Warning] 'NO_ZERO_DATE',' NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2017-04-27T05:54:10.228192Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2017-04-27T05:54:10.228232Z 0 [Note]-- secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2017-04-27T05:54:10.228266Z 0 [Note] / opt/app/mysql/5.7.18/bin/mysqld (mysqld 5.7.18-log) starting as process 12768.

2017-04-27T05:54:10.233042Z 0 [Note] InnoDB: PUNCH HOLE support not available

2017-04-27T05:54:10.233072Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2017-04-27T05:54:10.233077Z 0 [Note] InnoDB: Uses event mutexes

2017-04-27T05:54:10.233081Z 0 [Note] InnoDB: GCC builtin _ _ sync_synchronize () is used for memory barrier

2017-04-27T05:54:10.233085Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3

.

.

2)。 Confirm that the startup is successful:

[root@chengshuidai mysql] # ps-ef | grep mysqld | grep-v grep

Root 12768 12577 1 13:54 pts/2 00:00:00 / opt/app/mysql/5.7.18/bin/mysqld-defaults-file=/etc/my.cnf-user=root

four。 Upgrade database (upgrade data dictionary)

1)。 Run mysql_upgrade to upgrade the data dictionary

[root@chengshuidai mysql] # / opt/app/mysql/5.7.18/bin/mysql_upgrade-uroot-p123456

Mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

2017-04-27T05:57:08.942356Z 3 [Warning] System table 'servers' is expected to be transactional.

Checking system database.

Mysql.columns_priv OK

Mysql.db OK

Mysql.engine_cost OK

Mysql.event OK

Mysql.func OK

Mysql.general_log OK

Mysql.gtid_executed OK

Mysql.help_category OK

Mysql.help_keyword OK

Mysql.help_relation OK

Mysql.help_topic OK

Mysql.innodb_index_stats OK

Mysql.innodb_table_stats OK

Mysql.ndb_binlog_index OK

Mysql.plugin OK

Mysql.proc OK

Mysql.procs_priv OK

Mysql.proxies_priv OK

Mysql.server_cost OK

Mysql.servers OK

Mysql.slave_master_info OK

Mysql.slave_relay_log_info OK

Mysql.slave_worker_info OK

Mysql.slow_log OK

Mysql.tables_priv OK

Mysql.time_zone OK

Mysql.time_zone_leap_second OK

Mysql.time_zone_name OK

Mysql.time_zone_transition OK

Mysql.time_zone_transition_type OK

Mysql.user OK

Upgrading the sys schema.

Checking databases.

Sys.sys_config OK

Test.chenfeng OK

Test.duansf OK

Upgrade process completed successfully.

Checking if update is needed.

2)。 Restart MySQL 5.7.18 instance

Close the MySQL instance:

[root@chengshuidai tmp] # / opt/app/mysql/5.7.18/bin/mysqladmin-uroot-p123456-S / tmp/mysql.sock shutdown

Mysqladmin: [Warning] Using a password on the command line interface can be insecure.

2017-04-27T05:59:23.778618Z 0 [Note] Giving 0 client threads a chance to die gracefully

2017-04-27T05:59:23.778648Z 0 [Note] Shutting down slave threads

2017-04-27T05:59:23.778665Z 0 [Note] Forcefully disconnecting 0 remaining clients

[root@chengshuidai tmp] # 2017-04-27T05:59:23.778799Z 0 [Note] Binlog end

2017-04-27T05:59:23.782195Z 0 [Note] Shutting down plugin 'ngram'

2017-04-27T05:59:23.782234Z 0 [Note] Shutting down plugin 'partition'

2017-04-27T05:59:23.782242Z 0 [Note] Shutting down plugin 'ARCHIVE'

2017-04-27T05:59:23.782247Z 0 [Note] Shutting down plugin 'BLACKHOLE'

2017-04-27T05:59:23.782254Z 0 [Note] Shutting down plugin 'MRG_MYISAM'

2017-04-27T05:59:23.782263Z 0 [Note] Shutting down plugin 'MyISAM'

2017-04-27T05:59:23.782276Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'

2017-04-27T05:59:23.782283Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'

2017-04-27T05:59:23.782288Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'

2017-04-27T05:59:23.782293Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'

2017-04-27T05:59:23.782299Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'

2017-04-27T05:59:23.782304Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'

2017-04-27T05:59:23.782309Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'

2017-04-27T05:59:23.782314Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'

2017-04-27T05:59:23.782319Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'

2017-04-27T05:59:23.782324Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'

2017-04-27T05:59:23.782329Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'

2017-04-27T05:59:23.782334Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'

2017-04-27T05:59:23.782339Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'

2017-04-27T05:59:23.782345Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'

2017-04-27T05:59:23.782350Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'

2017-04-27T05:59:23.782355Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'

2017-04-27T05:59:23.782360Z 0 [Note] Shutting down plugin 'INNODB_METRICS'

2017-04-27T05:59:23.782366Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'

2017-04-27T05:59:23.782370Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'

2017-04-27T05:59:23.782384Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'

2017-04-27T05:59:23.782390Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'

2017-04-27T05:59:23.782395Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'

2017-04-27T05:59:23.782400Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'

2017-04-27T05:59:23.782406Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'

2017-04-27T05:59:23.782410Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'

2017-04-27T05:59:23.782416Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'

2017-04-27T05:59:23.782421Z 0 [Note] Shutting down plugin 'INNODB_CMP'

2017-04-27T05:59:23.782441Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'

2017-04-27T05:59:23.782446Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'

2017-04-27T05:59:23.782452Z 0 [Note] Shutting down plugin 'INNODB_TRX'

2017-04-27T05:59:23.782457Z 0 [Note] Shutting down plugin 'InnoDB'

2017-04-27T05:59:23.782547Z 0 [Note] InnoDB: FTS optimize thread exiting.

2017-04-27T05:59:23.782702Z 0 [Note] InnoDB: Starting shutdown...

2017-04-27T05:59:23.883069Z 0 [Note] InnoDB: Dumping buffer pool (s) to / database/mysql/data/ib_buffer_pool

2017-04-27T05:59:23.884131Z 0 [Note] InnoDB: Buffer pool (s) dump completed at 170427 13:59:23

2017-04-27T05:59:25.101878Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2874061

2017-04-27T05:59:25.106229Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2017-04-27T05:59:25.106254Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'

2017-04-27T05:59:25.106308Z 0 [Note] Shutting down plugin 'MEMORY'

2017-04-27T05:59:25.106316Z 0 [Note] Shutting down plugin 'CSV'

2017-04-27T05:59:25.106324Z 0 [Note] Shutting down plugin 'sha256_password'

2017-04-27T05:59:25.106330Z 0 [Note] Shutting down plugin 'mysql_native_password'

2017-04-27T05:59:25.106661Z 0 [Note] Shutting down plugin 'binlog'

2017-04-27T05:59:25.107913Z 0 [Note] / opt/app/mysql/5.7.18/bin/mysqld: Shutdown complete

[1] + Done / opt/app/mysql/5.7.18/bin/mysqld-defaults-file=/etc/my.cnf-user=root (wd: / opt/app/mysql)

Start MySQL 5.7.18 again

[root@chengshuidai tmp] # / opt/app/mysql/5.7.18/bin/mysqld-defaults-file=/etc/my.cnf-user=root &

[1] 12823

[root@chengshuidai tmp] # 2017-04-27T06:00:40.884043Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-04-27T06:00:40.884128Z 0 [Warning] 'NO_ZERO_DATE',' NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2017-04-27T06:00:40.884133Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2017-04-27T06:00:40.884168Z 0 [Note]-- secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2017-04-27T06:00:40.884200Z 0 [Note] / opt/app/mysql/5.7.18/bin/mysqld (mysqld 5.7.18-log) starting as process 12823.

2017-04-27T06:00:40.889079Z 0 [Note] InnoDB: PUNCH HOLE support not available

2017-04-27T06:00:40.889111Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2017-04-27T06:00:40.889116Z 0 [Note] InnoDB: Uses event mutexes

2017-04-27T06:00:40.889120Z 0 [Note] InnoDB: GCC builtin _ _ sync_synchronize () is used for memory barrier

2017-04-27T06:00:40.889124Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-04-27T06:00:40.889128Z 0 [Note] InnoDB: Using Linux native AIO

2017-04-27T06:00:40.889401Z 0 [Note] InnoDB: Number of pools: 1

2017-04-27T06:00:40.889516Z 0 [Note] InnoDB: Using CPU crc32 instructions

2017-04-27T06:00:40.890941Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128m, instances = 1, chunk size = 128m

2017-04-27T06:00:40.899250Z 0 [Note] InnoDB: Completed initialization of buffer pool

2017-04-27T06:00:40.901561Z 0 [Note] InnoDB: page_cleaner coordinator priority:-20

2017-04-27T06:00:40.914701Z 0 [Note] InnoDB: Highest supported file format is Barracuda.

2017-04-27T06:00:40.945405Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2017-04-27T06:00:40.945496Z 0 [Note] InnoDB: Setting file'. / ibtmp1' size to 12 MB. Physically writing the file full; Please wait...

2017-04-27T06:00:40.987464Z 0 [Note] InnoDB: File'. / ibtmp1' size is now 12 MB.

2017-04-27T06:00:40.989695Z 0 [Note] InnoDB: 96 redo rollback segment (s) found. 96 redo rollback segment (s) are active.

2017-04-27T06:00:40.989715Z 0 [Note] InnoDB: 32 non-redo rollback segment (s) are active.

2017-04-27T06:00:40.991635Z 0 [Note] InnoDB: Waiting for purge to start

2017-04-27T06:00:41.041879Z 0 [Note] InnoDB: 5.7.18 started; log sequence number 2874061

2017-04-27T06:00:41.042155Z 0 [Note] InnoDB: Loading buffer pool (s) from / database/mysql/data/ib_buffer_pool

2017-04-27T06:00:41.042846Z 0 [Note] Plugin 'FEDERATED' is disabled.

2017-04-27T06:00:41.044000Z 0 [Note] InnoDB: Buffer pool (s) load completed at 170427 14:00:41

2017-04-27T06:00:41.058759Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

2017-04-27T06:00:41.059402Z 0 [Note] Server hostname (bind-address):'*'; port: 3306

2017-04-27T06:00:41.059537Z 0 [Note] IPv6 is available.

2017-04-27T06:00:41.059557Z 0 [Note] -':: 'resolves to'::

2017-04-27T06:00:41.059599Z 0 [Note] Server socket created on IP:':'.

2017-04-27T06:00:41.079802Z 0 [Note] Event Scheduler: Loaded 0 events

2017-04-27T06:00:41.080095Z 0 [Note] / opt/app/mysql/5.7.18/bin/mysqld: ready for connections.

Version: '5.7.18 socket:' / tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)

2017-04-27T06:00:41.080121Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option'--disable-partition-engine-check' to skip this check.

2017-04-27T06:00:41.080128Z 0 [Note] Beginning of list of non-natively partitioned tables

2017-04-27T06:00:41.100591Z 0 [Note] End of list of non-natively partitioned tables

[root@chengshuidai tmp] # ps-ef | grep mysqld | grep-v grep

Root 12823 12577 1 14:00 pts/2 00:00:00 / opt/app/mysql/5.7.18/bin/mysqld-defaults-file=/etc/my.cnf-user=root

5. Confirm whether the upgrade is successful:

[root@chengshuidai tmp] # mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 4

Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > select @ @ version

+-+

| | @ @ version |

+-+

| | 5.7.18-log |

+-+

1 row in set (0.00 sec)

# an extra sys library will be created after a successful upgrade

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

| | test |

+-+

5 rows in set (0.00 sec)

At this point, all the upgrade steps are complete.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.