MARIADB Master-slave replication

First, MARIABD data replication principle and function

MySQL's built-in replication capabilities are the foundation for building large, high-performance applications. The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

the function of copying: data distribution; load balancing read; Data backup; high availability and failover; MySQL upgrade test

Second, the implementation of MARIABD master-slave replication

Master node (Master) configuration

①, start the binary log , set a global ID number for the current node

[Email protected] ~]# Vim/etc/my.cnlog_bin = Mysql-bin #启动二进制日志server_id = 1 #设置服务器I dinnodb_file_per_table = on #设置每表使用单独事务日志skip-name-resolve #跳过域名解析 [[email protected] ~]# SYSTEMCT L Restart Mariadb.service

②, create user account with copy permission

mariadb [(none)]> grant replication slave,  replication client on *.* to  ' WLW ' @ ' 192.168.0.% '  IDENTIFIED BY  ' WLW '; mariadb [(None)]> show master status;+------------------+----------+--------------+---- --------------+| file             |  position | binlog_do_db | binlog_ignore_db |+------------------+----------+---- ----------+------------------+| mysql-bin.000003 |      413 |               |                   |+------------------+----- -----+--------------+------------------+ #这里二进制日志已经滚到到第三个文件了, the first two files are the MySQL management library created for database initialization, so let's wait for configuration # The configuration from the position only needs to start at 413 from this moment 

Configuration from node (SLAVE)

①, start the relay log, set a global ID number for the current node

[[email protected] ~]# vim /etc/my.cnrelay_log  = relay-log                      #启动中继日志relay_log_index  = relay-log.indexread-only = 1                               #开启只读模式, this restriction is not valid for users with super privileges # If you need to lock all users, you can execute the SQL statement (FLUSH  TABLES WITH READ LOCK;) server_id = 8                              innodb_file_per_table = onskip-name-resolve 

②, before configuring, check the status of the server and whether the replication thread is started

mariadb [(None)]> show slave status; empty set  (0.01 sec) #这里我们查看从服务器的状态, you can see that there is no configuration, empty mariadb [(none)]> show  processlist;+----+------+-----------+------+---------+------+-------+------------------+----------+| id  | User | Host      | db   |  command | time | state | info              | progress |+----+------+-----------+------+---------+------+-------+- -----------------+----------+|  2 | root | localhost | null |  Query   |    0 | NULL  | SHOW  processlist |    0.000 |+----+------+-----------+------+---------+------+------- +------------------+----------+ #可以看到从服务器还没有启动复制线程

③, connect to the primary server with a user account with replication permissions and start the replication thread

MariaDB [(None)]> change MASTER to master_host= ' 192.168.0.151 ', master_user= ' WLW ', master_password= ' WLW ', master_ Log_file= ' mysql-bin.000003 ', master_log_pos=413; MariaDB [(None)]> START SLAVE;

④ Check the status of the server and whether the replication thread was started

after configuration

mariadb [(None)]> show slave status\g*************************** 1. row                  Slave_IO_State: Waiting for master to send event                   Master_Host:  192.168.0.151             #主服务器IP                    Master_User:  wlw                        #复制授权的用户                    Master_Port: 3306                       #主服务器端口                  Connect_Retry: 60                         #重试时间                Master_Log_File:  mysql-bin.000003          #读取二进制的文件            Read_Master_Log_Pos: 413                        #读取二进制的开始位置                 Relay_Log_File:  relay-log.000002          #读取的中继日志文件                  relay_log_pos: 529                        #读取的中继日志位置          Relay_Master_Log_File: mysql-bin.000003                      Slave_IO_Running: Yes                         #I/o thread Start              slave_sql_running: yes                        #SQL  thread Start                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: 413              relay_log _space: 817              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: 0master_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: 1              mariadb [(None)]> show  processlist;+----+-------------+-----------+------+---------+------+------------------------------------------- ----------------------------------+------------------+----------+| id | user         | host      | db   |  command | time | state                                                                           | info             | progress  |+----+-------------+-----------+------+---------+------+------------------------------------------------ -----------------------------+------------------+----------+|  2 | root         | localhost | NULL | Query   |     0 | NULL                                                                            | show processlist |    0.000 | |   3 | system user |            | null | connect |  404 | waiting for master to  send event                                              | NULL               |    0.000 | |   4 | system user |            | null | connect |  403 | slave has read all  relay log; waiting for the slave I/O thread to update  it | null             |     0.000 |+----+-------------+-----------+------+---------+------+------------------------------ -----------------------------------------------+------------------+----------+ #可以看到启动了两个线程, one time waiting for the primary server to send, One is to read the trunk log to do synchronization [[EMAIL PROTECTED] ~]# LL /VAR/LIB/MYSQL/MASTER.INFO-RW-RW----.  1  mysql mysql 75 nov  8 09:00 /var/lib/mysql/master.info# This file holds the link information for the master server, Including your account password, the next time you restart the service will automatically connect to the master server via this file [[email protected] ~]# cat /var/lib/mysql/relay-log.info ./relay-log.000002691mysql-bin.000003575# This file saves the trunk logs used from the server, the POS and the binary logs used by the primary server, the POS, and the server startup also reads this file 

⑤, test the results of the experiment, the primary server to create a database to see if the server automatically copy

mariadb [(none)]> create database wlw; mariadb [(None)]> create database wlw; mariadb [(None)]> show databases;+--------------------+| database            |+--------------------+| information_schema | |  wlw                | |  mysql              | |  performance_schema | |  test               | |  wlw                |+--- -----------------+ #可以看到主服务器创建的数据库从服务器已经自动复制完成 

This article is from the "Ma Gao" blog, please make sure to keep this source http://kgdbfmwfn.blog.51cto.com/5062471/1710690

MARIADB Master-slave replication