MySQL master-slave configuration steps

Master-Slave replication principle:

For enterprise environments with large throughput, the master-slave replication technique is to create a database that is identical to the primary database server and can switch to continue working from the database server when the primary database fails. Reduce the damage caused during the failure as short as possible. In the MySQL master-slave replication architecture, the master server writes the data in the database at the time of the local write operation, writes it to a copy of the binary log, and then starts a mysql_dump thread for each slave server in the master server. The binary log information is sent in plaintext to the I/O thread threads from the server (so for security reasons, the master-slave synchronization should only be carried out in the intranet, or transmitted using SSL encryption) from the server after receiving the binary log, save it in the trunk log, The SQL thread thread is then started locally from the server, which reads an event from the log and applies an event. This implementation is implemented locally to apply this data update. mysql5.6 supports multi-threaded replication, which means that SQL thread can start multiple, can have multiple threads simultaneously reading data from the log, while parallel application, can greatly improve the speed from the server. However, multithreaded replication supports only one library, and multiple threads can be opened if there are multiple libraries. If the dump thread of the primary server fails, it will cause I/O thread threads from the server to not work.

Requirements for building master-slave replication:

The version of the database is required to be consistent. (Major and minor version numbers)

There are databases that need to be synchronized

Synchronization requires a database account

To authorize a sync account

The primary server can have multiple slave servers, but only one master server from the server

Basic procedures for Master-slave replication:

First, master configuration

1. Enable the binary log

Vim/etc/my.cnf

Log-bin = Master-bin is added #二进制日志选项要在 the [mysqld] segment.

Log-bin-index = Master-bin.index

2, start the synchronous binary log, the event will not write to the buffer, and write directly to the binary log.

Sync-binlog = on #要写在 [mysqld] Segment

3. Select a unique Server-id number

Server-id = 1 #主从服务器的server The-id number cannot be the same.

4. Create a Database Sync account

Grant Replication Slave on * * to ' repluser ' @ ' from the server's IP ' identified by ' 123456 ';

Second, slave configuration

1. Enable the relay log

Vim/etc/my.cnf

Relay-log = Relay-log is added #中继日志也要在 the [mysqld] segment.

Relay-log-index = Relay-log.index

2. Enable read-only

Read-only = on #对具有super权限的用户, still able to write

3, choose a unique Server-id #一定不能和主服务器的server-id the same

Server-id = 2

4. Connect to the primary server

Change Master to Master_host = ' 192.168.10.1 ', master_port = ' 3306 ', master_user = ' Repluser ', Master_password = ' 123456 ' , Master_log_file = ' master-bin.0000019 ', master_log_pos = 633;

After execution, the MySQL data directory will generate two files, one is Master.info, used to record the primary server's binary log, position location, home server IP, port, synchronization user, password, time-out and other information. One is relay-log.info, used to record the current relay log and location and the binary log and location on the primary server being read

5. Start slave

Start salve;

6, check the synchronization status of slave

show slave status;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.10.1 #主服务器的IP地址

Master_user:repl_user #复制帐号

master_port:3306 #主服务器的mysqld端口号

Connect_retry:60 #超时重试时间

master_log_file:master-bin.000019 #主服务器的当前二进制日志

read_master_log_pos:27472961 #已经读到的二进制日志位置

relay_log_file:slave-relay-bin.000009 #从服务器所处的中继日志

relay_log_pos:1531586 #从服务器所处的中继日志位置

relay_master_log_file:master-bin.000019 #从服务器的中继日志

Slave_io_running:yes #I/othread thread state must be Yes (important)

Slave_sql_running:yes #SQLthread线程状态必须是YES (important)

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0 #上次执行遇到的错误

Last_error: #错误信息

skip_counter:0 #没有执行的事件

exec_master_log_pos:27472961 #已经执行到的二进制位置

relay_log_space:1534068 #中继日志的剩余空间

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no #是否允许SSL加密

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0 #从服务器比主服务器慢多少

Master_ssl_verify_server_cert:no

last_io_errno:0 #错误数量

Last_io_error: #错误信息

last_sql_errno:0

Last_sql_error:

Replicate_ignore_server_ids:

Master_server_id:1

1 row in Set (0.00 sec)

ERROR:

—————————————————————————————————————————————

Third, achieve semi-synchronous replication

When the primary server times out and is not contacted from the server, the primary server is automatically downgraded to semi-synchronous mode

1. Primary server Configuration

To install the plugin:

INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

SHOW GLOBAL VARIABLES like ' rpl% '

Rpl_recovery_rank 0

rpl_semi_sync_master_enabled OFF #半同步功能是否开启

Rpl_semi_sync_master_timeout 10000 #从服务器的超时时间, after this time, the primary server will no longer wait for the response from the server, in milliseconds.

Rpl_semi_sync_trace_level #追踪级别

Rpl_semi_sync_master_wait_no_slave on #是否必须有一个可用的slave

Configure Open semi-sync: Add in profile [Mysqld] segment in primary server (permanent, requires restart mysqld)

rpl_semi_sync_master_enabled = 1

Rpl_semi_sync_master_timeout = #1秒

Temporary entry: in MySQL command line input

SET GLOBAL VARIABLES rpl_semi_sync_master_enabled = 1

SET GLOBAL VARIABLES rpl_semi_sync_master_timeout = 3000

SHOW GLOBAL VARIABLES like ' rpl% '; #查看同步状态, you must restart the Io_thread thread from the server after the command line is set.

Rpl_semi_sync_master_clients 0

2, from the server configuration

To install the plugin:

INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

SHOW VARIABLES like ' rpl% '

Rpl_recovery_rank 0

rpl_semi_sync_slave_enabled OFF #是否启动

Rpl_semi_sync_slave_trace_lavel #追踪级别

Configure turn-on semi-sync: Add in the profile [Mysqld] segment from the server (permanent, requires restart mysqld)

rpl_semi_sync_slave_enabled = 1

Temporary entry: in MySQL command line input

SET GLOBAL VARIABLES rpl_semi_sync_slave_enabled = 1

STOP SLAVE Io_thread; START SLAVE Io_thread;

This article from "Beginner's mind, always" blog, declined reprint!

MySQL master-slave configuration steps