MySQL's dual-machine hot standby

MySQL's dual-machine hot standby

Dual-Machine hot standby, is to maintain the status of two databases automatically synchronized. Operations on any one database are automatically applied to another database, keeping two of the database data consistent.

The advantage of this approach is that:

1, can do disaster preparedness, one of the bad can switch to another;

2, can do load balancing, can request to spread to any of the platform, improve website throughput.

I'm using mariadb here.

[Email protected] ~]# mysql-v

MySQL Ver 15.1 distrib 5.5.52-mariadb, for Linux (x86_64) using ReadLine 5.1

1. Create a user on database A and authorize login

Grant Replication Slave on * * to ' rep ' @ ' 172.25.254.131 ' identified by ' 123456 ';

2. Turn on the binarylog of a server

Vim/etc/my.cnf

Log-bin = Mysql-bin #开启mysql的binlog日志功能

Binlog_format = Mixed #binlog日志格式

Server-id = 1 #服务器id号

Read-only=0 #关闭只读, can read and write

Binlog-do-db=laravel #指定对db_nameA记录二进制日志

Binlog-ignore-db=mysql #指定不对db_nameB记录二进制日志

#relay_log =mysql-relay-bin# Open the Relay-log log, the Relay-log log is logged from the server I/O thread that reads the primary server's binary log to the local file from the server. The SQL thread then reads the contents of the Relay-log log and applies it to the slave server

3, log on to the B server, turn on the relay

Vim/etc/my.cnf

Log-bin = Mysql-bin #开启mysql的binlog日志功能

Binlog_format = Mixed #binlog日志格式

Server-id = 1 #服务器id号

Read-only=0 #关闭只读, can read and write

Binlog-do-db=laravel #指定对db_nameA记录二进制日志

Binlog-ignore-db=mysql #指定不对db_nameB记录二进制日志

relay_log=mysql-relay-bin# The Relay-log log is turned on, the Relay-log log is logged from the server I/O thread that reads the primary server's binary log to the local file from the server. The SQL thread then reads the contents of the Relay-log log and applies it to the slave server

4. Turn on sync on server B

Change MASTER to master_host= '172.25.254.134', master_user= 'rep', master_password= '123456 ', master_log_file= 'mysql-bin.000006', master_log_pos=610;

5. Restart the B server database

Systemctl Restart MARIADB

6, check the status of synchronization

MariaDB [(none)]> show Slave status\g

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

Slave_io_state:waiting for Master to send event

master_host:172.25.254.134

Master_user:rep

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000006

read_master_log_pos:610

relay_log_file:mariadb-relay-bin.000003

relay_log_pos:529

relay_master_log_file:mysql-bin.000006

Slave_io_running:yes

Slave_sql_running:yes

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:610

relay_log_space:825

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

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:2

7, the above, such a change can only be implemented server a synchronization to Server B, is one-way.

To reverse the change of Server B can also be synchronized to server A, just one more time to do the above!

This article is from the "Ching, you share" blog, please be sure to keep this source http://chenxiaotao.blog.51cto.com/11430805/1959053

MySQL's dual-machine hot standby