Preliminary discussion on mysql8.0: (i) installation and initialization of use

Although it is strange that the next version of mysql5.7 is not 5.8, but instead jumps directly to 8.0, but features and functional optimizations improve a lot.

Read a lot of articles have said that mysql8.0 changes very much, but a test down, most of the operation is still relatively consistent, some operations are different.

Here is a step-by-step look at what the big difference, this article only said two aspects.

Installation

Because MySQL is officially the same as before, given the way the binary package, so and the previous installation method is not much different

First download the binary package, and then you can normally unzip the installation, I am accustomed 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/mysql80.mkdir-p/data/mysql/data80groupadd mysqluseradd-g mysql-s/sbin/ Nologin MySQL

In fact, the binary package is not a concept of installation, it is to use

Initialize the use of

1. Initialization

Actually, it's no different from mysql5.7.

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

And then the code was in the same place.

Sed-n '/password/p '/data/mysql/data80/mysql.err ..... A temporary password is generated for [email protected]:%pqgi? ATQ4FG,

Remember to set the permissions before starting

Chown-r mysql:mysql/data/mysql/data*/usr/local/mysql80/support-files/mysql.server Start

Log in with mysql8.0 's MySQL client and initial password, and you'll say why you're doing it later.

/usr/local/mysql80/bin/mysql-uroot-p '%pqgi? ATQ4FG '-s/tmp/mysql80.sock-p3308

Password change is also the same as 5.7

mysql> alter user ' root ' @ ' localhost ' identified by ' 123 ';mysql> set password for ' root ' @ ' localhost ' =password (' 123 ') );mysql> Update mysql.user set Authentication_string=password (' 123 ') where user= ' root ' and Host = ' localhost '; mysql& Gt Flush privileges;

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 inse Cure. 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 the mysql5.7 environment, although the Default_authentication_plugin can be set to change the authentication encryption method, but the general people will not go to set. Under mysql8.0, the default becomes the Default_authentication_ Plugin=caching_sha2_password, including the root user you just initialized is also this authentication encryption method, the result is that you do not have to use the new protocol driver, such as the need to use 8.0 of your own MySQL client line, Otherwise, it will not connect to the database. This will inevitably lead to incompatibility, fortunately, can be changed back to the old way.

Vim My.cnf[mysqld]default_authentication_plugin=mysql_native_password

However, this parameter is only valid for newly authorized users, and the old root password is used in the old way, similar to the following

#先用新客户端登陆/usr/local/mysql80/bin/mysql -uroot -p123 -p3308 -s /tmp/mysql80.sock# View individual user conditions 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 |  $A $005$. sf}q/jh>3b6cji5/vmjvdiqbs1dbfwacvuiby7ax3zy4lb6/m7vvny5dua |+-----------+------------------+---------- -------------+------------------------------------------------------------------------+4 rows in  set  (0.00 SEC)

You can see that the plugin is not the same, the password encryption method is also obviously inconsistent caused the difference.

Then we create a new user, we can see 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 |  $A $005$. 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:         [email protected]SSL:             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--------------

Resolves a compatibility issue.

2. Authorization

It says that creating a user is not the same as mysql8.0 creating a user and authorizing it. In fact, it is not a different, but more stringent, you need to create a user and set a password before authorization.

#先创建一个用户create user ' sroot ' @ '% ' identified by ' 123123 ', #再进行授权grant all privileges on *. * to ' sroot ' @ '% ' with GRANT option;

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

Grant all privileges on * * to ' sroot ' @ ' percent ' identified by ' 123123 '; Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' identified by ' 123123 ' a T Line 1

So the authorized user should pay attention to the method

3. Parameter changes

Because of the limited layout, I did not post the my.cnf file, and for some reasons, mysql8.0 some of the configuration and before the same, the following list I found.

Query_cache completely disappeared, in mysql5.7 also need to manually set to close, now can ignore, 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, the Undo stand-alone table space is enabled by default, and the value is 2, is the default two, but can not be set innodb_undo_logs This parameter to specify that the rollback segment is small, the rollback segment by default is 128

#innodb_undo_logs

In fact, I haven't found it yet.

Then also found that the system is in the InnoDB engine, in fact, before also seen in the features.

Preliminary discussion on mysql8.0: (i) installation and initialization of use