Shulou(Shulou.com)06/01 Report--

Let's talk about what is the AB replication principle of MYSQL. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe that after reading what is MYSQL's AB replication principle of this article you will certainly benefit.

Myslq AB replication

Before doing AB replication, the data on both sides must be the same. Mysql replication defaults to asynchronous replication, and master writes events to binlog, but does not know when slave will accept and has accepted. In the case of asynchronous replication mechanism, if master is down, the transaction has been committed on master, but it is likely that these things have not been transferred to any slave. At this point, salve may lose the transaction.

The official concept of semi-synchronous replication:

1 when the slave host connects to the master. You can see if you are in the mechanism of semi-synchronous replication.

2 when semi-synchronous replication is enabled on master, at least one slave should turn on its function.

3. When the events of a transaction have been written to its relay-log and flushed to disk, the Slave will inform you that it has been received.

Synchronous replication: Master commits the transaction, and the transaction is not returned to the client until it has been committed at all Slave, and the transaction is completed. Cons: there may be a big delay in completing a transaction.

Asynchronous replication: when Slave is ready, it requests binlog from Master. Cons: there is no guarantee that some events will be received by all Slave.

Semi-synchronous replication: the mechanism of semi-synchronous replication is between synchronous and asynchronous, and the transaction commit of Master is blocked, as long as an Slave has received and logged the event of the transaction. It does not wait for all Slave to be notified that it has been received, and it just receives it, not for its full execution and submission.

Experimental server2: master

Server3: slave

Mysql-5.1.71 version

[root@server2 ~] # yum install mysql-server-y

[root@server3~] # yum install mysql-server-y

[root@server2 ~] # / etc/init.d/mysqld start

[root@server2 ~] # vim / etc/my.cnf

Add the following parameters in [mysqld]

8 log-bin=mysql-bin startup binary log

9 name of the database for binlog-do-db=westos binary log synchronization

10 server-id=1 must be a positive integer between 1 and 2 ^ 32-1

[root@server2 ~] # / etc/init.d/mysqld restart if it fails in the process of restarting the service, you have to go to / var/log/mysql.log to check the reason. Generally speaking, the modified configuration file above is wrong. If you are not looking at other reasons, delete the file in / var/lib/mysql and initialize the restart.

[root@server2 mysql] # mysql_secure_installation change password

[root@server2 mysql] # mysql-pwestos

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 11

Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. Allrights reserved.

Oracle is a registered trademark of Oracle Corporation and/orits

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the currentinput statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

+-+

2 rows in set (0.00 sec)

Mysql > create database weCtrl-C-- exit!

Aborted

[root@server2 mysql] # mysql-pwestos

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 12

Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. Allrights reserved.

Oracle is a registered trademark of Oracle Corporation and/orits

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the currentinput statement.

Mysql > show master status; to view master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 737 | westos |

+-+

1 row in set (0.00 sec)

Mysql > create database westos; create a shared library

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | westos |

+-+

3 rows in set (0.00 sec)

Mysql > grant replication slave on *. * tolinux@'172.25.137.3'identified by 'redhat'

Query OK, 0 rows affected (0.00 sec) authorized slave

Mysql > Bye

There are mysql-bin.00000x binaries, which record all the operations of the heap database, which are used when backing up the database.

[root@server2 mysql] # mysqlbinlog mysql-bin.000001 uses mysqlbinglog to view the contents.

[root@server3 ~] # vim / etc/my.cnf

8 server-id=2

[root@server3 ~] # / etc/init.d/mysqld restart

[root@server3 ~] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. Allrights reserved.

Oracle is a registered trademark of Oracle Corporation and/orits

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the currentinput statement.

Mysql > show

->

ERROR 1064 (42000): You have an error in your SQL syntax; checkthe manual that corresponds to your MySQL server version for the right syntaxto use near''at line 1

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

Mysql > create database westos

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

| | westos |

+-+

4 rows in set (0.00 sec)

Mysql > show slave stauts

ERROR 1064 (42000): You have an error in your SQL syntax; checkthe manual that corresponds to your MySQL server version for the right syntaxto use near 'stauts' at line 1

Mysql > show slave status

Empty set (0.00 sec)

Mysql > change master tomaster_host='172.25.137.2',master_user='linux',master_password='redhat',master_log_file='mysql-bin.000001',master_log_pos=975

Query OK, 0 rows affected (0.81 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G; Slave_IO_Running,Slave_SQL_Running: both states are yes indicating that the status is good. Slave_IO_Running, if this is no, there may be a problem with writing. Generally, check whether the firewall is turned off and whether selinux is disabled.

Add table information to the masterside:

Mysql > createtable student (username varchar (12) not null, number varchar (12) not null)

Mysql > insertinto values ('dog','111')

Mysql > select * from student

+-+ +

| | username | number |

+-+ +

| | dog | 111 | |

+-+ +

1 row in set (0.00sec)

On the slave end

Mysql > usewestos

Mysql > select * from student

+-+ +

| | username | number |

+-+ +

| | dog | 111 | |

+-+ +

1 row in set (0.00sec)

The information of the master end is recorded in it.

The copy binaries on the mysql-bin.000001 slave side come from this file in master.

[root@server2mysql] # mysqlbinlog mysql-bin.000001 to view the operation on the database.

MysqlABC replication

A B C

Mastermaster slave slave

The data of B comes from master,C, so it is both master and slave to B, but BC only does read operation, does not write, only A does write.

Experimental server2: master

Server3: slave

Server4:slave

[root@server4 ~] # yum install mysql-server-y

[root@server3mysql] # / etc/init.d/mysqld stop

[root@server3mysql] # vim / etc/my.cnf

[root@server3 mysql] # / etc/init.d/mysqld restart

[root@server4 ~] # vim / etc/my.cnf

Add in mysqld

[root@server4 ~] # / etc/init.d/mysqld start

Server3:

Mysql > grantreplication slave on *. * to linux@'172.25.137.4' identified by 'redhat'

Query OK, 0 rowsaffected (0.00 sec) authorizes server4

[root@server4] # mysql-h 172.25.137.3-u linux-predhat test whether server3 can be connected

In order for Server4 to exist at the same time, it must be given the same environment, and if the data in the same westos database is consistent in the experiment, the library must exist.

Change master tomaster_host='172.25.137.3',master_user='linux',master_password='redhat',master_log_file='mysql-bin.000005',master_log_pos=106; is synchronized with server3.

[root@server2mysql] # mysqlbinlog mysql-bin.000001 found the command to create the table in the westos database in this file

1120 is the value at the end of this command.

The other is to find it in time.

[root@server2mysql] # mysqlbinlog-- start-position=975-- stop-position=1224 mysql-bin.000001 | less this is an operation related to the wesos database of master through numerical search.

[root@server2mysql] # mysqlbinlog--start-datetime='2016-07-30 13 stop-datetime='2016 53 13:55:10'mysql-bin.000001 26'--stop-datetime='2016-07-30 13:55:10'mysql-bin.000001 | this is a time-related operation to find the wesos database of master.

[root@server2mysql] # scp mysql-bin.000001 172.25.137.4:/mnt sends the binary file master to slave.

[root@server2mysql] # mysqldump-pwestos westos > backup.sql back up the westos library to the backup.sql file.

[root@server4 mnt] # ls

Mysql-bin.000001

[root@server4 mnt] # mysqlbinlog-- start-position=975-- stop-position=1224 mysql-bin.000001 | mysql pours this binary into the westos library, making the three westos databases in one environment.

Operate on master's westos database.

Mysql backup method:

Cold backup: the service needs to be shut down, and read and write requests are not allowed.

Hot backup: the service is normal, backup will not be affected.

Wenbei: the service is normal, but only reading and writing are allowed.

Mysql backup method:

Mysqldump single thread, slow

Mysqlpump multithreading, which can bind at least one thread per table

Mydumpe multithreading, at least multiple threads can be bound per table

Is there anything you don't understand about the above what is MYSQL's AB replication principle? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.