mysql主從分離

標籤：檔案   www.   使用者   conf   use   bsp   create   read   col   

1、工具：

兩台機器

master:192.168.0.1

slave:192.168.0.2

2、master的配置

找到mysql的設定檔，一般centos的是/etc/my.cnf，ubuntu的是/etc/mysql/mysql.conf.d/mysqld.cnf

跳到到[mysqld]下配置

bind-address = 192.168.0.1 #可注釋server-id = 1 #架構中唯一節點idlog_bin=mysql-binlog_bin = /var/log/mysql/mysql-bin.log #開啟binlogbinlog-ignore-db=mysql  #不需要複製的資料庫binlog-do-db=ufind_db   #要複製的資料庫

重啟master

service mysql restart

進入mysql命令列，加使用者

CREATE USER ‘zhylioooo‘@‘%‘ IDENTIFIED BY ‘123456‘;GRANT REPLICATION SLAVE ON *.* TO ‘zhylioooo‘@‘%‘;

匯出master資料庫

給資料庫加鎖使其只可讀

FLUSH TABLES WITH READ LOCK;

到處資料庫檔案

mysqldump -u root -p --all-databases --master-data > dbdump.sql

給資料庫解鎖

UNLOCK TABLES;

查看master的二進位記錄檔案及檔案目前記錄的位置

SHOW MASTER STATUS;

+ ------------------+----------+--------------+------------------+-------------------+ | File    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | + ------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |  1050 |    |     |     | + ------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 3、slave配置slave配置重點

server-id = 2 #架構中唯一節點id，比master大log_bin=mysql-binlog_bin = /var/log/mysql/mysql-bin.log #開啟binlog

重啟mysql

匯入master匯出的資料庫檔案，使資料庫一致

 mysql -u root -p < dbdump.sql

進入mysql使slave與master串連

STOP SLAVE;CHANGE MASTER TO -> MASTER_HOST=‘192.168.0.1‘, -> MASTER_USER=‘zhylioooo‘, -> MASTER_PASSWORD=‘123456‘, -> MASTER_LOG_FILE=‘mysql-bin.000001‘, -> MASTER_LOG_POS=1050;START SLAVE;

4、叢集策略：https://www.cnblogs.com/KTblog/p/5122825.html

 

 

mysql主從分離