Shulou(Shulou.com)06/01 Report--

Some versions of mysql do not turn on binary logging, so be sure to check if it is enabled. If it is not enabled at first and needs to be opened later, the database server must be restarted, and the restart of the database server will have a great impact on the business. Therefore, although binary logging has a slight impact on performance, it is recommended to turn on whether or not you want to use replication and backup functions (incremental logging also depends on binary logs).

Currently, mysql supports two types of replication:

1. Binary log point

2.GTID (mysql > = 5.7recommended)

Some configurations will not take effect until they are restarted. In order not to affect the normal use of the database, it is best to configure them before going online. In particular, the configuration of the master server should be configured as an initial parameter.

Log_bin:mysql-bin is the prefix for log files (the reason for separating log files from data files is to improve io performance)

Server_id: used to distinguish between different servers

Log_bin:mysql-bin is the prefix for log files (the reason for separating log files from data files is to improve io performance)

Server_id: used to distinguish between different servers

Relay_log:slave relay logs should also be separated from data files to improve io performance

Read_only: read-only (makes it impossible for all users without super permission to write on the slave server, regardless of whether the user has write permission or not. The advantage of this is to avoid the problem of master inconsistency caused by misoperation of writing to the slave server. However, this parameter does not restrict users with super privileges, such as root accounts. In order to solve this problem, mysql5.7 later introduced the parameter super_read_only, which limits the ability of users with super privileges to write from the server.

Skip_slave_start: the replication link is not automatically started when the slave server is restarted (by default, mysql automatically starts the replication link when the slave is restarted, and if there is a problem, the master-slave replication link is interrupted. So normally, we should check if there is a problem after the server is rebooted, and then manually start the master-slave replication link)

Master_info_repository and relay_log_info_repository: store the information of the master and slave servers in the innodb table, which is stored in the file system by default, so that if the slave server goes down, it is easy to get file records out of sync with the actual synchronization information. By storing the relevant information in the table, we can make use of the rich recovery mechanism of innodb to ensure the consistency of recorded data.

Set up a replication account on master:

(note: only REPLICATION and SLAVE permissions are set)

Initialize slave data:

Start the log point-based replication link:

Master-slave replication demonstration:

Master server configuration:

First, check whether the binlog log of the master server is enabled, and configure server-id (here it is configured as the last three digits of ip):

From the server (slave) configuration:

Configure server-id and relay_log, master_info_repository, relay_log_info_repository, plus read_only

Manually change the server-id of master to 100 (because master is not restarted):

Slave does not have business access, so it can be restarted:

(if it is mysql5.7 or above, there is another problem to note: a UUID value has been added. By default, there is an auto.cnf file in the data directory. If you install the mysql server by image, the server-uuid should be the same, so you need to delete the UUID and restart to automatically generate a new UUID value. There will be problems with the same master-slave replication of uuid)

Establish a replication account on the primary server and authorize:

(in this case: the slave server is all on the 192.168.3.% network segment)

Mysql fully initializes the data from the server:

Copy everything to the slave server:

Copy the configuration of the link (slave server):

(dba_repl is the master-slave replication account created by master, master_log_file is the binary log file of the master server, and master_log_pos is the log point)

How to view the master_log_file and master_log_pos of the master server (for example, it is shown independent of this example):

Start the slave server:

Check the status of slave to see if it starts:

Execute the command show slave status:

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.