单机多实例 mysq 8.0l 部署安装
目的需求
在单台虚拟机部署部署多实例 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 日志报错明细 (一):
bash 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 值
sql (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 配置
bash mysqlx_socket=/data/mysqldata/3307/mysqlx.sock
error 日志报错明细 (二):
bash 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 值
sql (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 文件
sql mysqlx_port = 33070
这样数据库重启启动, 不会报错. 两个实例的 MySQL 的条件具备, 可以搭建基于 GTID 的主从.
posted on 2018-06-14 10:25 东瑜 阅读 (...) 评论 (...) 编辑 收藏
来源: https://www.cnblogs.com/zhangshengdong/p/9181867.html