Shulou(Shulou.com)05/31 Report--

This article will explain in detail how the master-slave replication of the Mysql database is, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Demand

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

Solution

Generally, data is synchronized by master-slave replication (Master- Slave), and then read separation is used to improve the concurrent load capacity of the database.

Principle of master-slave replication

1. Before the update data of each transaction is completed, the master server records these changes in the binary file, and after writing the binary log file, the master server notifies the storage engine to commit the transaction.

2.Slave copies the Binary log of Master to its relay log. First, Slave starts a worker thread-the Imax O thread, which opens a normal connection on the Master, and then starts Binlog dump process. Binlog dump process reads events from Master's binary log, and if it has caught up with Master, it sleeps and waits for Master to generate new events. The Icano thread writes these events to the relay log.

3.SQL slave thread (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log and replays the events to update the data in Slave to make it consistent with the data in Master. As long as the thread is consistent with the IWeiO thread, the relay log is usually in the cache of OS, so the overhead of the relay log is very small.

Experimental environment

Hostname operating system IP address package master CentOS 7.4 192.168.65.138 mysql-5.5.24.tar.gzSlave1 CentOS 7.4 192.168.65.159 mysql-5.5.24.tar.gzSlave2 CentOS 7.4 192.168.65.142 mysql-5.5.24.tar.gz

Experimental goal

Implementing Master-Slave replication of mysql Database

Experimental procedure

First, establish a time synchronization environment and set up a time synchronization server on the main node

1. Install NTP (system installed by default)

two。 Configure NTP

Vim / etc/ntp.conf # add the following 2 lines server 127.127.65.0 # Local clock source fudge 127.127.65.0 stratum 8 # set the time level to 8

3. Start the service

Systemctl start ntpd

4. Time synchronization on the slave node

Yum install ntp ntpdate-y/usr/sbin/ntpdate 192.168.65.138 # for time synchronization

5. Turn off the firewall and start the service

Systemctl start ntpd.servicesystemctl stop firewalld.service setenforce 0

Second, install the MySQL database on master,slave1,slave2

Configure on three servers at the same time:

1. Decompression package

Tar zxvf mysql-5.5.24.tar.gz-C / opt/

two。 Install the environment package

Yum install gcc gcc-c++ make cmake ncurses-devel bison libaio-devel-y

3. Add mysql users and join the mysql group

Useradd-s / sbin/nologin mysqlmkdir / usr/local/mysql

4. Compilation and installation

Cd / opt/mysql-5.5.24.tar.gz

Cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql

-DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock

-DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci

-DWITH_EXTRA_CHARSETS=all

-DWITH_MYISAM_STORAGE_ENGINE=1

-DWITH_INNOBASE_STORAGE_ENGINE=1

-DWITH_MEMORY_STORAGE_ENGINE=1

-DWITH_READLINE=1

-DENABLED_LOCAL_INFILE=1

-DMYSQL_DATADIR=/home/mysql

-DMYSQL_USER=mysql

-DMYSQL_TCP_PORT=3306

Make & make install

5. Modify the owner and group of the mysql installation directory

Chown-R mysql.mysql / usr/local/mysql

6. Optimization and adjustment

Export PATH=$PATH:/usr/local/mysql/bin/\ * Refresh on boot *\ cp support-files/my-medium.cnf / etc/my.cnf cp support-files/mysql.server / etc/init.d/mysqld chmod 755 / etc/init.d/mysqld chkconfig-- add / etc/init.d/mysqld chkconfig mysqld-- level 35 on

7. Initialize the database

/ usr/local/mysql/scripts/mysql_install_db\

-- user=mysql\

-- ldata=/var/lib/mysql\

-- basedir=/usr/local/mysql\

-- datadir=/home/mysql

8. Establish a soft connection directly

Ln-s / var/lib/mysql/mysql.sock / home/mysql/mysql.sock

9. Modify the configuration file

Vi / etc/init.d/mysqldbasedir=/usr/local/mysqldatadir=/home/mysql

three。 Configure the primary server

1. Modify the configuration file

Vim / etc/my.cnf server-id = 11 # id number is unique and cannot be the same as other servers log-bin=master-bin # master server log file log-slave-updates=true # update binary logs from the server

two。 Restart the mysql server

Systemctl restart mysqld.service

3. Log in to mysql to authorize the slave server

Mysql-u root-p # Log in to mysqlGRANT REPLICATION SLAVE ON *. * TO 'myslave'@'192.168.65.%' IDENTIFIED BY' 123456permission; # Refresh to authorize replication permissions from the server FLUSH PRIVILEGES; # to let the settings take effect

4. View primary server status

four。 Slave1 configuration from server

1. Modify the configuration file

Vim / etc/my.cnfserver-id = 22 # id number cannot be the same as other servers relay-log=relay-log-bin # synchronize log files from the master server to the local relay-log-index=slave-relay-bin.index # define the location and name of the relay-log

2.. Log in to mysql and set up master-slave synchronization

Mysql-u root-p # Login mysqlchange master to master_host='192.168.65.159',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=339; # Settings to synchronize with the master server start slave; # enable synchronization show slave status\ G # View status Slave_IO_Running: Yes # yes indicates successful io connection: Slave_SQL_Running: Yes # yes indicates that the mysql process synchronously connects successfully

five。 Slave2 configuration from server

1. Modify the configuration file

Vim / etc/my.cnfserver-id = 33 # id number cannot be the same as other servers relay-log=relay-log-bin # synchronize log files from the master server to the local relay-log-index=slave-relay-bin.index # define the location and name of the relay-log

2.. Log in to mysql and set up master-slave synchronization

Mysql-u root-p # Log in to mysqlchange master to master_host='192.168.65.142',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=339; # set master-slave synchronization start slave # enable synchronization function show slave status\ G # Vertical View synchronization connection status Slave_IO_Running: Yes # yes indicates successful io connection Slave_SQL_Running: Yes # yes indicates successful synchronization connection of mysql process

six。 Verify master-slave synchronization

1. Create database hhhh on the primary server

two。 To show databases from server 1

3. To show databases from server 2

On the Mysql database master-slave replication is how to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.