How to compile and install MySQL 5.7.9 in CentOS

MySQL 5.7 GA version is released, which means that MySQL 5.7 can be used in the production environment from now on. The official team will fix any problems immediately.

Main features of MySQL 5.7:

Better performance: it provides better optimization for multi-core CPUs, Solid State Disks, and locks. 100 QPS per second is no longer the pursuit of MySQL, whether or not the next version can reach 200 million QPS is more important for our users.

Better InnoDB storage engine

More robust replication: replication brings about a solution that never loses data. Traditional financial customers can also choose to use MySQL databases. In addition, the smooth online upgrade of GTID is also possible.
Better Optimizer: the significance of the optimizer code reconstruction will bring huge improvements in this version and later versions. Oracle officially solves the biggest problem of MySQL earlier.

Native JSON type support

Better geographic information service support: InnoDB native supports geographic location types, GeoJSON and GeoHash features
New sys database: this will be the database frequently accessed by DBAs.
MySQL 5.7 has been added to OneinStack as a database option

Install dependency packages

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

Download the source code package

Cd/root/oneinstack/src
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-5.7.9.tar.gz

Add a mysql User

Useradd-M-s/sbin/nologin mysql

Pre-compile

Tar xzf boost_000059_0.tar.gz
Tar xzf mysql-5.7.9.tar.gz
Mkdir-p/data/mysql
Cd mysql-5.7.9
Cmake.-DCMAKE_INSTALL_PREFIX =/usr/local/mysql \
-DMYSQL_DATADIR =/data/mysql \
-DDOWNLOAD_BOOST = 1 \ # Boost library is required starting from MySQL 5.7.5
-DWITH_BOOST = ../boost_000059_0 \
-DSYSCONFDIR =/etc \
-DWITH_INNOBASE_STORAGE_ENGINE = 1 \
-DWITH_PARTITION_STORAGE_ENGINE = 1 \
-DWITH_FEDERATED_STORAGE_ENGINE = 1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE = 1 \
-DWITH_MYISAM_STORAGE_ENGINE = 1 \
-DENABLED_LOCAL_INFILE = 1 \
-DENABLE_DTRACE = 0 \
-DDEFAULT_CHARSET = utf8mb4 \
-DDEFAULT_COLLATION = utf8mb4_general_ci \
-DWITH_EMBEDDED_SERVER = 1

Compile and install

Make-j 'grep processor/proc/cpuinfo | wc-L' # compilation consumes a lot of system resources. Compilation of small memory may fail.
Make install

Start script, set auto-start

/Bin/cp/usr/local/mysql/support-files/mysql. server/etc/init. d/mysqld
Chmod + x/etc/init. d/mysqld
Chkconfig -- add mysqld
Chkconfig mysqld on
/Etc/my. cnf, for reference only
Cat>/etc/my. cnf <EOF
[Client]
Port = 3306
Socket =/tmp/mysql. sock
Default-character-set = utf8mb4

[Mysqld]
Port = 3306
Socket =/tmp/mysql. sock

Basedir =/usr/local/mysql
Datadir =/data/mysql
Pid-file =/data/mysql. pid
User = mysql
Bind-address = 0.0.0.0
Server-id = 1

Init-connect = 'set NAMES utf8mb4'
Character-set-server = utf8mb4

# Skip-name-resolve
# Skip-networking
Back_log = 300

Max_connections = 1000
Max_connect_errorrs = 6000
Open_files_limit = 65535
Table_open_cache = 128
Max_allowed_packet = 4 M
Binlog_cache_size = 1 M
Max_heap_table_size = 8 M
Tmp_table_size = 16 M

Read_buffer_size = 2 M
Read_rnd_buffer_size = 8 M
Sort_buffer_size = 8 M
Join_buffer_size = 8 M
Key_buffer_size = 4 M

Thread_cache_size = 8

Query_cache_type = 1
Query_cache_size = 8 M
Query_cache_limit = 2 M

Ft_min_word_len = 4

Log_bin = mysql-bin
Binlog_format = mixed
Expire_logs_days = 30

Log_error =/data/mysql/mysql-error.log
Slow_query_log = 1
Long_query_time = 1
Slow_query_log_file =/data/mysql/mysql-slow.log.

Performance_schema = 0
Explicit_defaults_for_timestamp

# Lower_case_table_names = 1

Skip-external-locking

Default_storage_engine = InnoDB
# Default-storage-engine = MyISAM
Innodb_file_per_table = 1
Innodb_open_files = 500
Innodb_buffer_pool_size = 64 M
Innodb_write_io_threads = 4
Innodb_read_io_threads = 4
Innodb_thread_concurrency = 0
Innodb_purge_threads = 1
Innodb_flush_log_at_trx_commit = 2
Innodb_log_buffer_size = 2 M
Innodb_log_file_size = 32 M
Innodb_log_files_in_group = 3
Innodb_max_dirty_pages_pct = 90
Innodb_lock_wait_timeout = 120

Bulk_insert_buffer_size = 8 M
Myisam_sort_buffer_size = 8 M
Myisam_max_sort_file_size = 10G
Myisam_repair_threads = 1

Interactive_timeout = 28800
Wait_timeout = 28800

[Mysqldump]
Quick
Max_allowed_packet = 16 M

[Myisamchk]
Key_buffer_size = 8 M
Sort_buffer_size = 8 M
Read_buffer = 4 M
Write_buffer = 4 M
EOF

Initialize database

/Usr/local/mysql/bin/mysqld -- initialize-insecure -- user = mysql -- basedir =/usr/local/mysql -- datadir =/data/mysql
Note:

In earlier versions, mysql_install_db was stored in mysql_basedir/script, 5.7 was stored in the mysql_install_db/bin directory, and has been deprecated.

"-Initialize" will generate a random password (~ /. Mysql_secret), and "-initialize-insecure" does not generate a password
-Data files cannot be found in the target datadir directory.
START database
Service mysqld start
Set database password

Dbrootpwd = oneinstack # Database root password
/Usr/local/mysql/bin/mysql-e "grant all privileges on *. * to root @ '2017. 0.0.1 'identified by \ "$ dbrootpwd \" with grant option ;"
/Usr/local/mysql/bin/mysql-e "grant all privileges on *. * to root @ 'localhost' identified by \" $ dbrootpwd \ "with grant option ;"