MySQL Replication Master-slave replication-(instance)

Master-Slave replication principle

• MySQL replication is an asynchronous replication process that replicates from master to one or more slave.

• The entire replication process between master and slave is done primarily by three threads, one IO thread on the master side and two threads (SQL thread and IO thread) on the slave side.

• The ability to open binary log files via the master server, slave the log information from the master, and then parse the binaries into SQL statements and execute the various operations recorded by the SQL statement in full order. (slave acquired binary files are also written to their own relay log file)
  

Replication concept

• MySQL replication technology is a log copy process in which a server acts as the primary, one or more other servers acting as slave servers during the replication process;

• The data consistency is ensured by pulling the binary log file from the server to the master server, parsing the log file into the appropriate SQL statement, and then re-performing the operation of the master server from the server.
  

Master-Slave Replication configuration steps:

• Set Server-id (server identity, which cannot be duplicated in a group of hosts)

• Turn on the binary log and specify the path to save the binary log file

• Record Bin-log files and bin-log (position) locations

• If you keep on master, add a global lock, backup the database that needs to be synchronized to the slave node, and unlock the global lock.

• Create a user for synchronous replication

• Using change master to establish a connection between slave and master (slave node setting master server)

• Start slave

• Check the status of the slave

  
  

STEP1: Configuring/ETC/MY.CNF files for Master and slave

# #Master [mysqld]basedir=/usr/local/mysqldatadir=/data/mysql/mysqlport=3306socket=/var/lib/mysql/ Mysql.sockserver-id=1 #服务标识log-bin=/data/mysql/binlog/mysql-bin #binlog日志文件保存的路径binlog-cach                      e-size=10m #binlog日志缓存大小sync-binlog=1 #每隔N秒将缓存中的二进制日志记录写回硬盘expire_logs_days =30 #二进制日志文件过期时间 (automatic cleanup time)

# #Slave [mysqld]basedir=/usr/local/mysqldatadir=/data/mysql/mysqlport=3306socket=/var/lib/mysql/ mysql.sockserver_id=2relay-log=/data/mysql/binlog/mysql-relay-binreplicate-wild-do-table=testdb1.% # Specifies the database to be synchronized replicate-wild-do-table=testdb2.% #指定需要同步的数据库 # (corresponding to the replicate-w ild-ignore-table)

STEP2: Manually synchronizing databases to slave

1. Write operations on tables that lock master (do not exit the terminal)

Mysql> flush tables with read lock; Query OK, 0 rows Affected (0.00 sec)

2. Back up the database on master and upload to Slave

[Email protected] ~]# mysqldump-uroot-predhat testdb1 >/root/testdb1.sql[[email protected] ~]# tar zcf testdb1.tar. GZ Testdb1.sql[[email protected] ~]# rsync-av/root/testdb1.tar.gz 192.168.1.211:/root/[[email protected] ~]# scp/root/ testdb1.tar.gz [Email protected]:/tmp/

3. Create a synchronized database on slave and import the data file

[[email protected] ~]# mysql-uroot-predhat-e ' CREATE Database testdb1 ' [[email protected] ~]# mysql-uroot-predhat test DB1 < Testdb1.sql mysql> show tables;+-------------------+| TABLES_IN_TESTDB1 |+-------------------+| TT1 | | TT2 |+-------------------+

Step3:master Creating a synchronized user

1, Master Unlock

mysql> unlock tables;

2. Create a sync user

mysql> grant replication Slave on * * to ' repl_user ' @ ' 192.168.1.211 ' identified by ' repl_passwd ';mysql> flush Privil Eges;

Step4:slave Connect master, set Node1 to its home server

1. View Master's Master-log-file file and position location

Mysql> Show Master status;+------------------+----------+--------------+------------------+------------------- +| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+|      mysql-bin.000001 |              120 |                  |                   | |+------------------+----------+--------------+------------------+-------------------+

2. Connect master on Slave, and start slave

mysql> Change Master tomaster_host= ' 192.168.1.210 ', master_user= ' repl_user ', master_password= ' repl_passwd ', Master_port=3306,master_log_file= ' mysql-bin.000001 ',master_log_pos=120;mysql> start slave;

3. View Slave status on Slave (slave_io_running,slave_sql_running, and Seconds_behind_master )

mysql> show slave status\g;*************************** 1. row ***************                slave_io _state: waiting for master to send event                   Master_Host: 192.168.1.210                   master_ user: repl_user                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           read_master_log_pos: 120                Relay_Log_File: mysql-relay-bin.000002                 Relay_Log_Pos: 283         relay_master_log_file: mysql-bin.000001              Slave_IO_Running: Yes             slave_sql_running: yes               replicate_do_db:           replicate_ignore_db:             Replicate_Do_Table:         replicate_ignore_table:       replicate_wild_ do_table: testdb1.%,testdb2.%  replicate_wild_ignore_table:                     Last_Errno: 0                    Last_Error:                   skip_counter:  0          Exec_Master_Log_Pos: 471               Relay_Log_Space: 807               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: 0

STEP5: Test master-Slave synchronization function

1. Creating databases and tables on master

mysql> INSERT INTO TT1 (id,name) VALUES (1, ' Hoai '), (2, ' dime ');mysql> CREATE database testdb2;

2, slave on check whether synchronization

mysql> select * from tt1;+------+------+| id    | name |+------+------+|    1 | hoai | |     2 | dime |+------+------+2 rows in set  (0.00  SEC) mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema | |  test               | |  testdb1            | |  testdb2            |+--------------------+6  rows in set  (0.00 sec) 
 

Clear the Binary log method (reset master/slave):

mysql> Reset Master;

mysql> reset Slave; (for relay-log files from the top)

MySQL Replication Master-slave replication-(instance)