单机多实例mysq 8.0l部署安装

2022-12-29 11:28:23

单机多实例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部署安装

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

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

3.初始化数据库

4.启动数据库

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

相关文章