mariadb-10.1 Master-slave replication

MARIADB version:10.1.11

Schema:node1.example.com (192.168.180.100) as the primary server

node2.example.com (192.168.180.101) as slave server

Master-slave replication schematic diagram: 650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7C/98/wKioL1bTCLHB8iwfAAFMQ1JqEG0850.png "title = "MySQL master-slave copy. png" alt= "Wkiol1btclhb8iwfaafmq1jqeg0850.png"/>

Master:node1.example.com Slave:node2.example.com

Master-slave replication principle: MySQL's binary log records all changes to the database operation, that is, as long as the database will be modified operations will be logged into the binary log. There are two main purposes for logging binary logs:

1. Recovery (recovery)

2. Copy (replication)

MySQL replication is done based on binary logs and works as follows:

When the master data changes, Master will actively notify Slave, let slave actively to master to take the binary log, so slave open an I/O thread, to master request the statement recorded in the binary log , Master sends the statements recorded in the binary log to Slave,slave, and then the statements are stored in the trunk log, which in turn reads a sentence from the log, executing a sentence until all the statements are executed. And after the SQL statement from the log read out, and then one by one of the process is called SQL thread, after the execution of these statements, the data from the server and the primary server data is the same, this is called MySQL master-slave replication;

The master-slave replication principle from MySQL shows:

1, master must open the binary log;

2, slave must turn on the relay log;

3, slave need to close the binary log;

4, slave need to connect to master.

5, master and slave Server-id must not be the same.

Configuration of Master-slave replication:

Master (192.168.180.100):

1, change Server-id;

2, enable binary log;

3. Create an account with copy rights.

Slave (192.168.180.101):

1, change Server-id;

2, enable the relay log;

3, connect the main server;

4. Enable the replication thread.

Implementation process:

Master:

[[email protected] ~]# vim/etc/my.cnf log-bin=/data/binlogs/mysql-bin server-id = # Create an account with copy rights mariadb [(None)]& Gt                    GRANT REPLICATION SLAVE, REPLICATION CLIENT on *. * to ' repluser ' @ ' 192. 168.180.101 ' identified by ' Redhat '; MariaDB [(None)]> FLUSH privileges;

Slave:

[[email protected] ~]# vim /etc/my.cnf     #log-bin=/data/binlogs/ mysql-bin        #找到这一行, notes      #binlog_format =mixed               #找到这一行, Comments      server-id   = 10               #在配置文件里修改这一行     relay-log = /data/relaylogs/relay-bin   # Add this line [[email protected] ~]# mkdir /data/relaylogs[[email protected] ~]#  Chown -r mysql:mysql /data/relaylogs/[[email protected] ~]# service mysqld  restartmariadb [(none)]> change master to master_host= ' 192.168.180.100 ',  master_user= ' Repluser ',          master_password= ' redhat ';         mariadb [(None)]> show slave status\g*************************** 1.  row ***************************                Slave_IO_State: Waiting for master to send event                   master_ host: 192.168.180.100                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000004           Read_Master_Log_Pos: 658                Relay_Log_File: relay-bin.000006                 Relay_Log_Pos: 537         Relay_Master_Log_File: mysql-bin.000004              Slave_IO_Running: No             Slave_SQL_Running: No               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: 658               Relay_Log_Space: 1526               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                Master_SSL_Crl:             Master_SSL_Crlpath:                     Using_Gtid: No                   Gtid_IO_Pos:        Replicate_Do_Domain_Ids:   Replicate_Ignore_Domain_Ids:                  parallel_mode:  conservativemariadb  [(None)]> start slave; mariadb [(None)]> start slave;        mariadb [ (none)] > show slave status\g*************************** 1. row *********************                slave_io_state : waiting for master to send event                   Master_Host: 192.168.180.100                   master_ user: repluser                   Master_Port: 3306                 connect_retry: 60              master_log_file: mysql-bin.000004           Read_Master_Log_Pos: 658                Relay_Log_File: relay-bin.000006                 Relay_Log_Pos: 537         Relay_Master_Log_File: mysql-bin.000004              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               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: 658              relay_log_ Space: 1526              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                Master_SSL_Crl:             Master_SSL_Crlpath:                     Using_Gtid: No                   gtid_io_pos:        replicate_do_domain_ids:   replicate_ignore_domain_ Ids:    &nBsp;            parallel_mode: conservative 

Here, MySQL's master-slave replication has been built, and next, we do a validation. The verification process is as follows: 1, create the database mydb on the master side, and create the table T1 in the MyDB database, and insert the data;

2, on the slave side to see if there are such libraries and tables and tables of data, of course, the premise is that there is no such tables and libraries from the server;

Master side:

MariaDB [(None)]> CREATE DATABASE mydb; MariaDB [(None)]> use MyDB; MariaDB [mydb]> CREATE TABLE T1 (ID INT not NULL); MariaDB [mydb]> INSERT into T1 VALUES (1), (2), (3), (4); MariaDB [mydb]> SELECT * from t1;+-------+|   ID |+-------+|   1 | |   2 | |   3 | | 4 |+-------+

Slave side to view:

mariadb [(none)]> show databases;+-------------------- +| database           |+--------------------+|  information_schema | |  mydb               | |  mysql              | |  performance_schema |+--------------------+mariadb [(none)]> use mydb;database  changedmariadb [mydb]> show tables;+----------------+| tables_in_mydb |+---- ------------+| t1             |+-------- --------+mariadb [mydb]> select * from t1;+----+| id |+----+|   1 | |   2 | |   3 | |   4 |+----+ 

At this point, the MySQL master-slave replication is normal ...

mariadb-10.1 Master-slave replication