MySQL可以通过在一台服务器上,部署运行多个MySQL服务进程,通过不同的socket监听不同的服务端口来提供各自的服务。各个实例之间是相互独立的,每个实例的datadir, port, socket, pid都是不同的
操作系统
redhat 7.4
数据库 mysql
5.7.19
安装前准备
运行两个实例,端口分别是4000,4001
midir /data/4000
midir /data/4001
cd /data/4000
mkdir binlog data logs redolog relaylog soket undolog
cd /data/4001
mkdir binlog data logs redolog relaylog soket undolog
chown -R mysql:mysql /data
配置参数文件
每个实例的my.cnf的配置文件
vim /data/4000/my.cnf
vim /data/4001/my.cnf
例如:
[client]
port = 4000
socket = /data/4000/soket/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
port = 4000
server_id = 4000
basedir = /usr/local/mysql
datadir = /data/4000/data
log-bin = /data/4000/logs/rays-bin.log
socket = /data/4000/soket/mysql.sock
innodb_log_group_home_dir = /data/4000/redolog
innodb_undo_directory = /data/4000/undolog
tmpdir = /data/4000/data
relay_log = /data/4000/redolog/redhat-relay
log-error = /var/log/mysqld4000.log
default_time_zone = "+08:00"
character_set_server = utf8mb4
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
max_allowed_packet = 16M
lower_case_table_names = 1
open_files_limit = 10240
secure_file_priv = ""
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# connection settings #
interactive_timeout = 31536000
wait_timeout = 31536000
lock_wait_timeout = 3600
skip_name_resolve = 1
max_connections = 1024 # ulimit -n >= 1834
thread_cache_size = 1536
thread_stack = 512K
max_connect_errors = 50000
connect_timeout = 60
# table cache performance settings #
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 64
# session memory settings #
read_buffer_size = 8M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M
join_buffer_size = 16M
tmp_table_size = 32M
key_buffer_size = 32M
query_cache_type = 0
query_cache_size = 0
# log settings #
slow_query_log_file=/data/4000/logs/slow-log.log
slow_query_log = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 10
log_bin_trust_function_creators = 1
binlog_cache_size = 4M
max_binlog_cache_size = 4G
binlog_gtid_simple_recovery = 1
log_timestamps = system
# innodb settings #
#innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_size = 32M
innodb_buffer_pool_instances = 1
innodb_log_file_size = 32M
innodb_log_files_in_group = 2
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 60
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_method = O_DIRECT
innodb_undo_tablespaces = 3
innodb_thread_concurrency = 8 # cores * 2
innodb_write_io_threads = 2 # cores / 2
innodb_read_io_threads = 2 # cores / 2
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 64M
innodb_stats_persistent_sample_pages = 64
innodb_online_alter_log_max_size = 4G
innodb_log_buffer_size = 128M
innodb_rollback_on_timeout = ON
innodb_purge_threads = 8
innodb_page_cleaners = 8
innodb_undo_log_truncate = 1
innodb_sync_spin_loops = 30
innodb_spin_wait_delay = 6
# replication settings #
gtid_mode = ON
enforce_gtid_consistency = 1
master_verify_checksum = ON
# master settings #
auto_increment_increment
auto_increment_offset
# slave settings #
master_info_repository = TABLE
relay_log_info_repository = TABLE
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 64
slave_rows_search_algorithms = "INDEX_SCAN,HASH_SCAN"
log_slave_updates = 1
relay_log_recovery = 1
# semi-sync replication settings #
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 3000
[mysqldump]
quick
max_allowed_packet = 32M
[mysqld_safe]
log-error = /var/log/mysqld4000.log #错误日志打印在这里
pid-file = /var/lib/mysqld/mysqld4000.pid
安装,完成后记住root密码
例如:
mysqld --defaults-file=/data/4000/my.cnf --initialize --user=mysql --datadir=/data/4000/data --explicit_defaults_for_timestamp
mysqld_safe --defaults-file=/data/4000/my.cnf 2>&1 > /dev/null &
#启动实例并修改root密码
mysql -uroot -p -S /data/4000/soket/mysql.sock
#密码在 /var/log/mysqld4000.log 中 A temporary password is generated for root@localhost:密码
mysql>set password=password('123456');
mysql>flush privileges;
4001步骤也一样
访问管理
超级管理员
mysql> create user cup_dba@'%' IDENTIFIED by 'OStem@00';
mysql> grant all privileges on *.* to cup_dba@'%';
mysql> flush privileges;
关闭某个实例
mysqladmin -uroot -prootpwd -S /data/4000/soket/mysql.sock shutdown
mysqladmin -uroot -prootpwd -S /data/4001/soket/mysql.sock shutdown
客户端登陆,开放端口防火墙
firewall-cmd --zone=public --add-port=4000/tcp --permanent
firewall-cmd --zone=public --add-port=4001/tcp --permanent
firewall-cmd --reload
参考:
MySQL 5.7.15 多实例安装(二进制安装模式)
mysql5.7多实例安装部署
mysql 5.7 多实例安装
【干货】MySQL 5.7 多实例(多进程)配置教程