MySQL的複製(主從,主主,基於ssl)

1.準備的主機
node1:172.16.133.11
node2:172.16.133.12
均已安裝好MySQL
MySQL安裝見:http://5142926.blog.51cto.com/5132926/935652
2.mysql主從複製
node1:主伺服器
node2:從伺服器
(1)node1:
    啟用二進位日誌(預設就是啟用的)
 建立具有複製許可權的使用者
 設定server-id
node2:
    啟用中繼日誌(預設是禁用的,如果不需要的話,可以手動關閉二進位日誌)
 設定server-id
 啟動從服務,並指定主伺服器參數
node1:node1中mysql伺服器binary log預設就是開啟的,server-id也不用修改,預設即可
建立具有複製許可權的使用者
 

 

  
mysql>grant replication slave,replication client on *.* to repluser@'172.16.133.%' identified by 'redhat' 
  
mysql>flush privileges;  
 

node2:修改mysql主設定檔my.cnf中的server-id為21,注釋掉log-bin=mysql-bin
並在其後添加relay-log=mysql-relay
完成後,進入mysql,查詢下全域變數show global variables like '%log%;
 

 

  
mysql>change master to master_host='172.16.133.11',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000003',master_log_pos=356;  
 

mysql>show slave status/G查看從伺服器工作狀態,可以看到Slave_IO_Running: No和Slave_SQL_Running: No還是no,啟用這兩項
 

 

  
mysql>start slave;  
  
mysql>set global read_only=1;  
  
mysql>show slave status/G  
 

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/19402121S-0.jpg" />

然後就算配置完成了,可以在node1中建立一個測試資料庫testdb,和一個測試表t1

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940216155-1.jpg" />

進入node2的mysql查看

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940211447-2.jpg" />

(2).如果不想讓從伺服器線程在mysql服務啟動時自動啟動,則可以在從伺服器中設定skip-slave-start=1
為防止主伺服器突然崩潰,可以在主伺服器上設定
sync_binlog=1
innodb_flush_logs_at_trx_commit=1
(3).資料庫複寫過濾
主伺服器
[mysqld]
binlog-do-db=magedu
在主伺服器過濾：任何不涉及到資料庫相關的寫操作都不會被記錄到二進位日誌當中,所以最好不要設定,一般在從伺服器中設定即可
從伺服器：
replicate_do_db
rpplicate_ignore_db

replicate_do_table
replicate_ignore_table

replicate_wild_do_table
replicate_wild_ignore_table
在從伺服器上只複製testdb一個資料庫：
[mysqld]
replicate_do_db=testdb
replicate_do_db=mysql
(4).如果主伺服器以運行很長時間,才接入一台新的從伺服器,如果採取複製,會比較慢,可以採用備份的方式
node1:先對mysql伺服器施加讀鎖
mysql>flush tables with read lock;
而後對mysql所在資料目錄的邏輯卷,進行備份
lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata
mysql>show master status;查看現在所在位置

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/194021C02-3.jpg" />

mysql>unlocak tables(備份完成後要立即解鎖)
mount /dev/myvg/mydata-snap /mnt
cd /mnt
ll
find . | cpio -o -H newc --quiet | gzip > /root/alldatabase.gz
cd
umount /mnt
scp alldatabase.gz node2:/root
mysql>use testdb
mysql>create table tb2

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/19402160N-4.jpg" />

node2:
gzip -d /root/alldatabase.gz
cp alldatabase /data/mydata
cd /data/mydata
cpio -id < alldatabase
rm alldatabase
然後就可以直接service mysqld start
然後進入mysql,重新設定主從
mysql>change master to master_host='172.16.133.11',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000003',master_log_pos=542;
mysql>start slave;
mysql>show slave status\G
mysql>use testdb;
mysql>show tables
備份+複製完成
(5).半同步主從複製
node1:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; 
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
node2:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; 
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
也可通過設定全域變數的方式來設定,如下：
set global rpl_semi_sync_master_enabled=1
取消載入外掛程式
mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
查看從伺服器上的semi_sync是否開啟:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
查看主伺服器上的semi_sync是否開啟，注意clients 變為1 ，證明主從半同步複製串連成功:
(6).基於ssl傳輸的mysql主從複製
①.修改設定檔
node1:
server_id=10        
log_bin=mysql-bin
sync_binlog=1事務提交後立即寫入磁碟二進位檔案,不再先緩衝再寫
node2:
read_only=1
②準備認證,私密金鑰
Ⅰ.建立字簽證伺服器
node1:
vim /etc/pki/tls/openssl.cnf
   dir=/etc/pki/CA
(umask 077;openssl genrsa 2048 > private/cakey.pem)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3655

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940214N3-5.jpg" />

mkdir certs crl newcerts
touch index.txt
echo 01 > serial
Ⅱ.為node1上的mysql準備私密金鑰及頒發認證
mkdir /usr/local/mysql/ssl
cd ssl/
(umask 077;openssl genrsa 1024 > mysql.key)
openssl req -new -key mysql.key -out mysql.csr
openssl ca -in mysql.csr -out mysql.crt
cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
Ⅲ.為node2上的mysql準備私密金鑰及頒發認證
mkdir /usr/local/mysql/ssl
cd ssl/
(umask 077;openssl genrsa 1024 > mysql.key)
openssl req -new -key mysql.key -out mysql.csr
scp ./mysql.csr node1:/root
Ⅳ.為node2簽發認證
openssl ca -in mysql.csr -out mysql.crt
scp ./mysql.crt node2:/usr/local/mysql/ssl
cd /etc/pki/CA
scp ./cacert.pem node2:/usr/local/mysql/ssl
完成後,確定node1和node2中的/usr/local/mysql/ssl目錄下,有這4個檔案

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940215223-6.jpg" />

③.開啟mysql的ssl功能
node1:
mysql>show variables like '%ssl%';

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/19402145K-7.jpg" />

其中have_openssl,have_ssl顯示為disabled,表示未開啟ssl
編輯主設定檔/etc/my.cnf在[mysqld]中添加
ssl
即可,重啟mysql服務
mysql>show variables like '%ssl%';

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940211B3-8.jpg" />

node2:同node1
④配置主從伺服器中的ssl功能
node1:
修改主設定檔,在[mysqld]中添加如下幾項
vim /etc/my.cnf
    ssl-ca=/usr/local/mysql/ssl/cacert.pem
    ssl-cert=/usr/local/mysql/ssl/mysql.crt
    ssl-key=/usr/local/mysql/ssl/mysql.key
mysql>show variables like '%ssl%';

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/194021N59-9.jpg" />

node2:同node1
⑤.配置主從服務
node1:
mysql> SHOW MASTER STATUS;

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940211093-10.jpg" />

node2:這裡重新定義主從服務,所以上面實驗的內容需要刪除
mysql>slave stop;
mysql>reset slave;
mysql>change master to master_host='172.16.133.11',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000008',master_log_pos=107,master_ssl=1,master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',master_ssl_cert='/usr/local/mysql/ssl/mysql.crt',master_ssl_key='/usr/local/mysql/ssl/mysql.key';
mysql>show slave status\G

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940211919-11.jpg" />

mysql>slave start;
mysql>show slave status\G

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940214564-12.jpg" />

說明基於ssl的mysql主從複製已配置成功
使用ssrepl使用者登入mysql的主伺服器,驗證
mysql --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/mysql.crt --ssl-key=/usr/local/mysql/ssl/mysql.key -urepluser -h172.16.133.11 -p

登入mysql

然後

mysql>\s

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940214449-13.jpg" />

其中SSL:Cipher in use is DHE-RSA-AES256-SHA說明mysql已建立在ssl上了
3.主主複製
經常容易出錯,不建議用
(1).在兩台伺服器上各自建立一個具有複製許可權的使用者；
mysql>grant replication slave,replication client on *.* to repluser@'172.16.133.%' identified by 'redhat'
mysql>flush privileges;
(2).修改設定檔：
node1:
[mysqld]
server-id = 10
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1
node2:
[mysqld]
server-id = 20
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 2
(3).如果此時兩台伺服器均為建立立，且無其它寫入操作，各伺服器只需記錄當前自己二進位記錄檔及事件位置，以之作為另外的伺服器複製起始位置即可
node1:
mysql> SHOW MASTER STATUS;

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940212209-14.jpg" />

node2:
mysql> SHOW MASTER STATUS;

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1940214642-15.jpg" />

(4).各伺服器接下來指定對另一台伺服器為自己的主伺服器即可：
node1:
mysql>change master to master_host='172.16.133.12',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000004',master_log_pos=107;
mysql>slave start;
node2:
mysql>change master to master_host='172.16.133.11',master_user='repluser',master_password='redhat',master_log_file='mysql-bin.000004',master_log_pos=107;
mysql>slave start;

 

 

另:基於ssl的mysql主從複製中遇到一個問題,就是沒有在my.cnf中加入ssl時,

mysql>show variables like '%ssl%';

是disabled,加了之後就啟用了,顯示為yes,問題來了,在我添加完ssl-ca,ssl-cert,ssl-key路徑時,重啟服務後,發現他有自動變為disabled了,並且只有重新刪掉ssl-ca,ssl-cert,ssl-key才可以重新啟動,很是不解,求指教

這個問題以解決,是我在建立ssl目錄後,沒有注意其中crt等的許可權,將ssl目錄中的4個檔案屬主,屬組改為mysql即可

本文出自 “周鈺欽” 部落格，請務必保留此出處http://zhouyuqin.blog.51cto.com/5132926/956576