transferred from: http://www.cnblogs.com/xuchenliang/p/6843990.html
1.MySQL Multi-Instance Introduction
1.1. What is MySQL multi-instanceMySQL 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 points1: 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: Save 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, resulting in other instances on the server service quality degradation;
1.3. Two ways to deploy MySQL multi-instanceThe first is to use multiple configuration files to launch 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 to use a separate configuration file for multiple instances with the official mysqld_multi. 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 EnvironmentRed Hat Enterprise Linux Server Release 6.4
2.2. Install the MySQL software version
2.2.1. Compile-free binary packagesMysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
2.3. Decompression and migrationTAR-XVF MYSQL-5.6.21-LINUX-GLIBC2.5-X86_64.TAR.GZMV Mysql-5.6.21-linux-glibc2.5-x86_64/usr/local/mysql
2.4. Close IptablesTemporary shutdown: Service iptables stop permanently closed: chkconfig iptables off
2.5. Turn off SELinuxVi/etc/sysconfig/selinux changes selinux to DISABLED, which is selinux=disabled
2.6. Create a MySQL userGroupadd-g mysqluseradd-u 27-g mysql mysqlid mysqluid=501 (mysql) gid=501 (MySQL) groups=501
2.7. Create a related directoryMkdir-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 PermissionsChown-r Mysql:mysql/data/mysql/chown-r mysql:mysql/usr/local/mysql/
2.9. Add Environment VariablesEcho ' Export path= $PATH:/usr/local/mysql/bin ' >>/etc/profile source/etc/profile
2.10. Copy the my.cnf file to the ETC directoryCp/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. Initializing 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 successfullyTwo "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]# lsauto.cnf ibdata1 IB_LOGFILE0 Ib_logfile1 MySQL mysql.pid performance_schema Test View 3307 database [[email protected] ~]# Cd/data/mysql/mysql_3307/data[[em AIL protected] data]# lsauto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mysql.pid performance_schema test
2.13. Setting the Startup fileCp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysql
2.14.mysqld_multi for multi-instance managementStart all instances:/usr/local/mysql/bin/mysqld_multi Start View All instance states:/usr/local/mysql/bin/mysqld_multi report launches a single instance:/usr/local /mysql/bin/mysqld_multi start 3306 Stop Single instance:/usr/local/mysql/bin/mysqld_multi Stop 3306 View 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 reportreporting MySQL Serversmysql server from group:mysqld3306 are Runningmysql server from group:mysqld3307 are RU Nning
2.15. View the startup process
2.16. Change the passwordMySQL root user initial password is empty, so 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-penter Password:
2.17. New User and authorizationIn 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 [email 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 message1: Install MySQL error checking for tgetent in-lncurses ... nochecking for tgetent in-lcurses ... nochecking for tgetent In-ltermcap. .. Nochecking for tgetent in-ltinfo ... nochecking for termcap functions Library ... configure:error:No curses/termcap libra Ry found reason: missing ncurses installation package workaround: Yum list|grep ncursesyum-y install Ncurses-develyum install Ncurses-devel2:.../depcomp: Line 571:exec:g++: Not FOUNDMAKE[1]: * * [MY_NEW.O] Error 127make[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 + + b Uilt-in type ' bool ' make[2]: * * [MY_NEW.O] Error 1make[2]: Leaving directory '/home/tools/mysql-5.0.22/mysys ' make[1]: * * * [all-recursive] Error 1make[1]: Leaving directory '/home/tools/mysql-5.0.22 ' make: * * * [ALL] Error 2 because gcc-c++ Is installed after configure, only need to 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=Mysqlinstalling MySQL system tables ... error:1136 Column Count doesn ' t match value count at row 1150414 7:15:56 [ERROR] aborting150414 7:15:56 [Warning] Forcing shutdown of 1 plugins150414 7:15:56 [Note]/usr/local/mysql//libexec/mysqld:shutdown Completeinstallation 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 comma nd line Tool/usr/local/mysql//bin/mysqlto Connect to the MySQL database and look at the grant Tables:shell>/usr/local /mysql//bin/mysql-u root mysqlmysql> show tablestry ' 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 athttp://www.mysql.com/. please consult the MySQL manual Section ' Problems running mysql_install_db ', and the manual section Thatdescribes Problems on your os. another information source is Themysql email archives available at http://lists.mysql.com/. Please check the above before mailing us! and remember, ifyou do mail us, you must use the/usr/local/mysql// Scripts/mysqlbug script! Reason: The original installation of MySQL information did not remove the clean solution: Delete the/var/lib/mysql directory
MySQL Multi-instance installation under Linux (GO)
