How to configure MySQL master-slave replication

MySQL supports one-way, asynchronous replication, in which one server acts as the primary server and one or more other servers act as the server. The master server writes the update to the binary log file and maintains an index of the log file to track the log loop. When a server connects to the primary server, it notifies the primary server of the location of the last successful update that was read from the server in the log. Receive any updates from the server that have occurred since then, and then block and wait for the master server to notify the next update.

Why use master-slave replication?

1, the primary server/from the server settings increased robustness. When there is a problem with the primary server, you can switch to backup from the server.

2, through the primary server and from the Server segmentation processing customer inquiries load, can get better customer response time. However, do not update on the master-slave server at the same time, which can cause conflicts.

3. Another benefit of using replication is that you can use a backup from the server without interfering with the primary server. The primary server can continue processing updates during the backup process.

MySQL uses 3 threads to perform replication functions (of which 1 are on the primary server and two on the server). When the start slave is issued, an I/O thread is created from the server to connect to the primary server and have the primary server send the binary log. The primary server creates a thread that sends the contents of the binary log to the server. Reads the contents of the primary server Binlog dump thread from the server I/O thread and copies the data to a local file from the server data directory, that is, the relay log. The 3rd thread is a SQL thread that uses this thread from the server to read the relay log and perform the updates that are contained in the log. The show Processlist statement can query for information about replication that occurs on the primary server and from the server.

The default relay log uses the host_name-relay-bin.nnnnnn file name, where host_name is from the server host name and nnnnnn is the serial number. Use sequential serial numbers to create continuous relay log files, starting at 000001. Track the relay log index files from the server to identify the relay logs that are currently in use. The default relay log index file name is Host_name-relay-bin.index. By default, these files are created in the data directory from the server. The relay log is the same format as the binary log and can be read with Mysqlbinlog. When the SQL thread finishes all the events in the relay log, the relay log is automatically deleted.

Create two additional state files from the server in the data directory--master.info and Relay-log.info. The status file is saved on the hard disk and will not be lost when it is shut down from the server. The next time you start from the server, read the files to determine how many binary logs it has read from the primary server, and how much to handle your own relay logs.

Set up master-slave replication:

1. Make sure that the MySQL version installed on the primary server and from the server is the same, and preferably the latest stable version of MySQL.

2. Set up a connection account for replication on the primary server. The account must be granted replication slave permissions. If the account is for replication only (recommended), no additional permissions are required.

Mysql> GRANT REPLICATION SLAVE on *.*

-> to ' replication ' @ '%.yourdomain.com ' identified by ' slavepass ';

3. Execute flush tables with READ lock statement to empty all tables and block write statements:

Mysql> FLUSH TABLES with READ LOCK;

Keep the MySQL client program and do not exit. Open another terminal to take a snapshot of the primary server data directory.

Shell> cd/usr/local/mysql/

Shell> Tar-cvf/tmp/mysql-snapshot.tar./data

If the user account from the server is different from the primary server, you may not want to copy the MySQL database. In this case, the database should be excluded from the archive. You also do not need to include any log files or Master.info or relay-log.info files in the archive.

Read the current binary log name and offset value on the primary server when flush TABLES with read lock are in effect (that is, the MySQL client does not exit):

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 position displays the offset. In this example, the binary log value is mysql-bin.003 and the offset is 73. Record the value. You will need to use these values later when you set up from the server. They represent replication coordinates from which the server should start a new update from the primary server.

If the primary server is not enabled--logs-bin,show master status displays a log name and a location value that is empty. In this case, the value to be used when specifying the log files and locations from the server at a later time is an empty string ("") and 4.

After taking the snapshot and logging the log name and offset, return to the previous midrange to re-enable the write activity:

Mysql> UNLOCK TABLES;

4. Ensure that the [Mysqld] section of the my.cnf file on the master server host includes a log-bin option. The 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 you do not provide those options, you should add them and restart the server.

5. Stop the MYSQLD service from the server and add the following line to its my.cnf file:

[Mysqld]

server-id=2

The slave_id value, like the master_id value, must be a positive integer value between 1 and 232–1. Also, the ID of the server must be different from the ID of the primary server.

6, the data in the directory. Make sure that the permissions are correct for these files and directories. Users of the server MySQL operation must be able to read and write files as if they were on the primary server.

Shell> Chown-r Mysql:mysql/usr/local/mysql/data

7, start from the server. To execute the following statement from the server, replace the option value with the actual value of 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 from server thread:

Mysql> START SLAVE;

After these programs are executed, you should connect to the primary server from the server and supplement any updates that have occurred since the snapshot.

9. If a replication error occurs, an error message will also appear from the server's error log (HOSTNAME.ERR).

10. When replicating from the server, files Master.info and Hostname-relay-log.info are found in their data directories. Use these two files from the server to track how many master servers have been processed by binary logs. Do not remove or edit these files unless you know exactly what you are doing and fully understand its meaning. Even so, it is best to use the change MASTER to statement.