Multi-instance and slave of single host Mysqld_multi in binary package

Recently, there is only one server to deploy the database due to business requirements, but to ensure that the data is backed up in real time so that the data analysis backup uses Mysqld_multi to implement multiple instances of MySQL and slave on the same host. In this way, you can add new slave when you do not affect the service on the line to make a backup to create slave server, as for the master and slave synchronization settings have done before, here will not do more than repeat, need to see:/http jim123.blog.51cto.com/4763600/1862808.

Let's talk about Mysqld_multi, which can be used to listen to different sockets and TCP/IP ports to connect the MYSQLD process, this can be found in the MySQL official web site detailed information, here is a simple implementation of the process and some points of attention. Because MySQL is a shared my.cnf file, it is good to use Mysqld_multi as long as you add the appropriate configuration in MY.CNF. First, the configuration of the MYSLD is copied down, and the configuration file mysqld the configuration name [mysqld] to [Mysqld1], and then add [Mysqld2] as the slave configuration file, configuration can use Mysqld1, But there are a few places to change, to change the place as follows:

[Mysqld2]server-id = * This is very important, can not be the same as master Idport = 3307socket =/tmp/mysql3307.socklog=slow-queries=/da Ta/mysqldata3307/localhost-slow.logdatadir =/data/mysqldata3307# The main change is the port, IP, DataDir path and socket, and the other is to add slave corresponding configuration [MYSQLD_MULTI]MYSQLD =/usr/local/mysql/bin/mysqld_safemysqladmin =/usr/local/mysql/bin/mysqladmin# Added on Mysqld_ Safe and mysqladmin path, which facilitates the startup and shutdown of appropriate instances of MYSQLD when using Mysqld_multi

Initializes the new instance in the scripts directory under Basedir after modifying the my.cnf configuration file.

[Email protected] scripts]#/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/mysqldata3307--user= MySQL--skip-name-resolve

Then stop the mysqld process and use Mysqld_multi to manage the multi-instance mysqld, which is simple:

Mysqld_multi [OPTIONS] {start|stop|report} [Gnr,gnr,gnr ...] or Mysqld_multi [OPTIONS] {start|stop|report} [Gnr-gnr,gnr,gnr-gnr,...] Start: [[email protected] scripts]# mysqld_multi start 1-2 or [[email protected] scripts]# mysqld_multi start 1[[email protected ] scripts]# Mysqld_multi start 2

Usually log in to the interface of MySQL using the specified socket file login (http://jim123.blog.51cto.com/4763600/1855645), so that mysqld multi-instance deployment is complete, Next is the MYSQLD multi-instance of the slave configuration, to note that the same host on the IP is the same as the master-slave difference is their port and the socket is different, so on the master using the GRANT statement to configure the slave account when the host will be specified, Otherwise it will appear error 1133 (42000): Can ' t find any matching row in the user table errors, corresponding to the slave on the use of the Change master to statement will be added Master_port = 3306 is used to differentiate, so that the master-slave of multiple instances under the same host is deployed.

This article from the "Technical essay" blog, reproduced please contact the author!

Multi-instance and slave of single host Mysqld_multi in binary package