MySQL5.5.22 single-host multi-instance configuration practice _ MySQL

MySQL5.5.22 single-host multi-instance configuration practices MySQL 5.5.22 Single-host, multi-instance configuration practices

Background/requirement:

Compile and install a MySQL database of version 5.5 or above through source code on a server. store all configuration files and data in/opt/mysql, it facilitates fast migration, overall backup, and fast replication in the future;

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; both instances adopt InnoDB as the default storage engine, character encoding adopts UTF-8; both instances adopt the same performance optimization configuration parameters;

Practice/solution:

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 steps: Environment: RedHat 5.4 64-bit 1. Compile and install MySQL 1. install cmake

MySQL 5.5 and later versions have been replaced by the cmake tool./configure compilation and configuration method.

Therefore, we must first compile and install the cmake tool in the system source code.

# Wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz

# Tar zxvf cmake-2.8.4.tar.gz

# Cd cmake-2.8.4

#./Configure
# Make
# Make install

2. make sure that the following system software packages are installed:

Run the rpm-qa | grep name command to verify whether all of the following software packages have been installed.

Gcc * gcc-c ++ * autoconf * automake * zlib * libxml * ncurses-devel * libgcrypt * libtool *

If the related software package is missing, you can install it online using yum-y install, or find it directly from the system installation disc and install it using rpm-ivh.

3. system settings before installation

Create the mysql installation directory and data storage directory
# Mkdir/opt/mysql
# Mkdir/opt/mysql/data

Create users and user groups
# Groupadd mysql
# Useradd-g mysql

Grant the data storage directory permission
# Chown mysql: mysql-R/opt/mysql/data

4. start compiling and installing MySQL

Download the software package from http://www.mysql.com/downloads/mysqlofficial website or your sohuimage, such as the latest MySQL 5.5.22.
# Wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.22.tar.gz

# Tar zxvf mysql-5.5.22.tar.gz

# Cd mysql-5.5.22
# 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

The percentage of progress can be seen in make and make install, which is better than configure.

2. create configuration files that support multiple instances

Go to the MySQL main directory
# Cd/opt/mysql/

Delete the default data directory
# Rm-rf data

Create the required Directory
# Mkdir etc tmp init. d run log binlogs data data2
# Chown-R mysql: mysql tmp run init. d log binlogs data data2

Create my. cnf configuration file
# Vim 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/mysqld/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

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

Switch to mysql User
# Su-mysql

Go to the MySQL main directory
# Cd/opt/mysql/

Initialize an instance [mysqld1]
# Scripts/mysql_install_db -- basedir =/opt/mysql -- user = mysql -- datadir =/opt/mysql/data/

Initialize an instance [mysqld2]
# Scripts/mysql_install_db -- basedir =/opt/mysql -- user = mysql -- datadir =/opt/mysql/data2/

Return to root
# Exit

Create mysqld_multi.server script
# Cp support-files/mysqld_multi.server/opt/mysql/init. d/

# Vim/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

IV. overall MySQL backup

# Cd/opt/
# Tar czvf mysql_5.22_full.tar.gz mysql/

After the dig command is complete, directly remove mysql_5.5.20_full.tar.gz to other servers. after decompression, you can enable it directly.

V. manage MySQL instances

Start/close the instance [mysqld1] and [mysqld2] at the same time:
#/Opt/mysql/init. d/mysqld_multi.server start 1, 2

Then, we can see that both MySQL instances have been successfully started.
# Netstat-lntp | grep mysqld

Disable 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

VII. other initialization settings 1. set the initial password for the MySQL root account

#/Opt/mysql/bin/mysqladmin-u root-h127.0.0.1-P3306 password 'new-password'
#/Opt/mysql/bin/mysqladmin-u root-h127.0.0.1-P3307 password 'new-password'

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 = new-password

3. 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
Mysql> select Host, User, Password from user; // view all users
Mysql> delete from user where password = "";
Mysql> flush privileges; // refresh the permission
Mysql> select Host, User, Password from user; // check whether all users whose passwords are empty have been deleted.
Mysql> exit;

VIII. experience summary

1. using the source code to compile and install MySQL may take a long time for the first time, but it is very worthwhile because we can organize all the MySQL-related files by ourselves; mySQL after source code compilation and installation can be directly copied to other servers for running, greatly facilitating our future migration, backup, and new server configuration;

2. this article only uses two instances [mysqld1] and [mysqld2] for example. In fact, we can implement [mysqld3], [mysqld4], [mysqld5]... and other instances;

3. mySQL comes with several different configuration files placed under the/opt/mysql/support-files directory, which 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 for different server configuration, this article only a little about InnoDB configuration, is taken from the my-small.cnf, because I set it on a virtual machine; in a production environment, we can optimize the server by referring to some of the parameter configurations in the my-huge.cnf or my-innodb-heavy-4G.cnf;

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. this is a MySQL bug, that is, 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 3306. therefore, try to avoid such confusion, use 127.0.0.1 to bind a port or socket to log on;

This article is from the "xmshuiyong 'blog" blog, please be sure to keep this source http://xmshuiyong.blog.51cto.com/1980172/1113498