mysql小白系列_02 mysql源码安装标准化

问题：

2.如何标准化配置多实例?（例如：一台物理主机上部署3306与3307两个实例）

3.详细描述MySQL编译安装的过程（截图安装步骤）

不同作用的文件放到不同的磁盘、目录之上

数据文件
- 随机读写，可以放在高速盘上，如ssd、raid10
- 追求响应时间
日志文件
- 顺序写，可以放在raid5上
- 如果用rsync同步到远端时，可以同步整个日记目录
- 如果跟数据文件放在一起，数据文件会被一起同步过去
- 追求吞吐量

2.如何标准化配置多实例?（例如：一台物理主机上部署3306与3307两个实例）

使用多个不同配置文件
优点：配置简单
缺点：不方便管理

通过官方mysqld_mutil使用单独的配置文件
优点：方便集中管理
缺点：多个实例配置均在一个文件，不太方便

问题：

多个路径是否需要编译安装多个mysql软件？还是共用一个mysql软件，然后根据不同目录存放不同数据库实例的data/log？
实际生产中是使用一个my.cnf文件配合mysqld_mutil工具管理实例？还是每个实例一个my.cnf文件？

编译安装单实例

安装必要工具
下面的安装源用5.6的

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install -y mysql57-community-release-el7-11.noarch.rpm
https://dev.mysql.com/doc/refman/5.5/en/source-installation.html
yum install -y  cmake gcc gcc-c++ ncurses-devel bison zlib libxml openssl openssl-devel ncurses

关闭防火墙
不关闭也是可以的，打开对应的服务端口

service iptables stauts
service iptables stop
chkconfig iptables off

EL7
7以后默认是firewall防火墙

firewall-cmd --state
systemctl list-unit-files|grep firewalld.service
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
或者
firewall-cmd --set-default-zone=trusted

关闭SELINUX

getenforce
setenforce 0
vi /etc/selinux/config
permissive

配置sysctl.conf
检查操作系统上是否安装了MySQL

rpm -qa|grep -i mysql

添加用户和组

groupadd mysql
useradd mysql -g mysql -s /bin/false

配MySQL环境变量
创建目录及授权

mkdir -p /data/my3306/{data,log/binlog,log/iblog,run,tmp}
chown -R mysql:mysql /data
vi ~/.bash_profile
PATH=$PATH:$HOME/bin:/data/my3306/bin
export PATH

解压mysql5.6

wget https://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.39-1.el7.src.rpm
rpm2cpio MySQL-5.6.39-1.el7.src.rpm | cpio -div
tar -zxvf mysql-5.6.39.tar.gz

编译并安装

cmake \
-DCMAKE_INSTALL_PREFIX=/data/my3306 \
-DINSTALL_DATADIR=/data/my3306/data  \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_SSL=yes \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DMYSQL_UNIX_ADDR=/data/my3306/run/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DSYSCONFDIR=/etc \
-DWITH_READLINE=on

结果

CMake Warning:
  Manually-specified variables were not used by the project:

    INSTALL_DATADIR
    WITH_READLINE


-- Build files have been written to: /dvd/mysql-5.6.39

如果cmake失败，删除目录下的CMakeCache.txt文件

然后是make

[100%] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my_safe_process.dir/safe_process.cc.o
Linking CXX executable my_safe_process
[100%] Built target my_safe_process

最后是make install

-- Up-to-date: /data/my3306/sql-bench/test-transactions
-- Up-to-date: /data/my3306/sql-bench/test-wisconsin
-- Installing: /data/my3306/sql-bench/cmake_install.cmake
-- Installing: /data/my3306/sql-bench/CTestTestfile.cmake

参数含义

-DCMAKE_INSTALL_PREFIX= 指向mysql安装目录
-DINSTALL_SBINDIR=sbin 指向可执行文件目录（prefix/sbin）
-DMYSQL_DATADIR=/var/lib/mysql 指向mysql数据文件目录（/var/lib/mysql）
-DSYSCONFDIR=/etc/mysql 指向mysql配置文件目录（/etc/mysql）
-DINSTALL_PLUGINDIR=lib/mysql/plugin 指向插件目录（prefix/lib/mysql/plugin）
-DINSTALL_MANDIR=share/man 指向man文档目录（prefix/share/man）
-DINSTALL_SHAREDIR=share 指向aclocal/mysql.m4安装目录（prefix/share）
-DINSTALL_LIBDIR=lib/mysql 指向对象代码库目录（prefix/lib/mysql）
-DINSTALL_INCLUDEDIR=include/mysql 指向头文件目录（prefix/include/mysql）
-DINSTALL_INFODIR=share/info 指向info文档存放目录（prefix/share/info）
prefix官方推荐设为/usr
Storage Engine相关
类型csv,myisam,myisammrg,heap,innobase,archive,blackhole
若想启用某个引擎的支持：-DWITH_<ENGINE>_STORAGE_ENGINE=1
如：
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
若想禁用某个引擎的支持：-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1
如：
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1
Library相关
-DWITH_READLINE=1 启用readline库支持（提供可编辑的命令行）
-DWITH_SSL=system 启用ssl库支持（安全套接层）
-DWITH_ZLIB=system 启用libz库支持（zib、gzib相关）
-DWTIH_LIBWRAP=0 禁用libwrap库（实现了通用TCP包装的功能，为网络服务守护进程使用）
-DMYSQL_TCP_PORT=3306 指定TCP端口为3306
-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock 指定mysql.sock路径
-DENABLED_LOCAL_INFILE=1 启用本地数据导入支持
-DEXTRA_CHARSETS=all 启用额外的字符集类型（默认为all）
-DDEFAULT_CHARSET=utf8 指定默认的字符集为utf8
-DDEFAULT_COLLATION=utf8_general_ci 设定默认排序规则（utf8_general_ci快速/utf8_unicode_ci准确）
-DWITH_EMBEDDED_SERVER=1 编译嵌入式服务器支持
-DMYSQL_USER=mysql 指定mysql用户(默认为mysql)
-DWITH_DEBUG=0 禁用debug（默认为禁用）
-DENABLE_PROFILING=0 禁用Profiling分析（默认为开启）
-DWITH_COMMENT='string' 一个关于编译环境的描述性注释

MySQL参数配置
初始化MySQL脚本

chown -R mysql:mysql /data
cd /data/my3306
./scripts/mysql_install_db --defaults-file=/data/my3306/my.cnf --datadir=/data/my3306/data --user=mysql

FATAL ERROR: please install the following Perl modules before executing ./mysql_install_db: Data::Dumper
报错了，需要安装Perl
yum install -y perl perl-devel perl-Data-Dumper
需要在bin目录下执行，不然会报错：FATAL ERROR: Could not find ./bin/my_print_defaults

启动MySQL

cd /data/my3306/bin
./mysqld_safe --defaults-file=/data/my3306/my.cnf --user=mysql &

[root@mysql01 bin]# 180206 23:25:09 mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.

登录MySQL

[root@mysql01 bin]# netstat -ano|grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      off (0.00/0/0)
unix  2      [ ACC ]     STREAM     LISTENING     57177    /data/my3306/run/mysql.sock
[root@mysql01 bin]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.12 sec)

在上面的基础，再初始化一个实例

mkdir -p /data/my3307/{data,log/binlog,log/iblog,run,tmp}
chown -R mysql:mysql /data/my3307

2.修改my.cnf

cp -rp  /data/my3306/my.cnf /data/my3307/my.cnf
sed -i {s/3306/3307/g} /data/my3307/my.cnf

basedir=/data/my3306 参数不变

初始化数据库

cd /data/my3306/
./scripts/mysql_install_db --defaults-file=/data/my3307/my.cnf --datadir=/data/my3307/data --user=mysql

启动数据库服务

cd /data/my3306/bin
./mysqld_safe --defaults-file=/data/my3307/my.cnf --user=mysql &

查看端口信息

[root@mysql01 bin]# netstat -lnptu|grep 330
tcp6       0      0 :::3306                 :::*                    LISTEN      off (0.00/0/0)
tcp6       0      0 :::3307                 :::*                    LISTEN      off (0.00/0/0)

登录数据库

[root@mysql01 bin]# mysql -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.23 sec)

mysqld_mutil配置多个实例

mkdir -p /data/my3308/{data,log/binlog,log/iblog,run,tmp}
chown -R mysql:mysql /data/my3308

　　2. 修改my.cnf 增加

basedir=/data/my3306 参数不变

初始化数据库

cd /data/my3306
./scripts/mysql_install_db --defaults-file=/data/my3306/my.cnf --datadir=/data/my3308/data --user=mysql

启动数据库服务

cd /data/my3306/bin
mysqld_multi --defaults-extra-file=/data/my3306/my.cnf start 1,2

180207 00:19:24 mysqld_safe Logging to '/data/my3306/log/error.log'. 180207 00:19:24 mysqld_safe The file /mysqld

启动信息如下：

mysqld_multi log file version 2.16; run: Wed Feb  7 10:43:34 2018

Starting MySQL servers

180207 10:43:35 mysqld_safe Logging to '/data/my3306/log/error.log'.
180207 10:43:35 mysqld_safe Logging to '/data/my3308/log/error.log'.
180207 10:43:35 mysqld_safe Starting mysqld daemon with databases from /data/my3306/data
180207 10:43:35 mysqld_safe Starting mysqld daemon with databases from /data/my3308/data

查看端口信息

[root@mysql01 bin]# netstat -lnptu|grep 330
tcp6       0      0 :::3306                 :::*                    LISTEN      25185/mysqld        
tcp6       0      0 :::3307                 :::*                    LISTEN      26218/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      25197/mysqld

3.详细描述MySQL编译安装的过程（截图安装步骤）

选择对应版本，下载源码包

https://dev.mysql.com/downloads/mysql/

查看安装要求，必要的依赖包需要提前安装

https://dev.mysql.com/doc/refman/5.5/en/installing.html

如果CMAKE里面有额外的参数，如SSL，还需要安装openssl openssl-devel
make的时候时间会很长，具体所需时间看机器配置
实例运行用户需要对所需目录具有对应的读写权限，默认022一般足够
mysql_install_db初始化数据库时需要在basedir目录，因为要调用bin下面的my_print_defaults

$print_defaults = "$opt->{builddir}/extra/my_print_defaults";
$print_defaults = find_in_basedir($opt,"file","my_print_defaults","bin","extra");
$print_defaults='./bin/my_print_defaults';

3306和3308的配置如下:

[client]
port=3306
socket=/data/my3306/mysql.sock

[mysqld_multi]
mysqld=/data/my3306/bin/mysqld_safe
mysqladmin=/data/my3306/bin/mysqladmin
log=/data/my3306/log/mysqld_multi.log

[mysql]
pid_file=/data/my3306/run/mysqld.pid

[mysqld1]
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true

# system
basedir=/data/my3306
datadir=/data/my3306/data
max_allowed_packet=1g
max_connections=3000
max_user_connections=2800
open_files_limit=65535
pid_file=/data/my3306/run/mysqld.pid
port=3306
server_id=101
skip_name_resolve=ON
socket=/data/my3306/run/mysql.sock
tmpdir=/data/my3306/tmp

#binlog
log_bin=/data/my3306/log/binlog/binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=7
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100

#logging
log_error=/data/my3306/log/error.log
slow_query_log_file=/data/my3306/log/slow.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1

#relay
relay_log=/data/my3306/log/relaylog
relay_log_index=/data/my3306/log/relay.index
relay_log_info_file=/data/my3306/log/relay-log.info

#slave
slave_load_tmpdir=/data/my3306/tmp
slave_skip_errors=OFF


#innodb
innodb_data_home_dir=/data/my3306/log/iblog
innodb_log_group_home_dir=/data/my3306/log/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=8

#default
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 128M
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=67108864
innodb_log_file_size=1048576000
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=60
innodb_open_files=60000
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
innodb_write_io_threads=10

[mysqld2]
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true

# system
basedir=/data/my3306
datadir=/data/my3308/data
max_allowed_packet=1g
max_connections=3000
max_user_connections=2800
open_files_limit=65535
pid_file=/data/my3308/run/mysqld.pid
port=3308
server_id=101
skip_name_resolve=ON
socket=/data/my3308/run/mysql.sock
tmpdir=/data/my3308/tmp

#binlog
log_bin=/data/my3308/log/binlog/binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=7
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100

#logging
log_error=/data/my3308/log/error.log
slow_query_log_file=/data/my3308/log/slow.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1

#relay
relay_log=/data/my3308/log/relaylog
relay_log_index=/data/my3308/log/relay.index
relay_log_info_file=/data/my3308/log/relay-log.info

#slave
slave_load_tmpdir=/data/my3308/tmp
slave_skip_errors=OFF


#innodb
innodb_data_home_dir=/data/my3308/log/iblog
innodb_log_group_home_dir=/data/my3308/log/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=8

#default
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 128M
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=67108864
innodb_log_file_size=1048576000
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=60
innodb_open_files=60000
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
innodb_write_io_threads=10

my.cnf

3307的配置如下:

[client]
port=3307
socket=/data/my3307/mysql.sock

[mysql]
pid_file=/data/my3307/run/mysqld.pid

[mysqld]
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true

# system
basedir=/data/my3306
datadir=/data/my3307/data
max_allowed_packet=1g
max_connections=3000
max_user_connections=2800
open_files_limit=65535
pid_file=/data/my3307/run/mysqld.pid
port=3307
server_id=101
skip_name_resolve=ON
socket=/data/my3307/run/mysql.sock
tmpdir=/data/my3307/tmp

#binlog
log_bin=/data/my3307/log/binlog/binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=7
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100

#logging
log_error=/data/my3307/log/error.log
slow_query_log_file=/data/my3307/log/slow.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1

#relay
relay_log=/data/my3307/log/relaylog
relay_log_index=/data/my3307/log/relay.index
relay_log_info_file=/data/my3307/log/relay-log.info

#slave
slave_load_tmpdir=/data/my3307/tmp
slave_skip_errors=OFF


#innodb
innodb_data_home_dir=/data/my3307/log/iblog
innodb_log_group_home_dir=/data/my3307/log/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=8

#default
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 128M
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=67108864
innodb_log_file_size=1048576000
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=60
innodb_open_files=60000
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
innodb_write_io_threads=10

[mysqld_safe]
datadir=/data/my3307/data

my.cnf

参考：

http://blog.csdn.net/kadwf123/article/details/79145700
http://blog.csdn.net/mchdba/article/details/45798139

posted @ 2018-02-07 01:18 Jenvid 阅读(339) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

不懂ABAP的python不是好basis

mysql小白系列_02 mysql源码安装标准化

问题：

1.为什么数据目录和日志目录需要分开?

2.如何标准化配置多实例?（例如：一台物理主机上部署3306与3307两个实例）

编译安装单实例

在上面的基础，再初始化一个实例

mysqld_mutil配置多个实例

3.详细描述MySQL编译安装的过程（截图安装步骤）

公告