东瑜

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

0.单机多实例mysql部署安装

  • 目的需求
    在单台虚拟机部署部署多实例mysql,用于配置mysql replication,MHA等。
  • 思路
    多实例安装mysql可以参考《源编译MySQL8.0的安装》文档。修改my.cnf的配置文件和目录文件结构。基于不同的端口启动多个实例。

1.新建3307实例的文件目录

#cd /data/
#mkdir -p /data/mysqldata/{3307/{data,tmp,binlog,innodb_ts,innodb_log},backup,script
#chown -R mysql:mysql mysqldata
#su - mysql

2.使用新的mysql.cnf文件启动mysql实例

[client]
port = 3307
socket = /data/mysqldata/3307/mysql.sock

# The MySQL server
[mysqld]
port = 3307
mysqlx_port = 33070
user = mysql
socket = /data/mysqldata/3307/mysql.sock
mysqlx_socket=/data/mysqldata/3307/mysqlx.sock
pid-file = /data/mysqldata/3307/mysql.pid
basedir = /usr/local/mysql
datadir = /data/mysqldata/3307/data
tmpdir = /data/mysqldata/3307/tmp
open_files_limit = 60000
explicit_defaults_for_timestamp
server-id = 1203307
lower_case_table_names = 1
character-set-server = utf8
federated
#sql_mode=STRICT_TRANS_TABLES
max_connections = 1000
max_connect_errors = 100000
interactive_timeout = 86400
wait_timeout = 86400
back_log=100

default-storage-engine = InnoDB

#*********** GTID settting*******************
log_slave_updates = 1
gtid_mode=ON
enforce-gtid-consistency=true
innodb_flush_log_at_trx_commit=2
binlog_format= row
skip-slave-start=1
sync_binlog=5


#*********** Logs related settings ***********
log-bin = /data/mysqldata/3307/binlog/mysql-bin
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
long_query_time = 1
log_output = FILE
log-error = /data/mysqldata/3307/mysql-error.log
slow_query_log = 1
slow_query_log_file = /data/mysqldata/3307/slow_statement.log
#log_queries_not_using_indexes
general_log = 0
general_log_file = /data/mysqldata/3307/general_statement.log
#expire-logs-days = 14
binlog_expire_logs_seconds = 1728000
relay-log = /data/mysqldata/3307/binlog/relay-bin
relay-log-index = /data/mysqldata/3307/binlog/relay-bin.index

#****** MySQL Replication New Feature*********
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery

#*********** INNODB Specific options ***********
innodb_buffer_pool_size = 2048M
transaction-isolation=REPEATABLE-READ
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysqldata/3307/innodb_ts
innodb_data_file_path = ibdata1:2048M:autoextend

innodb_thread_concurrency = 8

innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3

innodb_log_group_home_dir = /data/mysqldata/3307/innodb_log
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 70
innodb_flush_method=O_DIRECT

[mysql]
no-auto-rehash
default-character-set=gbk
prompt = (\u@\h) [\d]>\_

3.初始化数据库

$/usr/local/mysql/bin/mysqld --defaults-file=/data/mysqldata/3307/my.cnf --initialize --user=mysql

4.启动数据库

$/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3307/my.cnf --user=mysql &

5.多实例配置过程中的一些问题总结

  • error日志报错明细(一):
      2018-06-01T06:49:48.544169Z 0 [ERROR] [MY-011292] [Server] Plugin mysqlx reported: 'Preparation of I/O interfaces failed, X Protocol won't be accessible'
      2018-06-01T06:49:48.544429Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of UNIX socket (/data/mysqldata/3306/mysqlx.sock) failed, another
      process with PID 17095 is using UNIX socket file'
    
  • 解决思路
    1.查看socket值
      (root@localhost) [(none)]> show variables like '%socket%' ;
      +-----------------------------------------+----------------------------------+
      | Variable_name                           | Value                            |
      +-----------------------------------------+----------------------------------+
      | mysqlx_socket                           | /data/mysqldata/3306/mysqlx.sock |
      | performance_schema_max_socket_classes   | 10                               |
      | performance_schema_max_socket_instances | -1                               |
      | socket                                  | /data/mysqldata/3307/mysql.sock  |
      +---------------------------------------+----------------------------------+
    
    2.编译mysql源码的时候,需指定了MYSQLX_UNIX_ADDR的值。修改mysqlx_socket配置
    mysqlx_socket=/data/mysqldata/3307/mysqlx.sock
    
  • error日志报错明细(二):
     2018-06-01T07:10:47.051235Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of TCP (bind-address:'*', port:33060) failed, `bind()` failed with
      error: Address already in use (98). Do you already have another mysqld server running with Mysqlx ?'
    
  • 解决思路
    1.查看port值
      (root@localhost) [(none)]> show variables like '%port%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | large_files_support      | ON    |
      | mysqlx_port              | 33060 |
      | mysqlx_port_open_timeout | 0     |
      | port                     | 3307  |
      | report_host              |       |
      | report_password          |       |
      | report_port              | 3307  |
      | report_user              |       |
      | require_secure_transport | OFF   |
      +--------------------------+-------+
    
    2.修改mysql.cnf文件
    mysqlx_port = 33070
    
    这样数据库重启启动,不会报错。两个实例的MySQL的条件具备,可以搭建基于GTID的主从。
posted on 2018-06-14 10:25  东瑜  阅读(1116)  评论(0编辑  收藏