Shulou(Shulou.com)06/01 Report--

The installation environment is:

Series: centos 6 X86_X64

Memory: not less than 4G

Mysql version: mysql-boost-5.7.18.tar.gz

This article focuses on deploying multiple instances of mysql on the same host.

Mysql Multi-instance scenario of Portal Application

Baidu search engine database is multi-instance, generally from the library, listed as an example of a department, IBM 48 core CPU. 96 GB of memory, running 3-4 instances; sina net also uses multiple instances, about 48 GB of memory. The purpose of using multiple instances in portal websites is to match servers with good hardware and save space in IDC cabinets. At the same time, running multiple instances does not waste hardware resources. For general servers, there are no more than 4 instances, and 1-2 is the most suitable.

Multiple instances can be multiple instances corresponding to multiple configuration files or multiple instances corresponding to one configuration file.

Disadvantages of multiple instances:

1. The degree of coupling is too high, and a configuration file is difficult to manage. Unified principle of work development and operation and maintenance; reduction of coupling

Mysql related software packages:

Yum-y install ncurses-devel libaio-devel cmake

Installation requires mysql requires the creation of users and groups

Useradd mysql-s / sbin/nologin-M

Download the mysql 5.7installation package

Wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.18.tar.gz

Extract the installation package

Tar-zxf mysql-boost-5.7.18.tar.gz & & cd mysql-5.7.18

Cmake\

-DCMAKE_INSTALL_PREFIX=/u01/app/mysql/db\

-DSYSCONFDIR=/u01/app/mysql/db/etc\

-DWITH_BOOST=./boost/boost_1_59_0\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DWITH_FEDERATED_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_INNODB_MEMCACHED=1\

-DWITH_READLINE=1\

-DENABLED_LOCAL_INFILE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DEXTRA_CHARSETS=all\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci

Make-j 24

Make install

# create a data directory

Mkdir-p / data/mysql/engine/innolog

Mkdir-p / data/mysql/data

Mkdir-p / data/mysql/engine/innodata

Mkdir-p / data/mysql/engine/undo

Mkdir-p / data/mysql/log/mysql-binlog

Mkdir-p / data/mysql/log/mysql-relaylog

Chown mysql. / data-R

# create a directory of mysql configuration files

Mkdir / u01/app/mysql/db/etc

Configure my.cnf

[mysqld]

#-basic_my.conf

# 5.7

# author: duxing

# modify: 20160902

# version: 0.8

#-basic--#

User=mysql

Basedir = / u01/app/mysql/db

Datadir = / data/mysql/data

Pid-file = / data/mysql/mysql.pid

Socket=/data/mysql/mysql.sock

Default-storage-engine = INNODB

Transaction_isolation = REPEATABLE-READ

Binlog_cache_size = 32m

Max_binlog_size=512M

Binlog_format=row

Character-set-server = utf8

Port=3306

Server-id= 2

# sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'

Explicit_defaults_for_timestamp = true

Log-error = / data/mysql/mysql-error.log

Slow_query_log

Long_query_time = 10

Log_warnings

#-basic--#

#-directroy--#

Innodb_log_group_home_dir=/data/mysql/engine/innolog

Innodb_data_home_dir=/data/mysql/engine/innodata

Innodb_data_file_path = ibdata1:500M;ibdata2:150M:autoextend

Innodb_undo_directory=/data/mysql/engine/undo

Log-bin = / data/mysql/log/mysql-binlog/mysql-binlog

Log-bin-index = / data/mysql/log/mysql-binlog/mysql-binlog.index

Expire-logs-days=5

Relay_log = / data/mysql/log/mysql-relaylog/mysql-relay-bin

Relay_log_index= / data/mysql/log/mysql-relaylog/mysql-relay-bin.index

# relay_log_info_file=/data/mysql/log/mysql-relaylog/relay-log.info

#-directroy--#

#-engine--#

Innodb_file_per_table=1

Innodb_api_enable_binlog=1

Innodb_buffer_pool_size=256M

Innodb_flush_log_at_trx_commit = 1

Innodb_log_buffer_size = 16m

Innodb_log_file_size = 512m

Innodb_log_files_in_group = 4

Innodb_max_dirty_pages_pct = 75

Innodb_lock_wait_timeout = 120

Innodb_buffer_pool_instances=2

Innodb_force_recovery=0 # recover

Innodb_purge_threads=4 # undo purge

Innodb_write_io_threads = 16

Innodb_read_io_threads = 16

Innodb_io_capacity=1200

Innodb_old_blocks_pct=37

Innodb_old_blocks_time=1000

Innodb_undo_tablespaces=12

Innodb_undo_logs=40

Innodb_commit_concurrency = 1000 # commit/s

Innodb_thread_concurrency = 1000 # use for cpu mutex

Innodb_thread_sleep_delay = 200 # ms delay time

# innodb_use_native_aio=1

#-engine--#

#-global cache-#

Read_buffer_size = 32m

Read_rnd_buffer_size = 128m

Sort_buffer_size = 64m

Join_buffer_size = 64m

Query_cache_size = 128m

Query_cache_limit = 4m

Tmp_table_size = 128m

#-global cache-#

#-replicat--#

# read_only=1

Relay_log_purge=0

Log_slave_updates=1

Slave_parallel_workers=4

Log_bin_trust_function_creators=1

Binlog_checksum = CRC32

Slave_allow_batching = 1

Master_verify_checksum = 1

Slave_sql_verify_checksum = 1

Binlog_rows_query_log_events = 1

Gtid-mode=on

Enforce-gtid-consistency=1

Sync-master-info=1

Automatic repair of relay_log_purge = 1 # relaylog

Relay_log_recovery = 1

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

# set global rpl_semi_sync_master_enabled = 1

# set global rpl_semi_sync_slave_enabled = 1

# rpl_semi_sync_master_timeout=200

#-replicat--#

#-thread--#

Table_open_cache = 2048 # reduce open descriptor

Thread_stack = 256K # * thead_num < useable mem

Thread_cache_size = 128 # reuse thread in mem

#-thread--#

#-network--#

Back_log = 512

# skip-networking

# skip-grant-tables

Skip-name-resolve

Max_connections = 3000

Max_connect_errors = 30

Table_open_cache = 4096

Max_allowed_packet = 64m

Max_heap_table_size = 512m

#-network--#

#-timeout----#

Connect-timeout = 10

Wait-timeout = 8000

Interactive-timeout = 8000

Slave-net-timeout = 60

Net_read_timeout = 30

Net_write_timeout = 60

Net_retry_count = 10

Net_buffer_length = 16384

#-timeout----#

[mysql]

Auto-rehash

[client]

Socket=/data/mysql/mysql.sock

Port=3306

Delete / etc/my.cnf

/ u01/app/mysql/db/etc

You need to check the error log before initialization

Cat / data/mysql/mysql-error.log

Cd / u01/app/mysql/db

. / bin/mysqld-initialize-basedir=/u01/app/mysql/db-datadir=/data/mysql/data-user=mysql

#-initialize-insecure option does not need to log in and alter user after initializing the database using this

# cat / data/mysql/mysql-error.log

/ u01/app/mysql/db/bin/mysqld_safe-skip-grant-tables &

Update mysql.user set authentication_string=password ('123.com') where user='root' and Host = 'localhost'

# start the mysql service

/ u01/app/mysql/db/support-files/mysql.server restart

# # you need to change your password or you cannot use the library normally

Alter user 'root'@'localhost' identified by' 123.com'

# set Boot self-boot

Cp / u01/app/mysql/db/support-files/mysql.server / etc/init.d/mysql.server-5.7

Chmod + x / etc/init.d/mysql.server-5.7

Chkconfig-addmysql.server-5.7

# set environment variables

Vim / .bashrc

# set mysql

NHOME=/u01/app/mysql/db

PATH=$PATH:$NHOME/bin

Source / .bashrc

Multiple instances of mysql can be changed directly when writing two different configuration files and different port initialization libraries.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.