Mysql多实例安装步骤

Mysql多实例也就是指在一台操作系统上安装多个mysql实例。
我这边以redhat 7.4作为用例，mysql版本为5.7.26版本
先下载mysql的二进制包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
个人认为二进制方法安装mysql是最简便的方法，具体细节请自行查阅资料

mkdir /data/3306

mkdir /data/3307

cd /data/3306

mkdir binlog data logs redologrelaylog soket undolog

cd /data/3307

mkdir binlog data logs redologrelaylog soket undolog

groupadd -g 27 mysql

useradd -u 27 -g mysql mysql

chown -R mysql:mysql /data

vim /data/3306/my.cnf

vim /data/3307/my.cnf
我在下面贴一下我my.cnf的配置文件：
——————————————————————————————————————————————
[client]
port = 3306
socket = /data/3306/soket/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
port = 3306
server_id = 3306
basedir = /usr/local/mysql
datadir = /data/3306/data
log-bin = /data/3306/binlog/mysql-bin.log
socket = /data/3306/soket/mysql.sock
innodb_log_group_home_dir = /data/3306/redolog
innodb_undo_directory = /data/3306/undolog
tmpdir = /data/3306/data
relay_log = /data/3306/redolog/redhat-relay
log-error = /var/log/mysqld3306.log
binlog_format = mixed

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/3306/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 = 3306
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/mysqld3306.log #错误日志打印在这里
pid-file = /var/lib/mysqld/mysqld3306.pid
——————————————————————————————————————————————
注意 两个实例的server_id必须得不同，我这边设置为3306和3307,使用的端口分别为3306端口和3307端口
注意现在是没有这个错误日志文件的，你要去自己创建，而且还要给文件的权限是mysql组和mysql用户。
在你安装完mysql之后，你得去错误日志里查找数据库的初始密码

解压tar包再初始化数据库：
mysqld --defaults-file=/data/3306/my.cnf --initialize --user=mysql --datadir=/data/3306/data --explicit_defaults_for_timestamp 初始化数据库

mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null & 启动数据库

Mysqladmin -uroot -p -S /data/3306/soket/mysql.sock shutdown 关闭数据库

#启动实例并修改root密码

mysql -uroot -p -S /data/3306/soket/mysql.sock

#密码在 /var/log/mysqld3306.log 中A temporary password is generated for root@localhost:密码

mysql>set password=password(‘123456’);

mysql>flush privileges;

#mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问