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

Today, I will talk to you about how to build the master-slave database on mysql on windows and what the problem is. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

According to the construction of the previous web project, we need to build the master-slave database. Here, we build the master-slave structure mysql on the windows server, and here we record the local simulation of the construction process.

Installing on windows is still a little different from linux, if you don't pay attention to it, you will waste a lot of time.

Download the installation package and install

The installation package downloaded by https://dev.mysql.com/downloads/file/?id=469273 is Archive

Need to manually install and establish services and initialize the data directory, you can search the Internet for many specific installation methods.

Second, establish the slave node

1 copy the installation directory C:\ Program Files\ MySQL\ MySQL Server 5.7 of the main server to another directory D:\ anzhuang\ MySQL\ mysqlSlave\ MySQL Server 5.7.

2 modify the copied my.ini file parameters

Including ports and paths

Port = 3307

Basedir = "D:\ anzhuang\ MySQL\ mysqlSlave\ MySQL Server 5.7"

Datadir = "D:\ anzhuang\ MySQL\ mysqlSlave\ MySQL Server 5.7\ data"

3 establish mysql slave server service

Mysqld install MySQLM-- defaults-file= "D:\ anzhuang\ MySQL\ mysqlSlave\ MySQL Server 5.7\ my.ini"

4 modify the contents of the service startup entries in the corresponding registry

The path used in the registry HKEY_LOCAL_MACHINE\ SYSTEM\ CurrentControlSet\ services\ MySQL57\ ImagePath is changed to

"D:\ anzhuang\ MySQL\ mysqlSlave\ MySQL Server 5.7\ bin\ mysqld"-- defaults-file=D:\ anzhuang\ MySQL\ mysqlSlave\ MySQL Server 5.7\ my.ini MySQLS

Mysqld_multi.pl-- defaults-extra-file=D:\ anzhuang\ MySQL\ mysqlSlave\ MySQL Server 5.7\ my.ini start 3307

So that when starting mysql, you can find the corresponding my.ini file to start

Configuration of three master nodes

1 modify my.ini on master node and slave node

[mysqld]

Server-id = 1

Binlog-do-db=test # databases to be synchronized

# binlog-ignore-db=mysql # for databases that are out of sync, if binlog-do-db is specified, it should be unnecessary to specify

Log-bin=mysql-bin # name of the binary journal file to be generated

Modify the slave configuration file:

[mysqld]

Server-id = 2

Log-bin = mysql-bin

Replicate-do-db=test

Create and authorize users on 2

Create user 'repla'@'127.0.0.1' identified by' test'

GRANT REPLICATION SLAVE ON *. * TO 'repla'@'127.0.0.1'

3 create test database

4 physical backup test directory to slave library

Copy the data directory of the directory specified by datadir to the slave directory

5 show master status

Check the parameters corresponding to the file and position fields

+-

-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid |

Set |

+-

-+

| | mysql-bin.000006 | 1847 | test |

| |

+-

Four slave node configuration

1 start the mysql service from the node

Mysql-uroot-P3307-p123456 if the port is different, you must specify a port to launch multiple instances on one machine at the same time

2 receive the master node delivery log

Change master to master_host='127.0.0.1',master_port=3306,master_user='repla',master_password='test',master_log_file='mysql-bin.000006',master_log_pos=1847

3 start the copy function from the database

Start slave

Finally, after the data is inserted into the main library, the data of the main library can be seen from the library.

Problem summary

1 the my.ini file here is specified in the registry. No matter whether or not you use the command to instruct the ini to use your command, the system will read and start db according to the location of the my.ini file in the registry.

For example, use the command C:\ Program Files\ MySQL\ MySQL Server 5.7\ bin > mysql-- defaults-file= "D:\ anzhuang\ MySQL\ my.ini"-uroot-p

Want to use the self-defined my.ini. But the path used in the registry HKEY_LOCAL_MACHINE\ SYSTEM\ CurrentControlSet\ services\ MySQL57\ ImagePath is not

In this path, the file is also read according to the path of the registry.

If you install on windows, to correctly identify the my.ini location, you can determine the my.ini location based on service selection or registry information

2 physical backup and logical backup can be used when backing up. If it is a physical backup, copy all the files under the data directory of the main library to slave.

This data directory must be the directory specified in the datadir parameter, which may use the data directory under the software installation directory instead of the data directory

3 when using physical backup, it is very possible to copy the auto.cnf file to slave, resulting in UUID inconsistency between master and backup. The solution here is in slave.

Rename auto.cnf, then restart the library to regenerate the new UUID

You can refer to the website http://www.linuxidc.com/Linux/2015-02/113564.htm

4 on slave, you need to verify whether the master and slave start sending log methods.

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: repla

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 1684

Relay_Log_File: SDSC-WIN7-ZHANGLU-relay-bin.000002

Relay_Log_Pos: 1595

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

.

Slave_IO_Running: Yes Slave_SQL_Running: Yes are all Yes.

The possible problem is that you have to execute the change master to command multiple times after stop slave.

After reading the above, do you have any further understanding of how mysql builds the master-slave database on windows and what the problems are? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.