Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "MySQL multi-master and one-slave backup method". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

For load balancing, we have done one master, one slave, one master and multiple slaves, so many masters and slaves are basically used in the backup strategy. We know that macroscopic "one master and many slaves" is actually adding several servers, and microcosmic to each server is the realization of one master and one slave. The implementation of multi-master and one-slave is also based on one master and one slave, and the difference lies in the way of configuration and start / stop.

Master server outline

Let's take a closer look at the slave server's configuration file / etc/my.cnf, which has two main parts: [mysqld_multi] and [mysqld] modules.

[mysqld_multi]

Mysqld = / usr/local/mysql/bin/mysqld_safe

Mysqladmin = / usr/local/mysql/bin/mysqladmin

User = root

Password = 123456

# The MySQL server

[mysqld1]

Port = 3306

Socket = / tmp/mysql.sock1

Pid-file = / data/dbfile/file-1.pid

Log-bin=mysql-bin-1

Binlog_format=mixed

Log-error=/data/dbfile/err-1.log

Server-id = 1

Master-host=192.168.1.1

Master-user=testrep

Master-password=rep123

Master-port=3306

Master-connect-retry=30

Master-info-file=master-1.info

Relay-log=relay-bin-2

Relay-log-index=relay-bin-1.index

Relay-log-info-file=relay-log-1.info

Replicate-do-db=mytestdb

Innodb_data_home_dir = / data/dbfile/1test

Innodb_data_file_path=ibdata1:10M:autoextend

Innodb_log_group_home_dir = / data/dbfile/1test

Log-slave-updates

[mysqld2]

Port = 3307

Socket = / tmp/mysql.sock2

Pid-file = / data/dbfile/file-2.pid

Log-bin=mysql-bin-2

Binlog_format=mixed

Log-error=/data/dbfile/err-2.log

Server-id = 2

Master-host=192.168.1.1

Master-user=testrep

Master-password=rep123

Master-port=3306

Master-connect-retry=30

Master-info-file=master-2.info

Relay-log=relay-bin-2

Relay-log-index=relay-bin-2.index

Relay-log-info-file=relay-log-2.info

Replicate-do-db=mytestdb

Innodb_data_home_dir = / data/dbfile/2test

Innodb_data_file_path=ibdata2:10M:autoextend

Innodb_log_group_home_dir = / data/dbfile/2test

Log-slave-updates

[mysqld_multi] Module description-this module is required, otherwise the mysqld multi-instance service will not work

Four of the options specify the startup and management location of mysqld, and the superuser password is required by mysqld_multi.

[mysqld] module-- this example should be familiar to all of you.

Startup part: [mysqld2] is a copy of [mysqld1], different from the port and file of [mysqld1].

Slave section: do not repeat the server-id and other options. Each [mysqld] Slave has its own attributes and files.

It is worth mentioning that the InnoDB part. Since the ibdata and ib_logfile* of each master server are basically the default settings, the Replicate will cross-overwrite after coming over, which will lead to confusion of slave server data. We can configure a separate storage location for each instance of the InnoDB file to solve this problem

Start / stop multiple instances

# / usr/local/mysql/bin/mysqld_multi start 1,2

# / usr/local/mysql/bin/mysqld_multi stop 1,2

Check to see if each instance is up.

# ps aux | grep mysq l

We can see four related processes, and each instance has two processes

Visit mysql

# mysql-uroot-p123456-P3306

# mysql-uroot-p123456-P3307

After logging in, we found that show database will list all the databases. It doesn't matter, only the data under your own port can be accessed, and those on other ports will prompt table does'nt exist! [@ more@]

For multiple mysqld instance processes, we recommend using services to manage them, just like service mysqld start

Copy the / data/source/mysql-5.1.38/support-files/mysqld_multi.server.sh file in the source package to the startup directory

# cp / data/source/mysql-5.1.38/support-files/mysqld_multi.server / etc/init.d/mysqld_multi

# chmod + x / etc/init.d/mysqld_multi

# service mysqld_multi start

There are many ways to start / stop

# / usr/local/mysql/bin/mysqld_multi-- config-file=/etc/my.cnf-- mysqld=/usr/local/mysql/bin/mysqld_safe start 1pm 2

# / usr/local/mysql/bin/mysqladmin shutdown 1,2

"MySQL multi-master one-slave backup method" content is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.