Compile and install MySQL 5.7.13 on the CentOS7 system

MySQL 5.7 main features

1. Better performance

There are better optimizations for multi-core CPU, SSD, and lock. 100W QPS per second is no longer the pursuit of MySQL, but whether the next version of 200W QPS is of more concern to users.

2. Better InnoDB storage engine

3. More robust replication

Replication provides a completely data-free solution that traditional financial customers can choose to use. In addition, the GTID online smooth upgrade is also possible.

4. Better optimizer

The meaning of the optimizer code refactoring will be greatly improved in this release and beyond, and Oracle officials are addressing the biggest problem before MySQL.

5. Native JSON type support

6. Better geographic information service support

InnoDB natively supports location types, GeoJSON, GeoHash features
7. Add sys library

In the future this will be DBA's most visited library, MySQL 5.7, which has been added to OneinStack as a database option

Installation preparation

Install dependency package

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

Download the source package

[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

Create a new MySQL user and user group

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

precompiled

[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 the installation

[root@snails mysql-5.7.13]# make -j `grep processor /proc/cpuinfo | wc -l`
# Compilation consumes system resources and may fail with small memory make install
[root@snails mysql-5.7.13]# make install

Set up the startup script and boot up automatically

[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 mysqld
mysqld.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld on

The configuration file

/etc/ my.cnf, for reference only

[root@snails mysql-5.7.13]# cat > /etc/my.cnf << EOF
[client]
port = 3306
socket = /dev/shm/mysql.sock
[mysqld]
port = 3306
socket = /dev/shm/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/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_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
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 = 64M
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 = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
EOF

Add environment variables for mysql

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

Initialize the database

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

Note:

The previous version of MySQL, mysql_install_db, was under mysql_basedir/script MySQL 5.7 is directly placed in the mysql_install_db/bin directory. "Wok 111en" has been deprecated to generate a random password (~/.mysql_secret). "WSD" does not generate a password Data files cannot be found in the "WSD 116en" directory

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 1 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...

7 month  18 11:15:32 snails systemd[1]: Starting LSB: start and stop MySQL...
7 month  18 11:15:35 snails mysqld[23927]: Starting MySQL..[ OK ]
7 month  18 11:15:35 snails systemd[1]: Started LSB: start and stop MySQL.

View the MySQL service processes and ports

[root@snails mysql-5.7.13]# ps -ef | grep mysql
root  23940  1 0 11:15 ?  00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid
mysql 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 3306
tcp  0  0 0.0.0.0:3306   0.0.0.0:*    LISTEN  24776/mysqld

Set the database root user password

Like MySQL and Oracle database 1, the database also comes with one root user by default (this is completely different from root user on the current Linux host). After setting up the security configuration of MySQL database, we initialize the password of root user. In the preparation process, 1 channel input y is enough. In version 5.7.13 of MySQL5.7.13, user password policies are divided into three types: low-level LOW, medium-level MEDIUM and super-strong STRONG. Medium MEDIUM is recommended!

[root@snails mysql-5.7.13]# mysql_secure_installation

Common operations

Share the dynamic link library of the MySQL database to the system link library

1 a MySQL database will be similar to the PHP services such as call, so we need to compile MySQL after lib library file is added to the current Linux host link library/etc/ld so. conf. d /, such MySQL services can be invoked by other services.

[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

0

Create other MySQL database users

[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

1

[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

2