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

What is the master-slave replication of MySQL? How to achieve master-slave synchronization of MySQL server? These problems may be encountered in our daily work. Through these questions, I hope you can gain more. Here are the details of uncovering these problems.

MySQL master-slave replication

Case Overview

In a corporate website, when there is only one back-end MySQL database, there are the following problems:

The single point of failure service is unavailable and cannot handle a large number of concurrent data requests for data loss

The transformation method increases the MySQL database server, carries on the backup to the data, forms the main standby to ensure that the data of the main and standby MySQL database server is the same, the main server is down, the backup server continues to work, the data is guaranteed.

The master-slave replication of MySQL is closely related to the separation of read and write.

More advanced solution

Synchronize data through master-slave replication, and improve the concurrency of the database through read-write separation.

Amoeba amoeba

Case implementation

1. All servers turn off firewalld or set rules

two。 Set up time synchronization environment install ntp time synchronization server on master server use yum install ntp service modify ntp.conf, set master server as time synchronization source, time synchronization on slave server use yum install ntpdate and time synchronization

Install mysql compilation and installation mysql optimization adjustment initialization database start mysql service and set root user password on three database servers

Configure mysql master master server modify / etc/my.cnf configuration file, add server id, configure binary log option

Log in to the mysql service and authorize all permissions to copy binary logs from the server

Configure two slave servers to modify / etc/my.cnf configuration file, add server id, configure binary log option to log in to mysql, and configure master-slave synchronization

Real fuck

Lab environment: prepare 5 virtual machines for centos 7 system

One master server, two slave servers, one amoeba, one client

Experimental topology diagram:

All three mysql servers install ntp and ntpdate first.

Used to configure time synchronization [root@master ~] # yum install ntp-y [root@slave1 ~] # yum install ntp ntpdate-y [root@slave2 ~] # yum install ntp ntpdate-y

Modify ntp configuration file

Set the ntp configuration file under the master server, then turn on ntpd and turn off the firewall

[root@master ~] # vim / etc/ntp.confserver 0.centos.pool.ntp.org iburstserver 1.centos.pool.ntp.org iburstserver 2.centos.pool.ntp.org iburstserver 3.centos.pool.ntp.org iburst'server 127.127.247.0 / / set the local clock source Here 127.127 refers to your own 192.168'fudge 127.127.247.0 stratum 8 / / set the time level (time ring) of the time ring to 8 [root@master ~] # systemctl start ntpd [root@master ~] # systemctl stop firewalld [root@master ~] # setenforce 0 [root@master ~] # systemctl enable ntpdCreated symlink from / etc/systemd/system/multi-user.target.wants/ntpd.service to / usr/lib/systemd/system/ntpd.service.

Configure slave server slave1, enable ntpd service directly, synchronize time, and match master server (ip address) time

[root@slave1 ~] # systemctl start ntpd [root@slave1 ~] # systemctl stop firewalld [root@slave1 ~] # setenforce 0 [root@slave1 ~] # systemctl enable ntpdCreated symlink from / etc/systemd/system/multi-user.target.wants/ntpd.service to / usr/lib/systemd/system/ntpd.service. [root@slave1 ~] # / usr/sbin/ntpdate 192.168.247.1608 Jan 18:39:26 ntpdate [114393]: the NTP socket is in use, exiting

Slave1 synchronization is complete, followed by synchronization of slave2

[root@slave2 ~] # systemctl start ntpd [root@slave2 ~] # systemctl enable ntpdCreated symlink from / etc/systemd/system/multi-user.target.wants/ntpd.service to / usr/lib/systemd/system/ntpd.service. [root@slave2 ~] # systemctl stop firewalld [root@slave2 ~] # setenforce 0 [root@slave2 ~] # / usr/sbin/ntpdate 192.168.247.1608 Jan 18:40:38 ntpdate [82655]: the NTP socket is in use, exiting

The next step is to install MySQL and optimize it.

Due to the complexity of the process, please refer to my previous blog for details:

Https://blog.51cto.com/14557905/2458283

Complete the installation of mysql and complete a series of optimizations

Start master-slave synchronization

1. Modify the master server configuration file

[root@master mysql-5.6.26] # vim / etc/my.cnf// is written on the log-bin=master-bin// under mysqld to enable the binary file log-slave-update=true// to enable updating from the server server-id = 11 / server id to 11 (id cannot be repeated)

Restart the service

[root@master mysql-5.6.26] # service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!

The master server logs in to mysql, creates a user for the slave server and allows all data to be copied (ON. )

[root@master mysql-5.6.26] # mysql- uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.mysql > GRANT REPLICATION SLAVE ON *. * TO 'myslave'@'192.168.247.%' IDENTIFIED BY' abc123';// allows replication, from the server as myslave user, from the network segment of 192.168.247.0, using the password of abc123, to copy all databases and the following tables (*. *) Query OK, 0 rows affected (0 sec) mysql > flush privileges / / Refresh database Query OK, 0 rows affected (0.00 sec) mysql > show master status / / View the location of the primary server The synchronization location point of the slave server is the following 412 audio + | File | Position | Binlog_Do_DB | Binlog_Ignore _ DB | Executed_Gtid_Set | +-+ | master-bin.000001 | 412 | | | +-+ 1 row in set (0.00 sec) |

Configure slave server slave1, server id is not consistent, enable relay log, index relay log

[root@slave1 mysql-5.6.26] # vim / etc/ my.cnf [mysqld] / / write under mysqld log-bin=mysql-binserver-id = 22 Accord / another slave2 id is 23relay-log=relay-log-binrelay-log-index=slave-relay-bin.index [root@slave1 mysql-5.6.26] # service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!

Log in to mysql from the server, add the master server (ip address, use the account mysalve of the master server, enter the password of the master server account, determine the synchronization binaries, synchronization location point)

[root@slave1 mysql-5.6.26] # mysql- uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.mysql > change master to master_host='192.168.247.160',master_user='myslave',master_password='abc123',master_log_file='master-bin.000001',master_log_pos=412;// add master server Query OK, 0 rows affected, 2 warnings (0.01 sec)

Enable slave server function

Mysql > start slave;Query OK, 0 rows affected (0.01 sec)

View slave server status

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.247.160 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 412 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000001' Slave_IO_Running: Yes / / show Indicates that the slave function is enabled. 'Slave_SQL_Running: Yes / / shows that the slave function is enabled. Exec_Master_Log_Pos: 412 Relay_Log_Space: 455 Master_Server_Id: 11 Master_UUID: e9a82741-3223-11ea-af25-000c29524d89 Master_Info_File: / home/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for the slave thread to update it Master_Retry_Count O thread to update it Master_Retry_Count: 864001 row in set (0.00 sec)

Synchronous configuration is complete, and then test

Master server, create a school database

Mysql > create database school;Query OK, 1 row affected (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | test | +-+ 5 rows in set (0.00 sec)

View directly from the server

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | test | +-+ 5 rows in set (0.00 sec)

The above is the master-slave replication introduction of MySQL and the method of realizing master-slave synchronization in MySQL server. Do you have anything to gain after reading it? If you want to know more about it, you are welcome to follow the industry information!

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.