Shulou(Shulou.com)05/31 Report--

Today, the editor will share with you the relevant knowledge points about the differences between mysql 5.6and 5.7. the content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

The difference between mysql version 5.6 and version 5.7: version 1,5.7 provides json format data, while version 5.6 does not provide json version data; version 2,5.7 supports multiple masters and one slave, while version 5.6 does not support multiple masters and one slave; version 3,5.7 initializes data in the bin directory, while version 5.6 is in the script directory.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the difference between 5.6and 5.7in mysql I. differences in compilation and installation

There are many changes after the update of the Mysql5.7 version, such as json, etc., even the installation has changed, he must install the BOOST library. There are two kinds of mysql source code on the official website: the source code with boost library and the source code without boost library. The source code without boost library needs to be installed separately with boost.

Mysql5.7 supports multiple masters and one slave, and there are different ways to do high.

Download software

Wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gzwget http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.20.tar.gzwget http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.20.tar.gz

Mysql source code installation with boost

1. Install dependency packages

Yum-y install make gcc-c++ cmake bison-devel ncurses-devel bison perl perl-devel perl perl-devel

two。 Compilation and installation

Cd / usr/local/mysql-5.7.20/cmake-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DWITH_BOOST=boostmakemake install

Mysql source code installation without boost

1. Install dependency packages

Yum-y install gcc gcc-c++ ncurses ncurses-devel cmake

two。 Compilation and installation

Cmake. -DCMAKE_INSTALL_PREFIX=/application/mysql-5.7.20\-DMYSQL_DATADIR=/application/mysql-5.7.20/data\-DMYSQL_UNIX_ADDR=/application/mysql-5.7.20/tmp/mysql.sock\ # Open BOOST library-DDOWNLOAD_BOOST=1\ # specify boost library location-DWITH_BOOST=/usr/local/boost_1_59_0\-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DWITH_EXTRA_CHARSETS=all\-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1\-DWITH_ZLIB=bundled\-DWITH_SSL=bundled\-DENABLED_LOCAL_INFILE=1\-DWITH_EMBEDDED_SERVER=1\-DENABLE_DOWNLOADS=1\-DWITH_DEBUG=0 makemake install II. The difference in initial recognition # [preliminary recognition of version 5.6] cd / usr/local/mysql/scripts/./mysql_install_db-- user=mysql-- basedir=/usr/local/mysql-- datadir=/usr/local/mysql/data# initialization The default password is in a file / usr/local/mysql/bin/mysqld-initialize-user=mysql-basedir=/usr/local/mysql-datadir=/usr/local/mysql/data

-initialize generates a random password to write to a file

-initialize-insecure does not generate random passwords

III. The difference between functions and features

Security.

The plugin field of the user table mysql.user is not allowed to be empty, the default value is mysql_native_password, not mysql_old_password, and the old password format is no longer supported

Add password expiration mechanism. You need to change the password after expiration, otherwise it may be disabled or enter sandbox mode.

Add password expiration mechanism. You need to change the password after expiration, otherwise it may be disabled or enter sandbox mode.

A simpler SSL secure access configuration is provided, and the default connection is encrypted with SSL.

Flexibility

Starting with version 5.7.8, the MySQL database also provides support for JSON.

It can store both structured and unstructured data, and has the advantages of both relational and non-relational databases.

Able to provide complete transaction support

Generated column is a new feature introduced by MySQL 5.7. the so-called generated column means that this column in the database is calculated by other columns.

Ease of use

Before MySQL 5.7. if the user typed the wrong SQL statement and pressed ctrl+c, it could "end" the run of the SQL statement, but it would also exit the current session. MySQL 5.7improves this counterintuitive place and no longer exits the session.

MySQL 5.7 can explain a running SQL, which is useful for DBA analysis of statements that take a long time to run.

Sys schema, a system library introduced in MySQL 5.7.7, contains a series of views, functions, and stored procedures, and the project focuses on the ease of use of MySQL.

Usability

You no longer need to restart MySQL for online replication filtering rules, you just need to stop SQLthread, and start SQLthread after the modification is completed.

Modify the size of buffer pool online.

Online DDL MySQL 5.7supports renaming indexes and changing the size of varchar, both of which required index or table rebuilding in previous versions.

GTID is enabled online. In the previous version, GTID is not supported online. If you want to upgrade a lower version of the database to a database version that supports GTID, you need to shut down the database before starting it in GTID mode, so it is very troublesome to upgrade.

Performance

Performance improvement of temporary tables.

Temporary tables are visible only in the current session

The life cycle of the temporary table is the current connection (if the MySQL goes down or restarts, the current connection ends)

Read-only transaction performance improvement.

MySQL 5.7optimizes the cost of read-only transactions and improves the overall performance of the database by avoiding allocating transaction ID for read-only transactions, not allocating rollback segments for read-only transactions, and reducing lock contention.

-accelerate connection processing

Before MySQL 5.7, the initialization operations of variables (THD, VIO) were completed in the connection receiving thread. Now these tasks are sent to the worker thread to reduce the workload of the connection receiving thread and improve the processing speed of the connection. This optimization will be very useful for applications that frequently establish short connections.

Improved replication performance (support for multithreaded replication (Multi-Threaded Slaves, MTS)

The default configuration of MySQL is library-level parallel replication. In order to make full use of the parallel replication function of MySQL 5.7, we need to configure slave-parallel-type as LOGICAL_CLOCK.

Support for multi-source replication (Multi-source replication)

Strict change

STRICT_TRANS_TABLES mode is enabled by default.

More complex feature support is implemented for ONLY_FULL_GROUP_BY mode and is enabled by default.

Other sql mode enabled by default is NO_ENGINE_SUBSTITUTION.

Change of default parameters

The default binlog format is adjusted to ROW format

The operation after the default binlog error is adjusted to ABORT_SERVER

Under the previous option (binlog_error_action=IGNORE_ERROR), if an error occurs that makes it impossible to write to binlog,mysql-server, the error is logged in the error log and the binlog feature is forced to be turned off. This causes the mysql-server to continue to run without recording the binlog, making it impossible to get the binlog of the master library from the slave library.

Binlog security in the event of a mysql crash is enabled by default.

Slave_net_timeout is lowered by default.

Installation is different

Mysql_install_db is no longer recommended. It is recommended to change it to mysqld-initialize to complete instance initialization. If there is already a data file in the target directory that datadir points to, there will be [ERROR] Aborting

If-- initial-insecure is added during initialization, a root@localhost account with an empty password will be created, otherwise a root@localhost account with a password will be created, and the password will be directly written in the log-error log file. New users need to change their password immediately after logging in, otherwise they will not be able to continue the follow-up work.

These are all the contents of this article entitled "what are the differences between 5.6and 5.7in mysql". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.