Build MySQL Master Slave under Windows

Ext.: http://www.cnblogs.com/gaizai/p/3248207.html

http://www.cnblogs.com/gaizai/archive/2013/03/15/2961868.html MySQL table Data Migration automation

http://www.cnblogs.com/gaizai/archive/2012/10/23/2735556.html Ubuntu10 under MySQL build Master Slave

First, background

The server put a lot of MySQL database, for security, now need to do master/slave solution, because the operating system is window, so there is no way to use keepalived this ha tool, but we can accept manual switch, what good plan?

Second, several master/slave logical architecture diagrams

(Figure1: Single master-single slave)

(Figure2: Single master-multi-Slave)

(Figure3: Single master-cascade slave)

(Figure4:master/slave deployment logic diagram)

Third, Building Process

Environment: Windows Server R2 + mysql-5.5.22-winx64

Primary server (Master): 192.168.1.66

Slave Server (Slave): 192.168.1.67

Using Master/slave architecture, Slave is read-only mode;

(i) Configuring the master server (master)

1. Edit the database configuration file My.ini, and add the following code below the [Mysqld] tab:

Log-bin=mysql-bin

Server-id=1

Innodb_flush_log_at_trx_commit=1

Sync_binlog=1

Binlog_do_db=barfoo_weibo_hottop

Binlog_ignore_db=mysql

Description

1) log-bin=mysql-bin enable binary log, will generate a series of scrolling files in the Data folder, similar to: mysql-bin.000002

2) 1 of server-id=1 can be defined as long as it is unique, in order to distinguish it from other master and slave;

3) innodb_flush_log_at_trx_commit = 0, Write Trax log once per second, and execute fsync;

Innodb_flush_log_at_trx_commit = 1, each time Trax submit the time to write Trax log, and execute fsync;

Innodb_flush_log_at_trx_commit = 2, each time Trax submit the time to write Trax log, will not execute fsync;

4) sync_binlog=1 indicates the asynchronous logging;

5) Binlog_do_db=barfoo_weibo_hottop is to indicate that only the data barfoo_weibo_hottop do log records, if you want to record multiple databases, add a few more binlog_do_db on it;

6) Binlog_ignore_db=mysql means that backup MySQL is ignored, and binlog_do_db and binlog_ignore_db are not added, which means backing up all databases.

2. Restart the MySQL service

3. in the master server to create a new user to the "REPLICATION SLAVE" permission, you do not need to give other permissions, the IP specified here is SLAVE IP;

Mysql>create USER [email protected] ' 192.168.1.67 ' identified by ' BarFoo2013 ';

Mysql>grant REPLICATION SLAVE On * * to [e-mail protected] ' 192.168.1.67 ' identified by ' abc2013 ';

4. Execute the following script on the master server to make the database read-only;

Mysql>flush TABLES with READ LOCK;

5. Copy the database folder and the Ibdata1 file to the local temp folder, and perform the following steps immediately;

6. Check the status of master server , write down the value of file and position, and use it when setting up slave server .

Mysql>show MASTER logs;

Mysql>show MASTER STATUS;

(Figure1:master status)

7. unlock the table and restore the database to read and write;

Mysql>unlock TABLES;

8. use FTP and other tools to upload the temporary files of the 5th step to the slave server;

(ii) configuration from server (Slave)

1. Edit the database configuration file My.ini, add the following code under [Mysqld], you can define the values, as long as the only guarantee that the only line, and the master can be separated from the line;

server-id=2

Read-only=1

2. restart MySQL

3. Log in to the MySQL server and execute the following script command:

Mysql>change MASTER to

Master_host= ' 192.168.1.66 ',

Master_user= ' Viajar ',

Master_password= ' abc2013 ',

master_port=3309,

Master_log_file= ' mysql-bin.000005 ',

master_log_pos=7179684,

master_connect_retry=30;

Description

1) IP of the master_host:master server;

2) Master_user: User name established when configuring the primary server;

3) Aster_password: password corresponding to the user name;

4) Aster_port: The main server MySQL port, if not modified, by default;

5) Master_log_file:log file name, reference Figure1;

6) Master_log_pos: Only the last backup LOG file offset, refer to Figure1;

7) Master_connect_retry: The time interval to reconnect the MASTER, in seconds;

4. start the slave process and execute the following script command;

Mysql>start SLAVE;

5. Check the master-slave synchronization state, execute the following script command, where the value of slave_io_running and slave_sql_running must be yes to indicate that the status is normal.

Mysql>show SLAVE STATUS;

(Figure2:slave State 1)

(Figure3:slave State 2)

6. View the MySQL log of the slave server;

(Figure4:slave log file)

Four, Additional Information

1. View Help

Mysql>help

2. View slave Help

Mysql>help slave

3. Find which binary log files are currently in

Mysql> show binary logs;

4. Delete all binary log files prior to mysql-bin.000018

Mysql> purge binary logs to ' mysql-bin.000018 ';

5. Starting with MySQL 5.1.12, you can end up with the following three forms: SQL statement-based replication (statement-based replication, SBR), row-based replication (row-based replication, RBR), Promiscuous form replication (mixed-based replication, MBR). Accordingly, Binlog's style also has three kinds: statement,row,mixed. In MBR form, the SBR form is the default.

#binlog_format = "STATEMENT"
#binlog_format = "ROW"
#binlog_format = "MIXED"

Of course, it is also possible to dynamically modify the Binlog style at run time. For example

mysql> SET SESSION binlog_format = ' STATEMENT ';
mysql> SET SESSION binlog_format = ' ROW ';
mysql> SET SESSION binlog_format = ' MIXED ';

mysql> SET GLOBAL binlog_format = ' STATEMENT ';
mysql> SET GLOBAL binlog_format = ' ROW ';
mysql> SET GLOBAL binlog_format = ' MIXED ';

6. mysql Sync failure: slave_sql_running:no

mysql> slave stop;

Mysql> set GLOBAL sql_slave_skip_counter=1;

mysql> slave start;

Five, Questions

(a) The replication level has the following, the default level is that? How do I explicitly set this value?

1. Row level:5.1.5 started to support. Mater records the change log for each row of data, and slave is applied on a per-line basis. Pros: Data consistency is more secure. Disadvantage: May cause the log file to be larger;

2. Statement Level:master records each query statement executed and some contextual information, slave nodes are re-executed on slave based on that information. Advantage: Binary log is smaller. Disadvantage: In some cases data consistency is difficult to protect;

3. Mixed Level:mysql Choose which replication method to select according to the situation. 5.1.8 began to support.

(b) Commonly used architectures have the following, I want the effect is: when the Mastera down, Masterb can read and write, Mastera after the recovery to re-let Mastera become the master master.

1. Master-slaves: This approach is usually used;

2. Dual Master (master-master): 2 Master nodes synchronize updates with each other. Because of the asynchronous replication of MySQL, in order to prevent inconsistencies caused by data conflicts, generally only one is used for write operations, the other is not used or only for read operations. The purpose is to use another master when one master is down maintenance or when the fault is interrupted;

3. Cascade Replication (Master-slaves-slaves): In master slaves, if slaves too much replication will increase the load on master, you can have master distribute the update log to only a few slave These slave distribute the update logs to the subordinate nodes as a first-level node.

(c) If slave error or downtime for a period of time, then how to re-sync the outage time master data to slave it?

(d) How can I set up a record of multiple databases when Master is setting up binlog_do_db=test?

Solution: Directly in the configuration file My.ini to add more than one binlog_do_db on it;

(v) The daily log file is 10GB, how to clean it? How to maintain? Are there any settings or methods to reduce the amount of log?

Six, Reference Documents

Windows under MySQL master-slave Configuration

build MySQL the Master/slave Architecture

Linux MySQL Master-slave Replication (Replication) Configuration

13.4.2.1. Change MASTER to Syntax

Innodb_flush_log_at_trx_commit option

Innodb_flush_log_at_trx_commit

Understanding mysql-- copy (Replication) (Graphics and Mau)

Build MySQL Master Slave under Windows