在這一章節(jié)里， 我們來(lái)了解下如何在 Mysql 中進(jìn)行用戶授權(quán)及主從復(fù)制
 
這里先來(lái)了解下 Mysql 主從復(fù)制的優(yōu)點(diǎn):
 
1、 如果主服務(wù)器出現(xiàn)問(wèn)題， 可以快速切換到從服務(wù)器提供的服務(wù)
2、 可以在從服務(wù)器上執(zhí)行查詢(xún)操作， 降低主服務(wù)器的訪問(wèn)壓力
3、 可以在從服務(wù)器上執(zhí)行備份， 以避免備份期間影響主服務(wù)器的服務(wù)
注意一般只有更新不頻繁的數(shù)據(jù)或者對(duì)實(shí)時(shí)性要求不高的數(shù)據(jù)可以通過(guò)從服務(wù)器查詢(xún)， 實(shí)時(shí)性要求高的數(shù)據(jù)仍然需要從主數(shù)據(jù)庫(kù)獲得
 
在這里我們首先得完成用戶授權(quán)， 目的是為了給從服務(wù)器有足夠的權(quán)限來(lái)遠(yuǎn)程登入到主服務(wù)器的 Mysql
 
在這里我假設(shè)
主服務(wù)器的 IP 為: 192.168.10.1
從服務(wù)器的 IP 為: 192.168.10.2
 
Mysql grant 用戶授權(quán)
 
查看 Mysql 的用戶表
代碼如下:
msyql> mysql -uroot -p123123;
msyql> select user, host, password from mysql.user;

結(jié)果如下:
代碼如下:+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root             | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+------------------+-----------+-------------------------------------------+
從如上表中看以看出 root 用戶只能從本機(jī)登入 Mysql， 也就是來(lái)自 localhost 或者 127.0.0.1
 
現(xiàn)在來(lái)通過(guò) grant 命令來(lái)添加授權(quán)用戶
代碼如下:
msyql> ？ grant   //查看 grant 的詳細(xì)用法
 
msyql> grant all on *.* to user1@192.168.10.2 identified by "123456"; // *.* = 所有的數(shù)據(jù)庫(kù).所有的表
//或者
msyql> grant replication slave on *.* to 'user2'@'192.168.10.%' identified by "123456"; // %代表通配符
通過(guò)了 grant 命令給予了來(lái)自 192.168.10.2 的用戶 user1 權(quán)限， 允許其遠(yuǎn)程登錄, 如下:
代碼如下:
+------------------+--------------+-------------------------------------------+
| user             | host         | password                                  |
+------------------+--------------+-------------------------------------------+
| root             | localhost    | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| root             | 127.0.0.1    | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
| user1            | 192.168.10.2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| user2            | 192.168.10.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------------+--------------+-------------------------------------------+
此時(shí)就可以在 192.168.10.2 的機(jī)器上訪問(wèn) 10.1 的 Mysql 了， 如下:
代碼如下:
msyql> mysql -uuser1 -p123456 -h192.168.10.1;

Mysql bin-log 日志
 
開(kāi)啟 bin-log 二進(jìn)制日志， 它保存了所有增刪改的操作， 以便于數(shù)據(jù)恢復(fù)或同步

修改主服務(wù)器 mysql 配置文件:
代碼如下:shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
 
/********** my.cnf **********/
[mysqld]
 
#開(kāi)啟慢查詢(xún)?nèi)罩荆?記錄查詢(xún)過(guò)長(zhǎng)的 sql 語(yǔ)句，以便于優(yōu)化
log_slow_queries   = /var/log/mysql/mysql-slow.log
 
#開(kāi)啟 bin-log 日志
log-bin            = /var/log/msyql/mysql-bin.log
添加完成后重啟 Mysql 服務(wù)
代碼如下:
shawn@Shawn:~$ sudo /etc/init.d/mysql restart
現(xiàn)在你可以通過(guò)如下命令來(lái)查看 bin-log 日志是否成功開(kāi)啟
代碼如下:
mysql> show variables like "%log_%";
 
| log_bin                 | ON        |
| log_slow_queries        | ON        |
如果顯示為 ON， 那么就可以在 /var/log/mysql/ 文件夾看到 mysql-bin.000001 二進(jìn)制文件
 
關(guān)于 bin-log 日志的相關(guān)操作:
代碼如下:
mysql> flush logs；
此時(shí)就會(huì)多一個(gè)最新的 bin-log 日志
代碼如下:
mysql> show master status;
查看最后一個(gè) bin-log 日志， 如下:
代碼如下:
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+
 
mysql> show master logs;
查看所有 bin-log 日志， 如下:
代碼如下:
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      4340 |
| mysql-bin.000002 |       107 |
+------------------+-----------+
 
mysql> reset master;
清空所有 bin-log 日志
代碼如下:
shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000001 | more
查看 bin-log 日志內(nèi)容
代碼如下:
#如果有字符集問(wèn)題的話可以執(zhí)行:
shawn@Shawn:~$ mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000001

shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000002 | mysql -uroot -p123123 test;
恢復(fù) mysql-bin.000002 中所有的操作到 test 數(shù)據(jù)庫(kù)中

shawn@Shawn:~$ mysqlbinlog /var/log/mysql/mysql-bin.000002 --start-position="193" --stop-position="398" | mysql -uroot -p123123 test;
恢復(fù) mysql-bin.000002 中指定的操作(position)到 test 數(shù)據(jù)庫(kù)中
 
Mysql 主從復(fù)制 - 數(shù)據(jù)同步
 
到這一步的時(shí)候首先確保 Mysql 用戶授權(quán)已經(jīng)完成以及 Mysql bin-log 日志已經(jīng)成功開(kāi)啟
并確保每臺(tái)服務(wù)器的 server-id 是唯一的
 
再次修改主服務(wù)器(192.168.10.1)的 mysql 配置文件:
代碼如下:
shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
 
/********** my.cnf **********/
#取消 server-id 注釋符號(hào)
server-id   = 1
/****************************/
 
#重啟 Mysql 服務(wù)
shawn@Shawn:~$ sudo /etc/init.d/mysql restart
到這里， 主服務(wù)器的配置已經(jīng)完成， 很簡(jiǎn)單
 
這次我們主要做的是讓從服務(wù)器同步主服務(wù)器的數(shù)據(jù)， 同步的是將來(lái)所有對(duì)主服務(wù)做的增刪改操作， 但是現(xiàn)有主服務(wù)器中的大量數(shù)據(jù)得先手動(dòng)同步到從服務(wù)器， 操作如下:
代碼如下:
#清空一下主服務(wù)器的 bin-log 日志， （可選: 保險(xiǎn)操作， 防止主從 bin-log 日志混亂）
mysql> reset master;
 
#然后備份導(dǎo)出主服務(wù)器中現(xiàn)有的 test 數(shù)據(jù)庫(kù)
shawn@Shawn:~$ mysqldump -uroot -p123123 test -l -F > /tmp/test.sql;
 
-F = flush logs, 生成新的日志文件， 包括 bin-log 日志
-l = lock 數(shù)據(jù)庫(kù)， 防止在導(dǎo)出的時(shí)候被寫(xiě)入數(shù)據(jù)， 完成后自動(dòng)解鎖
 
#完成后把文件傳輸給從服務(wù)器
shawn@Shawn:~$ scp /tmp/test.sql 192.168.10.2:/tmp/
 
#然后再查詢(xún)確保一下從服務(wù)器已經(jīng)成功授過(guò)權(quán)
mysql> show grants for user1@192.168.10.2\G
 
*************************** 1. row ***************************
Grants for user1@192.168.10.2:
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.10.2'
IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
完成后， 現(xiàn)在我們到從服務(wù)器 (192.168.10.2) 導(dǎo)入現(xiàn)有的數(shù)據(jù):
代碼如下:
#清空一下從服務(wù)器的 bin-log 日志， （可選: 保險(xiǎn)操作）
mysql> reset master;
 
#然后導(dǎo)入主服務(wù)器中現(xiàn)有的數(shù)據(jù)
shawn@Shawn:~$ mysqldump -uroot -p123123 test -v -f < /tmp/test.sql;
 
-v = 查看導(dǎo)入的詳細(xì)信息
-f = 是當(dāng)中間遇到錯(cuò)誤時(shí)， 可以 skip 過(guò)去， 繼續(xù)執(zhí)行下面的語(yǔ)句
當(dāng)然你也可以用 source 命令導(dǎo)入
好了， 目前為止主服務(wù)器(192.168.10.1)和從服務(wù)器(192.168.10.2)現(xiàn)有的數(shù)據(jù)已經(jīng)成功手動(dòng)同步
 
接下來(lái)修改從服務(wù)器(192.168.10.2)的 mysql 配置文件:
代碼如下:
shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
 
/********** my.cnf **********/
#取消 server-id 注釋符號(hào), 并修改值
server-id       = 2
 
#取消 master-host 注釋符號(hào), 并修改值
master-host     = 192.168.10.1
 
#取消 master-user 注釋符號(hào), 并修改值
master-user     = user1
 
#取消 master-password 注釋符號(hào), 并修改值
master-password = 123456
 
#取消 master-port 注釋符號(hào), 并修改值, 主服務(wù)器默認(rèn)端口號(hào)為: 3306
master-port     = 3306
/****************************/
 
#重啟 Mysql 服務(wù)
shawn@Shawn:~$ sudo /etc/init.d/mysql restart
配置文件修改完成， 此時(shí)在從服務(wù)器中登入自己的 Mysql, 而不是遠(yuǎn)程登入主服務(wù)器(192.168.10.1)
代碼如下:
#在從服務(wù)器中登入自身的 Mysql
msyql> mysql -uroot -p123123;
 
#查看是否已經(jīng)取得同步
msyql> show slave status\G
 
*************************** 1. row ***************************
      Connect_Retry: 60
    Master_Log_FIle: mysql-bin.000002
Read_Master_Log_Pos: 106
   Slave_IO_Running: Yes 
  Slave_SQL_Running: Yes
Slave_IO_Running 如果是 Yes 的話代表成功從主服務(wù)器中同步到 bin-log 日志
Slave_SQL_Running 如果是 Yes 的話代表成功執(zhí)行 bin-log 日志中的 SQL 語(yǔ)句
此時(shí)的 Master_Log_FIle 和 Read_Master_Log_Pos 的值應(yīng)該對(duì)應(yīng)主服務(wù)器中的 show master status 命令的值
Connect_Retry 中的 60 代表每 60 秒就去主服務(wù)器同步 bin-log 日志
 
OK, 如果你看到的是那兩個(gè)關(guān)鍵的 Yes， 那你就可以去測(cè)試了， 在主服務(wù)器插入新的數(shù)據(jù)， 再去從服務(wù)器查看， 不出意外的話， 你會(huì)興奮一下， 數(shù)據(jù)已經(jīng)同步了
 
這里再說(shuō)一下其他經(jīng)常用到的命令:
代碼如下:
#啟動(dòng)復(fù)制線程
msyql> start slave
 
#停止復(fù)制線程
msyql> stop slave
 
#動(dòng)態(tài)改變到主服務(wù)器的配置
msyql> change master to
 
#查看從數(shù)據(jù)庫(kù)運(yùn)行進(jìn)程
msyql> show processlist
這里也同時(shí)說(shuō)一下操作中的常見(jiàn)錯(cuò)誤:
 
問(wèn)題: 從數(shù)據(jù)庫(kù)無(wú)法同步
Slave_SQL_Running 值為 NO， 或 Seconds_Bebind_Master 值為 Null

原因:
一、 程序有可能在 slave 上進(jìn)行了寫(xiě)操作
二、 也有可能是 slave 機(jī)器重啟后， 事務(wù)回滾造成的

解決方法一:
代碼如下:
msyql> stop slave;
 
msyql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
 
msyql> start slave;
解決方法二:
代碼如下:
msyql> stop slave;
 
#查看主服務(wù)器上當(dāng)前的 bin-log 日志名和偏移量
msyql> show master status;
 
#獲取到如下內(nèi)容:
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      286 |              |                  |
+------------------+----------+--------------+------------------+
 
#然后到從服務(wù)器上執(zhí)行手動(dòng)同步
msyql> change master to
    -> master_host="192.168.10.1"
    -> master_user="user1"
    -> master_password="123456"
    -> master_post=3306
    -> master_log_file="mysql-bin.000005"
    -> master_log_pos=286;
    
msyql> start slave;
再次通過(guò) show slave status 查看:
如果 Slave_SQL_Running 的值變?yōu)?Yes， Seconds_Bebind_Master 的值為 0 時(shí)， 即正常
 
好了， 如上是我自己在操作中所總結(jié)的一些內(nèi)容， 如有更好的建議， 歡迎留言一起探討
順便說(shuō)一下， 我使用的是 Ubuntu 12.04

您可能感興趣的文章:

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com