ubuntu源码安装mysql5.7_Ubuntu18.04编译安装mysql5.7.23

本博客为源码安装mysql5.7.23案例全过程

环境ubuntu18.04

安装规划：

配置文件：/etc/my.cnf

数据目录:/r2/mysqldata

安装目录：/usr/local/mysql

1.下载mysql-boost

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