High availability MySQL Reading notes-standalone MySQL master-slave configuration

　　

　　The master-slave is the simplest single master-slave copy (i.e. a master and a slave, without any engine backup and multi-slave), the basic topology diagram of the master-slave structure is as follows:

Establishing a basic master-slave replication can be summarized in the following three basic steps:

1. Configuring the master server
2. Configuring the Slave server
3. Connect the slave to master

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

The master and slave servers involved in this article are located on the same single machine, using different port numbers to differentiate between the basic environment: Window 7, Ultimate 32-bit, and MySQL version 5.6.21. In the previous note (compressed version of MySQL installation in Windows environment), the basic process of the compressed version of MySQL configuration has been introduced, which needs to be copied and extracted to another directory (such as the d:/mysql_slave directory). To differentiate between master and slave, we use different port numbers:Master uses 3306,slave to use the 3307 port number.

First, configure the master

To configure the server as master, you need to make sure that the server has an active binary log and a unique server ID (Server-id, which distinguishes the server). The binary log contains all changes to master and can be executed on slave.

Configure the My.ini file for master:

Open the My.ini file in the master directory and add the following to the file:

Server-=1binlog-do-=  TestLog -Bin=Master-binlog-bin-Index  =Master-bin. Index

which

(1) server-id=1 is used to configure the server ID, which is a unique distinguishing number in MySQL master-slave cluster. All servers should have their own unique ID, and if a slave is connected to master, but has the same ID as master, it will produce the same error as the master and slave IDs.

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

(3) The log-bin field gives the base name of all the files generated by the binary log, and the binaries can contain multiple files, as shown in:

(4) Log-bin-index. This is actually specifying an index file (plain text, which is a list of files) that contains a list of all the binaries. If the default value is not set for Log-bin-index, the Log-bin-index file is generated using the hostname of the machine, so that after the server's hostname changes, the index file may not be found and the list of binary files is considered empty. Causes the binary Bin-log to be generated incorrectly. Therefore, it is recommended that you use a machine-independent name as the Bin-log-index file name.

After configuring the INI file, you need to start the MYSQLD service from the command line and enter the master's Bin directory (D:. Mysql/bin) and execute the following command (if you previously configured MySQL, close the MySQL service net stop MySQLthat was started before executing):

Mysqld–-console

If the startup succeeds, the CMD window will appear similar to the following (the red part is the key):

At this point the master server has been started. Minimize the cmd window without having to take care of it. A new CMD window is opened, connected to master (Mysql–u root-pxxx), and the status of Master can be viewed through show master status .

When slave connects to master, a standard client is started to connect to master, and the master is asked to dump all changes to it. Therefore, when connecting, you need a user with copy permission on master. In master's console, execute the following command:

Create User Reply_user; Grant REPLICATION  on *. *  to  by  Privileges;

Replication slave permissions are nothing special except that this user is able to dump data from binary files on master. It is entirely possible to give this permission to a regular user, but it is best to distinguish between the user who copied the slave and the other user, so that if you want to disable certain slave links, simply delete the user.

Second, the configuration slave

The process of configuring slave is basically the same as configuring master, but the difference is that you need to configure a different port number, which requires a different server-id number. And you need to configure Relay-log (trunk log) and Relay-log-index (the index file for the trunk log) for slave. Open the My.ini (D:/mysql_slave directory) under the Slave directory and add the following:

#mysql Slave Port Port= 3307#slave Server ID server_id= 2 #Replication Database Replicate-Do-Db=Test #relayLog fileRelay-Log=Slave-Relay-bin #relayLog Index fileRelay-Log-Index=Slave-Relay-Bin.Index

After modifying slave. You also need to start slave's mysqld by command:

d:cd mysql_slave/binmysqld–-console

Now the master and slave are configured. However, there is no connection between master and slave, if performed at slave's console:show slave status; The prompt will appear: it is not a slave

third, slave connect to master

To connect slave to master, you need to know the four basic information of master:

(1) host name or IP address , because this is a stand-alone, the host IP is 127.0.0.1

(2) port number used by Master , 3306

(3) user with replication slave permission on Master

(4) The password of the account .

When configuring master, a user with the relevant permissions has been created and run the following command in the console of slave:

 to      master_host='127.0. 0.1 ',     Master_port=3306,     master_user="Reply_user",     Master_password="xxxx";

After execution, you can start Slave with the start Slave command.

If the show slave status is executed, the following similar content will be output (note the Red section):

Now, a basic MySQL master and slave has been built up. Let's do some simple tests to see if replication is working. The test steps are as follows:

1. Create a table on master:

CREATE TABLE' Web_user2 ' (' ID ')int(Ten) unsigned not NULLauto_increment, ' name 'varchar( -) not NULL DEFAULT "', ' Creatime 'datetime  not NULL DEFAULT '0000-00-00 00:00:00',  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=1 DEFAULTCHARSET=UTF8;

2. Inserting data

Insert  into Values (' First'), ('test'), ('showb') ), ('blood'); Select *  from ' web_user2 ';

3. Enter slave to see if the sync is ok

Show tables; Select *  from Web_user;

4. Viewing replicated events in Master's console-binlog events

Flush logs; Show Binlog events;

The log is long and only the first two are intercepted here:

From the content of Binlog, replication has been configured successfully.

Next, you will continue to explore the binary log related things.

Reference documents:

1. "High Availability MySQL"
2. Http://www.cnblogs.com/zhangjun516/archive/2013/03/18/2965604.html
3. http://blog.itpub.net/7607759/viewspace-719707/

High availability MySQL Reading notes-standalone MySQL master-slave configuration