Centos7 install MySQL8.0-manual
MySQL 8 The official version 8.0.11 has been released. Officially, MySQL 8 is 2 times faster than MySQL 5.7, and it also brings a lot of improvements and Faster performance!
1. Some features of Mysql8.0 version compared to the previous version
1) Default encoding utf8
The default encoding uses utf8mb4, utf8mb4 encoding is a superset of utf8 encoding, compatible with utf8, and can store 4-byte emoticon character history, MySQL database "utf8" is not the real concept of UTF-8. The "utf8" encoding in MySQL only supports a maximum of 3 bytes per character. The real UTF-8 encoding that everyone is using should be able to support 4 bytes per character. The developers of MySQL did not fix this bug. They added a workaround in 2010: a new character set "utf8mb4" In addition, utf-32 encoding uses 4 bytes, and 32bit storage is a waste of space compared to utf8.
2) Descending index
Can be created in the previous version, but the actual creation is still an ascending index
mysql> create table t1(id1 int,id2 int,key(id1,id2 desc));
Query OK,0 rows affected(0.10 sec)
mysql> show create table t1 \G;***************************1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1`(`id1`int(11) DEFAULT NULL,`id2`int(11) DEFAULT NULL,
KEY `id1`(`id1`,`id2` DESC)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row inset(0.00 sec)
ERROR:
No query specified
In addition, due to the introduction of descending index, MySQL 8.0 no longer implicitly sorts group by operations
3) Hide index
The hidden index feature is very useful for performance debugging. When an index is hidden, it will not be used by the query optimizer. In other words, we can hide an index and observe the impact on the database. If the performance of the database drops, it means that the index is useful, so "restore the display"; if the database performance does not change, it means that the index is redundant and can be deleted.
mysql> create index idx2 on t1(id1);
Query OK,0 rows affected(0.35 sec)
Records:0 Duplicates:0 Warnings:0
mysql> alter table t1 alter index idx2 invisible;
Query OK,0 rows affected(0.09 sec)
Records:0 Duplicates:0 Warnings:0
mysql> show index from t1 where key_name='idx2' \G;***************************1. row ***************************
Table: t1
Non_unique:1
Key_name: idx2
Seq_in_index:1
Column_name: id1
Collation: A
Cardinality:0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: NO
1 row inset(0.01 sec)
ERROR:
No query specified
When the index is hidden, its content is still updated in real time as the normal index. This feature is specifically for optimized debugging. If you hide an index for a long time, it is better to delete it altogether, because after all, the existence of the index will affect the performance of insert, update and delete.
4) Set persistence
In oracle, you can use the scope to set the parameters to determine whether to reach the spfile. Now mysql8.0 can also be placed in the file. How to use:
mysql> show variables like 'max_connects';
Empty set(0.07 sec)
mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections |500|+-----------------+-------+1 row inset(0.01 sec)
mysql>set persist max_connections=1000;
Query OK,0 rows affected(0.00 sec)
mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections |1000|+-----------------+-------+1 row inset(0.01 sec)
You can view the settings saved in a mysqld-auto.cnf file in the data directory datadir, and the default configuration parameters will be overwritten with this configuration at the next startup
{ " Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "1000" , "Metadata" : { "Timestamp" : 1540437420567571 , "User" : "root" , "Host" : "" } } } }
5) Common Table Expressions
It can also be called a virtual view, greatly simplifying complex queries
mysql> WITH
- > t1 AS(SELECT * FROM t1),-> t2 AS(SELECT * FROM t1)-> SELECT t1.*, t2.*-> FROM t1, t2;+------+------+------+------+| id1 | id2 | id1 | id2 |+------+------+------+------+|1|2|1|2|+------+------+------+------+1 row inset(0.00 sec)
6) Window Functions
One of the most complained features of MySQL is the lack of rank() function. When you need to implement ranking in the query, you must write the @ variable by hand. But since 8.0, MySQL has added a new concept called window function, which can be used to implement several new query methods
Note that the window can be created separately.
mysql> create table tbl2(name varchar(10),amount int);
Query OK,0 rows affected(0.16 sec)
mysql> insert into tbl2 values('usa',100),('china',101),('japan',103),('russian',99);
Query OK,4 rows affected(0.13 sec)
Records:4 Duplicates:0 Warnings:0
mysql> select t1.*,rank() over w as'rank'from tbl2 as t1 window w as(order by amount);+---------+--------+------+| name | amount | rank |+---------+--------+------+| russian |99|1|| usa |100|2|| china |101|3|| japan |103|4|+---------+--------+------+4 rows inset(0.00 sec)
mysql> select t1.*,rank()over()as'rank'from tbl2 as t1;+---------+--------+------+| name | amount | rank |+---------+--------+------+| usa |100|1|| china |101|1|| japan |103|1|| russian |99|1|+---------+--------+------+4 rows inset(0.00 sec)
mysql> select t1.*,rank()over(order by amount)as'rank'from tbl2 as t1;+---------+--------+------+| name | amount | rank |+---------+--------+------+| russian |99|1|| usa |100|2|| china |101|3|| japan |103|4|+---------+--------+------+4 rows inset(0.00 sec)
mysql> select t1.*,sum()over()as'rank'from tbl2 as t1;
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 ') over() as 'rank' from tbl2 as t1' at line 1
mysql> select t1.*,sum(amount)over()as'sum'from tbl2 as t1;+---------+--------+------+| name | amount | sum |+---------+--------+------+| usa |100|403|| china |101|403|| japan |103|403|| russian |99|403|+---------+--------+------+4 rows inset(0.00 sec)
7) safety
Improvements to OpenSSL, new default authentication, SQL roles, password strength, authorization. MySQL now maintains information about password history, allowing restrictions on the reuse of previous passwords
For example, a common connection error, refer to: https://blog.csdn.net/jc_benben/article/details/80652897
8) Add JSON (json enhancements) AND OpenGIS spatial types
mysql provides many json-related functions and API interfaces, adding the JSON_EXTRACT() function to extract data from JSON fields based on path query parameters, and the JSON_ARRAYAGG() and JSON_OBJECTAGG() for combining data into JSON arrays and objects, respectively ) Aggregate function
9) Atomic Data Definition Statements (Atomic DDL)
10) Resource management
MySQL now supports the creation and management of resource groups, and allows threads running in the server to be assigned to specific groups so that threads can execute according to the resources available to the group. Group attributes can control its resources to enable or limit the resource consumption of threads in the group
11) Data dictionary
MySQL now includes a transactional data dictionary for storing information about database objects. In previous MySQL releases, dictionary data was stored in metadata files and nontransactional tables
For example, myisam and innodb table definition frm files, etc.
**12) Innodb's enhancement **1 Each time the value changes, the current maximum auto-increment counter value will be written to the redo log and saved to the engine-specific system table on each checkpoint. These changes make the current maximum auto-increment counter value remain unchanged when the server is restarted.
2 When encountering index tree damage, InnoDB writes the damage flag into the redo log, which makes the damage flag crash safe. InnoDB also writes the damage flag data in memory to the engine-specific system table at each checkpoint. During recovery, InnoDB reads damage flags from two locations and merges the results before marking memory tables and index objects as damaged.
**3 **InnoDB distributed cache plugin supports multiple get operations (read in a single multi-key/value pair distributed cache query) and range query;
**4 **The new dynamic configuration option innodb_deadlock_detect can be used to disable deadlock detection. On a high-concurrency system, when many threads are waiting for the same lock, deadlock detection will slow down. Sometimes, it may be more effective to disable deadlock detection, and rely on the transaction rollback setting when innodb_lock_wait_timeout deadlock occurs.
5 The INFORMATION_SCHEMA.INNODB_CACHED_INDEXES table reports the number of index pages cached in each index buffer pool of InnoDB.
6 InnoDB now creates a temporary table ibtmp1 in the shared temporary table space.
**7 **InnoDB tablespace encryption function supports the encryption of redo logs and the undo log data;
8 InnoDB supports NOWAIT and SKIP LOCKED options SELECT ... FOR SHARE and SELECT ... FOR UPDATE lock read statements. NOWAIT If the requested row is locked by another transaction, the statement will be returned immediately. SKIP LOCKED deletes locked rows from the result set SELECT ... FOR SHARE replaces SELECT ... LOCK IN SHARE MODE, but LOCK IN SHARE MODE can still be used for backward compatibility;
9 Support ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REORGANIZE PARTITION, and REBUILD PARTITION ALTER TABLE options;
10 InnoDB storage engine now uses MySQL data dictionary instead of its own storage engine specific data dictionary;
11 The mysql system tables and data dictionary are represented in the single tablespace file named InnoDB in the MySQL data directory to create mysql.ibd. Previously, these tables were created by InnoDB in various tablespace files in the mysql database directory;
2. Mysql8.0 installation (YUM method)
1) First delete the system default or other versions of mysql that may have been installed before
[ root@DB-node01 ~]# for i in$(rpm -qa|grep mysql);do rpm -e $i --nodeps;done
[ root@DB-node01 ~]# rm -rf /var/lib/mysql && rm -rf /etc/my.cnf
2) Install Mysql8.0 yum resource library
mysql80-community-release-el7-1.noarch.rpm download address: https://pan.baidu.com/s/1QzYaSnzAQeTqAmk8FE9doA
retrieve password: 2maw
[ root@DB-node01 ~]# yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm3)Install Mysql8.0[root@DB-node01 ~]# yum install mysql-community-server
Start MySQL server and automatic start of MySQL
[ root@DB-node01 ~]# systemctl start mysqld
[ root@DB-node01 ~]# systemctl enable mysqld
4) After initial login with default password,Password must be reset
View default password,The following default password is"e53xDalx.*dE"[root@DB-node01 ~]# grep 'temporary password'/var/log/mysqld.log
2019- 03- 06 T01:53:19.897262Z 5[Note][MY-010454][Server] A temporary password is generated for root@localhost: e53xDalx.*dE
[ root@DB-node01 ~]# mysql -pe53xDalx.*dE
............
mysql> select version();
ERROR 1820(HY000): You must reset your password using ALTER USER statement before executing this statement.
An error message prompts that the initial password must be reset,Now start to reset the mysql login password (note that you need to switch to the mysql database, use use mysql)
mysql> use mysql;
ERROR 1820(HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
ERROR 1819(HY000): Your password does not satisfy the current policy requirements
This is actually the same as validate_password_The value of policy is related, mysql8.0 changed validate_password_Policy-related configuration name,This is similar to Mysql5.7 is a little different.
mysql>set global validate_password.policy=0;
Query OK,0 rows affected(0.00 sec)
mysql>set global validate_password.length=1;
Query OK,0 rows affected(0.00 sec)
Then change the password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK,0 rows affected(0.05 sec)
mysql> flush privileges;
Query OK,0 rows affected(0.03 sec)
drop out,Log in to mysql with the new password again
[ root@DB-node01 ~]# mysql -p123456
...........
mysql> select version();+-----------+|version()|+-----------+|8.0.15|+-----------+1 row inset(0.00 sec)
View service port
mysql> show global variables like 'port';+---------------+-------+| Variable_name | Value |+---------------+-------+| port |3306|+---------------+-------+1 row inset(0.01 sec)
View authorization information for mysql connection
mysql> select host,user,password from mysql.user;
ERROR 1054(42S22): Unknown column 'password'in'field list'
Above this is mysql5.View command for version 6 and below,
mysql5.MySQL in the database after 7.The password field is no longer in the user table, and the password field is changed to authentication_string。
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host | user | authentication_string |+-----------+------------------+------------------------------------------------------------------------+| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root | $A$005${7J0=4Dc7Jym8eI/FU4jimKWFvkD9XmoAkF1ca5.Un0bc6zgmPtU.0|+-----------+------------------+------------------------------------------------------------------------+4 rows inset(0.00 sec)=============================mysql8.0 modify user password command================================
mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> flush privileges;
3. Mysql8.0 installation (binary mode)
1) First delete the system default or other versions of mysql that may have been installed before
[ root@mysql8-node ~]# for i in$(rpm -qa|grep mysql);do rpm -e $i --nodeps;done
[ root@mysql8-node ~]# rm -rf /var/lib/mysql && rm -rf /etc/my.cnf
2) Install the required packages
[ root@mysql8-node ~]# yum -y install libaio
[ root@mysql8-node ~]# yum -y install net-tools
3) Download and install Mysql8.0.12
download link: https://pan.baidu.com/s/1LyXrkrCPP7QKLrWYbLRBlw
retrieve password: emmf
[ root@mysql8-node ~]# groupadd mysql
[ root@mysql8-node ~]# useradd -g mysql mysql
[ root@mysql8-node ~]# cd /usr/local/src/[root@mysql-node src]# ll
- rw-r--r--1 root root 620389228 Aug 222018 mysql8.0.12_bin_centos7.tar.gz
[ root@mysql-node src]# tar -zvxf mysql8.0.12_bin_centos7.tar.gz
[ root@mysql-node src]# mv mysql /usr/local/[root@mysql-node src]# chown -R mysql.mysql /usr/local/mysql
[ root@mysql-node src]# vim /home/mysql/.bash_profile
export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
[ root@mysql-node src]# source /home/mysql/.bash_profile
[ root@mysql-node src]# echo "PATH=$PATH:/usr/local/mysql/bin">>/etc/profile
[ root@mysql-node src]# source /etc/profile
4) Create data directory
[ root@mysql-node src]# mkdir -p /data/mysql/{data,log,binlog,conf,tmp}[root@mysql-node src]# chown -R mysql.mysql /data/mysql
5) Configure mysql
[ root@mysql-node src]# su - mysql
[ mysql@mysql-node ~]$ vim /data/mysql/conf/my.cnf
[ mysqld]
lower_case_table_names =1
user = mysql
server_id =1
port =3306default-time-zone ='+08:00'
enforce_gtid_consistency = ON
gtid_mode = ON
binlog_checksum = none
default_authentication_plugin = mysql_native_password
datadir =/data/mysql/data
pid-file =/data/mysql/tmp/mysqld.pid
socket =/data/mysql/tmp/mysqld.sock
tmpdir =/data/mysql/tmp/
skip-name-resolve = ON
open_files_limit =65535
table_open_cache =2000
################# innodb########################
innodb_data_home_dir =/data/mysql/data
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend
innodb_buffer_pool_size = 12000M
innodb_flush_log_at_trx_commit =1
innodb_io_capacity =600
innodb_lock_wait_timeout =120
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group =3
innodb_max_dirty_pages_pct =85
innodb_read_io_threads =8
innodb_write_io_threads =8
innodb_thread_concurrency =32
innodb_file_per_table
innodb_rollback_on_timeout
innodb_undo_directory =/data/mysql/data
innodb_log_group_home_dir =/data/mysql/data
################### session###########################
join_buffer_size = 8M
key_buffer_size = 256M
bulk_insert_buffer_size = 8M
max_heap_table_size = 96M
tmp_table_size = 96M
read_buffer_size = 8M
sort_buffer_size = 2M
max_allowed_packet = 64M
read_rnd_buffer_size = 32M
############ log set###################
log-error =/data/mysql/log/mysqld.err
log-bin =/data/mysql/binlog/binlog
log_bin_index =/data/mysql/binlog/binlog.index
max_binlog_size = 500M
slow_query_log_file =/data/mysql/log/slow.log
slow_query_log =1
long_query_time =10
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes =10
log_slow_admin_statements = ON
log_output = FILE,TABLE
master_info_file =/data/mysql/binlog/master.info
6) initialization(wait a moment,Can reach/data/mysql/log/mysqld.err日子里查看initialization过程,See if there is any error message)[mysql@mysql-node ~]$ mysqld --defaults-file=/data/mysql/conf/my.cnf --initialize-insecure --user=mysql
7) Start mysqld
[ mysql@mysql-node ~]$ mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &[mysql@mysql-node ~]$ lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 24743 mysql 23u IPv6 23132988 0t0 TCP *:mysql(LISTEN)8)Login to mysql,reset Password
No password is required to log in to mysql using the sock file locally for the first time
[ mysql@mysql-node ~]# mysql -S /data/mysql/tmp/mysqld.sock
.............
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK,0 rows affected(0.07 sec)
mysql> flush privileges;
Query OK,0 rows affected(0.03 sec)
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host | user | authentication_string |+-----------+------------------+------------------------------------------------------------------------+| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+------------------+------------------------------------------------------------------------+4 rows inset(0.00 sec)
drop out,After password reset at this time,You can’t use the sock file to log in without a password
[ root@mysql-node ~]# mysql -S /data/mysql/tmp/mysqld.sock
ERROR 1045(28000): Access denied for user 'root'@'localhost'(using password: NO)[root@mysql-node ~]# mysql -p123456
mysql:[Warning] Using a password on the command line interfacecan be insecure.
ERROR 2002(HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Make soft links to sock files
[ root@mysql-node ~]# ln -s /data/mysql/tmp/mysqld.sock /tmp/mysql.sock
[ root@mysql-node ~]# mysql -p123456
or
[ root@mysql-node ~]# mysql -uroot -S /data/mysql/tmp/mysqld.sock -p123456
.............
mysql> select version();+-----------+|version()|+-----------+|8.0.12|+-----------+1 row inset(0.00 sec)
# Grant user permissions.Must first create user,To authorize!!(When creating a user to bring@And specify the address,Then the address when grant is authorized is this@Specified later!,Otherwise, grant authorization will report an error!)
mysql> create user 'kevin'@'%' identified by '123456';
Query OK,0 rows affected(0.11 sec)
mysql> grant all privileges on *.* to 'kevin'@'%'with grant option;
Query OK,0 rows affected(0.21 sec)
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host | user | authentication_string |+-----------+------------------+------------------------------------------------------------------------+|%| kevin |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+------------------+------------------------------------------------------------------------+5 rows inset(0.00 sec)
mysql> update mysql.user set host='172.16.60.%' where user="kevin";
Query OK,1 row affected(0.16 sec)
Rows matched:1 Changed:1 Warnings:0
mysql> flush privileges;
Query OK,0 rows affected(0.05 sec)
mysql> select host,user,authentication_string from mysql.user;+-------------+------------------+------------------------------------------------------------------------+| host | user | authentication_string |+-------------+------------------+------------------------------------------------------------------------+|172.16.60.%| kevin |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-------------+------------------+------------------------------------------------------------------------+5 rows inset(0.00 sec)
mysql> create user 'bobo'@'172.16.60.%' identified by '123456';
Query OK,0 rows affected(0.09 sec)
mysql> grant all privileges on *.* to 'bobo'@'172.16.60.%';
Query OK,0 rows affected(0.17 sec)
mysql> flush privileges;
Query OK,0 rows affected(0.04 sec)
mysql> select host,user,authentication_string from mysql.user;+-------------+------------------+------------------------------------------------------------------------+| host | user | authentication_string |+-------------+------------------+------------------------------------------------------------------------+|172.16.60.%| bobo |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ||172.16.60.%| kevin |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-------------+------------------+------------------------------------------------------------------------+6 rows inset(0.00 sec)
mysql> show grants for kevin@'172.16.60.%';+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected].%|+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `kevin`@`172.16.60.%` WITH GRANT OPTION |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row inset(0.00 sec)
MySQL single-machine multi-instance installation configuration
From the above binary deployment, we can see that a MySQL instance with port 3306 has been set up. Now two more instances need to be set up, 3307 and 3308 respectively. The operations are as follows:
Create a data directory for the instance
[ root@mysql-node ~]# mkdir -p /data/mysql3307/{data,log,binlog,conf,tmp}[root@mysql-node ~]# mkdir -p /data/mysql3308/{data,log,binlog,conf,tmp}[root@mysql-node ~]# chown -R mysql.mysql /data/mysql3307
[ root@mysql-node ~]# chown -R mysql.mysql /data/mysql3308
Configure mysql
[ root@mysql-node ~]# cp -r /data/mysql/conf/my.cnf /data/mysql3307/conf/[root@mysql-node ~]# cp -r /data/mysql/conf/my.cnf /data/mysql3308/conf/[root@mysql-node ~]# sed -i 's#/data/mysql/#/data/mysql3307/#g'/data/mysql3307/conf/my.cnf
[ root@mysql-node ~]# sed -i 's#/data/mysql/#/data/mysql3308/#g'/data/mysql3308/conf/my.cnf
[ root@mysql-node ~]# sed -i 's/3306/3307/g'/data/mysql3307/conf/my.cnf
[ root@mysql-node ~]# sed -i 's/3306/3308/g'/data/mysql3308/conf/my.cnf
[ root@mysql-node ~]# chown -R mysql.mysql /data/mysql*
Initialize two instances
[ root@mysql-node ~]# mysqld --defaults-file=/data/mysql3307/conf/my.cnf --initialize-insecure --user=mysql
[ root@mysql-node ~]# mysqld --defaults-file=/data/mysql3308/conf/my.cnf --initialize-insecure --user=mysql
Then start mysqld
[ root@mysql-node ~]# mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf &[root@mysql-node ~]# mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf &[root@mysql-node ~]# ps -ef|grep mysql
mysql 239961014:37?00:00:00/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/conf/my.cnf
mysql 2474323996014:38?00:00:17/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/conf/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/mysql/log/mysqld.err --open-files-limit=65535--pid-file=/data/mysql/tmp/mysqld.pid --socket=/data/mysql/tmp/mysqld.sock --port=3306
root 3047323727015:33 pts/000:00:00/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf
mysql 31191304731715:33 pts/000:00:02/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql3307/conf/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3307/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql3307/log/mysqld.err --open-files-limit=65535--pid-file=/data/mysql3307/tmp/mysqld.pid --socket=/data/mysql3307/tmp/mysqld.sock --port=3307
root 3125423727015:33 pts/000:00:00/bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf
mysql 31977312543915:33 pts/000:00:02/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql3308/conf/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3308/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql3308/log/mysqld.err --open-files-limit=65535--pid-file=/data/mysql3308/tmp/mysqld.pid --socket=/data/mysql3308/tmp/mysqld.sock --port=3308
root 3204423727015:34 pts/000:00:00 grep --color=auto mysql
[ root@mysql-node ~]# lsof -i:3307
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 31191 mysql 22u IPv6 23144844 0t0 TCP *:opsession-prxy(LISTEN)[root@mysql-node ~]# lsof -i:3308
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 31977 mysql 22u IPv6 23145727 0t0 TCP *:tns-server(LISTEN)[root@mysql-node ~]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 24743 mysql 23u IPv6 23132988 0t0 TCP *:mysql(LISTEN)
Log in to port 3307 instance,And set a password
[ root@mysql-node ~]# mysql -S /data/mysql3307/tmp/mysqld.sock
............
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK,0 rows affected(0.11 sec)
mysql> flush privileges;
Query OK,0 rows affected(0.11 sec)
drop out,Login with new password
[ root@mysql-node ~]# mysql -uroot -S /data/mysql3307/tmp/mysqld.sock -p123456
.............
mysql>
The same,Log in to port 3308 instance,And set a password
[ root@mysql-node ~]# mysql -S /data/mysql3308/tmp/mysqld.sock
...........
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK,0 rows affected(0.13 sec)
mysql> flush privileges;
Query OK,0 rows affected(0.03 sec)
drop out,Login with new password
[ root@mysql-node ~]# mysql -uroot -S /data/mysql3308/tmp/mysqld.sock -p123456
....................
mysql>==========================================3306,3307,The startup commands for the three port instances of 3308 are:
mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &
mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf &
mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf &
The login commands are:
mysql -uroot -S /data/mysql/tmp/mysqld.sock -p123456
mysql -uroot -S /data/mysql3307/tmp/mysqld.sock -p123456
mysql -uroot -S /data/mysql3308/tmp/mysqld.sock -p123456
Write login script for mysql multi-port instance
After the above multi-port instance configuration,View a login script:myin(Pay attention to change the password in the script to your own password)[root@mysql-node ~]# ll /usr/local/mysql/bin/myin
- rwxrwxr-x 1 mysql mysql 161 Aug 222018/usr/local/mysql/bin/myin
[ root@mysql-node ~]# cat /usr/local/mysql/bin/myin
#! /bin/bash
p=$1
shift
mysql -h"127.0.0.1"-P"$p"--default-character-set=utf8mb4 --show-warnings -uroot -p'123456'-A --prompt="(\u@\p)[\d]> ""$@"
Try to log in
[ mysql@mysql-node ~]# myin 3306
mysql:[Warning] Using a password on the command line interfacecan be insecure.
ERROR 1130(HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
This is because the root user in each port instance is authorized to localhost,Instead of 127.0.0.1,You can add another one for 127.0.0.1 root user authorization
Or change the 127 in the myin script.0.0.1 Modify to localhost
[ root@mysql-node ~]# sed -i 's/127.0.0.1/localhost/g'/usr/local/mysql/bin/myin
[ root@mysql-node ~]# cat /usr/local/mysql/bin/myin
#! /bin/bash
p=$1
shift
mysql -h"localhost"-P"$p"--default-character-set=utf8mb4 --show-warnings -uroot -p'123456'-A --prompt="(\u@\p)[\d]> ""$@"
You can now log in to each instance,The login command is"myin port"
Log in to port 3306 instance
[ root@mysql-node ~]# myin 3306
mysql:[Warning] Using a password on the command line interfacecan be insecure.
Welcome to the MySQL monitor. Commands end with; or \g.
Your MySQL connection id is 27
Server version:8.0.12 MySQL Community Server - GPL
Copyright(c)2000,2018, 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.([email protected])[(none)]>
Log in to port 3307 instance
[ root@mysql-node ~]# myin 3307
mysql:[Warning] Using a password on the command line interfacecan be insecure.
Welcome to the MySQL monitor. Commands end with; or \g.
Your MySQL connection id is 28
Server version:8.0.12 MySQL Community Server - GPL
Copyright(c)2000,2018, 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.([email protected])[(none)]>
Log in to port 3308 instance
[ root@mysql-node ~]# myin 3308
mysql:[Warning] Using a password on the command line interfacecan be insecure.
Welcome to the MySQL monitor. Commands end with; or \g.
Your MySQL connection id is 29
Server version:8.0.12 MySQL Community Server - GPL
Copyright(c)2000,2018, 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.([email protected])[(none)]>
**Ideas in the production environment: The same business library is placed in a separate instance, do not mix databases. Single-machine multi-instance can be master-slave or master-master synchronization, or it can be master-slave or master-master synchronization between multiple machines and multiple instances. **
such as:
There are three instances of machine A: 3310, 3311, 3312
There are three instances of machine B: 3310, 3311, 3312
There are three instances of C machine: 3310, 3311, 3312
Then each corresponding port instance of the three machines is master-slave or master-master synchronization (GTID, MGR), one-master two-slave, or three-master mode. (Multi-instance environment is best not to use port 3306, otherwise it will often appear Some misoperation)
Four. Some pits stepped on during the use of Mysql8.0
1) Create user and authorization
User creation and authorization in mysql8.0 are not the same as before. In fact, strictly speaking, it can’t be said to be different. It can only be said to be stricter. mysql8.0 needs to create a user first** (when creating a user, bring @ And specify the address, then the address when grant authorization is specified after this @!, otherwise the grant authorization will report an error!)** and set the password before authorization.
mysql> create user 'kevin'@'%' identified by '123456';
Query OK,0 rows affected(0.04 sec)
mysql> grant all privileges on *.* to 'kevin'@'%'with grant option;
Query OK,0 rows affected(0.04 sec)
mysql> create user 'bobo'@'%' identified by '123456';
Query OK,0 rows affected(0.06 sec)
mysql> grant all privileges on *.* to 'bobo'@'%'with grant option;
Query OK,0 rows affected(0.03 sec)
mysql> flush privileges;
Query OK,0 rows affected(0.04 sec)
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host | user | authentication_string |+-----------+------------------+------------------------------------------------------------------------+|%| bobo | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 ||%| kevin | $A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 || localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | root | $A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2 |+-----------+------------------+------------------------------------------------------------------------+
If you still use the direct authorization method of Mysql5.7 and previous versions, an error will be reported:
mysql> grant all privileges on *.* to 'shibo'@'%' identified by '123456';
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 '123456'' at line 1
2) Mysql8.0 default is not to use the root account to log in remotely! The root account can only log in locally!
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host | user | authentication_string |+-----------+------------------+------------------------------------------------------------------------+|%| bobo | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 ||%| kevin | $A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | $A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2 |
+-----------+------------------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)
If you want to log in remotely,You need to update the permissions of the root account
mysql> update mysql.user set host='%' where user="root";
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.14 sec)
mysql> select host,user,authentication_string from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+------------------------------------------------------------------------+
| % | bobo | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 |
| % | kevin | $A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 ||%| root | $A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2 || localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED || localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |+-----------+------------------+------------------------------------------------------------------------+6 rows inset(0.00 sec)
In this way, you can use the root account to log in to the mysql8.0 database remotely
**3) After modifying the permissions of the root account and allowing the root account to log in remotely, when using Navicat to connect to mysql remotely, a pop-up error message appeared: **
The reason for this is that the encryption rule in the version before mysql8 is mysql_native_password, and after mysql8, the encryption rule is cache_sha2_password, there are two ways to solve the problem:
- One is to upgrade the navicat driver;
- One is to restore the mysql user login password encryption rule to mysql_native_password;
Here we choose the second method to solve:
# Modify encryption rules
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
Query OK,0 rows affected(0.16 sec)
# Update the user's password
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK,0 rows affected(0.08 sec)
# Refresh permissions
mysql> FLUSH PRIVILEGES;
Query OK,0 rows affected(0.03 sec)
This way the problem is solved.
4) 2058 exception when sqlyog linking
1) When using the sqlyog link, there will be an exception of 2058. At this time, we need to modify mysql, log in to mysql from the command line (the same as in the modified password, use the modified password), and then execute the following command:
mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
The password is the password you modified. Then reconnect in SQLyog, the connection can be successful, OK.
2) If an error is reported: ERROR 1396(HY000): Operation ALTER USER failed for'root'@'localhost'Then use the following command:
mysql > ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
5) Modify the default encoding
mysql8.0 The default encoding method is utf8mb4, so there is no need to modify it when using it, you can use the following command to view:
mysql > SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
If you need to modify other encoding methods, such as utf8mb4, you can use the following methods:
Modify the mysql configuration file my.cnf,After you find it, please add the following content in the following three parts:
[ client]default-character-set= utf8mb4
[ mysql]default-character-set= utf8mb4
[ mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
Then restart the mysqld service,among them:character_set_client(The character set used by the client source data)character_set_connection(Connection layer character set)character_set_database(The default character set of the currently selected database)character_set_results(Query result character set)character_set_server(The default internal operation character set)========================================
Database connection parameters:
characterEncoding=utf8 will be automatically recognized as utf8mb4, or it can be automatically detected without adding this parameter.
While autoReconnect=true must be added.
6) Some parameter configuration query commands
# Query mysql maximum connection number setting
mysql> show global variables like 'max_conn%';
mysql> SELECT @@MAX_CONNECTIONS AS 'Max Connections';
# View the maximum number of links
mysql> show global status like 'Max_used_connections';
# Check whether the slow query log is enabled and the log location
mysql> show variables like 'slow_query%';
# View the timeout recording time of the slow query log
mysql> show variables like 'long_query_time';
# View the link creation and the number of links currently in progress
mysql> show status like 'Threads%';
# View current database link
mysql> show processlist;
# View database configuration
mysql> show variables like '%quer%';
Recommended Posts