Because the customer requires to upgrade mysql5.6, and the service can not stop, the server does not have a network environment, so we can only install a mysql8.0.22 version at the same time, after data recovery, and then stop the old service. Note here the steps for installing mysql version 2 (mysql8) on the same server

1. Create a directory, create a data directory, and assign permissions

[root@MySQL-Test soft]# mysql - 8.0.22 - Linux - mv glibc2.12 - x86_64 / mysql8
[root@MySQL-Test soft]# mv mysql8/ /usr/local/
[root@MySQL-Test soft]# mkdir data
[root@MySQL-Test soft]# chown -R mysql:mysql /var/lib/mysql8
[root@MySQL-Test mysql]#chown -r mysql:mysql./ # change the owner of the current directory to mysql user
Copy the code

2. Initialize the database

[root@MySQL-Test mysql8]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql8 --datadir=/usr/local/mysql8/data
2021-01-28T17:35:34.719103Z 0 [Warning] [MY-010915] [Server] '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 inA future release. 2021-01-28T17:35:34.719186z 0 [System] [my-013169] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.0.22) initializing of server inProgress as Process 2776 2021-01-28T17:35:34.720308z 0 [Warning] [my-013242] [Server] --character-set-server:'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 inOrder to be unambiguous. 2021-01-28T17:35:34.725807z 1 [System] [my-013576] [InnoDB] Initialization has started. 2021-01-28T17:35:35.405836Z 1 [System] [my-013577] [InnoDB] InnoDB initialization has ended. 2021-01-28T17:35:37.156546z  6 [Note] [MY-010454] [Server] A temporary password is generatedfor root@localhost: -vHpdA8LOnxy
Copy the code

Note down the above -vhpda8lonxy temporary password, which will be used to connect to mysql

My.cnf configuration for MySQL8.0.22 (the path is customizable and recommended to be placed in the $base /etc/path). You need to specify the my.cnf file path at startup

  [client]
  port = 3307
  socket = /var/lib/mysql/mysql.sock
  # The MySQL server[mysqld] default-authentication-plugin=mysql_native_password #MySQL8 Need to add the words of the port = 3307 mysqlx_port = 33070 user = root socket = / var/lib/mysql/mysql. The sock mysqlx_socket=/var/lib/mysql/mysqlx.sock pid-file = /usr/local/DBdatas/mysql8/mysql.pid basedir = /usr/local/DBdatas/mysql8/ datadir = /usr/local/DBdatas/mysql8/data tmpdir = /usr/local/DBdatas/mysql8/tmp open_files_limit = 60000 explicit_defaults_for_timestamp server-id = 1203307 lower_case_table_names = 1 character-set-server = utf8mb4 federated  #sql_mode=STRICT_TRANS_TABLES
  max_connections = 1000
  max_connect_errors = 100000
  interactive_timeout = 86400
  wait_timeout = 86400
  back_log=100
  default-storage-engine = InnoDB
  #skip-grant-tables
  #*********** GTID settting*******************
  log_slave_updates = 1
  gtid_mode=ON
  enforce-gtid-consistency=true
  innodb_flush_log_at_trx_commit=2
  binlog_format= row
  skip-slave-start=1
  sync_binlog=5
  #*********** Logs related settings ***********
  #log-bin = /usr/local/DBdatas/mysql8/log/binlog/mysql-bin
  #binlog_cache_size=32m
  #max_binlog_cache_size=64m
  #max_binlog_size=512m
  long_query_time = 1
  log_output = FILE
  log-error = /usr/local/DBdatas/mysql8/log/mysql-error.log
  #slow_query_log = 1
  #slow_query_log_file = /usr/local/DBdatas/mysql8/log/slow_statement.log
  #log_queries_not_using_indexes
  #general_log = 0
  #general_log_file = /usr/local/DBdatas/mysql8/log/general_statement.log
  #expire-logs-days = 14
  #binlog_expire_logs_seconds = 1728000
  #relay-log = /usr/local/DBdatas/mysql8/log/binlog/relay-bin
  #relay-log-index = /usr/local/DBdatas/mysql8/log/binlog/relay-bin.index
  #****** MySQL Replication New Feature********* 
  #master-info-repository=TABLE
  #relay-log-info-repository=TABLE
  #relay-log-recovery
  #*********** INNODB Specific options *********** 
  innodb_buffer_pool_size = 2048M
  transaction-isolation=REPEATABLE-READ
  innodb_buffer_pool_instances = 8
  innodb_file_per_table = 1
  #innodb_data_home_dir = /usr/local/DBdatas/mysql8/innodb_ts
  innodb_data_home_dir = /usr/local/DBdatas/mysql8/data
  innodb_data_file_path = ibdata1:2048M:autoextend
  innodb_thread_concurrency = 8
  innodb_log_buffer_size = 16M
  innodb_log_file_size = 128M
  innodb_log_files_in_group = 3
  innodb_log_group_home_dir = /usr/local/DBdatas/mysql8/innodb_log
  innodb_flush_log_at_trx_commit = 2
  innodb_max_dirty_pages_pct = 70
  innodb_flush_method=O_DIRECT
  [mysql]
  no-auto-rehash
  default-character-set=utf8
  prompt = (\u@\h) [\d]>\_
Copy the code

3. Start the database

#Start the eight
/usr/local/DBdatas/mysql8/bin/mysqld_safe --defaults-file=/usr/local/DBdatas/mysql8/my.cnf --datadir=/usr/local/DBdatas/mysql8/data --socket=/var/lib/mysql/mysql.sock --basedir=/usr/local/DBdatas/mysql8 --user=mysql --port=3307
Copy the code

If you want to skip password authentication, you can modify the my.cnf configuration file to add skip-grant-tables, or add the — skip-grant-tables parameter at startup

4. Connect to the database

== Caution Go to the bin directory of the corresponding version before connecting to the database. The error is that mysql8 is not started in the bin directory after version 8.0 is installed. As a result, the password verification rule remains unchangedcaching_sha2_passwordCannot be changed to compatible with older versionsmysql_native_password, resulting in a continuous error during login. Only in this waymy.cnfConfigured indefault-authentication-plugin=mysql_native_passwordParameter takes effect == The following error message is displayed:View the summary rule as follows:

mysql> use mysql;   Switch to the mysql library
mysql>SELECT Host, User, plugin from user;
Copy the code

Formally execute the following code:

#Because multiple versions are available, you need to go to the bin directory before startup to prevent configuration files of other versions from being referenced
[root@WXB-QZ-1-5 database_bak]# cd /usr/local/DBdatas/mysql8/bin/
#Once in the bin directory, log in to the mysql8 sock file[root@WXB-QZ-1-5 bin]# ./mysql -u root -p -S /var/lib/mysql/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with; or \g. Your MySQL connection id is 8 Server version: Copyright (c) 2000, 2020, 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>alter user user() identified by "root";
mysql>use mysql;
#Create root@% without changing the remote permission of root localhost
mysql> CREATE USER 'root'@The '%' IDENTIFIED BY 'password';
mysql> ALTER USER 'root'@The '%' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@The '%' WITH GRANT OPTION ;
mysql>flush privileges;  # refresh permission
Copy the code