MySQL supports one-way and asynchronous replication. During the replication process, one server acts as the master server. One or more other servers act as slave servers. The master server writes updates to binary log files and maintains an index of the log files to track log rotation. When a slave connects to the master, it notifies the master of the location of the last successful update the slave read in the log. The slave server receives any updates that have occurred since then, then blocks and waits for the master server to notify the next update.
Why use master-slave replication?
1. The master server/slave server setting increases robustness. When something goes wrong with the master server, you can switch to the slave server as a backup.
2. By dividing the load of processing customer queries between the master server and the slave server, you can get better customer response time. But do not update on the master and slave servers at the same time, as this may cause conflicts.
3. Another benefit of using replication is that you can use a slave server to perform backups without disturbing the master server. The master server can continue processing updates during the backup process.
MySQL uses 3 threads to perform replication functions (1 on the master server and two on the slave server. When a START SLAVE is issued, the slave server creates an I/O thread to connect to the master server and let the master server send Binary log. The master server creates a thread to send the contents of the binary log to the slave server. The slave server I/O thread reads the content sent by the master server Binlog Dump thread and copies the data to a local file in the slave server data directory. , that is, the relay log. The third thread is the SQL thread. The slave server uses this thread to read the relay log and execute the updates contained in the log to query the replication that occurred on the master server and the slave server. Information.
The default relay log uses a file name of the form host_name-relay-bin.nnnnnn, where host_name is the slave server hostname and nnnnnn is the sequence number. Create consecutive relay log files with consecutive sequence numbers, starting with 000001. Track the relay log index file to identify the relay log currently in use. The default relay log index file is named host_name-relay-bin.index. By default, these files are created in the slave server's data directory. The log has the same format as the binary log and can be read with mysqlbinlog. When the SQL thread has executed all events in the relay log, the relay log will be automatically deleted
from the server and two additional status files will be created in the data directory
.--master.info and relay-log.info. Status files are saved on the hard disk and are not lost when the slave server is shut down. The next time the slave server starts, these files are read to determine how many binaries it has read from the master server. logs, and the extent to which they handle their own relay logs.
To set up master-slave replication:
1. Make sure the MySQL version installed on the master server and the slave server is the same, and preferably the latest stable version of MySQL
on the master server
.Replication sets up a connection account. This account must be granted REPLICATION SLAVE permission. If the account is used only for replication (recommended), no other permissions need to be granted
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'replication'. @'%.yourdomain.com' IDENTIFIED BY 'slavepass';
3. Execute the FLUSH TABLES WITH READ LOCK statement to clear all tables and block write statements:
mysql> FLUSH TABLES WITH READ LOCK;
keep the mysql client program from exiting. A terminal takes a snapshot of the main server data directory.
shell> cd /usr/local/mysql/
shell> tar -cvf /tmp/mysql-snapshot.tar ./data
If the user account of the slave server is different from that of the master server, you may not want to copy the mysql database. In this case, the database should be excluded from the archive. You also don't need to include any log files or master.info or relay-log.info files in the archive.
When the read lock set by FLUSH TABLES WITH READ LOCK is valid (that is, the mysql client program does not exit), read the current binary log name and offset value on the main server:
mysql > SHOW MASTER STATUS;
+------ ---------+----------+--------------+-------------- ----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+-------- ------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+-------- -------+----------+--------------+---------------- --+
The File column displays the log name, and the Position displays the offset. In this example, the binary log value is mysql-bin.003 at offset 73. Record this value. These values will be needed later when setting up the slave server. They represent the replication coordinates from which the slave should start new updates from the master.
If --logs-bin is not enabled when the master server is running, the log name and location values displayed by SHOW MASTER STATUS are empty. In this case, the values that need to be used when specifying the log file and location of the slave server in the future are empty strings ('') and 4.
After taking the snapshot and recording the log name and offset, return to the previous middle end and restart Enable write activity:
mysql> UNLOCK TABLES;
4. Make sure that the [mysqld] section of the my.cnf file on the master server host includes a log-bin option. This section should also have a server-id=Master_id option, where master_id must be a positive integer value between 1 and 232–1. For example:
[mysqld]
log-bin
server-id=1
If those options are not provided, you should add them and restart the server.
5. Stop the mysqld service on the slave server and add the following line to its my.cnf file:
[mysqld]
server-id=2
The slave_id value is the same as the Master_id value and must be a positive integer between 1 and 232–1. value. Also, the ID of the slave server must be different from the ID of the master server.
6. Save the data in the backup directory. Make sure the permissions on these files and directories are correct. The user the server MySQL is running under must be able to read and write files, just like on the main server.
Shell> chown -R mysql:mysql /usr/local/mysql/data
7. Start the slave server. Execute the following statement on the slave server, replacing the option values with the actual values for your system:
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
- > MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
8. Start the slave server thread:
mysql> START SLAVE;
After executing these procedures, the slave server should connect to the master server and supplement any updates that have occurred since the snapshot.
9. If a replication error occurs, an error message will also appear in the error log (HOSTNAME.err) of the slave server.
10. When copying from the server, the files master.info and HOSTNAME-relay-log.info will be found in its data directory. The slave uses these two files to keep track of how much of the master's binary log has been processed. Do not remove or edit these files unless you know exactly what you are doing and fully understand its significance. Even so, it is better to use the CHANGE MASTER TO statement.