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

Mysql master-slave copy

mysql master-slave replication principle:

There are two threads from the library IO thread and SQL thread

1. The IO thread of the slave library sends a request to the master process of the master library, and the master library verifies the slave library and hands it to the IO thread of the master library to be responsible for data transmission;

2. The IO thread of the master library compares the information in master.info sent from the slave library and sends binlog file information, offset and binlog file name to the slave library.

3. After receiving the information from the library, save the binlog information to relay-bin and update the offset of master.info and binlog file name

4. The SQL thread from the library continuously reads relay-bin information, writes the read offset and file name into relay-log.info file, writes binlog information into its own database, and completes a synchronization operation.

5. After the last synchronization, the slave IO thread keeps asking the master IO thread for binlog information.

6. If you want to master the slave library, you should also turn on log_bin and log-slave-update parameters.

Steps to configure read/write mysql master-slave replication:

1. Install mysql database in both master and slave repositories

2. Configure server-id and log-bin in the configuration file (/etc/my.cnf) of the main library

3. Create an authenticated user and authorize it after logging into the main database.

4. Configure server-id in the configuration file of the slave library (/etc/my.cnf)

5. After logging in to the slave library, specify master and turn on the synchronization switch.

Note that server-id master-slave libraries are configured differently.

Server-id exists for:

The mysql synchronized data contains the server-id, which identifies the server from which the statement was originally written. So server-id has to be there

Server-id cannot be the same reason: each slave in synchronization corresponds to a master thread on the master, and this thread is identified by the server-id of the slave; each slave has at most one master thread on the master side. If the server-id of the two slaves is the same, then when the latter connection succeeds, the slave actively connects to the master, and if the slave executes slave stop, the connection is disconnected, but the corresponding thread on the master does not exit; After slave start, the master cannot create another thread and keep the original thread, so synchronization may be problematic;

When mysql is the master synchronization, multiple masters need to form a ring, but when synchronizing, it is necessary to ensure that a piece of data does not fall into an endless loop, which is achieved by server-id;

Mysql master-slave replication (gtid) experimental configuration: configuration environment: rhel6.5 iptables selinx down172.25.40.1 server1.example.com master172.25.40.2 server2.example.com slave installation package: mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar Install mysql database after extracting on master and slave: tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.taryum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm /etc/init.d/mysqld start grep 'password' /var/log/mysqld.log #Filter initialization Yes Database login password After logging in to the database, modify the password: `alter user root@localhost identified by 'xxx';` ##Modify the local user password1. Master operation: vim /etc/my.cnf[mysqld]server-id=1log-bin=mysql-bingtid_mode=ON #Enforce-gtid-consistency=true #Force gtidSlave operations: [mysqld]server-id=2gtid_mode= ONforce-gtid-consistency=true Note: When modifying the mysql configuration files of the two servers, make sure that the server-id must be different. The server-id value range is: 2^32-12. Restart mysql` /etc/init.d/mysqld restart` on the two servers. 3. Enter the main database to create an authenticated user and authorize `grant replication slave on *.* to redhat@'172.25.40.% ' identified by ' xx';`4. Enter slave port to specify masterstop slave;change master to master_host='172.25.40.1', master_user=' redhat', master_password='xx', master_auto_position=1;start slave;5. In setting semi-synchronous replication master port operation mysql> install plugin rpl_semi_sync_master soname ' semisync_master.so'; Query OK, 0 rows affected (0.50 sec)mysql> set global rpl_semi_sync_master_enabled=1;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%rpl_semi%';+-------------------------------------------+------------+| Variable_name | Value |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 10000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_for_slave_count | 1 || rpl_semi_sync_master_wait_no_slave | ON || rpl_semi_sync_master_wait_point | AFTER_SYNC |+-------------------------------------------+------------+6 rows in set (0.01 sec)6.5. mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; Query OK, 0 rows affected (0.14 sec)mysql> set global rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%rpl_semi%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled | ON || rpl_semi_sync_slave_trace_level | 32 |+---------------------------------+-------+2 rows in set (0.00 sec)

If the IO thread is opened on the slave side, it will automatically switch to semi-synchronous mode for data transmission.

Closing the io thread will wait 10 seconds to switch from semi-synchronous to asynchronous when it transacts on master.

When the data is inserted a second time, it becomes asynchronous synchronization

Mysql parallel copy multithreaded work Slave side modification profile: [mysqld]slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 #Open 16 threads master_info_repository=TABLE #Store master_info information in table form relay_log_info_repository=TABLE #Store relay_log_info information in table form relay_log_recovery=ON #Restart the service `/etc/init.d/mysqld restart` When entering the mysql library of the database, you can execute sql statement: `select * from slave_master_info;` You can see that the relay-log.info file originally under/var/lib/mysql has been converted into a table in the database.

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.