單機多執行個體MYSQL主從複製

標籤：osi   tar.gz   character   hash   .so   建立   span   進位   密碼   

　　今天有時間寫寫，不然心坎裡總有點不爽。單機多執行個體一直都是屌絲的處事風格。。。

實驗環境

　　RHEL6.5　　

　　172.24.0.130  3306

　　172.24.0.130  3307

01、本次採用的MYSQL二進位快速安裝

：

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

http://pan.baidu.com/s/1qYI0ybq

http://pan.baidu.com/s/1pLHM2TH 密碼: pg5t

#操作

#根據需求選擇對應的版本

 

02、解壓並建立資料目錄

tar zxf xxx.tar.gz    -C /tmp

mkdir  -p {data_3306,data3307}     #存放資料

mkdir  conf                                    #放my.cnf

03、修改設定檔my.cnf

#3306my.cnf

[client]default-character-set=utf8    #用戶端串連編碼[mysql]auto-rehash                   #用戶端tab補全default-character-set=utf8    #編碼[mysqld]
user=mysql                    #運行賬戶port=3306                     #定義連接埠log-bin=mysql-bin             #開啟二進位日誌server-id=001                 #定義服務IDmax_allowed_packet=50M        ###最大wait_timeout=3600             ###等待逾時預設sinteractive_timeout=3600      ###活動逾時innodb_buffer_pool_size = 128Mjoin_buffer_size = 128Msort_buffer_size = 2Mread_rnd_buffer_size = 2Mbasedir=/tmp/mysql_mulit                 #MYSQL根目錄datadir=/tmp/mysql_mulit/data_3306       #MYSQL資料存放目錄socket=/tmp/3306_mysql.sock              #通訊端init_connect=‘SET collation_connection = utf8_unicode_ci‘init_connect=‘SET NAMES utf8‘character-set-server=utf8                #定義資料庫預設字元 server\collationcollation-server=utf8_unicode_ciskip-character-set-client-handshakesymbolic-links=0sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
lower_case_table_names = 0             #對錶的大小寫不敏感
[mysqld_safe]log-error=/tmp/3306_err.log              #錯誤記錄檔pid-file=/tmp/mysql_mulit/data_3306/mysqld.pid   #pid,多協議通訊 tcp/sock

3307my.cnf

[client]default-character-set=utf8    #用戶端串連編碼[mysql]auto-rehash                   #用戶端tab補全default-character-set=utf8    #編碼[mysqld] 
user=mysqlport=3307                     #定義連接埠log-bin=mysql-bin             #開啟二進位日誌server-id=011                 #定義服務IDmax_allowed_packet=50M        ###最大wait_timeout=3600             ###等待逾時預設sinteractive_timeout=3600      ###活動逾時innodb_buffer_pool_size = 128Mjoin_buffer_size = 128Msort_buffer_size = 2Mread_rnd_buffer_size = 2Mbasedir=/tmp/mysql_mulit                 #MYSQL根目錄datadir=/tmp/mysql_mulit/data_3307       #MYSQL資料存放目錄socket=/tmp/3307_mysql.sock              #通訊端init_connect=‘SET collation_connection = utf8_unicode_ci‘init_connect=‘SET NAMES utf8‘character-set-server=utf8                #定義資料庫預設字元 server\collationcollation-server=utf8_unicode_ciskip-character-set-client-handshakesymbolic-links=0sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES lower_case_table_names = 0             #對錶的大小寫不敏感[mysqld_safe]log-error=/tmp/3307_err.log              #錯誤記錄檔pid-file=/tmp/mysql_mulit/data_3307/mysqld.pid   #pid,多協議通訊 tcp/sock

04、初始化資料庫

./scripts/mysql_install_db  --defaults-file=conf/3306my.cnf

./scripts/mysql_install_db  --defaults-file=conf/3306my.cnf

05、啟動資料庫

./bin/mysqld_safe --defaults-file=conf/3306my.cnf &

./bin/mysqld_safe --defaults-file=conf/3307my.cnf &

06、初始化root密碼

./bin/mysqladmin   -P 3306 -u root password ‘123123‘

./bin/mysqladmin   -P 3307 -u root password ‘123123‘

07、master主機設定（3306）

建立從複製帳號

grant replication slave on *.* to ‘mysync‘@‘%‘ identified by ‘123456‘;             #所有IP
grant replication slave on *.* to ‘mysync‘@‘localhost‘ identified by ‘123456‘;   #127.0.0.1
grant replication slave on *.* to ‘mysync‘@‘zabibx‘ identified by ‘123456‘;      #172.24.0.130, zabbix是主機名稱

查看Master資訊

mysql> show master status;  #目的跟slave對上口徑
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      541 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

08、Slave從主機設定（3307）

進入mysql與master主機建立串連

change master to master_host=‘172.24.0.130‘,master_port=3306,master_user=‘mysync‘,master_password=‘123456‘, master_log_file=‘mysql-bin.000003‘,master_log_pos=541;

show slave status\G；

 start  slave;  #啟動從主機    stop slave；停止從主機

#中兩個yes說明從機已經串連到主機

 

單機多執行個體MYSQL主從複製