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

Quickly build MySQL master-slave synchronization

Summary: in the actual production environment, if the read and write of the database are operated in the same database server, it can not meet the time requirements in terms of security, high availability, high concurrency and so on.

Generally speaking, it is through master-slave replication (Master-Slave) to synchronize data, and then through read-write separation to improve the concurrent load capacity of the database.

Master and subordinate use

Real-time disaster recovery, used for failover. If there is a problem with the master database, you can switch to the slave database.

Separation of reading and writing, providing query service

Data backup to avoid affecting the business

The following is analyzed through an experimental case.

One master MySQL and two slave MySQL

Master 192.168.30.33Universe 24

From 192.168.30.34 to 24

192.168.30.35/24

First turn off the firewall and selinx

Service firewalld stop

Setenforce 0

1. Establish a time synchronization environment and build a time synchronization server on the master node.

Yum install-y ntp

[root@lin3033 ~] # vi / etc/ntp.conf

About 17 lines of modification to add constraint segments and subnets

[root@lin3033 ~] # systemctl start ntpd

Next, modify the configuration file of the main MySQL

[root@lin3033 ~] # vi / etc/my.cnf

Add the following under the global configuration module

[root@lin3033 ~] # systemctl restart mysqld

Next, configure two slave services.

Slave1:

Yum install-y ntp

Ntpdate 192.168.30.33

Echo'* / 30 * / usr/sbin/ntpdate 192.168.30.33'> > / var/spool/cron/root

[root@lin3035 ~] # vi / etc/my.cnf

[root@lin3035 ~] # systemctl restart mysqld

Slave2:

Yum install-y ntp

Ntpdate 192.168.30.33

Echo'* / 30 * / usr/sbin/ntpdate 192.168.30.33'> > / var/spool/cron/root

[root@lin3034 ~] # vi / etc/my.cnf

[root@lin3034 ~] # systemctl restart mysqld

Database operation with one master and multiple slaves

Master:

Authorized users have the appropriate permissions

GRANT REPLICATION SLAVE ON *. * TO 'myslave'@'192.168.30.%' IDENTIFIED BY' abc123'

Authorize myslave to log in to 192.168.30. % network segment

FLUSH PRIVILEGES

Show master status; / / to make a note that the value of the Position column will change, you need to confirm that there is no change before you proceed to the next step.

Slave1:

Mysql > change master to master_host='192.168.30.33', master_user='myslave', master_log_file='mysql-bin.000001', master_log_pos=471

Mysql > start slave

Slave 2

Mysql > change master to master_host='192.168.30.33', master_user='myslave', master_log_file='mysql-bin.000001', master_log_pos=471

Mysql > start slave

Verify whether the two Slave_IO_Running and Slave_SQL_Running are YES. Both YES means that the mysql master-slave replication configuration is successful.

Mysql > show slave status\ G

Slave 1

Slave 2

If you are worried, you can create a library in the main server, and then see if there are any

Add a database to the main MySQL

Mysql > create database client

View in two from the server

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.