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

 

转载于:https://www.cnblogs.com/JcLevy/p/11446920.html