Mysql配置多实例 - smile0821/learngit GitHub Wiki

  1. 创建多实例文件 mkdir -p /u01/test/mysql_3307/data mkdir -p /u01/test/mysql_3307/conf
  2. 授予文件夹mysql用户及用户组权限 chown -R mysql:mysql /u01/test/mysql_3307
  3. 单独配置文件 vi /u01/test/conf
[client]
port            = 35005
socket          = /u01/test/mysql_3307/mysql.sock

[mysqld]
server_id=1003
port=3307
user=mysql
default_password_lifetime=0
character_set_server=utf8
collation_server=utf8_general_ci
max_allowed_packet=1024M
max_connections=20000
back_log=256
group_concat_max_len=200000

basedir = /usr/local/mysql
datadir = /u01/test/mysql_3307/data
socket  = /u01/test/mysql_3307/mysql.sock
pid-file = /u01/test/mysql_3307/mysqld.pid


binlog_format=row
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=on
sync-master-info=1
skip-slave-start=true
relay_log_purge=0

#master-host=10.185.235.93
#master-user=userWy
#master-password=Huawei123
#master-port=3306

log-bin=/u01/mysql/log/master-bin_1001
log-bin-index=/u01/mysql/log/master-bin_1001.index
relay-log=/u01/mysql/log/relay-log_1001
relay-log-index=/u01/mysql/log/relay-log_1001.index
log-error=/u01/mysql/log/mysqld_1001.log

default_storage_engine=INNODB
skip-host-cache
table_open_cache=8192
lower_case_table_names=1
skip_name_resolve

key_buffer_size=384M
sort_buffer_size=8M
read_buffer_size=4M
read_rnd_buffer_size=8M
join_buffer_size=128M
tmp_table_size=2048M
event_scheduler=0
expire_logs_days=7
max_heap_table_size=128M
max_connect_errors=1000

innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2

innodb_flush_log_at_trx_commit=1
innodb_log_file_size=1G
innodb_log_files_in_group=2
innodb_file_per_table=1
innodb_io_capacity=9000
innodb_io_capacity_max=18000
innodb_adaptive_hash_index=off

#innodb_force_recovery=1
slow_query_log=TRUE
slow_query_log_file=/u01/mysql/log/slow_query_log.txt
long_query_time=30

gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

transaction_isolation=READ-COMMITTED
explicit_defaults_for_timestamp=true
  1. 初始化数据库 /usr/local/mysql/bin/mysql_install_db --defaults-file=/u01/test/conf/my_3307.cnf --datadir=/u01/test/mysql_3307/data --basedir=/usr/local/mysql
  2. 设置密码 /usr/local/mysql/bin/mysqld_safe --defaults-file=/u01/test/conf/my_3307.cnf --skip-grant-tables &
    mysqladmin -u root -S /u01/test/mysql_3307/mysql.sock password 'testwy' ./mysql -uroot -p -S /u01/test/mysql_3307/mysql.sock mysql> update user set authentication_string=password("testwy") where user='root'; mysql> flush privileges; mysql> quit
  3. 重启mysql kill pid /usr/local/mysql/bin/mysqld_safe --defaults-file=/u01/test/conf/my_3307.cnf & ./mysql -uroot -p -S /u01/test/mysql_3307/mysql.sock alter user user() identified by 'testwy';