MySQL 8.0.12 多数据库安装说明并配置主从同步 MySQL 8.0.12 多数据库安装说明并配置主从同步

说明

场景说明，在一台已有一个MySQL 8.0.12的服务器上再安装3个数据库并配置主从同步，但是不是通过多实例的方式，按照安装多个数据库的方式，即每一个数据库与其他的互补相关。

部署环境

IP	系统	主或从
10.3.248.195	CentOS Linux release 7.5.1804	主
10.3.248.196	CentOS Linux release 7.5.1804	从

安装

下载 MySQL 8.0.12 二进制安装包

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz

解压文件

tar -xJvf mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz -C /opt/

赋值并创建目录和授权

cp -r /opt/mysql-8.0.12-linux-glibc2.12-x86_64 /usr/local/mysql_3307
cp -r /opt/mysql-8.0.12-linux-glibc2.12-x86_64 /usr/local/mysql_3308
cp -r /opt/mysql-8.0.12-linux-glibc2.12-x86_64 /usr/local/mysql_3309
# 本例中不需要创建mysql 用户，因为在此环境中已经存在一个MySQL 8.0.12 数据库了。
# 如果本地没有mysql 用户，则需要创建mysql 用户
groupadd mysql
useradd -g mysql mysql
# 创建数据目录
mkdir -p /data/{mysql_3307,mysql_3308,mysql_3309}
chown -R mysql.mysql /data/{mysql_3307,mysql_3308,mysql_3309}
chmod 775 /data/{mysql_3307,mysql_3308,mysql_3309}
# 创建sock和pid 目录并授权
mkdir /var/run/mysql && chown -R mysql.mysql /var/run/mysql

配置文件

# 配置文件的位置放在/etc/路径中,下面的是3307端口的配置文件，其他端口的配置是类似的.
# 只是port、socket、mysqlx_port、mysqlx_socket、datadir、server-id、
# log-error、slow-query-log-file 参数需要更换为相应的参数即可。
​
cat /etc/my_3307.cnf
[client]
#password   = your_password
port        = 3307
socket      = /var/run/mysql/mysql_3307.sock

​
​
[mysqld]
port        = 3307
socket      = /var/run/mysql/mysql_3307.sock

mysqlx_port       = 33070

mysqlx_socket     = /var/run/mysql/mysqlx_3307.sock

datadir = /data/mysql_3307
skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 1M
table_open_cache = 4096
sort_buffer_size = 16M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 256M
thread_cache_size = 512
tmp_table_size = 512M
explicit_defaults_for_timestamp=true
#skip-networking
max_connections = 10000
max_connect_errors = 100
open_files_limit = 65535
log-error = error_3307.log
slow-query-log = 1
slow-query-log-file = slow_3307.log
long_query_time = 0.2
relay_log_recovery=1
log-bin=mysql-bin
binlog_format=row
binlog_row_image=full
binlog_expire_logs_seconds=2592000
early-plugin-load = ""
server-id   = 7000
auto_increment_increment=2  
auto_increment_offset=1 
log_slave_updates = 1
character-set-server=utf8mb4
sql_mode=''
default_authentication_plugin=mysql_native_password
slave_skip_errors = ddl_exist_errors
​
#loose-innodb-trx=0
#loose-innodb-locks=0
#loose-innodb-lock-waits=0
#loose-innodb-cmp=0
#loose-innodb-cmp-per-index=0
#loose-innodb-cmp-per-index-reset=0
#loose-innodb-cmp-reset=0
#loose-innodb-cmpmem=0
#loose-innodb-cmpmem-reset=0
#loose-innodb-buffer-page=0
#loose-innodb-buffer-page-lru=0
#loose-innodb-buffer-pool-stats=0
#loose-innodb-metrics=0
#loose-innodb-ft-default-stopword=0
#loose-innodb-ft-inserted=0
#loose-innodb-ft-deleted=0
#loose-innodb-ft-being-deleted=0
#loose-innodb-ft-config=0
#loose-innodb-ft-index-cache=0
#loose-innodb-ft-index-table=0
#loose-innodb-sys-tables=0
#loose-innodb-sys-tablestats=0
#loose-innodb-sys-indexes=0
#loose-innodb-sys-columns=0
#loose-innodb-sys-fields=0
#loose-innodb-sys-foreign=0
#loose-innodb-sys-foreign-cols=0
​
default_storage_engine = InnoDB
innodb_data_home_dir = /data/mysql_3307
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql_3307
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
​
[mysqldump]
quick
max_allowed_packet = 100M
user=root
password=Ta1mysqI@20l8
​
​
[mysql]
no-auto-rehash
default-character-set=utf8mb4
​
[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 16M
read_buffer = 2M
write_buffer = 2M
​
[mysqlhotcopy]
interactive-timeout
​