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

The following content mainly brings you the installation and initialization of mysql8.0 tutorials, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

Installation

Because the mysql official gives the binary package method as before, it is not much different from the previous installation method.

First of all, download the binary package on the official website, and then you can decompress and install it normally. I am used to the following way.

Tar xf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gzmv mysql-8.0.11-linux-glibc2.12-x86_64/ usr/local/cd / usr/local/ln-sf mysql-8.0.11-linux-glibc2.12-x86_64/ mysql80mkdir-p / data/mysql/data80groupadd mysqluseradd-g mysql- s / sbin/nologin mysql

In fact, binary packages do not have any installation concept, they are all used with them.

-Note: make sure there is no interference from other mysql distributions before operation, especially for yum installation and rpm installation

# check rpm-qa in the installed rpm package without mysql and mariadb | grep-E 'mysql | mariadb'mariadb-libs-5.5.44-2.el7.centos.x86_64# delete the found rpm installation package rpm-e-- nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64rpm-e-- nodeps mysql.x86_64

Initialize use

1. Initialization

In fact, it is no different from mysql5.7.

/ usr/local/mysql80/bin/mysqld-defaults-file=/usr/local/mysql80/my.cnf-initialize

And the password is in the same place as before.

Sed-n'/ password/p' / data/mysql/data80/mysql.err.A temporary password is generated for root@localhost:% pqGI?ATQ4fg

Remember to set the permissions before starting.

Chown-R mysql:mysql / data/mysql/data*/usr/local/mysql80/support-files/mysql.server start

First log in with the mysql client and initialization password that comes with mysql8.0, and then you will say why you did it.

/ usr/local/mysql80/bin/mysql-uroot-packs% pqGItemATQ4fg'-S / tmp/mysql80.sock-P3308

The way to change the password is the same as 5.7

Mysql > alter user 'root'@'localhost' identified by' 123 leaders political MySQL > set password for 'root'@'localhost'=password (' 123') mysql > update mysql.user set authentication_string=password ('123') where user='root' and Host =' localhost';mysql > flush privileges

At this time, if you log in with the default mysql client, you may have this error.

Mysql-uroot-P123-P3308-S / tmp/mysql80.sock mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: / usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

In mysql5.7 environment, although you can set default_authentication_plugin to change authentication encryption, but most people will not set it. Under mysql8.0, the default is default_authentication_plugin=caching_sha2_password, including the authenticated encryption method for the root users you just initialized. As a result, you will not be able to connect to the database unless you are driven by a new protocol, such as the mysql client that comes with 8.0. This will inevitably lead to incompatibility, but fortunately, it can be changed back to the old way.

Vim my.cnf[mysqld] default_authentication_plugin=mysql_native_password

However, this parameter only applies to newly authorized users, and the old root password is still in the old way, like the following

# Log in with the new client / usr/local/mysql80/bin/mysql-uroot-P123-P3308-S / tmp/mysql80.sock# to check the status of each user mysql > select Host,User,plugin,authentication_string from mysql.user + -+ | Host | User | plugin | authentication_string | + -+ | localhost | mysql.infoschema | mysql_native_password | * THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | mysql.session | mysql_native_password | * THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | mysql.sys | mysql_native_password | * THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | root | caching_sha2_password | $Aventi005 $.Sf} Q/Jh > 3b6CjI5/vMJVDIQbS1dbFWaCVuiby7aX3ZY4lB6/M7Vvny5DUA | +-- + | -+ 4 rows in set (0.00 sec)

You can see that plugin is different, and the encryption of passwords is obviously inconsistent.

Then we create another user, and we can see that it is the same as before.

Mysql > select Host,User,plugin,authentication_string from mysql.user + -+ | Host | User | plugin | authentication_string | + -+ |% | sroot | mysql_native_password | * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | localhost | mysql.infoschema | mysql_native_password | * THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | mysql.session | mysql_native_password | * THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | mysql.sys | mysql_native_password | * THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | root | caching_sha2_password | $Aventi005 $.Sf} Q/Jh > 3b6CjI5/vMJVDIQbS1dbFWaCVuiby7aX3ZY4lB6/M7Vvny5DUA | +-+-| -+-+ 5 rows in set (0.00 sec)

Log in again with the default mysql client

Mysql-usroot-p123123-P3308-S / tmp/mysql80.sockmysql >\ s-mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86'64) using EditLine wrapperConnection id: 17Current database: Current user: sroot@localhostSSL: Not in useCurrent pager: stdoutUsing outfile:''Using delimiter: Server version: 8.0.11 MySQL Community Server-GPLProtocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. Characterset: utf8UNIX socket: / tmp/mysql80.sockUptime: 1 hour 25 min 18 secThreads: 2 Questions: 120 Slow queries: 0 Opens: 230 Flush tables: 2 Open tables: 203 Queries per second avg: 0.023-

A compatibility problem has been solved.

two。 Authorization

It says above to create a user, but the user creation and authorization in mysql8.0 are not the same as before. In fact, it is not different, but more stringent, you need to create users and set passwords before authorization.

# first create a user create user 'sroot'@'%' identified by' 123123 sroot'@'%' with grant option # and then authorize grant all privileges on *. * to 'user

If you do it the way you did it before, you will report a grammar error.

Grant all privileges on *. * to 'sroot'@'%' identified by' 123123 alternate error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by' 123123'at line 1

So authorized users should pay attention to the method.

3. Parameter change

Because of the limited space, I did not post the my.cnf file, and due to some features, some configurations of mysql8.0 are different from the previous ones. Here is a list of what I found.

Query_cache has completely disappeared, and you still need to manually set it to off in mysql5.7. Now you can ignore it, so the corresponding two parameters can be commented out.

# query_cache_size=0#query_cache_type=0

Innodb_undo_logs can no longer be set. In mysql8.0, undo independent tablespaces are enabled by default, and the value is 2, that is, two by default. However, the parameter innodb_undo_logs cannot be set to specify the size of rollback segments. Rollback segments default to 128s.

# innodb_undo_logs

As a matter of fact, I haven't found it yet. I'll make it up when I find it.

Then also found that the system is all innodb engines, in fact, also seen in the features before.

For the above tutorials on the installation and initialization of mysql8.0, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.