MySQL Multi-instance installation

1.MySQL Multi-Instance Introduction 1.1. What is MySQL multi-instance

MySQL multi-instance is on a machine to open a number of different service ports (such as: 3306,3307), running multiple MySQL service process, through different sockets to listen to different service ports to provide their own services:;

1.2.MySQL multi-instance features have the following points

1: Effective use of server resources, when a single server resources are left, you can make full use of the remaining resources to provide more services.

2: Conserve server resources

3: Resource preemption problem, when a service instance service is high concurrency or slow query, will consume more memory, CPU, disk IO resources, causing other instances on the server to provide service quality degradation;

1.3. Two ways to deploy MySQL multi-instance

The first is to use multiple configuration files to start different processes to implement multiple instances, the advantages of this method is simple, simple configuration, the disadvantage is not easy to manage;

The second is through the official Mysqld_multi with a separate configuration file to achieve multi-instance, this way to customize the configuration of each instance is not too much, the advantage is easy to manage, centralized management;

1.4. Installing two databases in the same development environment must address the following issues

• Configuration file installation path cannot be the same
• The database directory cannot be the same
• Startup script cannot have the same name
• Ports cannot be the same
• Socket file generation path cannot be the same

2.Mysql Multi-instance installation deployment 2.1. Deployment environment

Red Hat Enterprise Linux Server Release 6.4

2.2. Install MySQL software version 2.2.1. Compile-free binary package

Mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz

2.3. Decompression and migration

TAR-XVF mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz

MV Mysql-5.6.21-linux-glibc2.5-x86_64/usr/local/mysql

2.4. Close Iptables

Temporary shutdown: Service iptables stop

Permanently closed: Chkconfig iptables off

2.5. Turn off SELinux

Vi/etc/sysconfig/selinux

Change SELinux to DISABLED, which is selinux=disabled

2.6. Create a MySQL user

Groupadd-g MySQL

Useradd-u 27-g MySQL MySQL

ID MySQL

uid=501 (MySQL) gid=501 (MySQL) groups=501 (MySQL)

2.7. Create a related directory

Mkdir-p/data/mysql/{mysql_3306,mysql_3307}

mkdir/data/mysql/mysql_3306/{data,log,tmp}

mkdir/data/mysql/mysql_3307/{data,log,tmp}

2.8. Change directory Permissions

Chown-r mysql:mysql/data/mysql/

Chown-r mysql:mysql/usr/local/mysql/

2.9. Add Environment variables

Echo ' Export path= $PATH:/usr/local/mysql/bin ' >>/etc/profile

Source/etc/profile

2.10. Copy the my.cnf file to the ETC directory

Cp/usr/local/mysql/support-files/my-default.cnf/etc/my.cnf

2.11. Modify MY.CNF (can be modified in one file)

[Client]

port=3306

Socket=/tmp/mysql.sock

[Mysqld_multi]

Mysqld =/usr/local/mysql/bin/mysqld_safe

Mysqladmin =/usr/local/mysql/bin/mysqladmin

Log =/data/mysql/mysqld_multi.log

[Mysqld]

User=mysql

Basedir =/usr/local/mysql

Sql_mode=no_engine_substitution,strict_trans_tables

[mysqld3306]

Mysqld=mysqld

Mysqladmin=mysqladmin

Datadir=/data/mysql/mysql_3306/data

port=3306

server_id=3306

Socket=/tmp/mysql_3306.sock

Log-output=file

Slow_query_log = 1

Long_query_time = 1

Slow_query_log_file =/data/mysql/mysql_3306/log/slow.log

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

Binlog_format = Mixed

Log-bin =/data/mysql/mysql_3306/log/mysql3306_bin

[mysqld3307]

Mysqld=mysqld

Mysqladmin=mysqladmin

Datadir=/data/mysql/mysql_3307/data

port=3307

server_id=3307

Socket=/tmp/mysql_3307.sock

Log-output=file

Slow_query_log = 1

Long_query_time = 1

Slow_query_log_file =/data/mysql/mysql_3307/log/slow.log

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

Binlog_format = Mixed

Log-bin =/data/mysql/mysql_3307/log/mysql3307_bin

2.12. Initialize the database 2.12.1. Initializing the 3306 database

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql/--datadir=/data/mysql/mysql_3306/data-- Defaults-file=/etc/my.cnf

2.12.2. Initializing the 3307 database

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql/--datadir=/data/mysql/mysql_3307/data-- Defaults-file=/etc/my.cnf

2.12.3. Check that the database is initialized successfully

Two "OK" appears

2.12.4. To see if the database was initialized successfully (2)

View 3306 Database

[Email protected] ~]# Cd/data/mysql/mysql_3306/data

[[email protected] data]# ls

AUTO.CNF ibdata1 ib_logfile0 ib_logfile1 mysql mysql.pid performance_schema test

View 3307 Database

[Email protected] ~]# Cd/data/mysql/mysql_3307/data

[[email protected] data]# ls

AUTO.CNF ibdata1 ib_logfile0 ib_logfile1 mysql mysql.pid performance_schema test

2.13. Setting the Startup file

Cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysql

2.14.mysqld_multi for multi-instance management

Start all instances:/usr/local/mysql/bin/mysqld_multi start

View all instance Status:/usr/local/mysql/bin/mysqld_multi report

Start a single instance:/usr/local/mysql/bin/mysqld_multi start 3306

Stop a single instance:/usr/local/mysql/bin/mysqld_multi Stop 3306

To view a single instance status:/usr/local/mysql/bin/mysqld_multi report 3306

2.14.1. Start all instances

[[email protected] ~]#/usr/local/mysql/bin/mysqld_multi start

[Email protected] ~]#/usr/local/mysql/bin/mysqld_multi report

Reporting MySQL Servers

MySQL server from group:mysqld3306 is running

MySQL server from group:mysqld3307 is running

2.15. View the startup process

2.16. Change the password

MySQL root user initial password is empty, so you need to log in to MySQL to change the password, the following 3306 for example:

Mysql-s/tmp/mysql_3306.sock

Set password for [email protected] ' localhost ' =password (' 123456 ');

Flush privileges;

Next Login:

[Email protected] ~]# mysql-s/tmp/mysql_3306.sock-p

Enter Password:

2.17. New User and authorization

In general, new databases require a new user to connect to the program, which requires only insert, UPDATE, delete, select permissions.

Add a user and authorize the following:

Grant Select,delete,update,insert on * * to [e-mail protected] ' 192.168.0.% ' identified by ' 123456 ';

Flush Privileges

2.18. External Software Login Database

2.19. Test success

3. Source code installation Common error message

1: Install MySQL Error

Checking for tgetent in-lncurses ... no

Checking for tgetent in-lcurses ... no

Checking for tgetent in-ltermcap ... no

Checking for tgetent in-ltinfo ... no

Checking for termcap functions Library ... configure:error:No curses/termcap Library found

Reason:

Missing ncurses installation package

Workaround:

Yum List|grep ncurses

Yum-y Install Ncurses-devel

Yum Install Ncurses-devel

2:.../depcomp:line 571:exec:g++: Not Found

MAKE[1]: * * [MY_NEW.O] Error 127

MAKE[1]: Leaving directory '/home/justme/software/mysql-5.1.30/mysys '

Make: * * [all-recursive] Error 1

Workaround:

Yum Install gcc-c++

3:.../include/my_global.h:909:error:redeclaration of C + + built-in type ' bool '

MAKE[2]: * * * [MY_NEW.O] Error 1

MAKE[2]: Leaving directory '/home/tools/mysql-5.0.22/mysys '

MAKE[1]: * * * [all-recursive] Error 1

MAKE[1]: Leaving directory '/home/tools/mysql-5.0.22 '

Make: * * * [ALL] Error 2

This is because gcc-c++ is installed after configure, just re-configure and then compile make.

4: Initialize Database error

Error phenomenon:

[Email protected] mysql-6.0.11-alpha]# scripts/mysql_install_db--basedir=/usr/local/mysql/--user=mysql

Installing MySQL system tables ...

error:1136 Column Count doesn ' t match value count at row 1

150414 7:15:56 [ERROR] Aborting

150414 7:15:56 [Warning] forcing shutdown of 1 plugins

150414 7:15:56 [Note]/usr/local/mysql//libexec/mysqld:shutdown complete

Installation of system tables failed! Examine the logs in

/var/lib/mysql for more information.

You can try to start the mysqld daemon with:

Shell>/usr/local/mysql//libexec/mysqld--skip-grant &

and use the command line Tool/usr/local/mysql//bin/mysql

To connect to the MySQL database and look at the grant tables:

shell>/usr/local/mysql//bin/mysql-u Root MySQL

Mysql> Show Tables

Try ' mysqld--help ' if you had problems with paths. Using--log

Gives you a log in/var/lib/mysql the May helpful.

The latest information about MySQL was available on the Web at

http://www.mysql.com/. Consult the MySQL manual section

' Problems running mysql_install_db ', and the manual section that

Describes problems on your OS. Another information source is the

MySQL Email Archives available at http://lists.mysql.com/.

Please check the above before mailing us! And remember, if

You do mail us, you must use The/usr/local/mysql//scripts/mysqlbug script!

Reason:

The original installed MySQL information is not removed cleanly

Workaround:

Delete/var/lib/mysql Directory

MySQL Multi-instance installation