What is the difference between 5.6 and 5.7 in mysql

The difference between 5.6 and 5.7 in mysql: 1. Version 5.7 provides json format data, but version 5.6 does not provide json version data; 2. Version 5.7 supports multiple masters and one slave, but version 5.6 does not support multiple masters First, when initializing data in versions 3 and 5.7, it is in the bin directory, while in version 5.6 it 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.6 and 5.7 in mysql?

1. Differences in compilation and installation

There are many changes after the Mysql5.7 version is updated, such as json, etc., and even the installation has changed. , he must install the BOOST library. The official website source code of mysql includes source code with boost library and source code without boost library. Those without boost library source code need to install boost separately.

mysql5.7 supports multiple masters and one slave, and has different ways to achieve high availability

Download software

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

Mysql source code installation with boost

1. Installation Dependency package

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

2. Compile and install

cd /usr/local/mysql-5.7.20/
cmake -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_BOOST=boost
make
make install

Mysql source code installation without boost

1.Install dependency package

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

2.Compile and install

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 \
#开启BOOST库
-DDOWNLOAD_BOOST=1 \
#指定boost库位置
-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 
make
make install

2. The difference during initial recognition

#【5.6版本初识化】
cd /usr/local/mysql/scripts/
./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
#【5.7版本初识化】这种初始化方式，默认密码在一个文件中
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

–initialize generates a random password and writes it to a file

–initialize-insecure does not generate a random password

3. 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. 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 it after expiration Change the password, otherwise it may be disabled or enter sandbox mode;

provides a simpler SSL security access configuration, and the default connection uses SSL encryption.

Flexibility

MySQL database also provides support for JSON starting from version 5.7.8.

Can store structured data and unstructured data mixedly, and has the advantages of relational database and non-relational database at the same time

Can provide complete transaction support

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

Ease of use

Before MySQL 5.7, if The user enters an incorrect SQL statement and presses ctrl c. Although it can "end" the execution of the SQL statement, it will also exit the current session. MySQL 5.7 has improved this counterintuitive aspect and no longer exits the session.

MySQL 5.7 can explain a running SQL, which will be very useful for DBAs to analyze statements that take a long time to run.

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

Availability

Online settings for replicated filtering rules no longer require restarting MySQL. You only need to stop the SQLthread. After the modification is completed, start the SQLthread.

Modify the buffer pool size online.

Online DDL MySQL 5.7 supports renaming indexes and modifying the size of varchar. In previous versions, these two operations required rebuilding the index or table.

Enable GTID online. In previous versions, since enabling GTID online was not supported, if users want to upgrade a lower version of the database to a database version that supports GTID, they need to close the database first and then start it in GTID mode. This makes upgrading particularly troublesome.

Performance

Performance improvements for temporary tables.

The temporary table is only visible in the current session

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

Read-only transaction performance Improve.

MySQL 5.7 optimizes the overhead of read-only transactions and improves the overall performance of the database by avoiding allocating transaction IDs for read-only transactions, not allocating rollback segments for read-only transactions, reducing lock competition, etc. .

-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 connection processing speed. This optimization will be very useful for applications that frequently establish short connections.

Improvements in replication performance (support for multi-threaded replication (Multi-Threaded Slaves, referred to as MTS))

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

Support multi-source replication (Multi-source replication)

Strict changes

The STRICT_TRANS_TABLES mode is enabled by default.

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

Other sql modes that are enabled by default include NO_ENGINE_SUBSTITUTION.

Change of default parameters

The default binlog format is adjusted to ROW format

The default operation after binlog error is adjusted to ABORT_SERVER

Under the previous option (binlog_error_action=IGNORE_ERROR), if an error occurs and the binlog cannot be written, mysql-server will record the error in the error log And forcefully turn off the binlog function. This will cause mysql-server to continue running in a mode that does not record binlog, causing the slave library to be unable to continue to obtain the binlog of the main library.

Binlog security when mysql crashes is enabled by default.

Reduce slave_net_timeout by default.

Different installations

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 pointed by datadir, 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 written directly 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 their work.

Recommended learning: mysql video tutorial

The above is the detailed content of What is the difference between 5.6 and 5.7 in mysql. For more information, please follow other related articles on the PHP Chinese website!