Turn: MySQL uses the master-slave replication mechanism (replication)

Content transferred from: http://blog.sina.com.cn/s/blog_4e424e2101000c1z.html;http://blog.sina.com.cn/s/blog_4e424e2101000c20.html

MySQL supports one-way, asynchronous replication, during which one server acts as the primary server, and one or more other servers act as slave servers. The primary 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 is connected to the primary server, it notifies the primary server where the last successful update was read from the server in the log. Receives from the server any updates that have occurred since then, and then blocks and waits for the primary server to notify the next update.

Why use master-slave replication?

1. The master server/slave server setting adds robustness. When there is a problem with the primary server, you can switch to recover from the server.

2. Better customer response time can be achieved by slicing the load of customer queries between the primary server and the server. However, do not update on the master and slave servers at the same time, which can cause conflicts.

3. Another benefit of using replication is that you can use one to perform backups from the server without interfering with the primary server. The master server can continue to process updates during the backup process.

The principle of Master-slave replication:

MySQL uses 3 threads to perform the replication function (1 of which are on the primary server and 2 on the slave server).

1th Thread: 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.

2nd thread: The primary server creates a thread that sends the contents of the binary log to the slave 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, which is the relay log.

The 3rd thread is a SQL thread that uses this thread from the server to read the trunk log and perform the updates contained in the log. The SHOW processlist statement can query information about replication that occurs on the primary server and from the server.

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

Create two additional state files--master.info and Relay-log.info from the server in the data catalog. The state file is saved on the hard disk and is not lost when the server is shut down. 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 it handles its own trunk logs.

To set up master-slave replication:

1. Make sure that the MySQL version installed on the master and slave servers 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 used for replication only (recommended for this), no additional permissions will be granted.

Mysql> GRANT REPLICATION SLAVE on *. *

-Replication ' @ '%.yourdomain.com ' identified by ' slavepass ';

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

Mysql> FLUSH TABLES with READ LOCK;

Keep the MySQL client program from exiting. Open another terminal to take a snapshot of the master server data directory.

Shell> cd/usr/local/mysql/

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

If the user account from the server differs from the primary server, you may not want to replicate 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 flush TABLES with read lock is in effect (that is, the MySQL client program does not exit), read the current binary log name and offset value on the primary 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 position displays the offset. In this example, the binary log value is mysql-bin.003 and the offset is 73. Record the value. You need to use these values later when you set up from the server. They represent replication coordinates from which the server should start new updates from the primary server.

If the primary server is running without the--logs-bin,show master status displayed, the log name and location values are empty. In this case, when you specify the log files and locations from the server later, you need to use the empty string (' ') and 4.

After taking a snapshot and logging the log name and offset, go back to the previous midrange to re-enable 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. 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

binlog-do-db = TestDB #用于master-slave-mode database

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 in 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 from the server must not be the same as the ID of the primary server.

6. The data is kept in the directory. Ensure that the permissions on these files and directories are correct. The user running the server MySQL must be able to read and write files as if it were on the primary server.

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

7. Start from the server. Replace the option value with the actual value of your system by executing the following statement from the server:

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 thread from the server:

Mysql> START SLAVE;

After you execute these programs, you should connect to the master 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 directory. Use these two files from the server to track how many primary server binary logs have been processed. 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.

Turn: MySQL uses the master-slave replication mechanism (replication)