Install and configure mysql master-slave environment _ MySQL

Install and configure mysql master-slave environment bitsCN.com

MySQL supports unidirectional and asynchronous replication. one server acts as the master server during the replication process, and one or more other servers act as slave servers. The master server writes updates to the binary log file and maintains an index of the log file to track log loops. When an slave server is connected to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of the next update.

Why do I use master-slave replication?

1. the master server/slave server settings increase robustness. When the master server encounters a problem, you can switch to the slave server as a backup.

2. by splitting the customer query load between the master server and slave server, a better customer response time can be obtained. But do not perform updates on the master/slave servers at the same time, which may cause conflicts.

3. another advantage of using replication is that one slave server can be used for backup without interfering with the master server. During the backup process, the master server can continue to process updates.

MySQL uses three threads to execute the replication function (one of them is on the master server and the other two are on the slave server. When start slave is issued, an I/O thread is created from the server to connect to the master server and send binary logs to the master server. The master server creates a thread to send the binary log content to the slave server. Read the content sent by the Binlog Dump thread of the master server from the server I/O thread and copy the data to a local file in the data directory of the slave server, that is, relay logs. The first thread is an SQL thread. the server uses this thread to read relay logs and execute updates contained in the logs. The show processlist statement can be used to query information about replication on the master server and slave server.

The default relay log uses a file name in the form of a host_name-relay-bin.nnnnnn, where host_name is the slave server host name and nnnnnnnn is the serial number. Create a continuous relay log file with a continuous serial number, starting from 000001. Track the relay log index file from the server to identify the currently used relay logs. The default relay log index file name is the host_name-relay-bin.index. By default, these files are created in the data directory of the slave server. Relay logs are in the same format as binary logs and can be read using mysqlbinlog. After the SQL thread executes all the events in the relay log, the relay log is automatically deleted.

The slave server creates two more state files --master.info and relay-log.info in the data directory. Status files are stored on the hard disk and will not be lost when the slave server is closed. When starting the next time from the server, read these files to determine how many binary logs it has read from the master server and how much it will process its own relay logs.
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 emuser@192.168.5.61 identified by 123456;
Flush privileges;
Modify the configuration of my. cnf on the master node
Wait_timeout = 30000000
Interactive-timeout = 30000000
Binlog-do-db = cdn_view # set the binary log record Library
Log-bin = mysql-bin # open mysql binary log
Binlog-ignore-db = mysql # set the database where binary logs are not recorded
Server-id = 12760 # set mysql_id. the master and slave cannot be the same.
Long_query_time = 5
Expire_logs_days = 3
2. modify the configuration of my. cnf for slave.
Wait_timeout = 30000000
Interactive-timeout = 30000000
Log-bin = mysql-bin
Servers-id = 12761
Replicate-do-db = cdn_view # set the database to be synchronized
Replicate-ignore-db = mysql # set a database that is not synchronized
Log-slave-updates # Record binary logs after synchronization
Slave-skip-errors = all
Slave-net-timeout = 60
Sync_binlog = 1
Binlog_format = mixed

######################################## ####################################
Restart the master-slave mysqld service, log on to the master mysql, and execute flush tables with read lock on the master;
Copy the data of the cdn_view database to the top, and record the result of show master statusG on the master:
Mysql> 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 run the unlock tables command;
Log on to mysql and run:
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 information is displayed, the master-slave synchronization is successful.
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

BitsCN.com