Linux 下源码编译安装 MySQL 数据库

1.MySQL 编译源码所需的工具和库,根据自己需要进行安装

yum -y install gcc gcc-c++ make autoconf libtool-ltdl-devel gd-devel freetype-devel libxml2-devel libjpeg-devel libpng-devel openssl-devel curl-devel bison patch unzip libmcrypt-devel libmhash-devel ncurses-devel bzip2 flex libaio-devel

2. 新增 MySQL 用户组

groupadd mysql

3. 创建用户 设置不允许登陆系统 -M不创建家目录,/dev/null 首先表示标准输出重定向到空设备文件

useradd -M mysql -s /sbin/nologin -d /dev/null -g mysql

4.新建MySQL安装目录,并设置权限

mkdir -p /usr/local/mysql          # mysql 安装目录
mkdir -p /data/logs/mysqllogs      # mysql 日志目录
mkdir -p /data/binlog              # mysql 二进值目录
mkdir -p /data/mysql/mysql         # 数据库安装目录
mkdir -p /data/temp/mysql         # 缓存目录
chown mysql.mysql -R /data/temp/mysql
chown mysql.mysql -R /data/binlog
chown mysql.mysql -R /data/mysql/mysql

5.编译安装 jemalloc 内存管理工具

wget https://github.com/jemalloc/jemalloc/releases/download/5.2.1/jemalloc-5.2.1.tar.bz2
tar xjf jemalloc-5.2.1.tar.bz2
cd jemalloc-5.2.1
./configure
make -j 2 && make install

6.注意: 64位系统 用这个

ln -s /usr/local/lib/libjemalloc.so.2 /usr/lib64/libjemalloc.so.1

7.注意: 32位系统 用这个

ln -s /usr/local/lib/libjemalloc.so.2 /usr/lib/libjemalloc.so.1

8.运行程序时可能会报找不到库 将jemalloc的库加载到系统中

[ -z "`grep /usr/local/lib /etc/ld.so.conf.d/*.conf`" ] && echo '/usr/local/lib' > /etc/ld.so.conf.d/local.conf
ldconfig

9.下载 Boost MySQL依赖库

wget https://dl.bintray.com/boostorg/release/1.73.0/source/boost_1_70_0.tar.gz

10.下载 MySQL 源码包并解压

官网下载网址 https://dev.mysql.com/downloads/mysql

wget https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.7/mysql-5.7.28-el7-x86_64.tar.gz
tar zxf mysql-5.7.28-el7-x86_64.tar.gz
tar zxf boost_1_70_0.tar.gz
pushd mysql-5.7.28-el7-x86_64

13.开始编译安装 MySQL5.7

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql/mysql \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=../boost_1_70_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 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DTRACE=0 \
-DENABLED_LOCAL_INFILE=1 \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DEXTRA_CHARSETS=all \
-DCMAKE_EXE_LINKER_FLAGS='-ljemalloc'          #这个是之前安装过的 jemalloc 优化内存
make -j 2 && make install              #等半小时后或者更长,这个和电脑配置有关

15.建立开机启动配置文件

/bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

16.修改启动文件安装路径

sed -i "s@^basedir=.*@basedir=/usr/local/mysql@" /etc/init.d/mysqld
sed -i "s@^datadir=.*@datadir=/data/mysql/mysql@" /etc/init.d/mysqld

17. 修改数据库数据存储路径

sed -i "s@^datadir=.*@datadir=/data/mysql/mysql@" /etc/init.d/mysqld

18. 设置启动文件权限

chmod +x /etc/init.d/mysqld

19. 添加开机启动

chkconfig --add mysqld
chkconfig mysqld on

20.生成配置文件 my.cnf

注意:下面配置可能有错,,小白这里只能提供您参考一下~

cat > /etc/my.cnf << EOF
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysql]
prompt="MySQL [\\d]> "
no-auto-rehash

[mysqld]
port = 3306
user = mysql
socket = /tmp/mysql.sock

basedir = /usr/local/mysql
datadir = /data/mysql/mysql
pid-file = /data/mysql/temp/mysql.pid
bind-address = 0.0.0.0
tmpdir = /data/mysql/temp
#slave-load-tmpdir = /data/mysql/temp
#secure-file-priv = /usr/local/mysql/uploads
log_error = /data/logs/mysqllog/mysql_error.log

#开启查询缓存
slow_query_log = 1
long_query_time = 1
#log-queries-not-using-indexes = 1
#log-slow-admin-statements = 1
#log-slow-slave-statements = 1
slow_query_log_file = /data/logs/mysqllog/mysql_slow.log

init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4

log-bin-trust-function-creators = 1

symbolic-links = 0

##^^^^^^^^^^^^^^^^^^^^^^主从同步相关设置开始^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^##
server-id = 10
binlog_cache_size = 4M
max_binlog_size = 1G
max-binlog-cache-size = 2G
max-relay-log-size = 1G
log_bin = /data/mysql/binlog/MySQL-Bin
binlog_format = mixed
log_bin_index = /data/mysql/binlog/Binlog.index
expire_logs_days = 30

##^^^^^^^^^^^^^^^^^^^^^^主从同步相关设置结束^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^##

#skip-locking
#禁止域名解析 只能IP访问
skip-name-resolve
#禁止外网
#skip-networking
#禁止ssl
skip-ssl
#禁止锁定
skip-external-locking
#数据库用户权限,如果不加这个选项所有的用户都可以 show databases
skip_show_database
skip_symbolic_links
#MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用
back_log = 300

max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535
#表缓存数
table_open_cache = 128
#单数据包
max_allowed_packet = 500M
binlog_cache_size = 1M
#最大表量
max_heap_table_size = 32M
tmp_table_size = 16M

#预读缓冲
read_buffer_size = 2M
#次缓冲
read_rnd_buffer_size = 8M
#排序缓冲
sort_buffer_size = 8M
#连续缓冲
join_buffer_size = 8M
#索引缓冲 比较理想的设置:Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
#指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。16M适用于 512MB内存,对于内存在4GB左右的服务器该参数可设置为256M,依此类推即可。注意:该参数值设置的过大反而会是服务器整体效率降低!
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

performance_schema = 0
explicit_defaults_for_timestamp

#必须要修改才能使用mycat
#lower_case_table_names = 1

flush_time = 0

group_concat_max_len=2048000
table_definition_cache = 1400
query_cache_min_res_unit = 1MB

#default-storage-engine = MyISAM
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
#参数的配置在物理内存的70%-80%之间
innodb_buffer_pool_size = 64M
#InnoDB 使用后台线程处理数据页上写 I/O(输入)请求的数量
innodb_write_io_threads = 4
#InnoDB 使用后台线程处理数据页上读 I/O(输出)请求的数量
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
#日志缓冲池,控制在8M-16M即可
innodb_log_buffer_size = 2M
#InnoDB 日志文件的大小
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
#控制了 Dirty Page 在 Buffer Pool 中所占的比率
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
#缓冲区刷新到磁盘时,刷新脏页数量,ssd磁盘,或做了raid后,可将此值设置较大
#innodb_io_capacity = 500
#innodb_adaptive_flushing = OFF

#*** MyISAM Specific options
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#导入大的sql文件的方法
interactive_timeout = 28800
wait_timeout = 28800
connect_timeout = 20
#thread_concurrency = 8

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
max_allowed_packet = 500M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

21. 建立初始化数据库,64位系统必须指定目录

/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql

22. 设置 my.cnf 权限 为 600 防止黑客攻击

chown mysql.mysql /etc/my.cnf
chmod 600 /etc/my.cnf
chown mysql.mysql -R /data/mysql/mysql

23.删除默认测试数据库文件

rm -rf "/usr/local/mysql/data"
rm -rf "/usr/local/mysql/mysql-test"

24. 添加 MySQL 到系统库中

"/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
ldconfig    #刷新动态库配置
reboot      # 最好重启一下系统

25. 设置 MySQL 加入PATH路径环境变量

vim /etc/profile                    # 修改export PATH= 在最下面添加
export PATH=/usr/local/mysql/bin:$PATH            # 如果有多个路径请用 英文分号(;) 隔开
source /etc/profile                 # 使/etc/profile里的配置立即生效

或者直接执行下面命令

[ -z "$(grep ^'export PATH=' /etc/profile)" ] && echo "export PATH=/usr/local/mysql/bin:\$PATH" >> /etc/profile
[ -n "$(grep ^'export PATH=' /etc/profile)" -a -z "$(grep /usr/local/mysql /etc/profile)" ] && sed -i "s@^export PATH=\(.*\)@export PATH=/usr/local/mysql/bin:\1@" /etc/profile

source /etc/profile

26. 启动数据库服务

service mysqld start

27. 删除默认 test 数据库

#删除默认 test 数据库
mysql -uroot -e "drop database test;"

28. 安全设置

mysql -uroot -e "delete from mysql.proxies_priv where Host!='localhost';
mysql -uroot -e "delete from mysql.db where User='';

29. 修改 root 用户名为 fooher_login

mysql -uroot -e "update mysql.user set user='fooher_login' where User='root';"
# 修改 127.0.0.1 密码
mysql -uroot -e "grant all privileges on *.* to fooher_login @'127.0.0.1' identified by "www.fooher.com" with grant option;"

# 修改 localhost 密码
mysql -uroot -e "grant all privileges on *.* to fooher_login@'localhost' identified by "www.fooher.com" with grant option;"

28. 创建用户

MySQL 数据库用户名
dbrootname='fooher_user'
MySQL 数据库密码
dbrootpwd='8%jMrwqwSZ6u@4'
MySQL 数据库名
db_name='data_db_fooher'

mysql -uroot -e "CREATE USER ${dbrootname}@'127.0.0.1' IDENTIFIED by \"${dbrootpwd}\";
mysql -uroot -e "grant all privileges on *.* to ${dbrootname}@'%' identified by \"${dbrootpwd}\" with grant option;

29. 创建数据库

mysql -uroot -e "create database IF NOT EXISTS ${db_name} default character set utf8mb4 collate utf8mb4_unicode_ci;

31. 授权 fooher_user 用户拥有 data_db_fooher 数据库的所有权限

mysql -uroot -e "grant all privileges on ${db_name}.* to '${dbrootname}'@'127.0.0.1' identified by \"${dbrootpwd}\" with grant option;

32. 刷新数据库

mysql -u"${fooher_login}" -p"${www.fooher.com}" -e "flush privileges;

33. 还原数据库

mysql -u"${fooher_login}" -p"${www.fooher.com}" -hlocalhost ${db_name} < fooherdb.sql