1MYSQL_UPGRADE(1)             MySQL Database System            MYSQL_UPGRADE(1)
2
3
4

NAME

6       mysql_upgrade - check and upgrade MySQL tables
7

SYNOPSIS

9       mysql_upgrade [options]
10

DESCRIPTION

12           Note
13           As of MySQL 8.0.16, the MySQL server performs the upgrade tasks
14           previously handled by mysql_upgrade (for details, see
15           Section 2.11.3, “What the MySQL Upgrade Process Upgrades”).
16           Consequently, mysql_upgrade is unneeded and is deprecated as of
17           that version, and will be removed in a future MySQL version.
18           Because mysql_upgrade no longer performs upgrade tasks, it exits
19           with status 0 unconditionally.
20
21       Each time you upgrade MySQL, you should execute mysql_upgrade, which
22       looks for incompatibilities with the upgraded MySQL server:
23
24       ·   It upgrades the system tables in the mysql schema so that you can
25           take advantage of new privileges or capabilities that might have
26           been added.
27
28       ·   It upgrades the Performance Schema, INFORMATION_SCHEMA, and sys
29           schema.
30
31       ·   It examines user schemas.
32
33       If mysql_upgrade finds that a table has a possible incompatibility, it
34       performs a table check and, if problems are found, attempts a table
35       repair. If the table cannot be repaired, see Section 2.11.13,
36       “Rebuilding or Repairing Tables or Indexes” for manual table repair
37       strategies.
38
39       mysql_upgrade communicates directly with the MySQL server, sending it
40       the SQL statements required to perform an upgrade.
41
42           Caution
43           You should always back up your current MySQL installation before
44           performing an upgrade. See Section 7.2, “Database Backup Methods”.
45
46           Some upgrade incompatibilities may require special handling before
47           upgrading your MySQL installation and running mysql_upgrade. See
48           Section 2.11, “Upgrading MySQL”, for instructions on determining
49           whether any such incompatibilities apply to your installation and
50           how to handle them.
51
52       Use mysql_upgrade like this:
53
54        1. Ensure that the server is running.
55
56        2. Invoke mysql_upgrade to upgrade the system tables in the mysql
57           schema and check and repair tables in other schemas:
58
59               shell> mysql_upgrade [options]
60
61        3. Stop the server and restart it so that any system table changes
62           take effect.
63
64       If you have multiple MySQL server instances to upgrade, invoke
65       mysql_upgrade with connection parameters appropriate for connecting to
66       each of the desired servers. For example, with servers running on the
67       local host on parts 3306 through 3308, upgrade each of them by
68       connecting to the appropriate port:
69
70           shell> mysql_upgrade --protocol=tcp -P 3306 [other_options]
71           shell> mysql_upgrade --protocol=tcp -P 3307 [other_options]
72           shell> mysql_upgrade --protocol=tcp -P 3308 [other_options]
73
74       For local host connections on Unix, the --protocol=tcp option forces a
75       connection using TCP/IP rather than the Unix socket file.
76
77       By default, mysql_upgrade runs as the MySQL root user. If the root
78       password is expired when you run mysql_upgrade, you will see a message
79       that your password is expired and that mysql_upgrade failed as a
80       result. To correct this, reset the root password to unexpire it and run
81       mysql_upgrade again. First, connect to the server as root:
82
83           shell> mysql -u root -p
84           Enter password: ****  <- enter root password here
85
86       Reset the password using ALTER USER:
87
88           mysql> ALTER USER USER() IDENTIFIED BY 'root-password';
89
90       Then exit mysql and run mysql_upgrade again:
91
92           shell> mysql_upgrade [options]
93
94
95           Note
96           If you run the server with the disabled_storage_engines system
97           variable set to disable certain storage engines (for example,
98           MyISAM), mysql_upgrade might fail with an error like this:
99
100               mysql_upgrade: [ERROR] 3161: Storage engine MyISAM is disabled
101               (Table creation is disallowed).
102
103           To handle this, restart the server with disabled_storage_engines
104           disabled. Then you should be able to run mysql_upgrade
105           successfully. After that, restart the server with
106           disabled_storage_engines set to its original value.
107
108       Unless invoked with the --upgrade-system-tables option, mysql_upgrade
109       processes all tables in all user schemas as necessary. Table checking
110       might take a long time to complete. Each table is locked and therefore
111       unavailable to other sessions while it is being processed. Check and
112       repair operations can be time-consuming, particularly for large tables.
113       Table checking uses the FOR UPGRADE option of the CHECK TABLE
114       statement. For details about what this option entails, see
115       Section 13.7.3.2, “CHECK TABLE Statement”.
116
117       mysql_upgrade marks all checked and repaired tables with the current
118       MySQL version number. This ensures that the next time you run
119       mysql_upgrade with the same version of the server, it can be determined
120       whether there is any need to check or repair a given table again.
121
122       mysql_upgrade saves the MySQL version number in a file named
123       mysql_upgrade_info in the data directory. This is used to quickly check
124       whether all tables have been checked for this release so that
125       table-checking can be skipped. To ignore this file and perform the
126       check regardless, use the --force option.
127
128           Note
129           The mysql_upgrade_info file is deprecated and will be removed in a
130           future MySQL version.
131
132       mysql_upgrade checks mysql.user system table rows and, for any row with
133       an empty plugin column, sets that column to 'mysql_native_password' if
134       the credentials use a hash format compatible with that plugin. Rows
135       with a pre-4.1 password hash must be upgraded manually.
136
137       mysql_upgrade does not upgrade the contents of the time zone tables or
138       help tables. For upgrade instructions, see Section 5.1.13, “MySQL
139       Server Time Zone Support”, and Section 5.1.14, “Server-Side Help
140       Support”.
141
142       Unless invoked with the --skip-sys-schema option, mysql_upgrade
143       installs the sys schema if it is not installed, and upgrades it to the
144       current version otherwise. An error occurs if a sys schema exists but
145       has no version view, on the assumption that its absence indicates a
146       user-created schema:
147
148           A sys schema exists with no sys.version view. If
149           you have a user created sys schema, this must be renamed for the
150           upgrade to succeed.
151
152       To upgrade in this case, remove or rename the existing sys schema
153       first.
154
155       mysql_upgrade supports the following options, which can be specified on
156       the command line or in the [mysql_upgrade] and [client] groups of an
157       option file. For information about option files used by MySQL programs,
158       see Section 4.2.2.2, “Using Option Files”.
159
160       ·   --help
161
162           Display a short help message and exit.
163
164       ·   --bind-address=ip_address
165
166           On a computer having multiple network interfaces, use this option
167           to select which interface to use for connecting to the MySQL
168           server.
169
170       ·   --character-sets-dir=dir_name
171
172           The directory where character sets are installed. See
173           Section 10.15, “Character Set Configuration”.
174
175       ·   --compress, -C
176
177           Compress all information sent between the client and the server if
178           possible. See Section 4.2.6, “Connection Compression Control”.
179
180           As of MySQL 8.0.18, this option is deprecated. It will be removed
181           in a future MySQL version. See the section called “Legacy
182           Connection Compression Configuration”.
183
184       ·   --compression-algorithms=value The permitted compression algorithms
185           for connections to the server. The available algorithms are the
186           same as for the protocol_compression_algorithms system variable.
187           The default value is uncompressed.
188
189           For more information, see Section 4.2.6, “Connection Compression
190           Control”.
191
192           This option was added in MySQL 8.0.18.
193
194       ·   --debug[=debug_options], -# [debug_options]
195
196           Write a debugging log. A typical debug_options string is
197           d:t:o,file_name. The default is d:t:O,/tmp/mysql_upgrade.trace.
198
199       ·   --debug-check
200
201           Print some debugging information when the program exits.
202
203       ·   --debug-info, -T
204
205           Print debugging information and memory and CPU usage statistics
206           when the program exits.
207
208       ·   --default-auth=plugin
209
210           A hint about which client-side authentication plugin to use. See
211           Section 6.2.17, “Pluggable Authentication”.
212
213       ·   --default-character-set=charset_name
214
215           Use charset_name as the default character set. See Section 10.15,
216           “Character Set Configuration”.
217
218       ·   --defaults-extra-file=file_name
219
220           Read this option file after the global option file but (on Unix)
221           before the user option file. If the file does not exist or is
222           otherwise inaccessible, an error occurs.  file_name is interpreted
223           relative to the current directory if given as a relative path name
224           rather than a full path name.
225
226           For additional information about this and other option-file
227           options, see Section 4.2.2.3, “Command-Line Options that Affect
228           Option-File Handling”.
229
230       ·   --defaults-file=file_name
231
232           Use only the given option file. If the file does not exist or is
233           otherwise inaccessible, an error occurs.  file_name is interpreted
234           relative to the current directory if given as a relative path name
235           rather than a full path name.
236
237           For additional information about this and other option-file
238           options, see Section 4.2.2.3, “Command-Line Options that Affect
239           Option-File Handling”.
240
241       ·   --defaults-group-suffix=str
242
243           Read not only the usual option groups, but also groups with the
244           usual names and a suffix of str. For example, mysql_upgrade
245           normally reads the [client] and [mysql_upgrade] groups. If the
246           --defaults-group-suffix=_other option is given, mysql_upgrade also
247           reads the [client_other] and [mysql_upgrade_other] groups.
248
249           For additional information about this and other option-file
250           options, see Section 4.2.2.3, “Command-Line Options that Affect
251           Option-File Handling”.
252
253       ·   --force
254
255           Ignore the mysql_upgrade_info file and force execution even if
256           mysql_upgrade has already been executed for the current version of
257           MySQL.
258
259       ·   --get-server-public-key
260
261           Request from the server the public key required for RSA key
262           pair-based password exchange. This option applies to clients that
263           authenticate with the caching_sha2_password authentication plugin.
264           For that plugin, the server does not send the public key unless
265           requested. This option is ignored for accounts that do not
266           authenticate with that plugin. It is also ignored if RSA-based
267           password exchange is not used, as is the case when the client
268           connects to the server using a secure connection.
269
270           If --server-public-key-path=file_name is given and specifies a
271           valid public key file, it takes precedence over
272           --get-server-public-key.
273
274           For information about the caching_sha2_password plugin, see
275           Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
276
277       ·   --host=host_name, -h host_name
278
279           Connect to the MySQL server on the given host.
280
281       ·   --login-path=name
282
283           Read options from the named login path in the .mylogin.cnf login
284           path file. A “login path” is an option group containing options
285           that specify which MySQL server to connect to and which account to
286           authenticate as. To create or modify a login path file, use the
287           mysql_config_editor utility. See mysql_config_editor(1).
288
289           For additional information about this and other option-file
290           options, see Section 4.2.2.3, “Command-Line Options that Affect
291           Option-File Handling”.
292
293       ·   --max-allowed-packet=value
294
295           The maximum size of the buffer for client/server communication. The
296           default value is 24MB. The minimum and maximum values are 4KB and
297           2GB.
298
299       ·   --net-buffer-length=value
300
301           The initial size of the buffer for client/server communication. The
302           default value is 1MB − 1KB. The minimum and maximum values are 4KB
303           and 16MB.
304
305       ·   --no-defaults
306
307           Do not read any option files. If program startup fails due to
308           reading unknown options from an option file, --no-defaults can be
309           used to prevent them from being read.
310
311           The exception is that the .mylogin.cnf file, if it exists, is read
312           in all cases. This permits passwords to be specified in a safer way
313           than on the command line even when --no-defaults is used.
314           (.mylogin.cnf is created by the mysql_config_editor utility. See
315           mysql_config_editor(1).)
316
317           For additional information about this and other option-file
318           options, see Section 4.2.2.3, “Command-Line Options that Affect
319           Option-File Handling”.
320
321       ·   --password[=password], -p[password]
322
323           The password of the MySQL account used for connecting to the
324           server. The password value is optional. If not given, mysql_upgrade
325           prompts for one. If given, there must be no space between
326           --password= or -p and the password following it. If no password
327           option is specified, the default is to send no password.
328
329           Specifying a password on the command line should be considered
330           insecure. To avoid giving the password on the command line, use an
331           option file. See Section 6.1.2.1, “End-User Guidelines for Password
332           Security”.
333
334           To explicitly specify that there is no password and that
335           mysql_upgrade should not prompt for one, use the --skip-password
336           option.
337
338       ·   --pipe, -W
339
340           On Windows, connect to the server using a named pipe. This option
341           applies only if the server was started with the named_pipe system
342           variable enabled to support named-pipe connections. In addition,
343           the user making the connection must be a member of the Windows
344           group specified by the named_pipe_full_access_group system
345           variable.
346
347       ·   --plugin-dir=dir_name
348
349           The directory in which to look for plugins. Specify this option if
350           the --default-auth option is used to specify an authentication
351           plugin but mysql_upgrade does not find it. See Section 6.2.17,
352           “Pluggable Authentication”.
353
354       ·   --port=port_num, -P port_num
355
356           For TCP/IP connections, the port number to use.
357
358       ·   --print-defaults
359
360           Print the program name and all options that it gets from option
361           files.
362
363       ·   --protocol={TCP|SOCKET|PIPE|MEMORY}
364
365           The connection protocol to use for connecting to the server. It is
366           useful when the other connection parameters normally result in use
367           of a protocol other than the one you want. For details on the
368           permissible values, see Section 4.2.4, “Connecting to the MySQL
369           Server Using Command Options”.
370
371       ·   --server-public-key-path=file_name
372
373           The path name to a file containing a client-side copy of the public
374           key required by the server for RSA key pair-based password
375           exchange. The file must be in PEM format. This option applies to
376           clients that authenticate with the sha256_password or
377           caching_sha2_password authentication plugin. This option is ignored
378           for accounts that do not authenticate with one of those plugins. It
379           is also ignored if RSA-based password exchange is not used, as is
380           the case when the client connects to the server using a secure
381           connection.
382
383           If --server-public-key-path=file_name is given and specifies a
384           valid public key file, it takes precedence over
385           --get-server-public-key.
386
387           For sha256_password, this option applies only if MySQL was built
388           using OpenSSL.
389
390           For information about the sha256_password and caching_sha2_password
391           plugins, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”,
392           and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
393
394       ·   --shared-memory-base-name=name
395
396           On Windows, the shared-memory name to use for connections made
397           using shared memory to a local server. The default value is MYSQL.
398           The shared-memory name is case-sensitive.
399
400           This option applies only if the server was started with the
401           shared_memory system variable enabled to support shared-memory
402           connections.
403
404       ·   --skip-sys-schema
405
406           By default, mysql_upgrade installs the sys schema if it is not
407           installed, and upgrades it to the current version otherwise. The
408           --skip-sys-schema option suppresses this behavior.
409
410       ·   --socket=path, -S path
411
412           For connections to localhost, the Unix socket file to use, or, on
413           Windows, the name of the named pipe to use.
414
415           On Windows, this option applies only if the server was started with
416           the named_pipe system variable enabled to support named-pipe
417           connections. In addition, the user making the connection must be a
418           member of the Windows group specified by the
419           named_pipe_full_access_group system variable.
420
421       ·   --ssl*
422
423           Options that begin with --ssl specify whether to connect to the
424           server using SSL and indicate where to find SSL keys and
425           certificates. See the section called “Command Options for Encrypted
426           Connections”.
427
428       ·   --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
429           mode on the client side. The --ssl-fips-mode option differs from
430           other --ssl-xxx options in that it is not used to establish
431           encrypted connections, but rather to affect which cryptographic
432           operations are permitted. See Section 6.5, “FIPS Support”.
433
434           These --ssl-fips-mode values are permitted:
435
436           ·   OFF: Disable FIPS mode.
437
438           ·   ON: Enable FIPS mode.
439
440           ·   STRICT: Enable “strict” FIPS mode.
441
442
443               Note
444               If the OpenSSL FIPS Object Module is not available, the only
445               permitted value for --ssl-fips-mode is OFF. In this case,
446               setting --ssl-fips-mode to ON or STRICT causes the client to
447               produce a warning at startup and to operate in non-FIPS mode.
448
449       ·   --tls-ciphersuites=ciphersuite_list
450
451           The permissible ciphersuites for encrypted connections that use
452           TLSv1.3. The value is a list of one or more colon-separated
453           ciphersuite names. The ciphersuites that can be named for this
454           option depend on the SSL library used to compile MySQL. For
455           details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
456           Ciphers”.
457
458           This option was added in MySQL 8.0.16.
459
460       ·   --tls-version=protocol_list
461
462           The permissible TLS protocols for encrypted connections. The value
463           is a list of one or more comma-separated protocol names. The
464           protocols that can be named for this option depend on the SSL
465           library used to compile MySQL. For details, see Section 6.3.2,
466           “Encrypted Connection TLS Protocols and Ciphers”.
467
468       ·   --upgrade-system-tables, -s
469
470           Upgrade only the system tables in the mysql schema, do not upgrade
471           user schemas.
472
473       ·   --user=user_name, -u user_name
474
475           The user name of the MySQL account to use for connecting to the
476           server. The default user name is root.
477
478       ·   --verbose
479
480           Verbose mode. Print more information about what the program does.
481
482       ·   --version-check, -k
483
484           Check the version of the server to which mysql_upgrade is
485           connecting to verify that it is the same as the version for which
486           mysql_upgrade was built. If not, mysql_upgrade exits. This option
487           is enabled by default; to disable the check, use
488           --skip-version-check.
489
490       ·   --write-binlog
491
492           By default, binary logging by mysql_upgrade is disabled. Invoke the
493           program with --write-binlog if you want its actions to be written
494           to the binary log.
495
496           When the server is running with global transaction identifiers
497           (GTIDs) enabled (gtid_mode=ON), do not enable binary logging by
498           mysql_upgrade.
499
500       ·   --zstd-compression-level=level The compression level to use for
501           connections to the server that use the zstd compression algorithm.
502           The permitted levels are from 1 to 22, with larger values
503           indicating increasing levels of compression. The default zstd
504           compression level is 3. The compression level setting has no effect
505           on connections that do not use zstd compression.
506
507           For more information, see Section 4.2.6, “Connection Compression
508           Control”.
509
510           This option was added in MySQL 8.0.18.
511
513       Copyright © 1997, 2020, Oracle and/or its affiliates. All rights
514       reserved.
515
516       This documentation is free software; you can redistribute it and/or
517       modify it only under the terms of the GNU General Public License as
518       published by the Free Software Foundation; version 2 of the License.
519
520       This documentation is distributed in the hope that it will be useful,
521       but WITHOUT ANY WARRANTY; without even the implied warranty of
522       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
523       General Public License for more details.
524
525       You should have received a copy of the GNU General Public License along
526       with the program; if not, write to the Free Software Foundation, Inc.,
527       51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
528       http://www.gnu.org/licenses/.
529
530

SEE ALSO

532       For more information, please refer to the MySQL Reference Manual, which
533       may already be installed locally and which is also available online at
534       http://dev.mysql.com/doc/.
535

AUTHOR

537       Oracle Corporation (http://dev.mysql.com/).
538
539
540
541MySQL 8.0                         03/06/2020                  MYSQL_UPGRADE(1)
Impressum