mysql部署及优化

安装部署

1.安装节点01

#安装依赖包
yum -y install wget ftp ncdu gcc gcc-c++ cmake make openssl-devel ncurses-devel zlib-devel bzip2 libaio-* gcc autoconf automake libxml* ncurses-devel libmcrypt* libtool-ltdl-devel* cmake pcre pcre-devel openssl openssl-devel
#下载源码文件
mkdir -p /usr/local/src /data/mysql01 /data/mysql02
cd /usr/local/src
wget http://img.9j6.cn/software/mysql/boost_1_59_0.tar.gz
wget http://img.9j6.cn/software/mysql/mysql-5.7.28.tar.gz
tar zxvf boost_1_59_0.tar.gz && mv boost_1_59_0 bootst
tar zxvf mysql-5.7.28.tar.gz && cd mysql-5.7.28

cmake . \
    -DCMAKE_INSTALL_PREFIX=/usr/local/mysql01 \
    -DWITH_BOOST=/usr/local/src/boost \
    -DMYSQL_DATADIR=/data/mysql01/ \
    -DMYSQL_USER=root \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITH_PARTITION_STORAGE_ENGINE=1 \
    -DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
    -DWITH_EXTRA_CHARSETS=all \
    -DWITH_DEBUG=0 \
    -DWITH_READLINE=1 \
    -DWITH_SSL=system \
    -DWITH_ZLIB=system \
    -DWITH_LIBWRAP=0 \
    -DENABLED_LOCAL_INFILE=1 \
    -DMYSQL_UNIX_ADDR=/usr/local/mysql01/tmp/mysql.sock \
    -DDEFAULT_CHARSET=utf8mb4 \
    -DDEFAULT_COLLATION=utf8mb4_general_ci

make -j `lscpu | grep -i '^cpu(s)'| tr -s ' ' '%' | cut -d% -f2` && make install

2.安装节点02

#安装依赖包
yum -y install wget gcc gcc-c++ cmake make openssl-devel ncurses-devel zlib-devel bzip2 libaio-*
#下载源码文件
mkdir -p /usr/local/src /data/mysql01 /data/mysql02
cd /usr/local/src
wget http://img.9j6.cn/software/mysql/boost_1_59_0.tar.gz
wget http://img.9j6.cn/software/mysql/mysql-5.7.28.tar.gz
tar zxvf boost_1_59_0.tar.gz && mv boost_1_59_0 bootst
tar zxvf mysql-5.7.28.tar.gz && cd mysql-5.7.28

cmake . \
    -DCMAKE_INSTALL_PREFIX=/usr/local/mysql02 \
    -DWITH_BOOST=/usr/local/src/boost \
    -DMYSQL_DATADIR=/data/mysql02/ \
    -DMYSQL_USER=root \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITH_PARTITION_STORAGE_ENGINE=1 \
    -DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
    -DWITH_EXTRA_CHARSETS=all \
    -DWITH_DEBUG=0 \
    -DWITH_READLINE=1 \
    -DWITH_SSL=system \
    -DWITH_ZLIB=system \
    -DWITH_LIBWRAP=0 \
    -DENABLED_LOCAL_INFILE=1 \
    -DMYSQL_UNIX_ADDR=/usr/local/mysql01/tmp/mysql.sock \
    -DDEFAULT_CHARSET=utf8mb4 \
    -DDEFAULT_COLLATION=utf8mb4_general_ci

make -j `lscpu | grep -i '^cpu(s)'| tr -s ' ' '%' | cut -d% -f2` && make install

配置文件

1.配置节点01

cat /etc/my.cnf
[mysqld]
server-id=1
log-bin=/data/mysql01/binlog
expire_logs_days = 30
#general_log = on
#general_log_file = /usr/local/mysql01/logs/generalLog.log
log_timestamps = SYSTEM

datadir=/data/mysql01
socket=/data/mysql01/mysql.sock
lower_case_table_names=1
log-error=/usr/local/mysql01/logs/error.log
pid-file=/usr/local/mysql01/pids/mysqld.pid
explicit_defaults_for_timestamp=true
slow_query_log=on
slow_query_log_file=/usr/local/mysql01/logs/mg-slow.log
long_query_time=1

character_set_server=utf8
character-set-server=utf8
symbolic-links=0
max_connections=10000
wait_timeout=30000
interactive_timeout=30000
lower_case_table_names=1
default_storage_engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_allowed_packet = 256M
[mysqld_safe]
log-error=/usr/local/mysql01/logs/error.log
pid-file=/usr/local/mysql01/pids/mysqld.pid

[mysql]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[mysqld.server]
character-set-server=utf8
[mysqld.safe]
default-character-set=utf8
[client]
default-character-set=utf8

2.配置节点02

cat /etc/my.cnf
[mysqld]
server-id=2
log-bin=/data/mysql02/binlog
expire_logs_days = 30
#general_log = on
#general_log_file = /usr/local/mysql02/logs/generalLog.log
log_timestamps = SYSTEM

datadir=/data/mysql02
socket=/data/mysql02/mysql.sock
lower_case_table_names=1
log-error=/usr/local/mysql02/logs/error.log
pid-file=/usr/local/mysql02/pids/mysqld.pid
explicit_defaults_for_timestamp=true
slow_query_log=on
slow_query_log_file=/usr/local/mysql02/logs/mg-slow.log
long_query_time=1

character_set_server=utf8
character-set-server=utf8
symbolic-links=0
max_connections=10000
wait_timeout=30000
interactive_timeout=30000
lower_case_table_names=1
default_storage_engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_allowed_packet = 256M
[mysqld_safe]
log-error=/usr/local/mysql02/logs/error.log
pid-file=/usr/local/mysql02/pids/mysqld.pid

[mysql]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[mysqld.server]
character-set-server=utf8
[mysqld.safe]
default-character-set=utf8
[client]
default-character-set=utf8

3.公共目录创建

mkdir /usr/local/mysql01/{logs,tmp,pids}
touch /usr/local/mysql01/logs/error.log
touch /usr/local/mysql01/pids/mysqld.pid

mkdir /usr/local/mysql02/{logs,tmp,pids}
touch /usr/local/mysql02/logs/error.log
touch /usr/local/mysql02/pids/mysqld.pid