MySQL 復(fù)制詳解及簡(jiǎn)單實(shí)例
主從復(fù)制技術(shù)在MySQL中被廣泛使用，主要用于同步一臺(tái)服務(wù)器上的數(shù)據(jù)至多臺(tái)從服務(wù)器，可以用于實(shí)現(xiàn)負(fù)載均衡，高可用和故障切換，以及提供備份等等。MySQL支持多種不同的復(fù)制技術(shù)，諸如單向，半同步異步復(fù)制等以及不同級(jí)別的復(fù)制，諸如數(shù)據(jù)庫(kù)級(jí)別，表級(jí)，跨庫(kù)同步等等。本文簡(jiǎn)要描述了一個(gè)基本的主從復(fù)制并給出示例。
1、復(fù)制的基本原理(步驟)
a、在主庫(kù)上把數(shù)據(jù)更改記錄的二進(jìn)制日志(binary log)
b、從庫(kù)上的I/O線程連接到主庫(kù)并請(qǐng)求發(fā)送其二進(jìn)制日志文件(主庫(kù)上的binlog dump線程將二進(jìn)制日志內(nèi)容發(fā)送到從庫(kù))
c、從庫(kù)上的I/O線程讀取主服務(wù)發(fā)送的二進(jìn)制內(nèi)容并將其拷貝到中繼日志
d、從庫(kù)上的SQL線程讀取中繼日志并執(zhí)行日志中包含的更新
2、為配置文件添加復(fù)制項(xiàng)
# 本文的演示基于同一服務(wù)器上的多實(shí)例環(huán)境，其中3406端口用作主庫(kù)，而3506用作從庫(kù)。
# 關(guān)于多實(shí)例的部署可參考：
# MySQL多實(shí)例配置(一) http://blog.csdn.net/leshami/article/details/40339167
# MySQL多實(shí)例配置(二) http://blog.csdn.net/leshami/article/details/40339295
# 3406與3506為都為新裝且含缺省庫(kù)等，所以本文演示中未涉及先遷移主庫(kù)數(shù)據(jù)到備庫(kù)步驟
a、主庫(kù)上的配置文件
# more my3406.cnf
[mysqld]
socket = /tmp/mysql3406.sock
port = 3406
pid-file = /data/inst3406/data3406/my3406.pid
user = mysql
log-error=/data/inst3406/data3406/inst3406.err
datadir=/data/inst3406/data3406
basedir=/app/soft/mysql5
#### for master items ####
server-id=3406
log_bin=/data/inst3406/log/bin/inst3406bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
b、從庫(kù)上的配置文件
# more my3506.cnf
[mysqld]
socket = /tmp/mysql3506.sock # Author : Leshami
port = 3506 # Blog : a target="_blank" rel="external nofollow" >http://blog.csdn.net/leshami
pid-file/a> = /data/inst3506/data3506/my3506.pid
user = mysql
log-error=/data/inst3506/data3506/inst3506.err
datadir=/data/inst3506/data3506
basedir=/app/soft/mysql5
#### for slave items ####
server-id=3506
relay_log=/data/inst3506/log/relay/relay-bin
read_only=1
3、創(chuàng)建復(fù)制賬號(hào)
#啟動(dòng)端口為3406的實(shí)例并添加賬戶
[mysql@app ~]$ mysqld_safe --defaults-file=/data/inst3406/data3406/my3406.cnf
[mysql@app ~]$ mysql -P3406 #登陸到3406
master@localhost[(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3406 |
+---------------+-------+
#創(chuàng)建用于復(fù)制的賬戶
master@localhost[(none)]> grant replication slave,replication client on *.*
-> to repl@'192.168.1.177' identified by 'repl';
#初始化主庫(kù)日志文件，生成環(huán)境慎用reset
master@localhost[(none)]> reset master;
Query OK, 0 rows affected (0.01 sec)
#查看主庫(kù)的狀態(tài)，日志初始化至000001，
master@localhost[(none)]> show master status,Position為120
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| inst3406bin.000001 | 120 | | | |
+--------------------+----------+--------------+------------------+-------------------+
4、配置主從同步
#啟動(dòng)端口為3506的實(shí)例
[mysql@app ~]$ mysqld_safe --defaults-file=/data/inst3506/data3506/my3506.cnf
[mysql@app ~]$ msyql -P3506
slave@localhost[(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3506 |
+---------------+-------+
1 row in set (0.00 sec)
#為從庫(kù)添加指向主庫(kù)的相關(guān)配置信息，該命令會(huì)生成及修改備庫(kù)上的master.info及relay-log.info文件
slave@localhost[(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.177',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl',
-> MASTER_PORT=3406,
-> MASTER_LOG_FILE='inst3406bin.000001',
-> MASTER_LOG_POS=0;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
#出現(xiàn)了2個(gè)warnings，查看一下
slave@localhost[(none)]> show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended.
Please see the MySQL Manual for more about this issue and possible alternatives.
2 rows in set (0.00 sec)
#此時(shí)查看從庫(kù)的狀態(tài)信息
slave@localhost[(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.177
Master_User: repl
Master_Port: 3406
Connect_Retry: 60
Master_Log_File: inst3406bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: inst3406bin.000001
Slave_IO_Running: No #IO線程沒(méi)有運(yùn)行
Slave_SQL_Running: No #SQL線程沒(méi)有運(yùn)行
......................
Master_Info_File: /data/inst3506/data3506/master.info
slave@localhost[(none)]> start slave; #啟動(dòng)slave
Query OK, 0 rows affected (0.01 sec)
#含義如下
START SLAVE with no thread_type options starts both of the slave threads. The I/O thread reads
events from the master server and stores them in the relay log. The SQL thread reads events from the
relay log and executes them.
#再次查看slave的狀態(tài)
robin@localhost[(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.177
Master_User: repl
Master_Port: 3406
Connect_Retry: 60
Master_Log_File: inst3406bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 285
Relay_Master_Log_File: inst3406bin.000001
Slave_IO_Running: Yes #IO線程處于運(yùn)行狀態(tài)
Slave_SQL_Running: Yes #SQL線程處于運(yùn)行狀態(tài)
..............
Exec_Master_Log_Pos: 120
Relay_Log_Space: 452
............
Master_Server_Id: 3406
Master_UUID: 32f53a0a-63ef-11e4-93d9-8c89a5d108ae
Master_Info_File: /data/inst3506/data3506/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 #重要的提示信息
#可以看到從庫(kù)上的2個(gè)線程，一個(gè)是用于I/O線程，用于連接到主庫(kù)請(qǐng)求主庫(kù)發(fā)送binlog，一個(gè)是用于執(zhí)行SQL的SQL線程。
slave@localhost[(none)]> show processlist\G
*************************** 1. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 510993
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 333943
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
5、驗(yàn)證同步情況
#下面在主庫(kù)上執(zhí)行一些操作以檢查從庫(kù)的同步情況
master@localhost[(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3406 |
+---------------+-------+
1 row in set (0.00 sec)
#主庫(kù)上Binlog Dump線程用于發(fā)送binlog日志文件到從庫(kù)，如下查詢
master@localhost[(none)]> show processlist\G
*************************** 1. row ***************************
Id: 12
User: repl
Host: 192.168.1.177:57440
db: NULL
Command: Binlog Dump
Time: 511342
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
#主庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)及表
master@localhost[(none)]> create database tempdb;
Query OK, 1 row affected (0.01 sec)
master@localhost[(none)]> use tempdb
Database changed
master@localhost[tempdb]> create table tb_engines as select * from information_schema.engines;
Query OK, 9 rows affected (0.02 sec)
Records: 9 Duplicates: 0 Warnings: 0
#下面是在從庫(kù)上檢查的結(jié)果
slave@localhost[(none)]> select count(*) from tempdb.tb_engines;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
感謝閱讀，希望能幫助到大家，謝謝大家對(duì)本站的支持！
您可能感興趣的文章:- MySQL主從復(fù)制的原理及配置方法（比較詳細(xì)）
- mysql把一個(gè)表某個(gè)字段的內(nèi)容復(fù)制到另一張表的某個(gè)字段的SQL語(yǔ)句寫(xiě)法
- MySQL數(shù)據(jù)表字段內(nèi)容的批量修改、清空、復(fù)制等更新命令
- mysql中復(fù)制表結(jié)構(gòu)的方法小結(jié)
- MySQL復(fù)制表結(jié)構(gòu)和內(nèi)容到另一張表中的SQL語(yǔ)句
- MySQL中表復(fù)制:create table like 與 create table as select
- Windows下實(shí)現(xiàn)MySQL自動(dòng)備份的批處理(復(fù)制目錄或mysqldump備份)
- Mysql主從復(fù)制(master-slave)實(shí)際操作案例
