Implementation of Mysql Replication

The implementation of Mysql Replication requires a small project, and the data of the three Mysql servers needs to be synchronized. After going online to query the data, the basic idea is completed. First, we have prepared three Centos6.2 servers, and configured them with basic webservers such as Apache Mysql and PHP, which are Master A: 192.168.1.49 www.2cto.com Slave B1: 192.168.1.50 B2: 192.168.1.51. The environment I need is unidirectional replication, make the data of the three machines consistent with that of the three machines from copying data from A to B1 and B2. Configuration logic: From the Perspective of the higher level, replication is divided into three steps: (1) the master will record changes to binary logs (these records are called binary log events, binary log events ); (2) slave copies the binary log events of the master to its relay log. (3) slave rewrites the events in the relay log to reflect its own data. Start configuration: www.2cto.com Step 1: Create a replication account. Each slave uses the standard MySQL user name and password to connect to the master. The REPLICATIONSLAVE permission is granted to the user who performs the copy operation. The username and password are stored in the master.info text file. Assume that you want to create a repl User: 1 mysql> grant replication slave, replication client on *. * 2-> TO repl @ '2017. 168.1.% 'identified BY '123'; Step 2: Configure My. cnf: Configure My. cnf. The default location of this file is/etc/my. cnf then configures the master, including opening binary logs and specifying a unique servr ID. For example, add the following value to the configuration file: [mysqld] log-bin = mysql-bin server-id = 10 Restart master and run show master status. The output is as follows: Configure My. cnf. The default location of this file is/etc/my. the cnf Slave configuration is similar to that of the master. You also need to restart the slave MySQL. As follows: log_bin = mysql-bin server_id = 2 www.2cto.com relay_log = mysql-relay-bin log_slave_updates = 1 read_only = 1 server_id is required and unique. Slave does not need to enable binary logs, but in some cases, it must be set. For example, if slave is another slave master, bin_log must be set. Here, we enable binary logs and display the name (the default name is hostname, but if the hostname is changed, the problem will occur ). Relay_log configures the relay log. log_slave_updates indicates that slave writes the replication event into its own binary log (which will be useful later ).
Some people have enabled the slave binary log, but have not set log_slave_updates, and then check whether the slave Data has changed. This is an incorrect configuration. Therefore, use read_only whenever possible, which prevents data changes (except for special threads ). However, read_only is very useful, especially for applications that need to create tables on slave. Step 3: Start slave. Next, let slave connect to the master and redo the events in the master binary log. Instead of using the configuration file for this operation, you should use the change master to statement. This statement can completely replace the modification TO the configuration file, and it can specify different masters for slave, you do not need to stop the server. 1 mysql> change master to MASTER_HOST = '2017. 168.1.49', 2-> MASTER_USER = 'repl', 3-> MASTER_PASSWORD = '000000', 4-> MASTER_LOG_FILE = 'mysql-bin.000001 ', 5-> MASTER_LOG_POS = 0; the value of MASTER_LOG_POS is 0 because it is the start position of the log. Then, you can use the show slave status statement to check whether the slave settings are correct: 01 mysql> show slave status \ G02 **************************** 1. row ************************** 03 www.2cto.com Slave_IO_State: 04 Master_Host: server105 Master_User: repl06 Master_Port: 330607 Connect_Retry: 6008 Master_Log_File: mysql-bin.00000109 Read_Master_Log_Pos: 410 Relay_Log_File: mysql-relay-bin.00000111 Relay_Log_Pos: 412 Relay_Master_Log_File: mysql- Bin.00000113 restart: No14 Slave_ SQL _Running: No15... omitted... 16 Seconds_Behind_Master: NULL Slave_IO_State, idle, and Slave_ SQL _Running indicate that the replication process has not started yet. The log location is 4 rather than 0, because 0 is the start position of the log file, not the log location. In fact, MySQL knows that the first event is located at 4. To START replication, run: 01 mysql> start slave; 02 mysql> show slave status \ G03 run show slave status to view the output result: 04 **************************** 1. row ************************** 05 Slave_IO_State: Waiting for master to send event06 www.2cto.com Master_Host: server107 Master_User: repl08 Master_Port: 330609 Connect_Retry: 6010 Master_Log_File: mysql-bin.00000111 Read_Master_Log_Pos: 16412 Relay_Log_File: mysql-relay-bin. 00000113 Relay_Log_Pos: 16414 Relay_Master_Log_File: mysql-bin.00000115 Slave_IO_Running: Yes16 Slave_ SQL _Running: Yes17... omitted... 18 Seconds_Behind_Master: 0 note that slave's I/O and SQL threads have started to run, and Seconds_Behind_Master is no longer NULL. The log location is increased, meaning that some events are obtained and executed. If you modify the log file on the master, you can view the changes in the location of various log files on the slave, and you can also see the changes in the database data. You can view the status of the master and slave threads. On the master, you can see the connection created by the slave I/O thread: 01 mysql> show processlist \ G02 **************************** 1. row ************************** 03 Id: 104 User: root05 Host: localhost: 209606 db: test www.2cto.com 07 Command: Query08 Time: 009 State: NULL10 Info: show processlist11 ***************************** 2. row ************************** 12 Id: 213 User: repl14 Host: localhost: 214415 db: NULL16Command: Binlog Dump17 Time: 1838 www.2cto.com 18 State: Has sent all binlog to slave; waiting for binlog to be updated19 Info: NULL202 rows in set (0.00 sec) is basically done here, as for the later enhancement operations, I will add them separately. Main problems during the test: 1. Configure Mysql2. copy the Mysql database and table framework before enabling replication. (Check whether the table can be copied !) Author Tingel