Mysql一主多從部署的實現步驟

Posted on by

1.下載地址

https://dev.mysql.com/downloads/mysql/

2.下載tar.gz包

選擇redhat版本

在這裡插入圖片描述

在這裡插入圖片描述

3.安裝

1.linux系統上創建mysql1用戶

useradd mysql1

2.將tar.gz包上傳到服務器上並且解壓

tar -zxvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

3.將mysql-5.7.31-el7-x86_64目錄下的文件mv到 /home/mysql1下

4.mkdir -p /home/mysql1/data 文件存儲mysql1數據目錄

mkdir -p /home/mysql1/data

5.將/etc/my.cfg文件復制到mysql傢目錄下

cp /etc/my.cnf /home/mysql1/

6.對/home/mysql1下賦權

chmod -R 755 /home/mysq1l
chown mysql:mysql -R /home/mysql1/data
chmod 777 /home/mysql1/data

7.修改mysql配置文件

vi /home/mysql1/my.cnf

[mysqld]
user                            = 
port                            = 3307
basedir                         = /home/mysql1
datadir                         = /home/mysql1/data
socket                          = /home/mysql1/mysql.sock
pid_file                        = /home/mysql1/mysql.pid
log_error                       = /home/mysql1/data/error.log
#binlog日志文件
log_bin                         = /home/mysql1/data/mysql-bin
relay_log                       = /home/mysql1/data/relay-bin
slow_query_log_file             = /home/mysql1/data/slow.log
#binlog過期清理時間
expire_logs_days                = 15
log-slave-updates               = 1
log_bin_trust_function_creators = 1
lower_case_table_names          = 1
max_connections                 = 3000
max_connect_errors              = 1000000 
# 每個實例的id都設置成不一樣的，比如a主機，3307 b主機設置成23307，c主機設置成33307，後面1主2從會用到
server-id                       = 3307                              
autocommit                      = 1
# pool_size根據實際情況進行更新
innodb_buffer_pool_size         = 40G
innodb_buffer_pool_instances    = 8
innodb_write_io_threads         = 16
innodb_read_io_threads          = 16
#mysql復制主要有三種方式：基於SQL語句的復制(statement-based replication, SBR)，基於行的復制(row-based replication, RBR)，混合模式復制(mixed-based replication, MBR)。對應的，binlog的格式也有三種：STATEMENT，ROW，MIXED
binlog_format                   = ROW
gtid_mode                       = on
enforce_gtid_consistency        = 1
innodb_flush_log_at_trx_commit  = 1  
#add 2                          
innodb_thread_concurrency       = 20
innodb_print_all_deadlocks      = 1
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 8000
innodb_io_capacity_max          = 15000
enforce_gtid_consistency        = 1
binlog_rows_query_log_events    = 1
character_set_server            = utf8mb4
default-storage-engine          = INNODB
transaction_isolation           = READ-COMMITTED
max_allowed_packet              = 67108864
event_scheduler                 = 1
slow_query_log                  = on
explicit_defaults_for_timestamp = 1   
master_info_repository          = TABLE
relay_log_info_repository       = TABLE
relay_log_recovery              = 1
relay_log_purge                 = 0  
slave_rows_search_algorithms    = 'INDEX_SCAN,HASH_SCAN'
slave_parallel_type             = LOGICAL_CLOCK
slave_parallel_workers          = 16
slave_preserve_commit_order     = 1
slave_transaction_retries       = 64
sync_relay_log                  = 0
sync_relay_log_info             = 0
sync_master_info                = 0
sync_binlog                     = 1
collation_server                = utf8mb4_bin
skip_name_resolve               = 1 
plugin-load                     = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled    = 1
rpl-semi-sync-slave-enabled     = 1

8.數據文件初始化

1.初始化

./mysqld --defaults-file=/home/mysql1/my.cnf --initialize --basedir=/home/mysql1 --datadir=/home/mysql1/data --user=mysql1

2.啟動

./mysqld_safe --defaults-file=/home/mysql1/my.cnf --user=mysql1 &

查看mysql進程已經起來，並且監聽3307接口

在這裡插入圖片描述

3.本機登陸，root的初始密碼可以在errorlog中找到

cat error.log | grep "temporary password"

在這裡插入圖片描述

4.本機登錄指定sock文件以及指定端口及輸入臨時密碼

./mysql -uroot -P3307 -S /home/mysql1/mysql.sock -p

5. 登錄後修改root密碼

set password=password('XXXXXXXX'); 修改密碼
flush privileges;

6. 賦予權限

grant all privileges on *.* to 'root'@'%' identified by 'XXXXXXX' with grant option;
flush privileges;

7. 停止服務

./mysqladmin shutdown -uroot -p******** -S /home/mysql1/mysql.sock

4.使用mysql客戶端datagrip連接mysql成功

在這裡插入圖片描述

5.通過如上相同操作安裝從庫，隻修改配置文件相關信息，並且初始化，並且客戶端連接

在這裡插入圖片描述

6.在master數據庫上執行

1.創建repl用戶並且賦予同步權限

CREATE USER 'repl' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'slavepass';
flush privileges;

在這裡插入圖片描述

2.查看master狀態

SHOW MASTER STATUS\G;

在這裡插入圖片描述

7.在slave上面執行

1.配置slave從節點的master節點以及binlog偏移位置等

CHANGE MASTER TO MASTER_HOST='master地址',
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_LOG_FILE='mysql-bin.000003', #為master的log_file
MASTER_LOG_POS=1597,       #master的POS
MASTER_PORT=3307;

在這裡插入圖片描述

2.啟動slave同步進程以及查看slave狀態

start slave;  #啟動slave同步進程
show slave status\G;

在這裡插入圖片描述

8.測試

在mysql1中創建testdb，使用datagrip在mysql2中即可查看從mysql1中同步的新增的庫以及數據，不過datagrip需要刷新下客戶端。

,

9.總結主從部署

註意主從mysql的my.cnf配置文件的server-id必須配置不一樣

到此這篇關於Mysql一主多從部署的實現步驟的文章就介紹到這瞭,更多相關Mysql一主多從內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：