How to Compile and Install MySQL 5.7.13 on CentOS 7 Step by Step

MySQL 5.7 Key Features

1. Better Performance
   Optimized for multi-core CPUs, SSDs, and locks — 1 million QPS is no longer the goal for MySQL; users are more concerned about whether the next version can hit 2 million QPS.

• Improved InnoDB Storage Engine
• More Robust Replication
  Replication brings a zero-data-loss solution, making MySQL a viable choice even for traditional financial customers. Additionally, online smooth upgrades for GTID are now possible.
• Better Optimizer
  The refactored optimizer code will bring significant improvements in this and future versions. Oracle is finally tackling one of MySQL’s biggest long-standing challenges.
• Native JSON Support
• Better Geospatial Service Support
  InnoDB natively supports geographic location types, GeoJSON, and GeoHash features
• New sys Schema
  This will become the most frequently accessed database for DBAs. MySQL 5.7 has already been added as an optional database in OneinStack

Installation Preparation

Install Dependencies

[root@snails ~]# yum -y install gcc gcc-c++ ncurses ncurses-devel cmake bison

Download Source Packages

[root@snails ~]# wget https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz[root@snails ~]# wget http://cdn.mysql.com/Downloads/MySQL-5.7/mysql-5.7.13.tar.gz

You can also use the official download link.

Create MySQL User and Group

[root@snails ~]# groupadd -r mysql && useradd -r -g mysql -s /sbin/nologin -M mysql 

Pre-compilation Configuration

[root@snails ~]# tar -zxvf boost_1_59_0.tar.gz[root@snails data]# md5sum mysql-5.7.13.tar.gz 8fab75dbcafcd1374d07796bff88ae00  mysql-5.7.13.tar.gz[root@snails ~]# tar -zxvf mysql-5.7.13.tar.gz[root@snails data]# mkdir -p /data/mysql[root@snails data]# cd mysql-5.7.13[root@snails data]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql /-DMYSQL_DATADIR=/data/mysql /-DWITH_BOOST=../boost_1_59_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

[root@snails mysql-5.7.13]# make -j `grep processor /proc/cpuinfo | wc -l`# Compilation is very resource-intensive; low memory may cause it to fail make install[root@snails mysql-5.7.13]# make install

Set Up Startup Script for Auto-start on Boot

[root@snails mysql-5.7.13]# ls -lrt /usr/local/mysql[root@snails mysql-5.7.13]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[root@snails mysql-5.7.13]# chmod +x /etc/init.d/mysqld[root@snails mysql-5.7.13]# systemctl enable mysqldmysqld.service is not a native service, redirecting to /sbin/chkconfig.Executing /sbin/chkconfig mysqld on

Configuration File

/etc/my.cnf, for reference only [root@snails mysql-5.7.13]# cat > /etc/my.cnf << EOF[client]port = 3306socket = /dev/shm/mysql.sock[mysqld]port = 3306socket = /dev/shm/mysql.sockbasedir = /usr/local/mysqldatadir = /data/mysqlpid-file = /data/mysql/mysql.piduser = mysqlbind-address = 0.0.0.0server-id = 1init-connect = 'SET NAMES utf8mb4'character-set-server = utf8mb4#skip-name-resolve#skip-networkingback_log = 300max_connections = 1000max_connect_errors = 6000open_files_limit = 65535table_open_cache = 128max_allowed_packet = 4Mbinlog_cache_size = 1Mmax_heap_table_size = 8Mtmp_table_size = 16Mread_buffer_size = 2Mread_rnd_buffer_size = 8Msort_buffer_size = 8Mjoin_buffer_size = 8Mkey_buffer_size = 4Mthread_cache_size = 8query_cache_type = 1query_cache_size = 8Mquery_cache_limit = 2Mft_min_word_len = 4log_bin = mysql-binbinlog_format = mixedexpire_logs_days = 30log_error = /data/mysql/mysql-error.logslow_query_log = 1long_query_time = 1slow_query_log_file = /data/mysql/mysql-slow.logperformance_schema = 0explicit_defaults_for_timestamp#lower_case_table_names = 1skip-external-lockingdefault_storage_engine = InnoDB#default-storage-engine = MyISAMinnodb_file_per_table = 1innodb_open_files = 500innodb_buffer_pool_size = 64Minnodb_write_io_threads = 4innodb_read_io_threads = 4innodb_thread_concurrency = 0innodb_purge_threads = 1innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 32Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120bulk_insert_buffer_size = 8Mmyisam_sort_buffer_size = 8Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1interactive_timeout = 28800wait_timeout = 28800[mysqldump]quickmax_allowed_packet = 16M[myisamchk]key_buffer_size = 8Msort_buffer_size = 8Mread_buffer = 4Mwrite_buffer = 4MEOF

Add MySQL Environment Variables

[root@snails mysql-5.7.13]# echo -e '/n/nexport PATH=/usr/local/mysql/bin:$PATH/n' >> /etc/profile && source /etc/profile

Initialize Database

[root@snails mysql-5.7.13]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

Note:

• In previous MySQL versions, mysql_install_db was located under mysql_basedir/script.
• In MySQL 5.7, it is directly under the mysql_install_db/bin directory.
• "–initialize" is deprecated and generates a random password (~/.mysql_secret).
• "–initialize-insecure" does not generate a password.
• The "–datadir" directory must not contain any data files.

Start Database

[root@snails mysql-5.7.13]# systemctl start mysqld[root@snails mysql-5.7.13]# systemctl status mysqld● mysqld.service - LSB: start and stop MySQL   Loaded: loaded (/etc/rc.d/init.d/mysqld)   Active: active (running) since Mon 2016-07-18 11:15:35 CST; 8s ago     Docs: man:systemd-sysv-generator(8)  Process: 23927 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)   CGroup: /system.slice/mysqld.service           ├─23940 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid           └─24776 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql-err...Jul 18 11:15:32 snails systemd[1]: Starting LSB: start and stop MySQL...Jul 18 11:15:35 snails mysqld[23927]: Starting MySQL..[  OK  ]Jul 18 11:15:35 snails systemd[1]: Started LSB: start and stop MySQL.

Check MySQL Service Process and Port

[root@snails mysql-5.7.13]# ps -ef | grep mysqlroot     23940     1  0 11:15 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pidmysql    24776 23940  0 11:15 ?        00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/mysql.pid --socket=/dev/shm/mysql.sock --port=3306[root@snails mysql-5.7.13]# netstat -tunpl | grep 3306tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      24776/mysqld

Set the Database root User Password

Like Oracle Database, MySQL also comes with a default root user (this is completely unrelated to the root user on your current Linux host). We will initialize the root user’s password after configuring MySQL’s security settings. During the configuration process, simply enter y to all prompts. Note that in MySQL 5.7.13, the user password policy is divided into three levels: LOW, MEDIUM, and STRONG. The MEDIUM level is recommended!

[root@snails mysql-5.7.13]# mysql_secure_installation

Common Operations

Share MySQL Dynamic Link Libraries with the System Linker

Usually, MySQL will be called by services like PHP, so we need to add the compiled lib library files of MySQL to the current Linux host’s linker configuration /etc/ld.so.conf.d/
so that the MySQL service can be used by other services.

 [root@snails mysql-5.7.13]# ldconfig |grep mysql[root@snails mysql-5.7.13]# echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf[root@snails mysql-5.7.13]# ldconfig[root@snails mysql-5.7.13]# ldconfig -v |grep mysqlldconfig: Cannot stat /libx32: No such file or directoryldconfig: Multiple paths given“/usr/lib”ldconfig: Multiple paths given“/usr/lib64”ldconfig: Cannot stat /usr/libx32: No such file or directory/usr/lib64/mysql:    libmysqlclient.so.18 -> libmysqlclient.so.18.0.0/usr/local/mysql/lib:    libmysqlclient.so.20 -> libmysqlclient.so.20.3.0

Create Other MySQL Database Users

[root@snails mysql-5.7.13]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 4Server version: 5.7.13-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql>

mysql>CREATE DATABASE `tonnydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || tonnydb            |+--------------------+5 rows in set (0.00 sec)mysql> grant all privileges on tonnydb.* to 'tonny@%' identified by 'Hi.Tonny@888';Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql> exitBye