Shulou(Shulou.com)05/31 Report--

This article focuses on "how to install and configure mysql master-slave environment", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to install and configure the mysql master-slave environment.

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

Why use master-slave replication?

1. The master / slave server setting adds robustness. When there is a problem 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 at the same time on the master and slave servers, which may cause conflicts.

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

MySQL uses three threads to perform the replication function (one on the master server and the other two on the slave server. When the START SLAVE is issued, create an Imax O thread from the slave server to connect to the master server and have the master server send binary logs. The master server creates a thread to send the contents of the binary log to the slave server. The content sent by the master server Binlog Dump thread is read from the slave server Icano thread and copied to the local file in the slave server data directory, that is, the relay log. The third thread is the SQL thread, which is used from the server to read the relay log and perform updates contained in the log. The SHOW PROCESSLIST statement can query information about replication that occurs on the master and slave servers.

The default relay log uses a file name in the form of host_name-relay-bin.nnnnnn, where host_name is the hostname of the slave server and nnnnnn is the serial number. Create a continuous relay log file with a sequential serial number, starting at 000001. Track the relay log index file from the server to identify the relay log that is 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 of the slave server. The relay log is in the same format as the binary log and can be read with mysqlbinlog. When the SQL thread finishes executing all the events in the relay log, the relay log is automatically deleted.

From the server, create two additional state files, master.info and relay-log.info, in the data directory. The status file is saved on the hard drive and will not be lost when shutting down from the server. The next time you boot from the slave server, read these files to determine how many binary logs it has read from the master server and the extent to which it has processed its own relay logs.

Master master:192.168.5.60

From slave:192.168.5.61

1. Create a synchronization user in master mysql

Grant emuser slave,file on *. * to identified by 123456

Flush privileges

Modify the configuration of my.cnf for master

Wait_timeout = 30000000

Interactive-timeout = 30000000

Binlog-do-db=cdn_view # set up the library for binary logging

Log-bin=mysql-bin # Open mysql binary log

Binlog-ignore-db=mysql # # set libraries that are not recorded by binary logs

Server-id = 12760 # set mysql_id, master and slave cannot be the same

Long_query_time = 5

Expire_logs_days= 3

2. Modify the configuration of my.cnf in slave

Wait_timeout = 30000000

Interactive-timeout = 30000000

Log-bin=mysql-bin

Server-id = 12761

Replicate-do-db=cdn_view # set up synchronized libraries

Replicate-ignore-db=mysql # sets out-of-sync libraries

Log-slave-updates # record binary log after synchronization

Slave-skip-errors=all

Slave-net-timeout=60

Sync_binlog=1

Binlog_format=mixed

#

Restart the master-slave mysqld service, log in to the master mysql, and execute flush tables with read lock on the master

Then copy the data of cdn_view to the slave, and record the result of the show master statusG on the master:

> show master statusG

* * 1. Row *

File: mysql-bin.000009

Position: 341

Binlog_Do_DB: cdn_view

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

Then execute unlock tables

Log in to the slave mysql and execute on the slave:

Stop slave

Change master to master_host=192.168.5.60,master_user=emuser,master_password=123456, master_log_file=mysql-bin.000009, master_log_pos=341

Start slave

Show slave statusG

If the following message appears, the master-slave synchronization is successful.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

At this point, I believe you have a deeper understanding of "how to install and configure the mysql master-slave environment". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.