MySQL multi-master one from hot backup in Linux

In the previous article http://5148737.blog.51cto.com/5138737/1570274 how to make a MySQL hot backup in Linux, belonging to a master one from the situation, but most of the situation is more than one host backup to a slave, save equipment.

Looking for a long time, only to find a solution, write down, because really forget quickly.

The configuration of the host is the same, modify the configuration file, add the configuration of Server-id, reboot, create the slave user and authorize, view the master status, to this host end.

The next is from the machine, but also mainly from the machine.

Because we need to do a change master operation on the database, if there is only one DB instance, the change master setting parameter will be overwritten by the latter, so the question is, how to create multiple DB instances.

To create multiple DB instances for a slave machine

2. Modify slave configuration file
   

   Using mysqld_multi The method, mainly modify the configuration file/etc/my.cnf, specific mysqld_multi usage of their own degrees Niang bar, I here directly posted my configuration.

# for advice on how to change settings please see# http:// dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html[mysqld_multi]mysqld      = /usr/bin/mysqld_safeuser       = rootpassword    = chinacache.gzlog = /var/lib/mysql/multi.log    <!--error log print at   -->[mysqld3305]     <!--module name, MYSQLDXX&NBSP;&NBSP;XX only for digital  -- >port             = 3305        <!--specified port  -->log-error=/var/lib/mysql3305/mysqld-error.log   <!- -Specify error log  -->pid-file=/var/lib/mysql3305/mysqld.pid           <!--designation pid -->datadir=/var/lib/mysql3305                        <!--Specify basedir -->socket=/var/ lib/mysql3305/mysql.sock            <!-- Specify basedir -->server-id       = 195                             <!--designation server-id-->log-bin=/var/lib/mysql3305/ mysqld-relay-binreplicate-do-db=cdnquality                       <!--The database that needs to be backed up in the specified host  -->user        = mysql[mysqld3306]                              <!--Multiple DB instances to multiple modules  -->port            = 3306log-error=/var/lib/mysql3306/ mysqld-error.logpid-file=/var/lib/mysql3306/mysqld.piddatadir=/var/lib/mysql3306socket=/var/lib/mysql3306/ mysql.sockserver-id       = 196log-bin=/var/lib/mysql3306/ Mysqld-relay-binreplicate-do-db=vbumnguser       = mysql

2. Create multiple DB instances

mysql_install_db--datadir=/var/lib/mysql3305--user=mysqlmysql_install_db--datadir=/var/lib/mysql3306--user= Mysql

Grant MySQL user permissions to the database

Chown-r Mysql/var/lib/mysql3305chown-r mysql/var/lib/mysql3306

3. Start multiple DB instances

The boot here is not started with a regular/etc/init.d/mysqld start, it needs to be started by Mysqld_multi

Mysqld_multi--defaults-file=/etc/my.cnf Start 3306//3306 the module name in my.cnf corresponds to Mysqld_multi--defaults-file=/etc/my.cnf Start 3305//can view the boot status netstat-ntlp|grep MySQL

PS: If you can see every database instance that starts up here, it's almost done.

4. Enter each DB instance separately for the change master operation

To enter a different database, specify port or sock

Mysql-uroot-p-P 3305-s/var/lib/mysql3305/mysql.sock Copy the database that needs to be backed up from the host to change the database master configuration starts slave start slave view slave status   Show slave status\gmysql-uroot-p-P 3306-s/var/lib/mysql3306/mysql.sock Copy the database that needs to be backed up from the host to change the database to the master configuration start slave Start slave view slave status show slave status\g

PS: After entering different database instances, the steps of the operation are the same, you can refer to the previous one from the case of a master

This article is from the "Bulajunjun" blog, make sure to keep this source http://5148737.blog.51cto.com/5138737/1570305

MySQL multi-master one from hot backup in Linux