MySQL主從複製

標籤：mysql 主從複製

MySQL的叢集，MySQL的主從架構，MySQL的負載平衡

Mysql主從同步
原理：
Replication 線程
Mysql的 Replication 是一個非同步複製過程，從一個 Mysql instace(我們稱之為 Master)複製到另一個 Mysql instance(我們稱之 Slave)。在 Master 與 Slave 之間的實現整個複製過程主要由三個線程來完成，其中兩個線程(Sql線程和IO線程)在 Slave 端，另外一個線程(IO線程)在 Master 端。
　　要實現 MySQL 的 Replication ，首先必須開啟 Master 端的Binary Log(mysql-bin.xxxxxx)功能，否則無法實現。因為整個複製過程實際上就是Slave從Master端擷取該日誌然後再在自己身上完全 順序的執行日誌中所記錄的各種操作。開啟 MySQL 的 Binary Log 可以通過在啟動 MySQL Server 的過程中使用 “—log-bin” 參數選項，或者在 my.cnf 設定檔中的 mysqld 參數組([mysqld]標識後的參數部分)增加 “log-bin” 參數項。
MySQL 複製的基本過程如下：
1. Slave 上面的IO線程串連上 Master，並請求從指定記錄檔的指定位置(或者從最開始的日誌)之後的日誌內容;
2. Master 接收到來自 Slave 的 IO 線程的請求後，通過負責複製的 IO 線程根據請求資訊讀取指定日誌指定位置之後的日誌資訊，返回給 Slave 端的 IO 線程。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊在 Master 端的 Binary Log 檔案的名稱以及在 Binary Log 中的位置;
3. Slave 的 IO 線程接收到資訊後，將接收到的日誌內容依次寫入到 Slave 端的Relay Log檔案(mysql-relay-bin.xxxxxx)的最末端，並將讀取到的Master端的bin-log的檔案名稱和位置記錄到master- info檔案中，以便在下一次讀取的時候能夠清楚的高速Master“我需要從某個bin-log的哪個位置開始往後的日誌內容，請發給我”
4. Slave 的 SQL 線程檢測到 Relay Log 中新增加了內容後，會馬上解析該 Log 檔案中的內容成為在 Master 端真實執行時候的那些可執行檔 Query 語句，並在自身執行這些 Query。這樣，實際上就是在 Master 端和 Slave 端執行了同樣的 Query，所以兩端的資料是完全一樣的
實現步驟：
環境：
Master伺服器：192.168.1.215
Slave伺服器 ：192.168.1.216
兩台伺服器中安裝了相同版本的mysql。
1.修改master伺服器mysql的設定檔my.cnf ，將server-id設定為1，並開啟binlog ，並重設binlog 。
vim /etc/my.cnf
[mysqld]
........
........
server-id= 1
log-bin=mysql-bin
重啟服務 ： service mysql restart
重設binlog ：mysql>reset master;
2.授權一個使用者可以從從伺服器登入主伺服器進行備份，將主伺服器上的資料庫全部匯出拷貝到從伺服器上。
授權：grant replication slave on *.* to ‘wcy‘@‘192.168.1.216‘ identified by ‘123456‘;
(完成後可以在從機上做連結測試 在從伺服器終端上輸入命令: mysql -h192.168.1.215 -uwcy -p，如果不能連通請檢查主機防火牆或主機 MySQL 連接埠)
查看建立的使用者：
> select user.host from mysql.user;
mysql鎖表唯讀(其他賬戶登入mysql後無法進行寫表操作，防止備份資料庫後，主mysql表更新，導致和從資料庫內容不一致)
> flush tables with read lock;
查看鎖表倒計時時間：
> show variables like ‘%timeout%‘;
------------------------
....
wait_timeout | 28800
------------------------
將master的資料庫表全部備份匯出，並傳送到slave伺服器上。
#mysqldump -u root -p123456 --opt --flush-logs --all-database > /root/allbak.sql
#scp allbak.sql [email protected]:/root
查看mysql位移量(資料庫如果有寫操作，位移值會遞增)
# mysql -u root -p123456 -e "show master status"
3、在從伺服器上
恢複server的資料庫到slave
#mysql -u root -p123456 < /root/allbak.sql
vim /etc/my.cnf
[mysqld]
....
server-id= 2
log-bin=mysql-bin
#service mysql restart
4、配置slave
mysql>slave stop;
mysql> reset slave;
mysql>change master to master_host=‘192.168.1.215‘,master_user=‘wcy‘,master_password=‘123456‘,master_port=3306,master_log_file=‘mysql-bin.000003‘,master_log_pos=106;
mysql>slave start;
mysql>show slave status\G;
如果這兩行為yes則成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
最後回到主mysql資料庫解鎖（mysql master）
# mysql -u root -p123456
> unlock tables;
測試主從是否同步：
(server)
# mysql -u root -p123456 -e "create database test02;"
# mysql -u root -p123456 -e "show databases like ‘test02‘;"
------------
+-------------------+
| Database (test02) |
+-------------------+
| test02 |
+-------------------+
------------
(cilent)
# mysql -u root -p123456 -e "show databases like ‘test02‘;"
-------------
+-------------------+
| Database (test02) |
+-------------------+
| test02 |
+-------------------+
-------------
測試成功。。

一、環境
master：192.168.124.51
MYSQL版本：5.1.48-community-log
slave: 192.168.124.52
MYSQL版本：5.1.48-community-log
 
二、主從資料庫
將主機上現有的Database Backup，然後在從機上建立同名資料庫並還原。
（這次做的是51上的兩個資料庫database1和database2）
 
三、master和 slave上的相關配置
在/etc目錄下可能無my.cnf檔案，從/user/share/mysql目錄中拷貝my-medium.cnf 到/etc並修改成my.cnf（master和slave上一樣）。
如 # cp /user/share/mysql/my-medium.cnf  /etc/my.cnf

1.修改master上的設定檔my.cnf。
在[mysqld]下添加如下欄位：
server-id=1
log-bin=log
binlog-do-db=database1          //需要同步的資料庫
binlog-do-db=database2
binlog-ignore-db=mysql                     //被忽略的資料庫
 
在master上位slave添加一個同步帳號
grant replication slave on *.* to ‘AffairLog‘@‘192.168.124.52‘ identified by ‘password‘;
                                                                          //在slave上登陸成功
重啟master的mysql服務：
service mysql restart;
 
用show master status命令查看日誌情況
mysql> show master status\G;
 
*************************** 1. row ***************************
File: log.000027
Position: 3151
Binlog_Do_DB: database1,database2
Binlog_Ignore_DB:
1 row in set (0.00 sec)
 
2.修改slave上的設定檔my.cnf。
在[mysqld]下添加如下欄位：
server-id=2
master-host=192.168.124.51
master-user= AffairLog
master-password= password
master-port=3306
master-connect-retry=60
replicate-do-db=database1     //同步的資料庫
replicate-do-db=database2
replicate-ignore-db=mysql  //被忽略的資料庫
 
重啟slave的mysql服務：
service mysql restart;
 
在進入slave機中的mysql。
mysql>start slave;
mysql>show slave status\G;
 
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.124.51
                  Master_User: AffairLog
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log.000027
          Read_Master_Log_Pos: 3151
               Relay_Log_File: localhost-relay-bin.000379
                Relay_Log_Pos: 245
        Relay_Master_Log_File: log.000027
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: database1,database2
          Replicate_Ignore_DB: mysql
           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: 3151
              Relay_Log_Space: 543
              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:
1 row in set (0.00 sec)
 
如果Slave_IO_Running、Slave_SQL_Running狀態為Yes則表明設定成功。
 
四、出現問題
Slave_IO_Running: No或者Slave_SQL_Running: No
1.停掉slave服務
mysql> slave stop;
Query OK, 0 rows affected (2.01 sec)
 
2.解決辦法
解決辦法1
a.在master上查看。
mysql> show master status\G;
*************************** 1. row ***************************
File: log.000027
Position: 3151
Binlog_Do_DB: database1,database2
Binlog_Ignore_DB:
1 row in set (0.00 sec)
b.到slave上手動同步。
mysql>change master to
>master_host=‘192.168.124.51‘,
>master_user=‘AffairLog‘,
>master_password=‘password‘,
>master_log_file=‘log.000027‘,
>master_log_pos=3151;
 
Query OK, 0 rows affected (0.00 sec)
 
解決方案2
mysql> slave stop;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
 
3．啟動slave服務
mysql> slave start;
 
4．再次查看Slave_IO_Running、Slave_SQL_Running狀態，為Yes則表明設定成功。
 
PS：
Slave_IO_Running：串連到主庫，並讀取主庫的日誌到本地，產生本地記錄檔
Slave_SQL_Running:讀取本地記錄檔，並執行日誌裡的SQL命令。
 
2012.07.06補充：今天遇到從機搬遷，伺服器IP改了，雖然之前停掉了主從，但是後來恢複利用上面方法還是沒有解決Slave_IO_Running: NO，確認了好幾次，後來發現是原來的機子上做了防火牆限制，哦買噶！記上一筆，這是經驗！
主從同步從5.1版本開始，設定檔my.cnf檔案中不在支援master 命令，只有登入資料庫後，通過change master to 命令進行相應的配置
 可以在my.cnf設定檔中定義用於主從同步的資料庫和日誌資訊。
主要資料庫my.cnf設定檔
 [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
open_files_limit = 10240
max_connections = 5000
max_connect_errors = 6000
table_cache = 614
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 300
#thread_concurrency = 8
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 246M
max_heap_table_size = 246M
max_binlog_cache_size = 8M
max_binlog_size = 1G
key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
interactive_timeout = 120
wait_timeout = 120
skip-name-resolve
#slave-skip-errors = 1032,1062,126,1114,1146,1048,139
#log_config
expire_logs_day=30
long_query_time = 3
log-slave-updates
back_log = 600
log-error = /var/lib/mysql/log/error_log/mysql_error.log
log-bin = /var/lib/mysql/log/bin_log/bin.log
binlog_cache_size = 4M
log-slow-queries = /var/lib/mysql/log/slow_query_log/slow.log
long_query_time = 5
#general_log = 1
#general_log_file = /var/lib/mysql/log/query_log/query.log
character_set_server=utf8
server-id=1
binlog-do-db=test01
binlog-ignore-db=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pid
[client]
#default-character-set=utf8
[mysql]
default-character-set=utf8

從資料庫伺服器的my.cnf設定檔
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# The MySQL server
[mysqld]
#datadir=/usr/local/mysql/data
port = 3306
socket = /tmp/mysql.sock
table_open_cache = 64
net_buffer_length = 8K
server-id=2
open_files_limit = 10240
max_connections = 5000
max_connect_errors = 6000
table_cache = 614
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 300
#thread_concurrency = 8
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 246M
max_heap_table_size = 246M
max_binlog_cache_size = 8M
max_binlog_size = 1G
key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
interactive_timeout = 120
wait_timeout = 120
skip-name-resolve
#slave-skip-errors = 1032,1062,126,1114,1146,1048,139
#log_config
expire_logs_day=30
long_query_time = 3
log-slave-updates
back_log = 600
log-error = /usr/local/mysql/data/log/mysql_error.log
log-bin = /usr/local/mysql/data/log/bin_log/bin.log
binlog_cache_size = 4M
binlog_format = MIXED
relay-log-index = /usr/local/mysql/data/log/relay_log/relaylog.index
relay-log-info-file = /usr/local/mysql/data/log/relay_log/relay_log_info
relay-log = /usr/local/mysql/data/log/relay_log/relay.log
log-slow-queries = /usr/local/mysql/data/log/slow_query_log/slow.log
long_query_time = 5
general_log = 1
general_log_file = /usr/local/mysql/data/log/query_log/query.log
#master-host=10.69.160.4
#master-user=slave
#master-password=root
#master-port=3306
#master-connect-retry=5
replicate-do-db=test01
replicate-ignore-db=mysql
#log-bin
#expire_logs_day=30
#general_log = 1
#general_log_file = /usr/local/mysql/data/mysql_query.log
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

本文出自 “咚咚的部落格” 部落格，轉載請與作者聯絡！

MySQL主從複製