Mysql master-slave replication technology (test)

Mysql master-slave replication technology (TEST) to 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 user who performs the copy operation will grant
REPLICATIONSLAVE permission. The username and password are stored in the master.info text file. Assume that you want TO create a repl user, such as mysql> grant replication slave, replication client on *. * 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, in the configuration file
Add the following values: [mysqld] server-id = 1 log-bin = mysql-bin restart mysql, service mysql restart, log on to mysql-uroot-p and run show master status. The output is as follows: + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000002 | 106 | + usage + -------- + -------------- + --------------- --- + 1 row in set (0.00 sec) 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. Server-id = 2 log-bin = mysql-bin 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
For example, if the slave is another master of the slave, bin_log must be set. Here, we have enabled binary
Log, and display the name (the default name is hostname, but if the hostname is changed, the problem will occur ). Relay_log: configure the relay log. log_slave_updates indicates that slave writes the replication event to its own binary log (
See its usefulness ). Some people enable the slave binary log, but do not set log_slave_updates, and then check whether the slave Data is 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. Restart mysql, service mysql restart, log on to mysql-uroot-p Step 3: Start slave www.2cto.com. Next, let slave connect to the master, and start to redo the events in the master binary log. You should not use the configuration
File, but the change master to statement should be used. This statement can completely replace the modification TO the configuration file,
In addition, it can specify different masters for the slave without stopping the server. Mysql> change master to MASTER_HOST = '192. 168.60.73 ', MASTER_USER = 'repl', MASTER_PASSWORD = '2016', MASTER
_ LOG_FILE = 'mysql-bin.20.02 ', MASTER_LOG_POS = 0; The MASTER_LOG_POS value is 0 because it is the start position of the log. Then, you can use SHOW SLAVE STATUS
Statement to check whether the slave settings are correct: mysql> show slave status \ G ***************************** 1. row ************************* Slave_IO_State: Master_Host: server1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 MAID: mysql-bin.000001 Read_Master_Log_Pos: 4 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_ SQL _Running: No... omi Tted... Seconds_Behind_Master: NULL Slave_IO_State, Slave_IO_Running, and Slave_ SQL _Running indicate that slave has not started the replication process.
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
The first event is located at 4. To START replication, run mysql> start slave; mysql> show slave status \ G to run show slave status to view the output result: * *************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: server1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 164 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 164 Relay_Ma Ster_Log_File: mysql-bin.000001 Plugin: Yes Slave_ SQL _Running: Yes... omitted... Seconds_Behind_Master: 0 note that slave's I/O and SQL threads are already running, and Seconds_Behind_Master is no longer NULL. Logs
It means that some events are obtained and executed. If you modify it on the master, you can view it on slave.
Changes to the location of various log files. Similarly, you can see changes in data in the database. 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: mysql> show processlist \ G **************************** 1. row ************************** Id: 1 User: root Host: localhost: 2096 db: test Command: Query Time: 0 State: NULL Info: show processlist **************************** 2. row ************************** Id: 2 User: repl Host: localhost: 2144 db: NULL Command: Binlog Dump Time: 1838 State: Has sent all binlog to sl Ave; waiting for binlog to be updated Info: NULL 2 rows in set (0.00 sec) is basically done here. As for the later enhancement operations, I will add them separately. At www.2cto.com, Mysql replication can be restricted (master) binlog-do-db = work # only copy binlog_ignore_db = mysql # Replicate_Do_DB = mysql replicate-ignore-db = mysql and several other commonly used commands flush mater; # Clear spam flush slave; # Clear spam start slave; # start Service reset slave; # reset Service stop slave; # The mechanism for stopping the service is to assume that the actual environment is not very practical when the Master and slave are created.
What if a new slave is added to the Master that has been running in the group for a long time? In this way, we need to prioritize data processing. 1. The Master database is locked to avoid data duplication. Flush tables with read lock; 2. use mysqldump to create a dump of the database you want to copy in another connection: shell> mysqldump -- all-databases -- lock-all-tables> dbdump. db 3. release the lock on the table. Mysql> unlock tables; in the preceding method, 2nd databases need to be copied to the new slave for restoration. In this way, the data sources of the Master and the new Slave are consistent. In fact, there are many methods in Part 1, and you can also use the Mysql software for synchronization. for example, the Navicat for Mysql method is not unique, as long as the results are consistent. 4. after the old data is synchronized, enter www.2cto.com mysql> show Master status; # In the master: + ------------------ + ---------- + -------------- + Position + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + ------------------ + ---------- + -------------- + mysql-bin.000024 | 38113 | mysql, test | 1 row in set (0.00 sec) 5. then, enter the content of the preceding Slave startup step based on the content in the table above. Change master to MASTER_HOST = '192. 168.60.73 ', MASTER_USER = 'repl', MASTER_PASSWORD = '2016 ',
MASTER_LOG_FILE = 'mysql-bin.000002 ', MASTER_LOG_POS = 0; 6. START slave start slave; 7. Check after startup. Mysql> show slave status \ G ***************************
1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.171 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000024 Read_Master_Log_Pos: 38255 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 393 rows: mysql-bin.000024 keys: Yes keys: Replicate_Ignor E_DB: mysql, wdcpdb, mysql, wdcpdb Protocol: 0 Last_Error: Skip_Counter: 0 Protocol: 38255 Relay_Log_Space: 548 Until_Condition: None Until_Log_File: Protocol: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Be Hind_Master: 0 lead: No Last_IO_Errno: 0 Last_IO_Error: Last_ SQL _Errno: 0 Last_ SQL _Error: 1 row in set (0.00 sec) the main problem for www.2cto.com testing is: 1. configure Mysql 2 first. copy the Mysql database and table framework before enabling replication. (Check whether the table can be copied !)