企业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

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<kj

启动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