MySQL 5.6.31 多实例安装

1.创建目录，并授权目录

mkdir /data/mysql/data32965
chown -R mysql.mysql /data/mysql/data32965

 3.建立my32965.conf

vim /etc/my32965.cnf
[client]
port = 32965
socket = /data/mysql/data32965/mysql.sock

[mysql]
no-auto-rehash
port = 32965
socket = /data/mysql/data32965/mysql.sock

[mysqld]
group_concat_max_len=4294967295
event_scheduler=1
connect_timeout=300
net_read_timeout=300
net_write_timeout=300
#group_concat_max_len=102400
log_bin_trust_function_creators=1
character-set-server = utf8
lower_case_table_names = 1
skip-character-set-client-handshake
init-connect='SET NAMES utf8'
wait_timeout=60
expire_logs_days = 7
#init_connect='set autocommit=0'  
skip-name-resolve
port = 32965
socket = /data/mysql/data32965/mysql.sock
datadir = /data/mysql/data32965/
max_connections = 3000
max_connect_errors = 30
table_open_cache = 1024
max_allowed_packet = 64M
max_heap_table_size = 32M
binlog_cache_size = 4M
binlog_format=mix
sort_buffer_size = 32M
join_buffer_size = 32M
thread_cache_size = 16
thread_concurrency=8
query_cache_size = 64M
query_cache_limit = 4M
query_cache_type=1
default-storage-engine=InnoDB
tmp_table_size = 32M
max_length_for_sort_data = 32M
transaction-isolation = READ-COMMITTED
#sort_buffer_size=256M

server-id=20
log-bin=mysql-bin
slave-skip-errors=1062,1053,1146,1032
#auto-increment-increment = 1
#auto-increment-offset = 1
#relay-log=mysql-relay
#relay-log-index=mysql-relay.index
#log-slave-update
replicate_wild_ignore_table=order.order
replicate_wild_ignore_table=order.order_api
replicate_wild_ignore_table=order.order_return_goods
replicate_wild_ignore_table=erp.%
replicate_wild_ignore_table=powers.%
replicate_wild_ignore_table=partner.%
sync_binlog=1
# general_log = on
# log_output=table

log-error=/data/mysql/data32965/mysqld_err.log
long_query_time = 3
#log-queries-not-using-indexes
slow_query_log=on
slow_query_log_file=/usr/local/mysql/log/slow32965.log

key_buffer_size = 64M
read_buffer_size = 32M
read_rnd_buffer_size = 32M
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 16M
myisam_max_sort_file_size = 16M
myisam_recover

#半同步复制
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 1000

relay_log_purge=1

# innodb选项
innodb_additional_mem_pool_size =32M
innodb_buffer_pool_size = 1G
innodb_autoextend_increment = 32M
innodb_file_io_threads = 8
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_buffer_pool_instances = 8

innodb_autoextend_increment = 100
innodb_data_home_dir = /data/mysql/data32965
innodb_flush_method = O_DIRECT
innodb_max_dirty_pages_pct = 5
innodb_lock_wait_timeout = 60
innodb_print_all_deadlocks = 1
innodb_io_capacity = 500


innodb_file_io_threads = 4
innodb_file_per_table = 1
innodb_write_io_threads = 8
innodb_read_io_threads = 8


[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
open-files-limit = 1024

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION

 3.初始数据库，启动数据库

# 初始化
/usr/local/mysql/scripts/mysql_install_db --datadir=/data/mysql/data32965 --user=mysql --basedir=/usr/local/mysql


# 前台启动
/usr/local/mysql/bin/mysqld_safe --defaults-extra-file=/etc/my32965.cnf --datadir=/data/mysql/data32965 --user=mysql 

# 后台启动
/usr/local/mysql/bin/mysqld_safe --defaults-extra-file=/etc/my32965.cnf --datadir=/data/mysql/data32965 --user=mysql &

 4.用户管理

# 登陆
mysql -uroot -p -h127.0.0.1 -P32965

# 密码修改
UPDATE mysql.user SET password=PASSWORD('PHmxxxxotpwds') WHERE user='root';

# 授权
grant all privileges on *.* to root@'10.1.8.%' identified by 'PHxxxwds';
flush privileges;


# 停止实例
/usr/local/mysql/bin/mysqladmin -uroot -p  -S /data/mysql/data32965/mysql.sock shutdown