Mariadb master-slave server experiment hot standby 1

Mariadb master-slave replication

Overview of mariadb master-slave replication:

The basic problem solved by replication is to keep the data of one server synchronized with another server.

A master server can connect to multiple slave servers, and the slave server can in turn act as the master server.

Master and slave servers can be located in different network topologies and can replicate entire servers, specific databases, and even specific tables.

1.2. Problems solved by master-slave replication

Mariadb replication technology has the following characteristics:

(1) data distribution (Data distribution)

(2) load balancing (load balancing)

(3) backup (Backups)

(4) High availability and failover High availability and failover

1.3 how master-slave replication works

Overall, there are three steps to replication:

(1) master records changes in binary log (binary log) (these records are called binary log events, binary log events)

(2) slave copies the binary log events of master to its relay log (relay log)

(3) slave redoes the events in the relay log and modifies the data on the salve.

Mariadb master-slave replication:

Step 1: master records binary logs. Before each transaction updates the data, master records these changes in the second log. Mariadb writes transactions to the binary log, even if the statements in the transaction are executed across each other. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

Step 2: slave copies master's binary log to its own relay log. First, slave starts a worker thread-- the Imax O thread. The iUnip O thread opens a normal connection on master and then starts binlog dump process. Binlog dump process reads events from master's binary log, and if it has finished executing all the files generated by master, it sleeps and waits for master to generate new events. The Icano thread writes these events to the relay log.

Step 3: the SQL slave thread (SQL thread) handles the last step of the process. The SQL thread reads events from the relay log and re-executes the events in it to update the data in slave to make it consistent with the data in master.

Yum install mariadb-server-y

Systemctl start mariadb

Mysql-h 127.0.0.1-u root-p

MariaDB [(none)] > create database pcdog

MariaDB [(none)] > use pcdog

MariaDB [pcdog] > create table test1 (id int)

MariaDB [pcdog] > show tables

Systemctl stop mariadb

Configure the name of the database that mariadb mainly synchronizes and open the corresponding binary log

Vim / etc/my.cnf # my.cnf is the mariadb main configuration file

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0 # in the original configuration file, add the following:

Log-bin=mariadblog

Server-id=1

Binlog-do-db=pcdog

Note:

Log-bin=mariadblog # enables binary logging, which exists under / var/lib/mariadb by default

Server-id=1 # unique label for native database ID.

Binlog-do-db=pcdog # libraries that can be copied from the server. Binary database name that needs to be synchronized

Systemctl start mariadb

Mysql

Grant replication slave on *. * to slave@192.168.10.130 identified by "123456"

Verify login on slave

Mysql-h 192.168.10.129-u slave-p123456

Exit all db from the host dump

Mysqldump-u root-p-A > all1.sql

Scp in the past

Scp all1.sql root@192.168.10.130:/root

Import from the machine

Mysql-u root-p

< all1.sql vi /etc/my.cnf 加一行 server-id=2 到主机上查看 mysql show master status; 从机上设置主人 mysql change master to master_host='192.168.10.129', master_user='slave', master_password='123456', master_port=3306, master_log_file='mariadblog.000001', master_log_pos=397, master_connect_retry=10; MariaDB [(none)]>

Start slave; # starts the slave service

MariaDB [(none)] > show slave status\ G # View slave server status

Slave_IO_Running: Yes # can see these two Yes, indicating a successful installation from the server.

Slave_SQL_Running: Yes

Slave_IO_Running: an io responsible for communicating with the host

Slave_SQL_Running: responsible for your own slave mariadb process

Insert data on the host

Insert into test1 values (1)

Check it from the plane.

View the log

Show binlog events

Summary:

1. The principle of master-slave synchronization

2. Configuration of master-slave synchronization

3. Test whether the master-slave synchronization is successful

Actual combat 2:mariadb master-two-way master-slave replication

Separation of master and slave reading and writing in practical 3:mariadb