mysql 主從復制優化、并行復制

實驗環境：

server2 為 server3 的 master

server3 為 server1 的 master

即server1、2、3的身份如下：

server2：master

server3：master / slave

server2：slave

由于先前做過其他數據庫實驗，環境是server2為master, server1和server3為slave

實驗操作：

server1、2、3配置文件修改如下：

server2
server3
server1
server3

mysql> grant replication slave on *.* to 'tutu'@'172.25.54.%' identified by '5820hhXM!@#';
Query OK, 0 rows affected, 1 warning (0.05 sec)

server1

mysql> change master to master_host='172.25.54.3',master_user='tutu',master_password='5820hhXM!@#',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.17 sec)

若出現Slave_SQL_Running: No
(原因：數據不同步，
在 server3 上 reset msater;
server1 上 shop slave; –> reset slave; –>start slave;)

server3
server1

測試

server2

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.13 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

server3 和 server1
server2

mysql> use test;
Database changed
mysql> create table userlist (
    -> username varchar(15) not null,
    -> password varchar(25) not null);
Query OK, 0 rows affected (0.41 sec)

mysql> desc userlist;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO   |     | NULL    |       |
| password | varchar(25) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into userlist values ('user1','147');
Query OK, 1 row affected (0.09 sec)

mysql> insert into userlist values ('user2','258');
Query OK, 1 row affected (0.16 sec)

mysql> insert into userlist values ('user3','369');
Query OK, 1 row affected (0.08 sec)

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
| user3    | 369      |
+----------+----------+
3 rows in set (0.00 sec)

mysql> Bye
[root@server2 ~]#

server3 和 server1

注：bin log索引文件: /var/lib/mysql/mysql-bin.index
查看二進制日志：mysqlbinlog -vv –base64-output=DECODE-ROWS mysql-bin.000006 //-vv詳細顯示，–base64查看加密的數據

并行復制

server3——單線程–>多線程工作
mysql> show processlist;

[root@server3 ~]# vim /etc/my.cnf
[root@server3 ~]# cat /etc/my.cnf | tail -n 11
server-id=3
log-bin=mysql-bin
log-slave-updates

gtid_mode=ON
enforce-gtid-consistency=true
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@server3 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server3 ~]# mysql -p5820hhXM\!\@#
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show processlist;
+----+-------------+---------------+------+------------------+-------+---------------------------------------------------------------+------------------+
| Id | User        | Host          | db   | Command          | Time  | State                                                         | Info             |
+----+-------------+---------------+------+------------------+-------+---------------------------------------------------------------+------------------+
|  1 | system user |               | NULL | Connect          |    58 | Slave has read all relay log; waiting for more updates        | NULL             |
|  2 | system user |               | NULL | Connect          |    60 | Waiting for master to send event                              | NULL             |
|  3 | system user |               | NULL | Connect          | 83590 | Waiting for an event from Coordinator                         | NULL             |
|  4 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
|  6 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
|  7 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
|  8 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
|  9 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 10 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 11 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 12 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 13 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 16 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 17 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 18 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 19 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 20 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 21 | system user |               | NULL | Connect          |    60 | Waiting for an event from Coordinator                         | NULL             |
| 22 | tutu        | server1:48836 | NULL | Binlog Dump GTID |     5 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 23 | root        | localhost     | NULL | Query            |     0 | starting                                                      | show processlist |
+----+-------------+---------------+------+------------------+-------+---------------------------------------------------------------+------------------+
20 rows in set (0.00 sec)

本文鏈接：https://blog.csdn.net/hh5820/article/details/81738558

智能推薦

MySQL5.7安裝+基于GTID主從復制+并行復制+增強半同步復制+讀寫分離+M-S-S架構（聯級復制）

實驗環境： Centos7.2 角色主機IP server_id 數據狀態 Proxysql 192.168.148.62 null 無 Master 192.168.148.62 1 剛安裝的全新MySQL實例 Slave1 192.168.148.61 2 剛安裝的全新MySQL實例 Slave2 192.168.148.64 3 剛安裝的全新MySQL實例一、安裝最新版本M...

mysql的并行復制

并行復制的介紹 MySQL 5.6版本也支持所謂的并行復制，但是其并行只是基于schema的，也就是基于庫的。如果用戶的MySQL數據庫實例中存在多個schema，對于從機復制的速度的確可以有比較大的幫助。 MySQL 5.7版本支持兩種復制方式：基于庫的復制方式和基于組的復制方式。為了兼容MySQL 5.6基于庫的并行復制，5.7引入了新的變量slave-parallel-type，其可以配置...

Mysql并行復制實踐總結

mysql并行復制總結實戰篇 Mysql5.6 并行復制一般Mysql主從復制有三個線程參與，都是單線程：Binlog Dump(主) -> IO Thread (從) -> SQL Thread(從)。復制出現延遲一般出在兩個地方: SQL線程忙不過來 (可能需要應用數據量較大，可能和從庫本身的一些操作有鎖和資源的沖突；主庫可以并發寫，SQL線程不可以；主要原因) 網絡抖動導致...

MySQL優化之主從復制

概念影響MySQL-A數據庫的操作，在數據庫執行后，都會寫入本地的日志系統A中。假設，實時的將變化了的日志系統中的數據庫事件操作，在MYSQL-A的3306端口，通過網絡發給MYSQL-B。 MYSQL-B收到后，寫入本地日志系統B，然后一條條的將數據庫事件在數據庫中完成。那么，MYSQL-A的變化，MYSQL-B也會變化，這樣就是所謂的MYSQL的復制，即MYSQL replication...

HTML中常用操作關于：頁面跳轉，空格

1.頁面跳轉 2.空格的代替符...