MySQL master-slave replication principle and its configuration process

First, the principle of MySQL replication.

Second, MySQL replication configuration.

First, the principle of MySQL replication

1.MySQL copy schematic diagram

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/92/F1/wKioL1kFyBLgeajPAABc7idEmSg591.png "title=" Slave.png "alt=" Wkiol1kfyblgeajpaabc7idemsg591.png "/>

Copy principle:

Slave boot IO thread and SQL thread

Master Boot Dumpthread

1.Slave sends a request via IO thread to the master's dump thread, and the master's dump thread requests the local binlog.

2.Master reads the local binlog and sends the read content to the IO thread thread of the slave.

The 3.Slave IO thread writes the received content to the local relaylog.

The 4.Slave SQL thread reads the contents of the local Relaylog file.

The SQL thread of 5.Slave writes the read content to the local database.

Second, MySQL replication configuration

1.MySQL Copy Diagram

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/92/F3/wKiom1kFzGzS-DjNAAAsVuLIy80870.png "title=" Slave2.png "alt=" Wkiom1kfzgzs-djnaaasvuliy80870.png "/>

2. Configure Master

2.1. Modify the master configuration file

VIM/ETC/MY.CNF[MYSQLD] #开启二进制日志文件log-bin = mysql-bin# Configure Unique Server Idserver-id = # transaction Security Sync_master_info = 1sync_binlog = 1 I Nnodb_support_xa = On

Full 2.2.Master configuration file

[client]port  = 3306socket = /tmp/mysql.sock[mysqld]port = 3306socket  = /tmp/mysql.sockskip-external-lockingkey_buffer_size = 256mmax_allowed_packet =  1mtable_open_cache = 256sort_buffer_size = 1mread_buffer_size = 1mread_rnd_ Buffer_size = 4mmyisam_sort_buffer_size = 64mthread_cache_size = 8query_cache_ Size= 16mthread_concurrency = 8log-bin=mysql-binbinlog_format=mixedserver-id = 1sync_ master_info = 1sync_binlog = 1 innodb_support_xa = ondatadir = / data/mysql/3306/datainnodb_data_home_dir = /data/mysql/3306/datainnodb_data_file_path =  ibdata1:10m:autoextendinnodb_log_group_home_dir = /data/mysql/3306/datainnodb_buffer_pool_size  = 256minnodb_additional_mem_pool_size = 20minnodb_log_file_size = 64minnodb_log_ Buffer_size = 8minnodb_flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 50innodb_file_per_table =  onskip_name_resolve = on[mysqldump]quickmax_allowed_packet = 16m[mysql] no-auto-rehash[myisamchk]key_buffer_size = 128msort_buffer_size = 128mread_buffer =  2mwrite_buffer = 2m[mysqlhotcopy]interactive-timeout

2.3. Create Replication permissions for users

MariaDB [(None)]> Grant replication Slave,replication Client on * * to ' repl ' @ ' 192.168.1.5 ' identified by ' slavepass '; Q Uery OK, 0 rows affected (0.39 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.06 sec)

3. Configure slave

3.1. Modify the slave configuration file

VIM/ETC/MY.CNF[MYSQLD] #设置唯一IDserver-id = 3 #启用relay logrelay_log= relay-logrelay_log_index=relay-log.index# Transaction Security Skip _slave_start = Onsync_relay_log = 1sync_relay_log_info = 1

[client]port            = 3306socket           = /tmp/mysql.sock[mysqld]port             = 3306socket           = /tmp/mysql.sockskip-external-lockingkey_buffer_size = 256mmax_allowed _packet = 1mtable_open_cache = 256sort_buffer_size = 1mread_buffer_size =  1mread_rnd_buffer_size = 4mmyisam_sort_buffer_size = 64mthread_cache_size =  8query_cache_size= 16Mthread_concurrency = 8server-id = 3relay_log=  Relay-logrelay_log_index=relay-log.indexskip_slave_start = onsync_relay_log = 1sync_relay_ log_info = 1innodb_data_home_dir = /data/mysql/3306/datainnodb_data_file_path =  Ibdata1:10m:autoextendinnodb_log_group_home_dir = /data/mysql/3306/datainnodb_buffer_pool_size = 256minnodb_additional_mem _pool_size = 20minnodb_log_file_size = 64minnodb_log_buffer_size = 8minnodb_ flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 50innodb_file_per_table =  onskip_name_resolve = on[mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehash[ Myisamchk]key_buffer_size = 128msort_buffer_size = 128mread_buffer = 2mwrite_ Buffer = 2m[mysqlhotcopy]interactive-timeout

4. Start copying

4.1. Viewing the Binlog Pos point on Master

MariaDB [(None)]> Show Master status\g*************************** 1. Row *************************** file:mysql-bin.000008 position:652 binlog_do_db:binlog_ignore_db: 1 row in Set (0.00 sec)

4.2. Perform a synchronous operation on the slave

MariaDB [(none)]> change master to master_host= ' 192.168.1.4 ', master_user= ' repl ', master_password= ' Slavepass ', Master_log_file= ' mysql-bin.000008 ', master_log_pos=652; Query OK, 0 rows affected (0.93 sec)

4.3. Start the slave on slave

MariaDB [(None)]> start slave; Query OK, 0 rows affected (0.02 sec)

4.3. View the slave status on slave

mariadb [(None)]> show slave status\g*************************** 1. row                  Slave_IO_State: Waiting for master to send event                   Master_Host:  192.168.1.4                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000008          &nbsP read_master_log_pos: 652                Relay_Log_File: relay-log.000002                 Relay_Log_Pos: 537         relay_master_log_file: mysql-bin.000008              Slave_IO_Running: Yes             slave_sql_running: yes               replicate_do_db:           replicate_ignore_db:             Replicate_Do_Table:         replicate_ignore_table:       replicate_wild_ Do_table:  &nBsp replicate_wild_ignore_table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 652               Relay_Log_Space: 829               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:  conservative1 row in set (0.00 SEC) 

5. Test synchronization

5.1. Create data in Master

mariadb [(None)]> create database ckldb; query ok, 1 row affected  (0.37 sec) mariadb [(none)]> use ckldb ;D Atabase changedmariadb [ckldb]> create table jone (Id int,name varchar ( 30)); query ok, 0 rows affected  (0.29 sec) mariadb [ckldb]> insert  Into jone values (1, ' Wukaka ');          query  ok, 1 row affected  (0.49 sec) Mariadb [ckldb]> delete from jone ; query ok, 1 row affected  (0.09 sec) mariadb [ckldb]> insert  Into jone values (1, ' Wukaka '), (2, ' side ');           query ok, 2 rows affected  (0.06 sec) records: 2  duplicates:  0  warnings: 0mariadb [ckldb]> select * from jone;+------+--------+| id   | name    |+------+--------+|    1 | wukaka | |     2 | side   |+------+--------+2 rows in set   (0.00 SEC)

mariadb [(None)]> show databases;+--------------------+| database            |+--------------------+| ckldb               | |  information_schema | |  mysql              | |  performance_schema | |  test               |+-------- ------------+5 rows in set  (0.34 sec) mariadb [(none)]> use ckldb;d atabase changedmariadb [ckldb]> show tables;+-----------------+| tables_in_ckldb  |+-----------------+| jone            | +-----------------+1 row in set  (0.00 sec) mariadb [ckldb]> select *  from jone;+------+--------+| id   | name   |+------+--------+|     1 | wukaka | |     2 | side   |+------+--------+2 rows in set   (0.00 SEC)

Note that if the main library has been running for a long time, it is best to back up the main library and record Binlog POS points before synchronizing. Import a backup into the slave library

, and then start recovery from Binlog Pos.

This article is from the "take a deep Breath again" blog, make sure to keep this source http://ckl893.blog.51cto.com/8827818/1920847

MySQL master-slave replication principle and its configuration process