MySQL Replication-(Instance)

MySQL Replication-(Instance)

Master-slave replication Principle

• MySQL Replication is an asynchronous Replication process that replicates data from the Master to one or more Slave instances.
• The entire replication process between the Master and Slave is mainly completed by three threads, one IO thread is on the Master side, and the two threads (SQL thread and IO thread) are on the Slave side.
• Enable the Binary Log (Binary Log file) function on the Master server. The Server Load balancer obtains the Log information from the Master server and parses the Binary file into an SQL statement, and perform the operations recorded by the SQL statement in full order. (The binary file obtained by Slave is also written to its own Relay Log file)

Replication Concept

• MySQL Replication is a log Replication process. During the Replication process, one server acts as the master, and one or more other servers act as slave servers;
• Pull the binary log file from the server to the master server, parse the log file into the corresponding SQL statement, and then re-execute the operations on the master server on the slave server, this method ensures data consistency.

Master-slave replication configuration steps:

• Set server-id (server id, which cannot be repeated in a group of Master/Slave nodes)
• Enable binary logs and specify the path for storing binary log files
• Record the position of the bin-log file and the bin-log (position)
• If a global lock is added when the Master node is not stopped, back up the database to be synchronized to the Slave node to remove the global lock.
• Create a user for Synchronous Replication
• Use change master to establish a connection between Slave and Master (Slave node sets the master server)
• Start Slave
• Check the Slave status

Step 1: configure the/etc/my. cnf file of the master and slave.

12345678910111213

##Master   [mysqld] basedir=/usr/local/mysqldatadir=/data/mysql/mysqlport=3306 socket=/var/lib/mysql/mysql.sock   server-id=1                             # Service IDlog-bin=/data/mysql/binlog/mysql-bin# Path to binlog log file storagebinlog-cache-size=10m                   # Binlog log cache sizesync-binlog=1                           # Write binary log records in the cache back to the hard disk every N secondsexpire_logs_days=30                     # Expiration time of binary log files (automatic cleanup time)

 

12345678910111213

##Slave   [mysqld] basedir=/usr/local/mysqldatadir=/data/mysql/mysqlport=3306 socket=/var/lib/mysql/mysql.sock   server_id=2 relay-log=/data/mysql/binlog/mysql-relay-binreplicate-wild-do-table=testdb1.%        # Specify the database to be synchronizedreplicate-wild-do-table=testdb2.%        # Specify the database to be synchronized                                         # (Replicate-wild-ignore-table)

Step 2: manually synchronize the database to slave

1. Lock write operations on the Master table (do not exit the Terminal)

12	mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)

2. Back up the database on the Master and upload it to the Slave.

12345

[root@node1 ~]# mysqldump -uroot -pRedHat testdb1 > /root/testdb1.sql [root@node1 ~]# tar zcf testdb1.tar.gz testdb1.sql   [root@node1 ~]# rsync -av /root/testdb1.tar.gz 192.168.1.211:/root/ [root@node1 ~]# scp /root/testdb1.tar.gz root@192.168.1.211:/tmp/

 

3. Create a synchronized database on Slave and import data files

123456789

[root@node2 ~]# mysql -uroot -predhat -e 'create database testdb1' [root@node2 ~]# mysql -uroot -predhat testdb1 < testdb1.sql  mysql> show tables; +-------------------+ | Tables_in_testdb1 | +-------------------+ | tt1               | | tt2               | +-------------------+

 

Step 3: The Master creates a synchronization user.

 

1. Unlock the Master

1	mysql> unlock tables;

2. Create a synchronization user

12	mysql> grant replication slave on *.* to 'repl_user'@'192.168.1.211' identified by 'repl_passwd'; mysql> flush privileges;

 

Step 4: Slave connects to the Master and set node1 as its Master server

1. view the Master-log-file and position of the master.

123456

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      120 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+

2. Connect to the Master on the Slave and start the slave

12345678910

mysql>  change master to master_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 the Slave status on the slave (Slave_IO_Running, Slave_ SQL _Running, and Seconds_Behind_Master)

1234567891011121314151617181920212223242526272829303132333435

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

Step 5: test the master-slave synchronization function

1. create databases and tables on the Master

123	mysql> insert into tt1(id,name) values(1,'hoai'),(2,'dime');   mysql> create database testdb2;

 

2. Check whether synchronization is performed on the Slave.

12345678910111213141516171819202122

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)

How to clear binary logs (reset Master/Slave ):

Mysql> reset master;

Mysql> reset slave; (for the previous relay-log File)

This article permanently updates the link address: