Mysql master-slave replication configuration method

Mysql master-slave replication configuration method

MySQL supports unidirectional and asynchronous replication. During the replication process, one server acts as the master server, and one or more other servers act as the slave server.. 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 the local file in the data directory of the slave server, that is, the relay log. 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 host_name-relay-bin.nnnnnn format, where host_name is the slave server host name and nnnnnnnn is the sequential column 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.

1. Configure the master server

Enable mysql Configuration

vim/usr/local/webserver/mysql/my.cnf 

Enable server_id and bin-log

Server_id = 'unique value' (enable server_id)

Log-bin =/data/mysql/3306/binlog

2. Configure the slave server 

Enable mysql Configuration

 vim /usr/local/webserver/mysql/my.cnf 

Enable server_id and bin-log

Server_id = 'unique value' (enable server_id)

log-bin = /data/mysql/3306/binlog/binlog  

After modifying the configuration, remember to restart the Database service mysqld restart.

Key point: the slave database must have a user who can connect to the master server on the slave database machine.

3. In the primary database, assign a user to the slave Database

Enter the mysql/usr/local/webserver/mysql/bin/mysql-p Password

Mysql> grant all on *. * to user @ slave database ip address identified by 'Password ';

Flush privileges; refresh permission

View the show master status in the master database. The latest bin-log File Name of the master database.

4. Go to the slave database and execute the following SQL statement

Check whether the slave database can access

mysql /usr/local/webserver/mysql/bin/mysql -u

The user-p password-h ip address of the primary database given by the primary database. If you can access the database, exit

mysql /usr/local/webserver/mysql/bin/mysql -u

Password

In particular, the slave database stop slave must be disabled before the change is executed;

Mysql> change master to master_host = 'master database ip', master_user = 'master database authorized Username ', master_password = 'master database authorized password ', master_log_file = 'file name of the bin-log of the primary database', master_log_pos = 'position value of the bin-log of the primary database'

5. Enable the slave database start slave;

6. view the slave database status show slave status \ G;

change master to master_host='101.200.136.226',master_user='zt',master_password='zt',master_log_file=' binlog.000001 ',master_log_pos=4826; 

If
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

OK

Master/Slave configuration complete

Binlog command

/usr/local/webserver/mysql/bin/mysqlbinlog binlog.000001

View binlog log files

Reset master binlog Initialization

Show master status; view the latest log files and position values

Flush logs refresh log files

Start slave enable slave Database

Stop slave Database

Show slave status; view slave database status

Restore data through binlog

Enter the binlog directory

cd /data/mysql/3306/binlog/ 

Viewing the binlog list ls

View binlog log files

/usr/local/webserver/mysql/bin/mysqlbinlog binlog.000001 

Restore data

/Usr/local/webserver/mysql/bin/mysqlbinlog binlog.000001 |/usr/local/webserver/mysql/bin/mysql-p (restored before binlog is cleared)

If a reset master is executed, it cannot be restored.

The above is to sort out the configuration data for master-slave replication of the mysql database. If you need to develop a database, you can check it.