MySQL Replication

概述

Mysql內建的復制功能是構建大型，高性能應用程序的基礎。將Mysql的數據分布到多個系統(tǒng)上去，這種分布的機制，是通過將Mysql的某一臺主機的數據復制到其它主機(slaves)上，并重新執(zhí)行一遍來實現的。復制過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。主服務器將更新寫入二進制日志文件，并維護文件的一個索引以跟蹤日志循環(huán)。這些日志可以記錄發(fā)送到從服務器的更新。當一個從服務器連接主服務器時，它通知主服務器從服務器在日志中讀取的最后一次成功更新的位置。從服務器接收從那時起發(fā)生的任何更新，然后封鎖并等待主服務器通知新的更新。

請注意當你進行復制時，所有對復制中的表的更新必須在主服務器上進行。否則，你必須要小心，以避免用戶對主服務器上的表進行的更新與對從服務器上的表所進行的更新之間的沖突。

工作流程

master將改變記錄到二進制日志(binary log)中(這些記錄叫做二進制日志事件，binary log events)；

slave將master的binary log events拷貝到它的中繼日志(relay log)；

slave重做中繼日志中的事件，將改變反映它自己的數據。

詳細工作流程如下：

該過程的第一部分就是master記錄二進制日志。在每個事務更新數據完成之前，master在二日志記錄這些改變。MySQL將事務串行的寫入二進制日志，即使事務中的語句都是交叉執(zhí)行的。在事件寫入二進制日志完成后，master通知存儲引擎提交事務。

下一步就是slave將master的binary log拷貝到它自己的中繼日志。首先，slave開始一個工作線程——I/O線程。I/O線程在master上打開一個普通的連接，然后開始binlog dump process。Binlog dump process從master的二進制日志中讀取事件，如果已經跟上master，它會睡眠并等待master產生新的事件。I/O線程將這些事件寫入中繼日志。

SQL slave thread(SQL從線程)處理該過程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新slave的數據，使其與master中的數據一致。只要該線程與I/O線程保持一致，中繼日志通常會位于OS的緩存中，所以中繼日志的開銷很小。

此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。復制過程有一個很重要的限制——復制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。

搭建

環(huán)境說明

角色IP版本系統(tǒng)

Master

192.168.100.203

mysql-5.6.31

CentOS 6.7

Slave

192.168.100.202

mysql-5.6.31

CentOS 6.7

Master創(chuàng)建復制帳號

mysql> grant replication slave on *.* to 'mysync'@'192.168.%.%' identified by 'redhat';

Query OK, 0 rows affected (0.00 sec)

拷貝數據

一般做主從是需要將Master的數據備份好然后在將備份的數據導入到Slave中，這樣做就可以在設置主從同步時避免過多的數據進行同步。

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hellodb |

| mysql |

| performance_schema |

| stu |

| test |

| ultrax |

+--------------------+

7 rows in set (0.00 sec)

# mysqldump -uroot -hlocalhost -p --databases hellodb stu ultrax --lock-all-tables --flush-logs --master-data=2 >/opt/backup/Master_`date +'%F-%H-%M'`.sql

Enter password:

# ll /opt/backup/Master_2017-03-07-22-12.sql

-rw-r--r-- 1 root root 2332999 3月 7 22:12 /opt/backup/Master_2017-03-07-22-12.sql

# scp /opt/backup/Master_2017-03-07-22-12.sql root@192.168.100.202:/data

Slave 數據導入

# mysql -uroot -hlocalhost -p < /data/Master_2017-03-07-22-12.sql

Enter password:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hellodb |

| mysql |

| performance_schema |

| stu |

| test |

| ultrax |

+--------------------+

7 rows in set (0.00 sec)

配置Master

# vim /etc/my.cnf

......

log-bin=mysql-bin

binlog_format=mixed

server-id = 203

......

配置Slave

# vim /etc/my.cnf

log_bin = slave-bin

relay_log = slave-relay-bin

read_only = 1

server_id = 202

注意：read_only僅能限制那不具有SUPER權限的用戶來執(zhí)行寫操作，如果是root權限還是可以執(zhí)行寫入刪除等操作的；主從的配置修改后都需要重啟mysqld。

主從同步配置

查看Master狀態(tài)：

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000029 | 352 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> show processlist;

+----+--------+-----------------------+---------+-------------+------+-----------------------------------------------------------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+--------+-----------------------+---------+-------------+------+-----------------------------------------------------------------------+------------------+

| 1 | root | localhost | hellodb | Query | 0 | init | show processlist |

| 4 | mysync | 192.168.100.202:38125 | NULL | Binlog Dump | 1062 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |

+----+--------+-----------------------+---------+-------------+------+-----------------------------------------------------------------------+------------------+

2 rows in set (0.00 sec)

在Slave做以下操作：

mysql> change master to master_host='192.168.100.203',master_user='mysync',master_password='redhat',master_log_file='mysql-bin.000029',master_log_pos=352;

Query OK, 0 rows affected, 2 warnings (0.54 sec)

mysql> start slave;

Query OK, 0 rows affected (0.56 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.100.203

Master_User: mysync

Master_Port: 3306

Connect_Retry: 60 #重試時間間隔

Master_Log_File: mysql-bin.000029 #I/O線程讀取的二進制日志文件

Read_Master_Log_Pos: 352 #I/O線程讀取的二進制日志文件事件位置

Relay_Log_File: slave-relay-bin.000002 #SQL線程正在讀取的中繼日志文件

Relay_Log_Pos: 283 #SQL線程讀取和執(zhí)行的中繼日志文件事件位置

Relay_Master_Log_File: mysql-bin.000029

Slave_IO_Running: Yes #Slave服務器IO線程狀態(tài)

Slave_SQL_Running: Yes #Slave服務器SQL線程狀態(tài)

Replicate_Do_DB: #下面Replicate開頭的表示用來指明哪些庫或者表在復制時不需要同步

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0 #SQL線程讀取日志參數的錯誤數量

Last_Error: #SQL線程讀取日志參數的錯誤消息

Skip_Counter: 0 #最近被用于SQL_SLAVE_SKIP_COUNTER的值

Exec_Master_Log_Pos: 352

Relay_Log_Space: 456 #所有原有中繼日志的總大小

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No #是否允許對Master服務器進行SSL連接

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0 #落后于Master服務器的時間

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 203

Master_UUID: 5d483197-3702-11e6-b990-000c292ad2da

Master_Info_File: /data/mydata/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

mysql> show processlist;

+----+-------------+-----------+---------+---------+--------+-----------------------------------------------------------------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------------+-----------+---------+---------+--------+-----------------------------------------------------------------------------+------------------+

| 1 | root | localhost | hellodb | Query | 0 | init | show processlist |

| 7 | system user | | NULL | Connect | 1099 | Waiting for master to send event | NULL |

| 8 | system user | | NULL | Connect | -11892 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |

+----+-------------+-----------+---------+---------+--------+-----------------------------------------------------------------------------+------------------+

3 rows in set (0.00 sec)

通過上面可以看到主從已經同步，下面做數據方面的測試。

測試主從同

Master 做插入數據操作：

mysql> insert into hellodb.newdb values('bols'),('longls'),('cangls');

Query OK, 3 rows affected (0.53 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from newdb;

+--------+

| Name |

+--------+

| bols |

| longls |

| cangls |

+--------+

3 rows in set (0.00 sec)

Slave進行查看：

mysql> select * from hellodb.newdb;

+--------+

| Name |

+--------+

| bols |

| longls |

| cangls |

+--------+

3 rows in set (0.00 sec)

配置參數

log-slave-updates：用來配置從庫上的更新操作是否寫入二進制日志，默認是不打開的。但是如果這個從庫同時也要作為其他服務器的主庫，搭建一個鏈式的復制，那么就需要打開這個選項，這樣它的從庫將獲得它的二進制日志進行同步操作。這個啟動參數需要和--logs-bin參數一起使用。

master-connect-retry：用來設置在和主庫丟失時重試的時間間隔，默認是60s。

read-only：用來設置從庫只能接受超級用戶的更新操作，從而限制應用程序錯誤的對從庫的更新操作，

replicate_do_db：數據庫白名單

replicate_ignore_db：數據庫黑名單

replicate_do_table= db_name.table_name：表的白名單

replicate_ignore_table=db_name.table_name：表的黑名單

replicate_wild_do_table：基于通配符來指定表的白名單

replicate_wild_ignore_table：基于通配符來指定表的黑名單

slave-skip-errors：用來定義復制過程中從庫可以自動跳過的錯誤號，這樣當復制過程中遇到定義中的錯誤號時，便可以自動跳過，直接執(zhí)行后面的SQL語句，以此來減少人工干預。此參數謹慎使用！

總結

以上是生活随笔為你收集整理的mysql md_MySQL主从.md的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。