For more blog posts, please note: Children without umbrellas must work hard (www.xuchanggang.cn). In our production environment, few dbservers are mysql instances, from the perspective of cost saving and full use of resources,
For more blog posts, please note: Children without umbrellas must work hard (www.xuchanggang.cn). In our production environment, few dbservers are mysql instances, from the perspective of cost saving and full use of resources,
For more blog posts, please note: Children without umbrellas must try to run ()
In our production environment, a few db servers have one mysql instance. From the perspective of cost saving and full use of resources, we tend to be in the master-slave environment, install Multiple mysql instances on the slave database server to synchronize the corresponding instances of the master database server.
1. mysql multi-instance control principle:
Multiple instances use Mysqld_multi to manage multiple mysqld processes that listen to different unix sets of byte files and TCP/IP ports, including start, stop, and status.
The default mysqld_multi is to read my. data in [mysqld/N] In cnf must be a positive integer. if you write a file separately, you can use the -- config-file parameter to specify the read configfile.
Optionusage of mysqld_multi: myysqld_multi [optinos] {start | stop | report} [GNR [, GNR] ..] option: start | stop | reportGNR indicates N in [msyqld/N]. The GNR cannot contain spaces or tabs.
Mysqld_multi start 10 is to start 10 instances in my. cnf; mysqld_multi start-6 is to start 1, 4 to 6 instances.
If no parameter is added, all instances are started by default.
Log on to the session and specify the socket file corresponding to the mysq instance: mysql-uuser-ppassword-S sockpath
2. Next we will build a DB server to start two mysql instances [master-slave synchronization will be set up in the next article]
(1). By default, I have installed a mysql instance on this DB server. [I am using the source code to install mysql. You can use RPM, binary, and source code to install a mysql instance.]
Database Installation Directory:/usr/local/mysql
3306 instance data storage directory:/home/mysql/data
(2) create a data storage directory for the second mysql instance named data_3307.
[Root @ client100 mysql] # mkdir-p/home/mysql/data_3307 # modify the owner of the data directory [root @ client100 mysql] # chown mysql. -R/home/mysql/data_3307
(3) edit the database configuration file:/etc/my. cnf
[Root @ client100 mysql] # vim/etc/my. cnf [mysqld_multi] # specify the command path mysqld =/usr/local/mysql/bin/mysqld_safemysqladmin =/usr/local/mysql/bin/mysqladmin # specify the user, used to start and close mysql services. [use root users as much as possible here. By default, root users have the permissions, remember to have the same root password in the two instances] user = rootpassword = kongzhong # specify the Error log Path log =/usr/local/mysql/multi. log [mysqld3306] basedir =/usr/local/mysqldatadir =/home/mysql/datasocket =/usr/local/mysql/mysqld3306.sockpid-file =/home/mysql/data/mysql3306.pidgeneral _ log = 1general_log_file =/home/mysql/data/mysql3306.logport = 3306server_id = 100 user = bytes = 128Msort_buffer_size = bytes = 1Mnet_buffer_length = bytes # bytes = bytes 1innodb_flush_method = O_DIRECT # connector = utf8collation-server = utf8_general_ci # LOGlog_error =/home/mysql/data/mysql-error.loglong_query_time = connector =/home/mysql/data/mysql-slow.log # connector = truemax_connections = 500open_files_limit = 65535sql_mode = NO_ENGINE_SUBSTITUTION, export [mysqld3307] basedir =/usr/local/mysqldatadir =/home/mysql/data_3307socket =/usr/local/mysql/export-file =/home/mysql/data_3307/mysql3307.pidgeneral _ log = 1general_log_file =/home/mysql/data_3307/mysql3307.logport = 3307server_id = 101 user = login = 128Msort_buffer_size = login # login = connector = O_DIRECT # connector = utf8collation-server = utf8_general_ci # LOGlog_error =/home/mysql/data_3307/mysql-error.loglong_query_time = connector =/home/mysql/data_3307/mysql-slow.log # connector = truemax_connections = 500open_files_limit = 65535sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
(4) initialize the database for the second instance:
# Go To The database installation directory [root @ client100 mysql] # cd/usr/local/mysql/scripts/# Run the initialization script to specify the data directory/user [root @ client100 scripts] #. /mysql_install_db -- basedir =/usr/local/mysql -- datadir =/home/mysql/data_3307 -- user = mysql
(5) use the mysqld_multi command to start the corresponding instance [if my. cnf is not in the default position, specify the location -- defaults-extra-file = *****]
# Start the corresponding instance [root @ client100 ~] # Mysqld_multi start 3306 [root @ client100 ~] # Mysqld_multi start 3307 # use the following command to check whether the mysql instance process starts [root @ client100 ~] # Ps-ef | grep mysql # Use netstat to check whether two mysql instance ports are enabled. [if you see that both ports are listened, it indicates that the ports are set up normally.] [root @ client100 ~] # Netstat-ntlp | grep mysql # use the following two commands to log on to the corresponding instance database [root @ client100 ~] # Mysql-uroot-S/usr/local/mysql/mysqld3306.sock-p [root @ client100 ~] # Mysql-uroot-S/usr/local/mysql/mysqld3307.sock-p # After logging in, you can view the parameters and data storage directory, to confirm whether to log on to the instance mysql> show variables like '% datadir %' # Close the instance [root @ client100 ~] # Mysqld_multi stop 3306 [root @ client100 ~] # Mysqld_multi stop 3307 # If you cannot close the above command, use the following method to disable [root @ client100 ~] # Mysqladmin-uroot-p-S/usr/local/mysql/mysqld3307.sock shutdown [root @ client100 ~] # Mysqladmin-uroot-p-S/usr/local/mysql/mysqld3306.sock shutdown # Some friends may be unable to close an instance when using mysqld_multi stop, here is/etc/my. role of user/password in cnf # perform the following operations: # If the user you specified is not a root user, it is best to have all the directory permissions of mysql. Otherwise, insufficient permissions will be reported, therefore, we recommend that you use mysql or root user # if you are a root user, because root has all permissions, you only need to set the passwords of both instances to be the same # For mysql, during compilation and installation, you must allow this user to log on, that is, do not apply the nologin restriction # and grant the shutdown permission after logging on to the instance database, set the password mysql> grant shutdown on *. * TO 'mysql' @ 'localhost' identified by 'kongzhong '; mysql> flush privileges # Close with mysqld_multi stop now. [in this case, we recommend that you use root].
This article is from the blog "children without umbrellas must be running hard". Please keep this source