久久国产高清字幕中文,日本免费久久久久久久网站,日韩欧美亚洲综合久久影院d3

這篇文章主要介紹了詳解mysql主從復制實戰 – 基于日志點的復制，具有一定的參考價值，感興趣的小伙伴們可以參考一下。

基于日志點的復制

1、在主庫與從庫上建立專用的復制賬號

MariaDB?[employees]&gt;?create?user?'repl'@'172.%'?identified?by?'123456';

注意在生產上的密碼必須依照相關規范以達到一定的密碼強度, 并且規定在從庫上的特定網段上才能訪問主庫

2、在主庫與從庫上授予復制權限

MariaDB?[employees]&gt;?grant?replication?slave?on?*.*?to?'repl'@'172.%';

3、配置主庫

注意啟用二進制日志需要重啟服務, 而server_id是一個動態參數, 可以結合命令行與mysql以達到免重啟的持久化配置. 注意server_id在集群中是唯一的.

[mysqld]  log_bin?=?/var/log/mysql/mariadb-bin  log_bin_index?=?/var/log/mysql/mariadb-bin.index  binlog_format?=?row  server_id?=?101

NOTE: 把日志與數據分開是個好習慣, 最好能放到不同的數據分區

4、配置從庫

選項log_slave_update決定是否把中繼日志relay_log存放到本機的binlog中, 如果是配置鏈路復制, 那么該選項必填. 注意server_id在集群中是唯一的.

[mysqld]  #?replication  log_bin?=?/var/log/mysql/mariadb-bin  log_bin_index?=?/var/log/mysql/mariadb-bin.index  server_id?=?102  #?slaves  relay_log????=?/var/log/mysql/relay-bin  relay_log_index??=?/var/log/mysql/relay-bin.index  relay_log_info_file??=?/var/log/mysql/relay-bin.info  log_slave_updates?=?ON  read_only

5、初始化從庫的數據

此處使用mysqldump在主庫上進行備份, 在生產上建議大家用xtrabackup進行無鎖的熱備(基于innodb引擎).

備份主庫上的employees數據庫的數據

?代碼如下:

mysqldump?--single-transaction?--master-data=1?--triggers?--routines?--databases?employees?-u?root?-p?&gt;&gt;?backup.sql

將備份文件backup.sql通過scp或者docker volume卷掛載到從服務器上, 并且導入至從庫中

mysql?-u?root?-p?<p>6、啟動復制鏈路</p><p>現有master@172.20.0.2和slave@172.20.0.3, 并且已經通過mysqldump將數據同步至從庫slave中. 現在在從服務器slave上配置復制鏈路</p><pre class="brush:sql;">MariaDB?[(none)]&gt;?CHANGE?MASTER?TO?MASTER_HOST='master',?  MASTER_USER='repl',?  MASTER_PASSWORD='123456',?  MASTER_LOG_FILE='mariadb-bin.000029',?  MASTER_LOG_POS=516;  Query?OK,?0?rows?affected?(0.02?sec)

在從庫上啟動復制鏈路

MariaDB?[(none)]&gt;?start?slave;  Query?OK,?0?rows?affected?(0.01?sec)

7、在從庫上檢查slave狀態

Slave_IO_Running與Slave_SQL_Running必須為YES, 如果出現錯誤須詳細閱讀Last_IO_Error或Last_SQL_Error的提示信息

MariaDB?[(none)]&gt;?show?slave?statusG  ***************************?1.?row?***************************  ????????Slave_IO_State:?Waiting?for?master?to?send?event  ?????????Master_Host:?master  ?????????Master_User:?repl  ?????????Master_Port:?3306  ????????Connect_Retry:?60  ???????Master_Log_File:?mariadb-bin.000029  ?????Read_Master_Log_Pos:?516  ????????Relay_Log_File:?relay-bin.000002  ????????Relay_Log_Pos:?539  ????Relay_Master_Log_File:?mariadb-bin.000029  ???????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:  ??????????Last_Errno:?0  ??????????Last_Error:  ?????????Skip_Counter:?0  ?????Exec_Master_Log_Pos:?516  ???????Relay_Log_Space:?831  ???????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  Master_SSL_Verify_Server_Cert:?No  ????????Last_IO_Errno:?0  ????????Last_IO_Error:  ????????Last_SQL_Errno:?0  ????????Last_SQL_Error:  ?Replicate_Ignore_Server_Ids:  ???????Master_Server_Id:?101  ????????Master_SSL_Crl:  ??????Master_SSL_Crlpath:  ??????????Using_Gtid:?No  ?????????Gtid_IO_Pos:  ???Replicate_Do_Domain_Ids:  ?Replicate_Ignore_Domain_Ids:  ????????Parallel_Mode:?conservative  1?row?in?set?(0.00?sec)

8、在主庫檢查dump線程

檢測是否已經正確啟動binlog dump線程

MariaDB?[(none)]&gt;?show?processlist?G  ***************************?1.?row?***************************  ???Id:?7  ??User:?root  ??Host:?172.20.0.1:41868  ???db:?employees  ?Command:?Sleep  ??Time:?56  ??State:  ??Info:?NULL  Progress:?0.000  ***************************?2.?row?***************************  ???Id:?10  ??User:?repl  ??Host:?172.20.0.3:45974  ???db:?NULL  ?Command:?Binlog?Dump  ??Time:?246  ??State:?Master?has?sent?all?binlog?to?slave;?waiting?for?binlog?to?be?updated  ??Info:?NULL  Progress:?0.000

可以看到row 2上有Command為Binlog Dump的命令被啟動, 證明復制線程已經被成功啟動

9、總結

優點