Ubuntu18.04编译安装mysql5.7.23
本博客为源码安装mysql5.7.23案例全过程
环境ubuntu18.04
安装规划：
配置文件：/etc/my.cnf
数据目录:/r2/mysqldata
安装目录：/usr/local/mysql
1.下载mysql-boost
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.23.tar.gz
2.安装工具
sudo apt-get install cmake -y
apt-get install gcc g++ -y
apt-get install git -y
apt-get install libncurses5 libncurses5-dev -y
sudo apt-get install -y build-essential
sudo apt-get install flex bison -y
sudo apt-get install mpi-default-dev libicu-dev python-dev libbz2-dev -y
3.下载并安装boost
3.1下载boost部署包
sudo wget https://nchc.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz
3.2解压
tar -zxvf boost_1_59_0.tar.gz
3.3编译
cd /r2/soft/boost_1_59_0 && ./bootstrap.sh
3.4b2
cd /r2/soft/boost_1_59_0 && ./b2 -a -sHAVE_ICU=1 # the parameter means that it support icu or unicode #执行时间稍微长
3.5安装
cd /r2/soft/boost_1_59_0 && sudo ./b2 install
这时就把包给装到/usr/local/include/boost目录下了
4.创建目录
cd /r2 && mkdir mysqldata
cd /usr/local && mkdir mysql
5.预编译msyql
cd /r2/soft/mysql-5.7.23 && sudo cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/r2/mysqldata \
-DSYSCONFDIR=/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/r2/mysqldata/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLE_DOWNLOADS=1
注意：如果出现错误，再次编译的时候需要删除CMakeCache.txt文件
6.编译并安装
6.1编译
cd /r2/soft/mysql-5.7.23 && make -j4
6.2安装
cd /r2/soft/mysql-5.7.23 && make install
7.新建mysql组和用户并授权
groupadd mysql
useradd -g mysql mysql
mkdir /usr/local/mysql/data
chown -R mysql /usr/local/mysql
chgrp -R mysql /usr/local/mysql
chown -R mysql /r2/mysqldata
chgrp -R mysql /r2/mysqldata
8.添加mysql的环境变量
vim /etc/profile
PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
export PATH
让其生效source /etc/profile
9.初始化
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/r2/mysqldata
这里可以看到初始化密码2018-10-04T09:36:02.519395Z 1 [Note] A temporary password is generated for root@localhost: p&Lta1fdsD,j
这个就是初始化密码：p&Lta1fdsD,j
10.优化便捷化操作
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
update-rc.d mysqld defaults
11.启动mysql
service mysqld start
12.登陆并创建账号
mysql -uroot -p 之后输入密码p&Lta1fdsD,j
set password=password("test123456");
grant insert,update,select,create,index,trigger,alter on *.* to maef@'%' identified by 'test123456';
grant insert,update,select,create,index,trigger,alter on *.* to maef@'localhost' identified by 'test123456';
flush privileges;
13.安装客户端
apt install mysql-client-core-5.7 -y
apt install mariadb-client-core-10.1 -y
注意：这时默认的配置什么都没有，位置/etc/mysql/my.cnf
14.编写脚本
启动脚本
启动脚本start.sh
start.sh脚本内容：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/r2/mysqldata &```
停止脚本：
```#!/bin/bash
/usr/local/mysql/bin/mysqladmin -uroot -p shutdown &```
#15.优化配置
执行停掉数据库之后vim /etc/my.cnf之后执行启动./start.sh即可正常使用
#16.my.cnf附录如下：
本配置文件主要适用于MySQL 5.7.23版本
******************************************
[client]
port = 3306
default-character-set = utf8
socket = /r2/mysqldata/mysql.sock
=======================================================================
MySQL客户端配置
=======================================================================
[mysql]
prompt="(\u@\h) \\R:\\m:\\s [\d]> "
no-auto-rehash
default-character-set = utf8
=======================================================================
MySQL服务器全局配置
=======================================================================
[mysqld]
user = mysql
port = 3306
server-id = 1
basedir = /usr/local/mysql
tmpdir = /r2/mysqldata
datadir = /r2/mysqldata
socket = /r2/mysqldata/mysql.sock
log-error=/r2/mysqldata/error.log
wait_timeout = 31536000
sql_mode = #sql_mode 配置为空值
skip_name_resolve #开启dns解析
lower_case_table_names = 0
character-set-server = utf8
collation-server = utf8_unicode_ci
log_timestamps = SYSTEM
init_connect= 'SET NAMES utf8'
max_allowed_packet = 128M
---------------------------- 性能参数 --------------------------------
open_files_limit = 10240
max_connections = 10000
max_user_connections=9990
max_connect_errors = 100000
table_open_cache = 1024
thread_cache_size = 64
max_heap_table_size = 32M
query_cache_type = 0
----------------global cache------------------------
key_buffer_size = 64M
query_cache_size = 0
tmp_table_size = 32M #内存临时表
binlog_cache_size = 4M #二进制日志缓冲
-----------------session cache--------------------------
sort_buffer_size = 8M #排序缓冲
join_buffer_size = 4M #表连接缓冲
read_buffer_size = 8M #顺序读缓冲
read_rnd_buffer_size = 8M #随机读缓冲
thread_stack = 256KB #线程的堆栈的大小
------------------------------ binlog设置 ------------------------------
binlog_format = MIXED
log_bin = /r2/mysqldata/binlog
max_binlog_size = 1G
expire_logs_days = 3 #binlog比较占空间，注意磁盘空间
sync_binlog = 0 #重要参数必须修改为0
--------------------------------- 复制设置 --------------------------
log_slave_updates = 1
-------GTID 配置--------
gtid_mode=ON
enforce-gtid-consistency=true
------------------------- innodb--------------------
default_storage_engine = InnoDB
innodb_buffer_pool_size = 128M #系统内存50%
innodb_open_files = 5120
innodb_flush_log_at_trx_commit = 2 #线上服务器必须配置为2
innodb_file_per_table = 1
innodb_lock_wait_timeout = 5
innodb_io_capacity = 200 #根据您的服务器IOPS能力适当调整innodb_io_capacity，配SSD盘可调整到 10000 - 20000
innodb_io_capacity_max = 20000
innodb_flush_method = O_DIRECT
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_large_prefix = 0
innodb_thread_concurrency = 64
innodb_strict_mode = OFF
innodb_sort_buffer_size = 4194304
---------------------- log 设置 -------------------------
log_error = /r2/mysqldata/error.log
slow_query_log = 1
long_query_time = 10
slow_query_log_file = /r2/mysqldata/slow.log
=======================================================================
MySQL mysqldump配置
=======================================================================
[mysqldump]
quick
max_allowed_packet = 128M
=======================================================================
MySQL mysqld_safe配置
=======================================================================
[mysqld_safe]
log_error = /r2/mysqldata/error.log
pid_file = /r2/mysqldata/mysqldb.pid