MySQL8.0.13源码编译安装
1.下载源码
链接: https://pan.baidu.com/s/1DGP_HZ9q-ry3SSKc2Sl4Mg 提取码: a1tp
http://mysql.mirror.kangaroot.net/Downloads/MySQL-8.0/mysql-8.0.13.tar.gz
2.安装依赖库
apt-get install -y unzip
apt-get install -y cmake
apt-get install -y ncurses-dev
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNODB_MEMCACHED=ON \
-DWITH_SSL=system \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DCOMPILATION_COMMENT="ppshuai edition" \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/tmp \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DSYSCONFDIR=/usr/local/mysql/data
make -j 4
make install
groupadd mysql
useradd -g mysql mysql
# 设置用户操作系统资源的限制
vi /etc/security/limits.conf
==================================
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65536
mysql hard nofile 65536
==================================
# 创建MySQL数据目录及赋予相应权限
mkdir -p /usr/local/mysql/data/{data,tmp,binlog,innodb_ts,innodb_log,backup,scripts}
chown -R mysql:mysql /usr/local/mysql/data
# 配置系统服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld -rf
# 配置my.cnf文件
vi /etc/my.cnf
====================================================
[client]
port = 3306
socket = /tmp/mysql.sock
# The MySQL server
[mysqld]
port = 3306
user = mysql
socket = /tmp/mysql.sock
pid-file = /usr/local/mysql/data/data/mysql.pid
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data/data
tmpdir = /usr/local/mysql/data/tmp
open_files_limit = 60000
explicit_defaults_for_timestamp
server-id = 1203306
lower_case_table_names = 1
character-set-server = utf8
federated
#sql_mode=STRICT_TRANS_TABLES
max_connections = 1000
max_connect_errors = 100000
interactive_timeout = 86400
wait_timeout = 86400
sync_binlog=0
back_log=100
default-storage-engine = InnoDB
log_slave_updates = 1
#*********** Logs related settings ***********
log-bin =/usr/local/mysql/data/binlog/mysql-bin
binlog_format= mixed
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
long_query_time = 1
log_output = FILE
log-error = /usr/local/mysql/data/mysql-error.log
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/slow_statement.log
#log_queries_not_using_indexes
general_log = 0
general_log_file = /usr/local/mysql/data/general_statement.log
#expire-logs-days = 14
binlog_expire_logs_seconds = 1728000
relay-log = /usr/local/mysql/data/binlog/relay-bin
relay-log-index = /usr/local/mysql/data/binlog/relay-bin.index
#****** MySQL Replication New Feature*********
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery
#*********** INNODB Specific options ***********
innodb_buffer_pool_size = 2048M
transaction-isolation=REPEATABLE-READ
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_data_home_dir = /usr/local/mysql/data/innodb_ts
innodb_data_file_path = ibdata1:2048M:autoextend
innodb_thread_concurrency = 8
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /usr/local/mysql/data/innodb_log
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 70
innodb_flush_method=O_DIRECT
# default config is caching_sha2_password after mysql 8.0.x
default_authentication_plugin=mysql_native_password
[mysql]
no-auto-rehash
default-character-set=utf8
prompt = (\u@\h) [\d]>\_
====================================================
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
cat /usr/local/mysql/data/mysql-error.log
====================================================
2019-01-19T19:07:14.435710Z 5 [Note] [MY-010454] [Server] A temporary
password is generated for root@localhost: #Edqagek41ca
====================================================
/usr/local/mysql/bin/mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;
常见问题：
如果使用navicat mysql工具出现
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决办法：
查找 my.cnf 文件： find / -name my.cnf 或者 whereis my.cnf
获取 sql_mode字段内容： SELECT @@sql_mode;
查询结果：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
去掉 sql_mode字段中的 ONLY_FULL_GROUP_BY
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
编辑 my.cnf：
vim /etc/my.cnf
将上一步骤的 sql_mode 字段内容添加至 my.cnf的[mysqld]节点下保存。
然后重启：
service mysqld restart
sql_mode 字段参考：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
