Mysql multi-instance installation in linux
1. MySQL multi-instance Introduction1. What is MySQL multi-instance?Multiple MySQL instances enable multiple service ports (such as 3306, 3307) on a single machine to run multiple MySQL service processes, use different sockets to listen on different service ports to provide their own services :;1.2.MySQL multi-instance features the following:1. effectively use server resources. When a single server resource is available, you can make full use of the remaining resources to provide more services. 2: Saving server resources 3: Resource preemption. When a service instance is highly concurrent or enables slow queries, more memory, CPU, and disk I/O resources are consumed, as a result, the quality of services provided by other instances on the server is reduced;1. 3. Two Methods for deploying mysql multi-instanceThe first is to use multiple configuration files to start different processes to implement multiple instances. This method has the advantages of simple logic and configuration, but is not easy to manage; the second method is to use a separate configuration file of mysqld_multi provided by the official website to implement multiple instances. In this way, the configuration of each instance is not very convenient, and the advantage is that it is easy to manage and centrally manage;. To install two databases in the same development environment, you must solve the following problems:
- The installation path of the configuration file cannot be the same
- The database directory cannot be the same
- The STARTUP script cannot have the same name.
- Ports cannot be the same
- The path for generating the socket file cannot be the same
2. Install and deploy Mysql multi-instance
2. 1. Deployment EnvironmentRed Hat Enterprise Linux Server release 6.4
2. Install the mysql software version
2.2.1. No compilation of Binary packagesMysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
2. 3. decompress and migrateTar-xvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gzmv/usr/local/mysql
2. 4. Disable iptablesTemporary shutdown: service iptables stop permanent shutdown: chkconfig iptables off
2. 5. Disable selinuxVi/etc/sysconfig/selinux change SELINUX to DISABLED, that is, SELINUX = DISABLED
2. 6. Create a mysql userGroupadd-g 27 mysqluseradd-u 27-g mysql mysqlid mysqluid = 501 (mysql) gid = 501 (mysql) groups = 501 (mysql)
2. 7. Create related directoriesMkdir-p/data/mysql/{mysql_3306, mysql_3307} mkdir/data/mysql/mysql_3306/{data, log, tmp} mkdir/data/mysql/mysql_3307/{data, log, tmp}
. 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 directory.Cp/usr/local/mysql/support-files/my-default.cnf/etc/my. cnf
2. 11. modify my. cnf (in a 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/logs log-output = file slow_query_log = long_query_time = 1 slow_query_log_file =/data/mysql/mysql_3306/log/slow. log-error =/data/mysql/mysql_3306/log/error. log binlog_format = mixed log-bin =/data/mysql/mysql_3306/log/logs [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-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. initialize database 3306/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. initialize database 3307/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 whether database Initialization is successfulTwo "OK"
2.12.4. Check whether the database Initialization is successful (2)View database 3306 [root @ mysql ~] # Cd/data/mysql/mysql_3306/data [root @ mysql data] # lsauto. cnf ibdata1 ib_logfile0 ib_logfile1 mysql. pid performance_schema test view 3307 database [root @ mysql ~] # Cd/data/mysql/mysql_3307/data [root @ mysql data] # lsauto. cnf ibdata1 ib_logfile0 ib_logfile1 mysql. pid performance_schema test
2. 13. Set 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 statuses:/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 view the status of a single instance: /usr/local/mysql/bin/mysqld_multi report 3306
2.14.1. Start all instances[Root @ mysql ~] #/Usr/local/mysql/bin/mysqld_multi start [root @ mysql ~] #/Usr/local/mysql/bin/mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is running
2. 15. view the Startup Process
2. 16. Change the passwordThe initial password of the root user of mysql is empty, so you need to log on to mysql to change the password. The following uses 3306 as an example: mysql-S/tmp/mysql_3306.sock set password for root @ 'localhost' = password ('000000'); flush privileges; next login: [root @ mysql ~] # Mysql-S/tmp/mysql_3306.sock-pEnter password:
2. 17. Create a user and authorize the userGenerally, a new user is required to create a database for program connection. Such users only need the insert, update, delete, and select permissions. Add a new user and grant the following permissions: grant select, delete, update, insert on *. * to admin @ '192. 192.% 'identified by '000000'; flush privileges
2. 18. log on to the database with external software
2. 19. Test successful
3. common error messages for source code Installation1: mysql installation 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 library found cause: ncurses installation package is missing solution: yum list | grep ncursesyum-y install ncurses-develyum install ncurses-devel2 :... /depcomp: line 571: exec: g ++: not foundmake [1]: *** [My_new.o] Error 127 make [1]: Leaving directory '/home/justme/software/mysql-5.1.30/mysys 'make: * ** [all-recursive] Error 1 solution: 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 because gcc-c ++ is installed after configure, you only need to re-configure and then compile make. 4: Initialize the Database Error Reporting phenomenon: root @ mysql 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: shell>/usr/local/mysql // libexec/mysqld -- skip-grant & and use the command 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 have problems with paths. using -- l Oggives you a log in/var/lib/mysql that may be helpful. the latest information about MySQL is 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 are theMySQL email archives available at http://lists.mysql.com/.Please check all of the above befo Re mailing us! And remember, ifyou do mail us, you MUST use the/usr/local/mysql // scripts/mysqlbug script! Cause: the original installed mysql information is not deleted. Solution: Delete the/var/lib/mysql directory.
