1. Copy the configuration


1. Create a backup account in the Master database: each slave connects to the Master using the standard MySQL username and password. The user performing the REPLICATION operation grants

GRANT REPLICATION SLAVE,RELOAD,SUPER ON. TO backup@ ‘10.100.0.200’

1.2, Copy data (this step is not necessary if you have completely new mysql master/slave server installed. Because the newly installed master and slave servers have the same data) Shut down the master server, copy the data in the master server to the B server, synchronize the data in the master and slave servers, and ensure that write operations on the master and slave servers are prohibited until all Settings are complete. Make sure the data in both databases is the same! Next, configure the master, including enabling the binary log and specifying the unique servr ID. For example, add the following values to the configuration file: server-id=1 log-bin=mysql-bin server-id: id of primary server A. log-bin: binary daily change restart the master and run the SHOW master STATUS command. The following output is displayed:

1.4. Configure slave




















Server_id is required and unique. It is not necessary to enable binary log on the slave. However, it must be enabled in some cases. For example, if the slave is the master of another slave, bin_log must be enabled. Here, we have binary logging turned on, and the naming of the display (the default name is hostname, but there are problems if the hostname changes). Relay_log configures the relay log, log_slave_updates means that slave writes replication events to its binary log (see how useful this is later). Some people enable binary logging for their slave without setting log_slave_updates, and then check to see if the slave data has changed. This is a configuration error. So, try to use read_only, which prevents data from changing (except for special threads). However, read_ONLY is not very useful, especially if you need to create tables on your slave. The next step is to have the slave connect to the master and start redoing the events in the master binary log. You should not do this with a configuration file, but instead use the CHANGE MASTER TO statement, which can completely replace the modification TO the configuration file, and it can specify a different MASTER for the slave without stopping the server as follows:

mysql> CHANGE MASTER TO MASTER_HOST=’server1′,

-> MASTER_USER=’repl’,

-> MASTER_PASSWORD=’p4ssword’,

-> MASTER_LOG_FILE=’mysql-bin.000001′,

-> MASTER_LOG_POS=0;

The value of MASTER_LOG_POS is 0 because it is where the log starts. <br>Copy the code

You can check whether the SLAVE is set correctly by using the SHOW SLAVE STATUS statement: mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Master_Host: server1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: 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 … omitted… Seconds_Behind_Master: NULL

Slave_IO_State, Slave_IO_Running, and Slave_SQL_Running are No indicating that slave has not started the replication process. The log position is 4 instead of 0 because 0 is just the start of the log file, not the log position. In fact, the location of the first event My SQL knows is 4.

To START the replication, you can run mysql> START SLAVE; Run SHOW SLAVE STATUS to view the output: mysql> SHOW SLAVE STATUS\G *************************** 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_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes … omitted… Seconds_Behind_Master: 0

Slave_IO_Running=Yes Slave_SQL_Running=Yes Slave I/O and SQL threads are running, and Seconds_Behind_Master is no longer NULL. The location of the log increases, meaning that some event is fetched and executed. If you make changes on the master, you can see changes in the location of various log files on the slave, as well as changes in the data in the database. You can view the status of threads on master and slave. On the master, you can see the connection created by the SLAVE I/O thread: on the master type show processList \G;

1.5. Add a new Slave server if the master server has been running for a long time and you want to synchronize data on the newly installed slave server, even if the slave server does not have the data of the master server. At this point, there are several ways to make slave start from another service, for example, copy data from master, clone data from another slave, and start sl AVE from the most recent backup. To synchronize the Slave with the master, three things are required: (1) a snapshot of the master’s data at some point in time; (2)master the current log file and the byte offset when the snapshot was generated. These two values are called log file coordinate, because they determine the location of a binary log, and you can use the SHOW MASTER STATUS command to figure out the coordinate of the log file; (3) Master binary log file.

You can clone a slave in one of the following ways:

(1) Cold copy

Stop master and copy master files to slave. Then restart the master. The disadvantages are obvious.

(2) Warm copy

If you only use MyISAM tables, you can use mysqlHotCopy even if the server is running.

(3) use the mysqldump

<1> Lock the table: if you have not locked the table, you should lock the table to prevent other connections from modifying the database. Otherwise, your data may be inconsistent. Mysql > FLUSH TABLES WITH READ LOCK; <2> Create a dump of the database you want to replicate using mysqldump on another connection: shell> mysqldump –all-databases –lock-all-tables >dbdump.db <3> Release the lock on the table. mysql> UNLOCK TABLES;