詳解mysql學習之主從複製

該文使用mysql5.5 centos6.5 64位元

#一、主從複製的作用

1、如果主伺服器出現問題，可以快速切換到從伺服器。

2、對與即時性要求不高或更新不頻繁的應用程式可以在從伺服器上執行查詢操作，降低主伺服器的存取壓力。將資料的讀寫進行分離從而達到負載的效果。

3、可以從伺服器進行資料備份操作，以避免備份期間對主伺服器的影響。

主從複製原理：

#原理解析：master伺服器開啟binlog日誌，slave伺服器透過master伺服器授予的使用者將master伺服器產生的binlog日誌會讀到本機並轉為relaylog日誌，然後執行relaylog日誌。

二、搭建主從複製環境

master：192.168.6.224

slave：192.168.6.222

1.在主伺服器中為從伺服器設定授權用戶

在主伺服器中為從伺服器192.168.6.222建立一個用戶名為user2的用戶，密碼是123

mysql> ; grant all on *.* to user2@192.168.6.222 identified by "123";

參數解釋：

grant:mysql授權關鍵字

#*.* :所有庫所有表

查看使用者授權是否成功：

mysql> show grants for user2@192.168.6.222;

測試在slave伺服器上使用user2能否登陸master伺服器上的mysql

[root@localhost tmp] # mysql -uuser2 -p123 test -h192.168.6.224;

2、開啟主伺服器的bin-log日誌並開啟設定server-id的值。

　 修改主伺服器的my.cnf設定檔：

[mysqld]
#开启mysql的bin-log日志
log-bin=mysql-bin
#主服务器该值设置为1
server-id    = 1

3、重設bin-log日誌：mysql> reset master; #​​##  

#查看最新的bin-log日誌狀態看是否在起始位置： mysql> show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+
| 
File
             | 
Position
 | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000
001
 |      107 |              |                  |
+------------------+----------+--------------+------------------+

4、備份主資料庫資料##　　a、備份資料

　　b、更新bin-log日誌

　　在這裡我們使用mysqldump方式備份資料並使用-l -F 參數直接在備份資料的時候設定讀鎖並更新bin-log日誌

 　　mysqldump -uroot -p111111 test -l -F > '/tmp/mysql_back/test.sql';

#5、將主伺服器備份的資料傳送到slave伺服器

　　[root@localhost tmp]# scp mysql_back/test.sql 192.168.6.222:/tmp/mysql_back/

6、重置slave伺服器上的bin -log日誌並在slave伺服器中使用備份的資料

　　mysql> rester master;

　　[root@localhost tmp]# mysql -uroot -p111111 test -v -f< /tmp/mysql_back/test.sql;

##7、設定slave伺服器中my.cnf參數

　　a、#配置從伺服器server-id =2 （如果有多台從伺服器則有一個唯一的server-id）　　　　server-id = 2

　　b、#開啟bin-log日誌
　　　　log-bin=my-bin

##　　c、#配置需要同步的主機、使用者名稱、密碼、連接埠號碼

#配置需要同步的主机
 master-host     =   192.168.6.224
# The username the slave will use for authentication when connecting
# to the master - required
 master-user     =   user2
#
# The password the slave will authenticate with when connecting to
# the master - required
 master-password =   123
#
# The port the master is listening on.
# optional - defaults to 3306
 master-port     =  3306

　　d、重啟mysql讓設定檔生效

　　[ root@localhost tmp]# service mysqld restart

　　如果改方式無法重啟mysql伺服器可以使用下面的方式

mysql> change master to master_host="192.168.6.224",
master_user="user2",
master_password="123",
master_port=3306,
master_log_file="mysql-bin.000002",master_log_pos=107;
mysql> slave start;

#8、查看slave狀態

#

mysql . row  master .bin.relaybin.bin.

Master_Log_File：代表主機上用於主備同步的日誌檔案名，Read_Master_Log_Pos：代表上一次成功同步到的記錄檔中的位置。

如果這兩項與先前在主伺服器上看到的File及Position的值不相符，則無法正確進行同步。

三、測試

1、在master伺服器中新增資料並查看bin-log日誌狀態

mysql> insert into t1 values(13);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(14);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(15);
Query OK, 1 row affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      656 |              |                  |
+------------------+----------+--------------+------------------+

2、查看slave同步狀態

mysql> show slave status
\G;*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.224
                  Master_User: user2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 656
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 802
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在這裡可以看到主伺服器的Postion與從伺服器的Read_Master_Log_Pos值相等且Slave_IO_Running，Slave_SQL_Running值都是Yes 。這樣mysql的主從配置成功。

 四、主從複製常用指令

1、start slave #啟動複製執行緒2、stop slave #停止複製執行緒

3、show slave status #查看從資料庫狀態

4、show master logs;#查主資料庫有哪些bin-log日誌

5、change master to #動態改變到主伺服器的設定

6、show processlist;#查看從資料庫的執行過程

以上是詳解mysql學習之主從複製的詳細內容。更多資訊請關注PHP中文網其他相關文章！