mysql5.7.20多实例_企业MySQL5.7.20多实例部署方案

部署环境

yum install -y ncurses-devel libaio-devel cmake gcc gcc-c++ openssl-devel lrzsz wget make perl autoconf automake zlib libxml libgcrypt libtool bison

mkdir /application

mkdir -p /home/Ricky/tools

mysql -V

mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper

cat /etc/redhat-release

CentOS release 6.4 (Final)

uname -r

2.6.32-358.el6.x86_64

MySQL服务启动用户

useradd -s /sbin/nologin -M mysql

下载boost库

5.7版本的 MySQL 都需要boost库的支持

cd /home/Ricky/tools

下载boost_1_59_0.tar.gz，或者下载到windows然后上传上来

部署MySQL

wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.20.tar.gz

tar -xf mysql-5.7.20.tar.gz

cd mysql-5.7.20

cmake -DCMAKE_INSTALL_PREFIX=/application/mysql-5.7.20 \

-DMYSQL_DATADIR=/application/mysql-5.7.20/data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.7.20/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSERTS=gbk,gb2312,utf8,asci \

-DENABLED_LOCAL_INFILE=ON \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \

-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \

-DWITH_FAST_MUTEXES=1 \

-DWITH_ZLIB=bundled \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_READLINE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DWITH_DEBUG=0 \

-DDOWNLOAD_BOOST=1 \

-DWITH_BOOST=/home/Ricky/tools

make

make install

编译过程报错

在编译过程会报以下错误，但不影响正常编译，耐心等待即可

cc1plus: 警告：无法识别的命令行选项“-Wno-unused-local-typedefs”

[ 41%] Building CXX object sql/CMakeFiles/sql.dir/item_geofunc_setops.cc.o

配置软链和权限

ln -s /application/mysql-5.7.20/ /application/mysql

chown -R mysql.mysql /application/mysql/

部署多实例MySQL

mkdir /data/{3306,3307,3308}/data –p

[root@m01 3308]# tree /data/

/data/

├── 3306

│ ├── data #数据目录

│ ├── my.cnf #配置文件

│ └── mysql #启动文件，可以不要

├── 3307

│ ├── data

│ ├── my.cnf

│ └── mysql

└── 3308

├── data

├── my.cnf

└── mysql

find /data/ -type f -name "mysql"|xargs chmod +x

环境变量设置

export PATH=/application/mysql/bin:$PATH

echo "export PATH=/application/mysql/bin:$PATH" >>/etc/profile

初始化mysql

mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data/ --user=mysql #弃用

mysqld --initialize --basedir=/application/mysql --datadir=/data/3306/data/ --user=mysql

#会随机生成一个root密码g1cujIze/FB- 、IH-)pmJRF8xd 、jl-G+.uw9

启动mysql

chown -R mysql.mysql /data/

/application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &

启动过程报错

把my.cnf的ibdata1参数改为12M

innodb_data_file_path = ibdata1:12M:autoextend

2018-10-17T16:58:30.192642Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!

2018-10-17T16:58:30.192827Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

2018-10-17T16:58:30.843461Z 0 [ERROR] Plugin 'InnoDB' init function returned error.

2018-10-17T16:58:30.843816Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2018-10-17T16:58:30.843901Z 0 [ERROR] Failed to initialize plugins.

2018-10-17T16:58:30.843971Z 0 [ERROR] Aborting

修改mysql 的root用户密码

mysqladmin -uroot -pg1cujIze/FB- password '123456' -S /data/3306/mysql.sock

my.cnf配置文件

#端口和server-id不同

[root@m01 3308]# cat my.cnf

[client]

port = 3308

socket = /data/3308/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user = mysql

port = 3308

socket = /data/3308/mysql.sock

basedir = /application/mysql

datadir = /data/3308/data

open_files_limit = 1024

back_log = 600

wait_timeout = 120

interactive_timeout = 120

max_connections = 800

max_connect_errors = 3000

#table_cache = 614

external-locking = FALSE

max_allowed_packet = 8M

sort_buffer_size = 1M

join_buffer_size = 1M

thread_cache_size = 100M

#thread_concurrency = 2

query_cache_size = 64M

query_cache_limit = 1M

query_cache_min_res_unit = 2k

#default_table_type = InnoDB

thread_stack = 192K

#transaction_isolation = READ-COMMITTED

tmp_table_size = 2M

max_heap_table_size = 2M

long_query_time = 1

slow_query_log = on #开启slow日志，以前是log_slow_queries

long_query_time = 2 #超过2秒，记录到LOG里

log_queries_not_using_indexes = on#开启没有走索引的语句，记录到LOG里功能

slow_query_log_file = /data/3308/slow.log#LOG文件

pid-file = /data/3308/mysql.pid

#relay-log = /data/3308/relay-bin

#relay-log-info-file = /data/3308/relay-log.info

#general_log = on

#general_log_file = /data/3308/data/MySQL_ricky.log

log-bin = /data/3308/mysql-bin

binlog_format="STATEMENT"

#binlog_format="ROW"

#binlog_format="MIXED"

binlog_cache_size = 1M

max_binlog_cache_size = 1M

max_binlog_size = 2M

key_buffer_size = 16M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 1M

lower_case_table_names = 1

skip-name-resolve

slave-skip-errors = 1032,1062

replicate-ignore-db = mysql

server-id = 3

#innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size = 32M

innodb_data_file_path = ibdata1:12M:autoextend

#innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 4M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[mysqldump]

quick

max_allowed_packet = 2M

[mysqld_safe]

log-error = /data/3308/mysql_ricky3308.err

pid-file = /data/3308/mysqld.pid