Mysql 5.7 多实例 - 多配置文件

Posted by: Thu Jul 11, 2019 | Word Count: 419 | Reading Time: 2 Minute

Mysql 5.7 多实例 - 多配置文件

Mysql5.7 默认支持mysqld multi方式来实现单机多实例，但是由于我这里Mysql已经由lnmp脚本默认创建了3306实例，并且投入使用，不想影响现有的服务，想通过添加配置文件，单独启动新实例的方式实现单机多实例

使用lnmp脚本搭建环境，默认mysql位于 /usr/local/mysql 目录下，默认数据目录为/usr/local/mysql/var ，默认配置文件为 /etc/my.cnf

创建新实例的数据目录（datadir）

# comezc @ localhost in ~
$ cd /usr/local/mysql/
    
# comezc @ localhost in /usr/local/mysql
$ sudo mkdir var3308
    
# comezc @ localhost in /usr/local/mysql
$ sudo chown -R mysql. var3308

初始化数据目录

# comezc @ localhost in /usr/local/mysql
$ sudo /usr/local/mysql/bin/mysqld --no-defaults \
--initialize-insecure \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/var3308 \
--user=mysql \
--explicit_defaults_for_timestamp
2019-07-10T07:39:47.417202Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-07-10T07:39:47.667017Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-07-10T07:39:47.728073Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e48ebb7b-a2e5-11e9-ad25-06e8184dad8c.
2019-07-10T07:39:47.729454Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-07-10T07:39:47.730085Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

复制默认配置文件，并进行修改，作为新实例的配置文件

# comezc @ localhost in /usr/local/mysql
$ sudo cp /etc/my.cnf /usr/local/mysql/var3308/my3308.cnf
    
# comezc @ localhost in /usr/local/mysql
$ sudo chown -R mysql. var3308
    
# comezc @ localhost in /usr/local/mysql
$ sudo vim var3308/my3308.cnf

新实例配置参考：

[client]
#password    = your_password
port          = 3308 #注意
socket        = /tmp/mysql-3308.sock #注意
    
[mysqld]
port          = 3308 #注意
socket        = /tmp/mysql-3308.sock #注意
datadir = /usr/local/mysql/var3308 #注意
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 1M
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 8K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 16M
tmp_table_size = 32M
performance_schema_max_table_instances = 1000
    
log-error = /usr/local/mysql/var3308/mysql3308_error.log #注意
pid-file = /usr/local/mysql/var3308/mysql3308.pid #注意
    
explicit_defaults_for_timestamp = true
#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
    
log-bin=mysql-bin
binlog_format=mixed
server-id    = 102
expire_logs_days = 10
early-plugin-load = ""
    
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = /usr/local/mysql/var3308 #注意
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var3308 #注意
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
    
[mysqldump]
quick
max_allowed_packet = 16M
    
[mysql]
no-auto-rehash
    
[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M
    
[mysqlhotcopy]
interactive-timeout

此处配置多数均为lnmp脚本代为写入，本人暂时水平太菜，不做修改

启动新实例

使用mysqld_safe直接启动，使用mysqladmin进行关闭和重载

# comezc @ localhost in ~
$ sudo mysqld_safe --defaults-file=/usr/local/mysql/var3308/my3308.cnf --datadir=/usr/local/mysql/var3308
    
# comezc @ localhost in ~
$ sudo /usr/local/mysql/bin/mysqladmin -u root -p -S /tmp/mysql-3308.sock shutdown|reload

空密码登录并修改root密码

# comezc @ localhost in ~ 
$ mysql -h127.0.0.1 -uroot -P3308 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
mysql> set password for root@localhost = password('******');
Query OK, 0 rows affected, 1 warning (0.00 sec)
    
mysql> exit
Bye

Mysql

mysql Mysql单机多实例