High Availability MySQL Reading Notes 2-standalone MySQL Master/Slave Configuration

High Availability MySQL Reading Notes 2-standalone MySQL Master/Slave Configuration
The Master-slave architecture is the simplest single Master-slave replication (that is, one Master and one slave without any hot backup and multiple slave). The basic topology of the Master-slave architecture is shown below:

The basic master-slave replication can be summarized as follows:

Configure the master server and configure the slave server to connect the slave to the master

After the configuration is complete, we will perform a simple test to test whether the replication is normal.

The master and slave servers involved in this article are located on the same single machine and differentiated by different port numbers. The basic environment is Windows 7 flagship edition 32-bit, and MySQL version is 5.6.21. In the previous note (MySQL installation for compressed version in Windows), we have introduced the basic process of mysql configuration for compressed version. here we need to copy it and decompress it to another directory (for example, D: /Mysql_slave directory ). To distinguish the master and slave, we use different port numbers: the master uses 3306, And the slave uses 3307 port numbers.

1. Configure Master

To configure the server as the Master, make sure that the server has an active binary log and a unique server ID (server-id, used to differentiate servers ). the binary log contains all changes to the Master and can be executed on the Slave.

Configure the my. ini file of the master:

Open the my. ini file under the master Directory and add the following content to the file:

server-id = 1binlog-do-db = testlog-bin=master-binlog-bin-index=master-bin.index

Where:

(1) server-id = 1 is used to configure the server ID, which is a unique identification number in the mysql master-slave cluster. All servers must have their own unique IDs. If a Server Load balancer instance is connected to the Master but has the same ID as the Master instance, an error with the same Master and Server Load balancer IDs will occur.

(2) binlog-do-db: Specifies the database for master-slave replication.

(3) The log-bin field shows the basic names of all files generated by binary logs. A binary file can contain multiple files, as shown in:

(4) log-bin-index. This actually specifies an index file (in plain text, the content is the file list), which contains a list of all binary files. If the default value is not set for log-bin-index, the machine's hZ limit is used? Http://www.bkjia.com/kf/ware/vc/ "target =" _ blank "class =" keylink "> vc3RuYW1lsvrJ + keys/keys + o6y007b4yM/Oqrb + keys/ci3tcTJ + rPJtv69 + keys/keys/ rG987eudi1xMP719bX986qYmluLWxvZy1pbmRleLXEzsS8/sp7lda-vcd4kpha + pgltzybzcm9 "http://www.2cto.com/uploadfile/Collfiles/20141018/2014101809022018.png" alt = "\">

After the INI file is configured, you need to start the mysqld service from the command line to enter the bin directory of the master (D :. mysql/bin) and execute the following commands (if mysql has been configured before, disable the mysql service net stop mysql that has been started before execution ):

mysqld –console

If the startup is successful, the following similar content appears in the cmd window (the red part is the key ):

The master server has been started. Minimize the cmd window and ignore it. Open a new cmd window and connect to the master (mysql-u root-pxxx). You can view the master status through show master status.

When Slave is connected to the Master, a standard client is started to connect to the Master, and the Master is requested to dump all the changes to it. Therefore, when connecting, you must have a user with the copy permission on the Master. Run the following command on the master console:

Create user reply_user;Grant REPLICATION SLAVE On *.* to reply_user IDENTIFIED BY ‘xxxxxxxxx’;Flush privileges;

Replication slave has no special permissions, but this user can dump data from the binary file on the Master. This permission can be granted to a general user, but it is better to distinguish the users who copy Slave from other users. In this way, if you want to disable some slave links, you only need to delete this user.

Ii. Configure Slave

The Slave configuration process is basically the same as the Master configuration process. The difference is that you need to configure a different port number and a different server-ID number. Configure relay-log and relay-log-index for Slave ). Open my. ini (D:/mysql_slave directory) under the Slave directory and add the following content:

#mysql slave port port = 3307 #slave server id server_id   = 2 #replication database replicate-do-db=test #relay log file relay-log=slave-relay-bin #relay log index file relay-log-index=slave-relay-bin.index

After modifying the Slave. Similarly, run the following command to start the mysqld of slave:

d:cd mysql_slave/binmysqld –console

Now the Master and Slave are configured. However, there is no connection between the Master and Slave. If you execute: show Slave status on the slave console, a prompt is displayed: it is not a slave.

Iii. Connecting Slave to Master

To connect Slave to the Master, you need to know the four basic information of the Master:

(1) Host Name or IP address. The host IP address is 127.0.0.1 because it is a single machine.

(2) Master port number, 3306

(3) users with replication slave permissions on the Master

(4) password of the account.

You have created a user with relevant permissions When configuring the Master. Run the following command on the Slave console:

Change Master to     Master_host=’127.0.0.1’,     Master_port=3306,     Master_user=”reply_user”,     Master_Password=”xxxx”;

After execution, you can run the start Slave command to start Slave.

If you execute show slave status, the following similar content will be output (note the red part ):

Now, a basic mysql Master/Slave has been set up. Next we will perform some simple tests to see if the replication works. The test procedure is as follows:

1. Create a table on the Master:

CREATE TABLE `web_user2` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(30) NOT NULL DEFAULT '',  `creatime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2. Insert data

Insert into web_user2 (`name`) values ('first'),('test'),('showb'),('blood');Select * from `web_user2`;

3. log on to Slave to check whether the synchronization is normal.

Show tables;Select * from web_user;

4. view the copied event-Binlog events on the master Console

Flush logs;Show binlog events;

The log is long. Here, only the first two logs are intercepted:

Htmlhttp: // blog.itpub.net/7607759/viewspace-719707/