mysql5.7 部署文档-rpm

一、准备工作

1.1、目录规划

（1）/mysql 使用单独的逻辑卷。

mkdir /mysql

lvcreate -n lv_mysql -L +100g vg

mkfs.ext4 /dev/mapper/vg-lv_mysql

echo "/dev/mapper/vg-lv_mysql   /mysql                    ext4    defaults        0 0" >>/etc/fstab

mount -a

（2）按照数据库服务端口创建相应目录。

/mysql/3306/	mkdir -p /mysql/3306/ mkdir -p /mysql/3306/data/ mkdir -p /mysql/3306/binlog/ mkdir -p /mysql/3306/log/ mkdir -p /mysql/3306/tmp/ mkdir -p /mysql/3306/undolog/
/mysql/3306/data/
/mysql/3306/binlog/
/mysql/3306/log/
/mysql/3306/tmp/
/mysql/3306/undolog/

1.2、软件包准备

（1）准备并上传rpm安装包，使用5.7.19版本。

mysql-5.7.19-1.el6.x86_64.rpm-bundle.tar

二、安装过程

2.1、操作系统中存在的旧版本卸载

(1) 检查系统中是否存在旧版本的数据库

rpm -qa |grep -i mysql

 

(2) 卸载旧版本数据库

rpm -qa|grep -i mysql|xargs rpm -e --nodeps

rm -rf /usr/lib64/mysql/

userdel mysql

groupdel mysql

 

2.2、安装mysql 5.7.19

(1) 依赖RPM包查看

rpm -qa | grep -i bison

rpm -qa | grep -i gcc-c++

rpm -qa | grep -i glibc

rpm -qa | grep -i perl

rpm -qa | grep -i ncurses

rpm -qa | grep -i ncurses-devel

rpm -qa | grep -i libaio

(2)创建mysql用户

groupadd -g 600 mysql

useradd -u 601 -g mysql mysql

passwd mysql

(3)安装必要的rpm包

rpm -ivh mysql-community-common-5.7.19-1.el6.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.19-1.el6.x86_64.rpm

rpm -ivh mysql-community-libs-compat-5.7.19-1.el6.x86_64.rpm

rpm -ivh mysql-community-client-5.7.19-1.el6.x86_64.rpm

rpm -ivh mysql-community-server-5.7.19-1.el6.x86_64.rpm

(4)参数配置

root用户

vi ~/.bash_profile

PATH=$PATH:$HOME/bin:/usr/bin:/usr/share/mysql:/var/lib/mysql

 

vi /etc/security/limits.conf

mysql      soft    nproc   16384

mysql      hard    nproc   16384

mysql      soft    nofile  65536

mysql      hard    nofile  65536

mysql      hard    stack   1024000

mysql      soft    stack   1024000

 

vi /etc/my.cnf

 

[client]

# CLIENT #

port = 3306

socket=/var/lib/mysql/mysql.sock #socket文件路径,需要根据实际情况修改

default-character-set = utf8

 

[mysqld]

# GENERAL #

basedir = /usr #软件存放路径,需要根据实际情况修改

datadir=/mysql/3306/data #data存放路径,需要根据实际情况修改

socket = /var/lib/mysql/mysql.sock #socket文件路径,需要根据实际情况修改

pid-file = /var/run/mysqld/mysqld.pid #pidfile文件路径,需要根据实际情况修改

server-id = 1  #需要根据实际情况修改

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

 

user = mysql

default_storage_engine = InnoDB

character-set-server = utf8

skip-external-locking

skip-host-cache

skip-name-resolve

autocommit = on

max_allowed_packet = 16M

max_connections = 1000

max_user_connections = 1000

max_connect_errors = 1000000

wait_timeout = 1800

transaction_isolation = READ-COMMITTED

explicit_defaults_for_timestamp = 1

query-cache-type = 0

query-cache-size = 0

thread-cache-size = 50

open-files-limit = 65535

tmpdir = /mysql/3306/tmp #tmp文件存放路径,需要根据实际情况修改

slave_load_tmpdir = /mysql/3306/tmp #tmp文件存放路径,需要根据实际情况修改

 

# session memory settings #

tmp-table-size = 32M

max-heap-table-size = 32M

read_buffer_size = 16M

read_rnd_buffer_size = 32M

sort_buffer_size = 32M

join_buffer_size = 64M

key-buffer-size = 64M

 

# table cache performance settings #

table_open_cache = 4096

table_definition_cache = 4096

table_open_cache_instances = 64  #最大64

 

# INNODB #

innodb_open_files=2048

innodb_buffer_pool_size = RAM*60% #innodb缓冲池大小,需要根据实际情况修改，建议为RAM的60%-70%

innodb_sort_buffer_size = 64M

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 2000

innodb_io_capacity = 2000

innodb_io_capacity_max = 4000

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_purge_threads = 4

innodb_large_prefix = 1

innodb_thread_concurrency = 64

innodb_print_all_deadlocks = 1

innodb_log_file_size = 1G

innodb_log_buffer_size = 16M

innodb_log_files_in_group = 2

innodb_file_per_table=1

innodb-strict-mode = 1

innodb_flush_log_at_trx_commit = 1

innodb_log_files_in_group = 3

innodb_flush_method = O_DIRECT

innodb_undo_directory=/mysql/3306/undolog

innodb_undo_logs = 128

innodb_undo_tablespaces = 3

 

# BINARY LOGGING #

log-bin = /mysql/3306/binlog/bin.log #binlog存放路径,需要根据实际情况修改

expire-logs-days = 7

sync-binlog = 1

binlog_format="ROW"

log_bin_trust_function_creators = 1

binlog_cache_size = 2097152

 

# LOGGING #

log-error = /mysql/3306/log/error.log #errorlog存放路径,需要根据实际情况修改

log-queries-not-using-indexes = 1

slow-query-log = 1

slow-query-log-file = /mysql/3306/log/slowquery.log #slowlog存放路径,需要根据实际情况修改

long_query_time = 2

min_examined_row_limit = 100

 

[mysqld-5.6]

# metalock performance settings

metadata_locks_hash_instances=64

 

[mysqld-5.7]

# new innodb settings #

loose_innodb_numa_interleave=1

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 6

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 2G

innodb_purge_rseg_truncate_frequency = 128

(5)权限修改

chown -R mysql:mysql /usr/share/mysql

chown -R mysql:mysql /mysql

chmod -R 755 /mysql

 

(6)启动

备注：第一次执行service mysqld start ，mysql会自动根据/etc/my.cnf参数创建数据库。

service mysqld start

[root@oracle12c mysql]# service mysqld start

Initializing MySQL database:                               [  OK  ]

Starting mysqld:                                           [  OK  ]

 

(7)root初始密码修改

grep 'temporary password' /var/log/mysqld.log

mysql -uroot -p

mysql> set global validate_password_policy=0;

mysql> set global validate_password_length=5;

mysql>ALTER USER USER() IDENTIFIED BY '12345678';

mysql>flush privileges;

三、单主机多mysql数据库服务配置过程

在不关闭/重启mysql的情况下，重新启动一个新的mysql实例，使用3390端口，实现一台机器上同时运行两个数据库实例。

3.1、创建新的mysql实例数据存储目录

/mysql/3390/	mkdir -p /mysql/3390/ mkdir -p /mysql/3390/data/ mkdir -p /mysql/3390/binlog/ mkdir -p /mysql/3390/log/ mkdir -p /mysql/3390/tmp/ mkdir -p /mysql/3390/undolog/
/mysql/3390/data/
/mysql/3390/binlog/
/mysql/3390/log/
/mysql/3390/tmp/
/mysql/3390/undolog/

3.2、复制一份 my.cnf配置文件

cp /etc/my.cnf /mysql/3390/my.cnf

3.3、修改/mysql/3390/my.cnf文件，把默认的3306端口改成 3390，根据实际情况修改pid-file，socket，basedir，datadir

[client]

# CLIENT #

port = 3390

socket=/mysql/3390/mysql.sock #socket文件路径,需要根据实际情况修改

default-character-set = utf8

 

[mysqld]

# GENERAL #

port = 3390

basedir = /usr #软件存放路径,需要根据实际情况修改

datadir=/mysql/3390/data #data存放路径,需要根据实际情况修改

socket = /mysql/3390/mysql.sock #socket文件路径,需要根据实际情况修改

pid-file = /mysql/3390/mysqld.pid #pidfile文件路径,需要根据实际情况修改

server-id = 2  #需要根据实际情况修改

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

 

user = mysql

default_storage_engine = InnoDB

character-set-server = utf8

skip-external-locking

skip-host-cache

skip-name-resolve

autocommit = on

max_allowed_packet = 16M

max_connections = 1000

max_user_connections = 1000

max_connect_errors = 1000000

wait_timeout = 1800

transaction_isolation = READ-COMMITTED

explicit_defaults_for_timestamp = 1

query-cache-type = 0

query-cache-size = 0

thread-cache-size = 50

open-files-limit = 65535

tmpdir = /mysql/3390/tmp #tmp文件存放路径,需要根据实际情况修改

slave_load_tmpdir = /mysql/3390/tmp #tmp文件存放路径,需要根据实际情况修改

 

# session memory settings #

tmp-table-size = 32M

max-heap-table-size = 32M

read_buffer_size = 16M

read_rnd_buffer_size = 32M

sort_buffer_size = 32M

join_buffer_size = 64M

key-buffer-size = 64M

 

# table cache performance settings #

table_open_cache = 4096

table_definition_cache = 4096

table_open_cache_instances = 64  #最大64

 

# INNODB #

innodb_open_files=2048

innodb_buffer_pool_size = 300M #innodb缓冲池大小,需要根据实际情况修改，建议为RAM的60%-70%

innodb_sort_buffer_size = 64M

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 2000

innodb_io_capacity = 2000

innodb_io_capacity_max = 4000

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_purge_threads = 4

innodb_large_prefix = 1

innodb_thread_concurrency = 64

innodb_print_all_deadlocks = 1

innodb_log_file_size = 1G

innodb_log_buffer_size = 16M

innodb_log_files_in_group = 2

innodb_file_per_table=1

innodb-strict-mode = 1

innodb_flush_log_at_trx_commit = 1

innodb_log_files_in_group = 3

innodb_flush_method = O_DIRECT

innodb_undo_directory=/mysql/3390/undolog

innodb_undo_logs = 128

innodb_undo_tablespaces = 3

 

# BINARY LOGGING #

log-bin = /mysql/3390/binlog/bin.log #binlog存放路径,需要根据实际情况修改

expire-logs-days = 7

sync-binlog = 1

binlog_format="ROW"

log_bin_trust_function_creators = 1

binlog_cache_size = 2097152

 

# LOGGING #

log-error = /mysql/3390/log/error.log #errorlog存放路径,需要根据实际情况修改

log-queries-not-using-indexes = 1

slow-query-log = 1

slow-query-log-file = /mysql/3390/log/slowquery.log #slowlog存放路径,需要根据实际情况修改

long_query_time = 2

min_examined_row_limit = 100

 

[mysqld-5.6]

# metalock performance settings

metadata_locks_hash_instances=64

 

[mysqld-5.7]

# new innodb settings #

loose_innodb_numa_interleave=1

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 6

innodb_undo_log_truncate = 2

innodb_max_undo_log_size = 2G

innodb_purge_rseg_truncate_frequency = 128

3.4、初始化数据库

chown -R mysql:mysql /mysql/3390

chmod -R 755 /mysql/3390

mysql_install_db --defaults-file=/mysql/3390/my.cnf --datadir=/mysql/3390/data/

mysql是yum安装的，所以basedir=/usr就可以，它会自动在/usr下找mysql的安装目录。

3.5、启动mysql，要指定my.cnf文件启动

mysqld_safe --defaults-file=/mysql/3390/my.cnf &

3.6、登陆3390端口的mysql数据库

cat /root/.mysql_secret

# Password set for user 'root@localhost' at 2019-09-02 01:13:30

Dy.t9oAhC,af

mysql -S /mysql/3390/mysql.sock -P 3390 -p

mysql>ALTER USER USER() IDENTIFIED BY '12345678';

mysql>flush privileges;

3.7、停止mysql，需要指定对应的sock文件。

mysqladmin -uroot -S /mysql/3390/mysql.sock shutdown -p