Production Environment MySQL 5.5.x single-host multi-instance configuration practices

Background requirements:
1) Compile and install a MySQL database of version 5.5.x and above on a newly purchased server, and store all configuration files and data in/opt/mysql, it facilitates fast migration, replication, and overall backup in the future;
2) run two instances in the same MySQL instance, one bound to port 3306 and the other bound to port 3307. binlog is not enabled for instances bound to port 3306, data is stored in/opt/mysql/data; binlog is enabled for instances bound to port 3307, and data is stored in/opt/mysql/data2;
3) both instances adopt InnoDB as the default storage engine, character encoding adopts UTF-8; both instances adopt the same performance optimization configuration parameters to realize the configuration application of single-host multi-instance.

Implementation Scheme:
During compilation and installation, set the database configuration file my. cnf and data Directories all point to the/opt/mysql directory. Two different instances are managed using mysqld_multi, and the same configuration file sharing performance optimization configuration parameters are used; in the same configuration file, use the [mysqld1] and [mysqld2] labels to implement differentiated configurations for different instances.

Configuration process:
1. Compile and install MySQL with source code
1) install the required system software package
# Yum-y install gcc-c ++ autoconf bison libjpeg-devel libpng-devel freetype-devel libxml2 libxml2-devel zlib-devel glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn-devel openssl-devel openldap-devel nss_ldap openldap-clients openldap-servers

2) system settings before installation
# Mkdir/opt/mysql // mysql installation directory
# Mkdir/opt/mysql/data // mysql data storage directory
# Groupadd mysql // create a user
# Useradd-g mysql // create a user group
# Chown mysql: mysql-R/opt/mysql/data // grant the data storage directory permission

3) install cmake
MySQL 5.5 and later versions have been replaced by the cmake tool./configure compilation and configuration method. Therefore, you must first compile and install cmake in the system source code.
# Mkdir/home/tools
# Cd/home/tools
# Wgethttp: // www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
# Tar xf cmake-2.8.4.tar.gz
# Cd cmake-2.8.4
#./Configure
# Make; make install

4) start compiling and installing MySQL
# Wgethttp: // downloads.mysql.com/archives/mysql-5.5/mysql-5.5.19.tar.gz
# Tar xf mysql-5.5.19.tar.gz
# Cd mysql-5.5.19
# Cmake-DCMAKE_INSTALL_PREFIX =/opt/mysql \
-DSYSCONFDIR =/opt/mysql/etc \
-DMYSQL_DATADIR =/opt/mysql/data \
-DMYSQL_TCP_PORT = 3306 \
-DMYSQL_UNIX_ADDR =/tmp/mysqld. sock \
-DMYSQL_USER = mysql \
-DEXTRA_CHARSETS = all \
-DWITH_READLINE = 1 \
-DWITH_SSL = system \
-DWITH_EMBEDDED_SERVER = 1 \
-DENABLED_LOCAL_INFILE = 1 \
-DWITH_INNOBASE_STORAGE_ENGINE = 1
# Make; make install

2. Create a single machine that supports multiple instances
1) Go to the MySQL main directory
# Cd/opt/mysql/

2) Delete the default data directory
# Rm-rf data

3) create the required directory
# Mkdir etc tmp run log binlogs data data2
# Chown-R mysql: mysql tmp run log binlogs data data2

4) Create the my. cnf configuration file
# Vi etc/my. cnf

# This server may run 2 + separate instances
# So we use mysqld_multi to manage their services
[Mysqld_multi]
Mysqld =/opt/mysql/bin/mysqld_safe
Mysqladmin =/opt/mysql/bin/mysqladmin
Log =/opt/mysql/log/mysqld_multi.log
User = root # Used for stopping the server via mysqladmin
# Password =

# This is the general purpose database
# The locations are default
# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi
[Mysqld1]
Socket =/opt/mysql/run/mysqld. sock
Port = 3306
Pid-file =/opt/mysql/run/mysqld. pid
Datadir =/opt/mysql/data
Lc-messages-dir =/opt/mysql/share/english

# These support master-master replication
# Auto-increment = 4
# Auto-increment-offset = 1 # Since it is master 1
# Log-bin =/opt/mysql/binlogs/bin-log-mysqld1
# Log-bin-index =/opt/mysql/binlogs/bin-log-mysqld1.index
# Binlog-do-db ### Leave this blank if you want to control it on slave
# Max_binlog_size = 1024 M

# This is exlusively for mysqld2
# It is in 3307 with data directory/opt/mysql/data2
[Mysqld2]
Socket =/opt/mysql/run/mysqld. sock2
Port = 3307
Pid-file =/opt/mysql/run/mysqld. pid2
Datadir =/opt/mysql/data2
Lc-messages-dir =/opt/mysql/share/english

# Disable DNS lookups
# Skip-name-resolve

# These support master-slave replication
Log-bin =/opt/mysql/binlogs/bin-log-mysqld2
Log-bin-index =/opt/mysql/binlogs/bin-log-mysqld2.index
# Binlog-do-db ### Leave this blank if you want to control it on slave
Max_binlog_size = 1024 M

# Relay log settings
# Relay-log =/opt/mysql/log/relay-log-mysqld2
# Relay-log-index =/opt/mysql/log/relay-log-mysqld2.index
# Relay-log-space-limit = 4G

# Slow query log settings
# Log-slow-queries =/opt/mysql/log/slow-log-mysqld2
# Long_query_time = 2
# Log-queries-not-using-indexes

# The rest of the my. cnf is shared
# Here follows entries for some specific programs
# The MySQL server
[Mysqld]
Basedir =/opt/mysql
Tmpdir =/opt/mysql/tmp
Socket =/opt/mysql/run/mysqld. sock
Port = 3306
Pid-file =/opt/mysql/run/mysqld. pid
Datadir =/opt/mysql/data
Lc-messages-dir =/opt/mysql/share/english

Skip-external-locking
Key_buffer_size = 16 K
Max_allowed_packet = 1 M
Table_open_cache = 4
Sort_buffer_size = 64 K
Read_buffer_size = 256 K
Read_rnd_buffer_size = 256 K
Net_buffer_length = 2 K
Thread_stack = 128 K

# Increase the max connections
Max_connections = 200

# The expiration time for logs, including binlogs
Expire_logs_days = 14

# Set the character as utf8
Character-set-server = utf8
Collation-server = utf8_unicode_ci

# This is usually only needed when setting up chained replication
# Log-slave-updates

# Enable this to make replication more resilient against server crashes and restarts
# But can cause higher I/O on the server
# Sync_binlog = 1

# The server id, shocould be unique in same network
Server-id = 1

# Set this to force MySQL to use a particle engine/table-type for new tables
# This setting can still be overridden by specifying the engine explicitly
# In the create table statement
Default-storage-engine = INNODB

# Uncomment the following if you are using InnoDB tables
# Innodb_data_home_dir =/opt/mysql/data
# Innodb_data_file_path = ibdata1: 10 M: autoextend
# Innodb_log_group_home_dir =/opt/mysql/data
# You can set .. _ buffer_pool_size up to 50-80% of RAM
# But beware of setting memory usage too high
Innodb_buffer_pool_size = 16 M
Innodb_additional_mem_pool_size = 2 M
# Set... _ log_file_size to 25% of buffer pool size
Innodb_log_file_size = 5 M
Innodb_log_buffer_size = 8 M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 50

[Mysqldump]
Quick
Max_allowed_packet = 16 M

[Mysql]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 8 M
Sort_buffer_size = 8 M

[Mysqlhotcopy]
Interactive-timeout

[Mysql. server]
User = mysql

[Mysqld_safe]
Log-error =/opt/mysql/log/mysqld. log
Pid-file =/opt/mysql/run/mysqld. pid
Open-files-limit = 8192

[Client]
Default-character-set = utf8

5) modify the read and write permissions of my. cnf to prevent normal users from obtaining the MySQL password.
# Chown-R root: root/opt/mysql/etc
# Chmod 600/opt/mysql/etc/my. cnf

3. initialize the database
1) switch to the mysql user and go to the MySQL home directory.
# Su-mysql
# Cd/opt/mysql/

2) initialize the instance [mysqld1] and [mysqld2]
# Scripts/mysql_install_db -- basedir =/opt/mysql -- user = mysql -- datadir =/opt/mysql/data/
# Scripts/mysql_install_db -- basedir =/opt/mysql -- user = mysql -- datadir =/opt/mysql/data2/

3) return to root and create the mysqld_multi.server script.
# Exit
# Mkdir-p/opt/mysql/init. d
# Cp support-files/mysqld_multi.server/opt/mysql/init. d/
# Vi/opt/mysql/init. d/mysqld_multi.server

#! /Bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my. cnf file exists either in/etc/my. cnf or
#/Root/. my. cnf and has groups [mysqld_multi] and [mysqldN]. See
# Mysqld_multi documentation for detailed instructions.
#
# This script can be used as/etc/init. d/mysql. server
#
# Comments to support chkconfig on RedHat Linux
# Chkconfig: 2345 64 36
# Description: A very fast and reliable SQL database engine.
#
# Version 1.0
#

Basedir =/opt/mysql
Bindir =/opt/mysql/bin

Conf =/opt/mysql/etc/my. cnf
Export PATH = $ PATH: $ bindir

If test-x $ bindir/mysqld_multi
Then
Mysqld_multi = "$ bindir/mysqld_multi ";
Else
Echo "Can't execute $ bindir/mysqld_multi from dir $ basedir ";
Exit;
Fi

Case "$1" in
'Start ')
"$ Mysqld_multi" -- defaults-extra-file = $ conf start $2
;;
'Stop ')
"$ Mysqld_multi" -- defaults-extra-file = $ conf stop $2
;;
'Report ')
"$ Mysqld_multi" -- defaults-extra-file = $ conf report $2
;;
'Restart ')
"$ Mysqld_multi" -- defaults-extra-file = $ conf stop $2
"$ Mysqld_multi" -- defaults-extra-file = $ conf start $2
;;
*)
Echo "Usage: $0 {start | stop | report | restart}"> & 2
;;
Esac

4. Back up MySQL as a whole
# Cd/opt/
# Tar czvf mysql_5.5.19_full.tar.gz mysql5519/

After the dig command is completed, you can directly download mysql_5.5.19_full.tar.gz to other servers. After decompression, you can enable it directly.

5. Manage MySQL instances
1) Start the instance [mysqld1] and [mysqld2] simultaneously:
#/Opt/mysql/init. d/mysqld_multi.server start 1, 2

2) Check whether both MySQL instances have been successfully started.
# Netstat-lntp | grep mysqld

Tcp 0 0 0.0.0.0: 33060.0.0.0: * LISTEN 28752/mysqld
Tcp 0 0 0.0.0.0: 33070.0.0.0: * LISTEN 28756/mysqldDisable both the instance [mysqld1] and [mysqld2]:
#/Opt/mysql/init. d/mysqld_multi.server stop 1, 2

Start/close an instance only [mysqld1]:
#/Opt/mysql/init. d/mysqld_multi.server start 1
#/Opt/mysql/init. d/mysqld_multi.server stop 1

6. log on to the MySQL instance
After the instance [mysqld1] and [mysqld2] are started, log on to different instances as follows:

Log on to [mysqld1]:
#/Opt/mysql/bin/mysql-uroot-h127.0.0.1-P3306-p

Log on to [mysqld2]:
#/Opt/mysql/bin/mysql-uroot-h127.0.0.1-P3307-p

7. Other initialization settings
1) set the initial password for the root account of MySQL
#/Opt/mysql/bin/mysqladmin-u root-h127.0.0.1-P3306 password '2016'
#/Opt/mysql/bin/mysqladmin-u root-h127.0.0.1-P3307 password '2016'

2) modify the MySQL root Account password in the my. cnf configuration file
# Vim/opt/mysql/etc/my. cnf

User = root # Used for stopping the server via mysqladmin
Password = 1234563) delete an empty password account for an anonymous connection
Log on to the instance [mysqld1] and [mysqld2] respectively, and run the following command:
Mysql> use mysql; // select the system database mysql
Database changed
Mysql> select Host, User, Password from user; // view all users
+ ----------- + ------ + --------------------------------------------- +
| Host | User | Password |
+ ----------- + ------ + --------------------------------------------- +
| Localhost | root | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| Mysql-01 | root |
| 127.0.0.1 | root |
|: 1 | root |
| Localhost |
| Mysql-01 |
+ ----------- + ------ + --------------------------------------------- +
6 rows in set (0.00 sec)

Mysql> delete from user where password = ""; // delete an empty password account for an anonymous connection
Query OK, 5 rows affected (0.00 sec)

Mysql> flush privileges; // refresh the permission
Query OK, 0 rows affected (0.00 sec)

Mysql> select Host, User, Password from user; // check whether all users whose passwords are empty have been deleted.
+ ----------- + ------ + --------------------------------------------- +
| Host | User | Password |
+ ----------- + ------ + --------------------------------------------- +
| Localhost | root | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+ ----------- + ------ + --------------------------------------------- +
1 row in set (0.00 sec)

Mysql> exit;

Experience summary:
1. Use the source code to compile and install MySQL, so that we can easily organize the location of all MySQL-related files. At the same time, after the source code is compiled and installed, MySQL can be directly copied to other servers for running, this greatly facilitates our future migration, backup, and configuration of new servers;
2. This solution only implements two instances [mysqld1] and [mysqld2]. In fact, we can expand and implement [mysqld3], [mysqld4], [mysqld5]... and other instances;
3, MySQL comes with a few different configuration files, placed in the/opt/mysql/support-files directory, are my-huge.cnf, my-innodb-heavy-4G.cnf, my-large.cnf, my-medium.cnf, my-small.cnf, through the name, we can intuitively understand that they are configured for different servers;
4. When running multiple instances on a single machine, do not use mysql-hlocalhost or directly ignore the-h parameter to log on to the server, because if you use localhost or ignore the-h parameter, instead of specifying 127.0.0.1, even if the selected port is 3307, it will still log on to the 3306 instance, so try to avoid this confusion, and use 127.0.0.1 to bind the port or socket to log on.