编译需要耗时,且对系统的依赖更为复杂,更容易出错, 如果没有必要,可以参考使用 二进制安装MySQL5.7

安装编译依赖

1
sudo apt-get install -y build-essential cmake libaio-dev libncurses5-dev pkg-config
1
sudo yum -y install zlib-devel openssl-devel libaio-devel ncurses-devel cmake gcc-c++

编译

设置安装路径

1
2
export BASE_DIR="/usr/local/mysql"
export DATA_DIR="/data/mysql"

准备工作

1
2
3
4
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.38.tar.gz
tar xf mysql-boost-5.7.38.tar.gz && cd mysql-5.7.38
[[ -d build ]] && rm -r build
mkdir -p build && cd build

开始编译

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
cmake .. \
-DBUILD_CONFIG=mysql_release \
-DFEATURE_SET="community" \
-DCMAKE_INSTALL_PREFIX="${BASE_DIR}" \
-DWITH_EMBEDDED_SERVER=off \
-DINSTALL_SBINDIR=bin \
-DINSTALL_SCRIPTDIR=bin \
-DINSTALL_LIBDIR=lib/mysql \
-DINSTALL_PLUGINDIR=lib/plugin \
-DINSTALL_INCLUDEDIR=include \
-DINSTALL_INFODIR=share/info \
-DINSTALL_MANDIR=share/man \
-DINSTALL_MYSQLSHAREDIR=share/mysql \
-DINSTALL_SUPPORTFILESDIR=share/mysql \
-DINSTALL_MYSQLTESTDIR=mysql-test \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_LIBEVENT=bundled \
-DWITH_SSL=bundled \
-DWITH_ZLIB=bundled \
-DWITH_INNODB_MEMCACHED=on \
-DWITH_BOOST=../boost \
-DWITH_UNIT_TESTS=on \
-DWITH_DEBUG=off

make -j4 && make install
# 根据cpu核心数设置,直接make也行

收尾

创建目录和用户

1
2
3
4
5
mkdir -p ${DATA_DIR}/{binlog,data,logs,redolog,relaylog,tmp,undolog}
mkdir -p ${BASE_DIR}/etc
groupadd -g 27 -o -r mysql
useradd -M -g mysql -o -r -d /dev/null -s /sbin/nologin -u 27 mysql
chown -R mysql.mysql ${DATA_DIR}

创建配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
cat > ${BASE_DIR}/etc/my.cnf <<-EOF
## MySQL 5.7 Configuration File

[mysqld]

## General
user = mysql
bind_address = 0.0.0.0
port = 3306
basedir = ${BASE_DIR}/
datadir = ${DATA_DIR}/data
tmpdir = ${DATA_DIR}/tmp
socket = ${DATA_DIR}/logs/mysql.sock
pid-file = ${DATA_DIR}/logs/mysqld.pid

character_set_server = utf8
sql_mode = NO_ENGINE_SUBSTITUTION # default "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
transaction_isolation = READ-COMMITTED # default REPEATABLE-READ
explicit_defaults_for_timestamp = ON # default OFF
secure_file_priv =

skip-symbolic-links
skip_name_resolve = ON
skip_external_locking = ON

performance_schema = ON # default ON
performance-schema-instrument = 'memory/%=ON'
autocommit = ON # default ON
#event_scheduler = ON # default OFF
#lower_case_table_names = 0 # default 0
show_compatibility_56 = ON # >= 5.7.8 default OFF

## ssl
#ssl_ca = ${DATA_DIR}/ssl/ca.pem
#ssl_cert = ${DATA_DIR}/ssl/server-cert.pem
#ssl_key = ${DATA_DIR}/ssl/server-key.pem

## Networking
back_log = 1000
max_connections = 1100
max_user_connections = 1000
max_connect_errors = 1000000
interactive_timeout = 300
wait_timeout = 300
connect_timeout = 10
net_buffer_length = 1M
max_allowed_packet = 256M

## Cache
thread_cache_size = 192 # since 5.6.8 default -1, autosized ( max_connections / 100 ) + 8
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 8
query_cache_type = 0
query_cache_size = 0
#query_cache_size = 32M
#query_cache_limit = 1M
#query_cache_min_res_unit = 2K

## Per_thread Buffers
sort_buffer_size = 32M # default 256K
read_buffer_size = 16M # default 256K
read_rnd_buffer_size = 32M # default 256K
join_buffer_size = 128M # default 256K
bulk_insert_buffer_size = 64M
thread_stack = 256K # default 192K

## Temp Tables
tmp_table_size = 512M
max_heap_table_size = 512M

## Sort
max_length_for_sort_data = 2048 # default 1024
eq_range_index_dive_limit = 200 # default 10

### Storage Engines
default_storage_engine = InnoDB

## InnoDB
innodb_file_per_table = ON
innodb_file_format_check = ON
innodb_checksum_algorithm = crc32
innodb_page_size = 16k # default 16k
innodb_buffer_pool_size = 2G # default 1G, = innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size * n
innodb_buffer_pool_instances = 8 # since 5.6.6 if innodb_buffer_pool_size < 1G default 1 else 8
innodb_buffer_pool_chunk_size = 128M # defautl 128M
innodb_data_file_path = ibdata1:100M:autoextend
innodb_temp_data_file_path = ../tmp/ibtmp1:12M:autoextend
innodb_log_group_home_dir = ${DATA_DIR}/redolog/
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_rollback_segments = 128 # defautl 128
innodb_undo_directory = ${DATA_DIR}/undolog/
innodb_undo_tablespaces = 4 # from 5.7.21 deprecated
innodb_open_files = 4000
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_timeout = 1 # defautl 1, when innodb_flush_log_at_trx_commit = 0 or 2
innodb_purge_threads = 4
innodb_print_all_deadlocks = ON
innodb_max_dirty_pages_pct = 70
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT
innodb_old_blocks_time = 1000 # since 5.6.6 default 1000
innodb_io_capacity = 600 # default 200
innodb_io_capacity_max = 2000 # default 2000
innodb_lru_scan_depth = 1024 # default 1024
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_filename = ib_buffer_pool # default ib_buffer_pool
innodb_sort_buffer_size = 64M # default 1M , 64K - 64M , for change index

innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 16
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128

## Gtid
#gtid_mode = ON
#enforce_gtid_consistency = ON
#binlog_gtid_simple_recovery = 1

## Replication
server_id = 101
log_bin = ${DATA_DIR}/binlog/mysql_bin
expire_logs_days = 7
binlog_format = ROW
binlog_row_image = noblob # default full
#innodb_autoinc_lock_mode = 2 # default 1
binlog_rows_query_log_events = 1
max_binlog_size = 500M
binlog_cache_size = 1M
sync_binlog = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
skip-slave-start = 1
relay_log = ${DATA_DIR}/relaylog/relay_log
max_relay_log_size = 500M # default 0, use max_binlog_size
log_slave_updates = ON # default OFF
#read_only = ON
#super_read_only = ON
#relay_log_purge = 1
#slave_net_timeout = 60
#replicate_wild_do_table = mysql.%
#replicate_wild_ignore_table = test.%
#auto_increment_offset = 1
#auto_increment_increment = 2
#plugin_dir = ${DATA_DIR}/lib/plugin
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#rpl_semi_sync_master_enabled = ON
#rpl_semi_sync_slave_enabled = ON
#rpl_semi_sync_master_timeout = 1000
slave_transaction_retries = 128

## MTS
relay_log_recovery = 1
#slave_parallel_type = LOGICAL_CLOCK
#slave_parallel_workers = 16
#slave_preserve_commit_order = 1

## Logging
log_output = FILE
slow_query_log = ON
slow_query_log_file = ${DATA_DIR}/logs/slow_mysqld.log
log_queries_not_using_indexes = OFF # default OFF
log_throttle_queries_not_using_indexes = 10 # default 0
min_examined_row_limit = 0 # default 0
log_slow_admin_statements = ON
log_slow_slave_statements = ON
long_query_time = 1
#log-short-format = 0
log_error = ${DATA_DIR}/logs/error_mysqld.log
general_log = OFF
general_log_file = ${DATA_DIR}/logs/general_mysqld.log
log_timestamps = system

## Index
ft_min_word_len = 4

[mysqld_safe]
open_files_limit = 65535

[mysql]
no_auto_rehash
prompt = "MySQL [\\d] > "

[mysqldump]
quick
max_allowed_packet = 256M

[mysqlhotcopy]
interactive_timeout

[client]
socket = ${DATA_DIR}/logs/mysql.sock

EOF

创建软链接

my.cnf 文件客户端和服务端都需要使用的。

1
sudo ln -s ${BASE_DIR}/etc/my.cnf /etc/my.cnf

创建 service 文件

1
2
sudo cp ${BASE_DIR}/share/mysql.service /etc/init.d/
sudo systemctl daemon-reload

初始化数据库

1
2
${BASE_DIR}/bin/mysqld --initialize-insecure      # 空密码, 可以通过mysql命令登录
${BASE_DIR}/bin/mysqld --initialize # 或者使用随机密码,error_log日志会打印临时密码

优化工作

添加环境变量

1
2
3
4
5
6
7
if [ "$( grep "${BASE_DIR}/bin" /etc/profile | wc -l )" -eq 0 ]; then
echo "export PATH=\$PATH:${BASE_DIR}/bin" >> /etc/profile
else
echo "PATH变量已添加"
fi

source /etc/profile

修改 root 密码

1
2
3
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Vb6CAEJqqtcmKndiAkEl';
CREATE USER 'root'@'%' IDENTIFIED BY 'Vb6CAEJqqtcmKndiAkEl';
Grant all privileges on *.* to 'root'@'%' with grant option;

查看编译参数

1
cat /user/local/mysql/bin/mysqlbug |grep configure