mysql--主從複製
主從複製簡介
首先需要兩臺機器，並且主從複製的前提是需要先做資料同步，先在我的master機器上用mysqldump 將所有的資料備份，然後scp傳輸到我的slave機器上，然後在slave機器上再將資料匯入實現資料同步
然後master機器必須要開啟二進位制日誌，主從伺服器配置不同的server_id ,從伺服器需要開啟slave服務，保證我的I/O thread 和SQL thread 都是開啟的，當我的主伺服器上的資料改變時，首先會記錄到二進位制日誌，主上的log dump 會向我的從伺服器的io thread 傳送訊息，讓它來拿我更新的二進位制日誌，然後slave的io執行緒會連線到master ，來讀取master的二進位制日誌，寫入到slave的中繼日誌relaylog中，然後Slave中的SQL執行緒讀取中繼日誌relaylog進行SQL 的回放（replay）操作，完成主從複製，從而達到主從伺服器上的資料一致性
實操
環境準備：兩臺機器（master slave），編譯安裝mysql，且最好mysql版本一致
非同步複製：
同步複製：
半同步複製：
主從複製的前提：先做主從複製的資料同步
二進位制日誌的位置號：主的二進位制位置號肯定比從的二進位制號大，所以就會存在一個問題：主並不知道從哪個號碼開始推送
I/O thread
SQL thread
兩臺機器，master --二進位制
實操
在master主機上要進行的操作
1，開啟mysql的二進位制日誌
[root@master backup]# cat /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
#skip-grant-tables
#binary log
server_id=1
log_bin
[mysql]
auto-rehash
prompt=\\u@\\d \\R:\\m mysql>
#檢視二進位制日誌是否開啟
root@(none) 09:18 mysql>show variables like "%log_bin%";
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/master-bin |
| log_bin_index | /usr/local/mysql/data/master-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------+
6 rows in set (0.01 sec)
2，建立一個mysql新使用者並授予複製許可權
root@(none) 08:38 mysql>grant replication slave on *.* to 'xionghan'@'192.168.0.173' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@(none) 08:38 mysql>show slave status\G
Empty set (0.00 sec)
3.備份所有的庫
[root@master ~]# mysqldump -uroot -p'Sanchuang1234#' --all-databases >/backup/2020-10-23-all-db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
4，上傳資料到從伺服器
[root@master ~]# cd /backup/
[root@master backup]# ls
2020-10-22-all-db.sql 2020-10-23-all-db.sql
[root@master backup]# scp 2020-10-23-all-db.sql root@192.168.0.173:/root
root@192.168.0.173's password:
2020-10-23-all-db.sql 100% 834KB 24.3MB/s 00:00
5.檢視當前的二進位制日誌的檔名和位置號
root@(none) 08:54 mysql>show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 453 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在從伺服器上要做的操作
1.配置server_id（必須的）也開啟二進位制日誌（二進位制日誌不是必須的）
[root@slave ~]# vim /etc/my.cnf
[root@slave ~]# cat /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
server_id=2
log_bin
[mysql]
auto-rehash
prompt=\\u@\\d \\R:\\m mysql>
[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@slave ~]# ps aux|grep mysqld
root 46275 0.1 0.0 11816 1592 pts/1 S 08:56 0:00 /bin/sh /usr/local/sc_mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/slave.pid
mysql 46443 1.5 5.2 2026404 203020 pts/1 Sl 08:56 0:00 /usr/local/sc_mysql/bin/mysqld --basedir=/usr/local/sc_mysql --datadir=/data/mysql --plugin-dir=/usr/local/sc_mysql/lib/plugin --user=mysql --log-error=slave.err --open-files-limit=8192 --pid-file=/data/mysql/slave.pid --socket=/data/mysql/mysql.sock --port=3306
root 46475 0.0 0.0 112824 980 pts/1 S+ 08:56 0:00 grep --color=auto mysqld
#檢視二進位制日誌是否開啟
root@(none) 20:33 mysql>show variables like "%log_bin%";
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/slave-bin |
| log_bin_index | /data/mysql/slave-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows in set (0.24 sec)
2，匯入master上的資料
[root@slave ~]# mysql -uroot -p'Sanchuang1234#' < 2020-10-23-all-db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
3.在slave上配置master的資訊和授權的使用者名稱和密碼
root@(none) 08:59 mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.171',
-> MASTER_USER='xionghan',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master-bin.000002',
-> MASTER_LOG_POS=453;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
5.檢視slave的狀態
root@(none) 09:00 mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.171
Master_User: xionghan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 453
Relay_Log_File: slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
#啟動slave功能
root@(none) 09:00 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
#再次檢視
```bash
root@(none) 09:01 mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.171
Master_User: xionghan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 453
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
7.驗證主從複製的功能
1.在主上插入新的資料
2.在從上看是否有資料
在主伺服器上的操作
#新建庫
root@(none) 09:03 mysql>create database xh1;
Query OK, 1 row affected (0.01 sec)
root@(none) 09:03 mysql>use xh1;
Database changed
#新建表
root@xh1 09:03 mysql>create table t1(id int);
Query OK, 0 rows affected (0.03 sec)
root@xh1 09:04 mysql>show tables;
+---------------+
| Tables_in_xh1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
#插入資料
root@xh1 09:04 mysql>insert into t1(id) values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
在從伺服器上檢視資料是否一致
root@(none) 09:01 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aliwangwang |
| chenran |
| mysql |
| performance_schema |
| sanchuang |
| sc |
| sys |
| wangtc |
| xh1 |
| xionghan |
| zwx |
+--------------------+
12 rows in set (0.00 sec)
root@(none) 09:04 mysql>use xh1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@xh1 09:04 mysql>show tables;
+---------------+
| Tables_in_xh1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
root@xh1 09:09 mysql>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
擴充套件
上述是一主一從，可不可以一主多從？答案是可以的
一主多從的優點：
1，互相備份
2，流量分流，讀寫分離，寫只往主伺服器上寫，讀主從伺服器都可以讀，達到一個冗餘和負載均衡的效果
非同步主從複製的缺點：資料有延遲，如何解決？ —引出半同步
日誌首先在記憶體產生—》再寫到磁碟 —二進位制日誌的優點：方便資料恢復
如果slave遲遲沒有回覆，會自動切換為非同步模式
半同步複製的優點：減小資料延遲，master在寫資料時會先向從伺服器傳送訊息，主從伺服器一起寫資料
日誌檔案 表檔案
半同步實操
1，在master上的配置
root@(none) 19:39 mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.05 sec)
#檢視有哪些外掛
root@(none) 19:41 mysql>show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows in set (0.00 sec)
開啟相應的服務
root@(none) 19:43 mysql>set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
檢視一下
root@(none) 19:44 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)
rpl_semi_sync_master_wait_point | AFTER_SYNC
在master 同步了二進位制日誌後，
在從伺服器上安裝外掛
root@(none) 19:51 mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)
開啟相應的服務
root@(none) 19:51 mysql>set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
檢視一下
root@(none) 19:52 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)
半同步配置成功
問題：從伺服器是如何知道主伺服器更新了哪些資料，從伺服器又是怎麼知道該從哪裡拿資料呢？
[root@slave mysql]# cd /data/mysql
[root@slave mysql]# ls
aliwangwang relay-log.info
auto.cnf sanchuang
ca-key.pem sc
ca.pem server-cert.pem
chenran server-key.pem
client-cert.pem slave-bin.000001
client-key.pem slave-bin.000002
ib_buffer_pool slave-bin.000003
ibdata1 slave-bin.index
ib_logfile0 slave.err
ib_logfile1 slave.pid
ibtmp1 slave-relay-bin.000001
localhost.localdomain.err slave-relay-bin.000002
localhost.localdomain.pid slave-relay-bin.index
master.info sys
mysql wangtc
mysql.sock wangwang
mysql.sock.lock wangwang1
node2-db.err xh1
performance_schema xionghan
private_key.pem zwx
public_key.pem
[root@slave mysql]# cat master.info
25
master-bin.000002
1370
192.168.0.171
xionghan
123456
3306
60
0
0
30.000
0
3b416199-07a7-11eb-83b5-000c29ab471a
86400
0
[root@slave mysql]# cat relay-log.info
7
./slave-relay-bin.000002
1238
master-bin.000002
1370
0
0
1
相關文章
- MySQL主從複製與主主複製MySql
- MySQL主從複製、半同步複製和主主複製MySql
- mysql5.7主從複製，主主複製MySql
- MySQL主從複製、半同步複製和主主複製概述MySql
- MySQL的主從複製、半同步複製、主主複製詳解MySql
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- mysql複製--主從複製配置MySql
- MySQL的主從複製與MySQL的主主複製MySql
- Redis - 主從複製Redis
- Redis：主從複製Redis
- MongoDB主從複製MongoDB
- MySQL 主從複製MySql
- 【MySql】主從複製MySql
- MySQL主從複製MySql
- redis主從複製Redis
- MySQL主從複製_複製過濾MySql
- 主從複製、雙主複製及半同步複製、以及基於SSL的複製
- MySQL主從複製之GTID複製MySql
- MySQL主從複製之半同步複製MySql
- MySQL主從複製之非同步複製MySql非同步
- 主從複製是啥或者主從複製的原理是什麼？
- Windows 環境下，MySQL 的主從複製和主主複製WindowsMySql
- windows環境下，Mysql的主從複製和主主複製WindowsMySql
- mysql主從複製(一):一主多從MySql
- Redis 主從複製原理Redis
- MySQL主從複製原理MySql
- Redis 主從複製（Replication）Redis
- redis系列：主從複製Redis
- MySQL的主從複製MySql
- mysql主從複製搭建MySql
- redis（14）主從複製Redis
- MySql 主從複製配置MySql
- MySQL主從複製配置MySql
- redis主從複製例子Redis
- PostgreSQL 主從複製方案SQL
- MySQL(二)：主從複製結構、半同步複製、雙主複製結構、利用SSL實現安全的MySQL主從複製MySql
- mysql資料庫的主從複製和主主複製實踐MySql資料庫
- MysqL主從複製_模式之GTID複製MySql模式