MySQL安装部署之单实例

系统配置

创建用户和组

$ groupadd mysql
$ useradd mysql -g mysql

创建数据目录

$ mkdir /service/data
$ mkdir /service/binlog
$ chown -R mysql:mysql /service/data
$ chown -R mysql:mysql /service/binlog

配置参数文件(/etc/my.cnf)

[client]
port            = 3306
socket          = /service/data/mysqld.sock

[mysqld]
##BASIC CONFIGURATION
server-id = 5220
port= 33006
socket = /service/data/mysqld.sock
basedir = /usr/local/mysql
datadir = /service/data
skip_name_resolve = 1
default-storage-engine = INNODB
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
back_log = 500
wait_timeout = 600
interactive_timeout = 600
connect_timeout=120
plugin_load = validate_password.so
symbolic-links = 0
lower_case_table_names = 1
local_infile=OFF
skip_networking=OFF
skip_show_database=OFF
max_allowed_packet = 512M
event_scheduler=OFF
explicit_defaults_for_timestamp = 1
group_concat_max_len = 102400
log_error_verbosity=2

##BINLOG CONFIGURATION
log-bin = /service/binlog/mysql-bin
log_bin_trust_function_creators = ON
binlog_format = row
binlog_cache_size = 4M
max_binlog_cache_size = 1G
max_binlog_size = 512M
expire_logs_days = 20

##LOG CONFIGURATION
slow_query_log = 1
slow_query_log_file = /service/data/mysqlslow.log
long_query_time = 1
log_slow_admin_statements = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log-error = /service/data/mysqld.log

##SESSION CONFIGURATION
max_connections = 2000
max_user_connections = 2000
max_connect_errors = 100000
table_open_cache = 2048
table_definition_cache = 4096
table_open_cache_instances = 32
read_buffer_size = 16M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
tmp_table_size = 128M
thread_cache_size = 64
thread_stack = 256K
query_cache_type = 0
query_cache_size = 0
open_files_limit = 65535
max_prepared_stmt_count=1048576

##GTID CONFIGURATION
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
slave_allow_batching = 1
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
slave-parallel-workers=4
relay_log_purge = 1
relay_log_recovery = 1

##INNODB CONFIGURATION
innodb_buffer_pool_size = 140G  #(65%-75% Physical memory)
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 100
innodb_thread_concurrency = 1000
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 4
innodb_write_io_threads = 16
innodb_io_capacity = 4000
innodb_io_capacity_max = 10000
innodb_file_per_table = 1
innodb_strict_mode = 1
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_purge_batch_size = 32
innodb_change_buffering = all
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 0
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 67108864
innodb_stats_persistent_sample_pages = 64
innodb_online_alter_log_max_size=1G
transaction_isolation = READ-COMMITTED
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[mysql]
prompt = "\u@\d \R:\m:\s "
default-character-set = utf8mb4
no-auto-rehash

Limit配置

$ cat /etc/security/limits.conf
mysql soft nproc 10240
mysql hard nproc 10240
mysql soft nofile 65535
mysql hard nofile 65535

关闭selinux

[root@t-luhxdb01-p-szzb ~]# vi /etc/selinux/config
SELINUX=disabled
[root@t-luhxdb01-p-szzb ~]# setenforce 0

关闭transparent_hugepage

$ echo never >  /sys/kernel/mm/transparent_hugepage/enabled
$ echo never > /sys/kernel/mm/transparent_hugepage/defrag
$ echo "echo never >  /sys/kernel/mm/transparent_hugepage/enabled" >> /etc/rc.local
$ echo "echo never > /sys/kernel/mm/transparent_hugepage/defrag" >> /etc/rc.local

安装数据库

解压安装包

$ tar -xvf  mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
$ mv mysql-5.7.17-linux-glibc2.5-x86_64   /usr/local/mysql
$ chown -R root.mysql /usr/local/mysql

配置环境变量

$ echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
$ source /etc/profile

初始化数据库

$ mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/service/data

Tips: 初始化后随机root密码记录在mysql日志中

启动数据库

$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
$ /etc/init.d/mysqld start

修改root密码

$ mysql -uroot -p
root@(none) 15:16> set password=password('NewPassword');
root@(none) 15:16> flush privileges;

多实例环境

创建不同的数据目录

[root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db1/data -p
[root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db1/binlog -p
[root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db2/binlog -p
[root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db2/data -p
[root@t-luhx02-v-szzb mysql]# chown -R mysql.mysql /service/mysql/

配置多实例my.cnf

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /service/mysql/mysqld_multi.log
user=multi_admin
pass=ceshi@123

[mysqld33006]
server_id=111
port=33006
datadir=/service/mysql/db1/data
log_error=/servie/mysql/db1/data/mysqld.log
log_bin=/service/mysql/db1/binlog/mysql-bin
pid-file = /service/mysql/db1/data/mysqld.pid
socket = /service/mysql/db1/data/mysql.sock

[mysqld33007]
server_id=222
port=33007
datadir=/service/mysql/db2/data
log_error=/servie/mysql/db2/data/mysqld.log
log_bin=/service/mysql/db2/binlog/mysql-bin
pid-file = /service/mysql/db2/data/mysqld.pid
socket = /service/mysql/db2/data/mysql.sock

模块名称必须以mysqld开头，并且会继承[mysqld]中的其它参数

初始化实例

mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/service/mysql/db1/data
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/service/mysql/db2/data

查看实例服务

[root@t-luhx02-v-szzb mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld33006 is not running
MySQL server from group: mysqld33007 is not running

启动实例

[root@t-luhx02-v-szzb mysql]# mysqld_multi start 33006
[root@t-luhx02-v-szzb mysql]# mysqld_multi start 33007

创建multi管理用户

> create user 'multi_admin'@'localhost' identified by 'ceshi@123';
> grant shutdown on *.* to 'multi_admin'@'localhost';
> flush privileges;

停止实例

[root@t-luhx02-v-szzb mysql]# mysqld_multi stop 33006
[root@t-luhx02-v-szzb mysql]# mysqld_multi stop 33007