mysql5.6.31安装_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