最近在做一次MySQL資料遷移的時候，突然發現自己遺漏了一個地方，那就是許可權資訊沒有匯出，如果我們使用mysqldump --all-databases的時候沒有新增--flush-privileges的時候，匯出的資料中是不會包含mysql資料庫的。

而我其實是比較懶的，不想因為這個重新匯出一次，那麼我就有幾種方式選擇。

如果在MySQL 5.5，5.6的版本中，我可以直接匯出mysql.user的資料即可。

如果使用指令碼化完成，基本是這樣的形式即可，本意其實就是show grants for 'xxx'的組合形式，不斷拼接解析。

mysql -e "SELECT DISTINCT CONCAT('show grants for ','''',user,'''@''',host,'''',';') AS query FROM mysql.user where user!='root'" | grep -v query >/tmp/showgrants.sql && mysql </tmp/showgrants.sql | egrep -v 'Grants for|query'

執行後的語句大體是如下的形式：

GRANT ALL PRIVILEGES ON *.* TO 'adm'@'localhost' IDENTIFIED BY PASSWORD '*3DCFB64FE0CB05D63B9AF64492B5CD6269D82EE8'
GRANT ALL PRIVILEGES ON `Cyou_DAS`.* TO 'adm'@'localhost'
GRANT USAGE ON *.* TO ''@'mysqlactivity'

這一招在5.5,5.6中都是可行的，但是遷移的資料庫是5.7的，看到下面匯出的語句，我感覺不對勁，難道都不要密碼，如果確實沒有，這是一個多麼大的坑。

GRANT USAGE ON *.* TO 'phplamp'@'localhost'
GRANT ALL PRIVILEGES ON `phplampDB`.* TO 'phplamp'@'localhost' 我知道5.7做了一些改進，本身對於show grants也有一些限制，沒想到真碰上這種情況，教訓是如此的深刻。

    所以回到問題，如果現在要解決，就有大體的三種方式來同步許可權;
方法1：重新匯出匯入整個資料庫    不評論，我絕對不會這麼做，只是看起來是一個完整的過程，但是無用功太多，很容易被鄙視
方法2：匯出mysql的許可權配置    如果是在5.5,5.6的環境，直接匯出mysql.user表資料即可，但是在5.7中可不行，一種方式就是匯出整個mysql庫的資料
方法3：pt工具匯出         使用自定義指令碼或者pt工具來匯出許可權資訊

當然解決方法很多，我就說說方法2，方法3

我對比了5.6和5.7 的表結構情況。不看不知道，一看差別還真不小。

MySQL 5.7的mysql.user表含有45個欄位
MySQL 5.6的mysql.user表含有43個欄位

這是表面現象，不是5.7多兩個欄位這麼簡單，真實情況如下：

1) MySQL5.7中多了下面的3個欄位，欄位和資料型別如下：

password_last_changed | timestamp
password_lifetime      | smallint(5) unsigned
account_locked         | enum('N','Y')
2）這麼一看總數對不上，這是因為MySQL 5.7相比5.6少了password欄位

3）還有個細節可能被忽略，那就是MySQL 5.7的欄位user相比MySQL 5.6長度從16字元增長到了32字元。

這就奇怪了，為什麼沒有了password欄位呢，沒有了password欄位，這個功能該怎麼補充呢？

MySQL5.6中檢視mysql.user的資料結果如下:

max_connections: 0
max_user_connections: 0
                plugin: mysql_native_password
authentication_string:
      password_expired: N
password_last_changed: 2016-11-09 11:38:39
     password_lifetime: 0

基於這個安全策略，可以做很多的事情，5.7預設就是這種模式。

看起來之前的那種show grants得到的資訊很有限，那麼我們來看看pt工具的效果，直接執行./pt-show-grants即可

-- Grants for 'webadmin'@'10.127.8.207'
CREATE USER IF NOT EXISTS 'webadmin'@'10.127.8.207';
ALTER USER 'webadmin'@'10.127.8.207' IDENTIFIED WITH 'mysql_native_password' AS '*DA43F144DD67A3F00F086B0DA1288C1D5DA7251F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'webadmin'@'10.127.xx.xx';

這樣的語句相對來說就是完整的，使用show grants的結果少了很多，只包含基本的許可權資訊。

> show grants for 'webadmin'@'10.12.20.133';
| GRANT ALL PRIVILEGES ON *.* TO 'webadmin'@'10.12.xx.xxx' | 為什麼使用pt工具能夠得到更多，不是這個工具有多神奇，而是裡面充分利用了新特性的東西。

pt-show-grants裡面是這樣寫的，對於MySQL 5.7的處理方式。

      # If MySQL 5.7.6+ then we need to use SHOW CREATE USER
      my @create_user;
      if ( VersionCompare::cmp($version, '5.7.6') >= 0 ) {
         eval {
            @create_user = @{ $dbh->selectcol_arrayref("SHOW CREATE USER $user_host") };
         };
         if ( $EVAL_ERROR ) {
            PTDEBUG && _d($EVAL_ERROR);
            $exit_status = 1;
         }
         PTDEBUG && _d('CreateUser:', Dumper(\@create_user));
         # make this replication safe converting the CREATE USER into
         # CREATE USER IF NOT EXISTS and then doing an ALTER USER
            my $create = $create_user[0];
            my $alter = $create;
            $create =~ s{CREATE USER}{CREATE USER IF NOT EXISTS};
            $create =~ s{ IDENTIFIED .*}{};
            $alter =~ s{CREATE USER}{ALTER USER};
            @create_user = ( $create, $alter );
            PTDEBUG && _d('AdjustedCreateUser:', Dumper(\@create_user));
      }

簡化一下就是使用show create user這種方式，在這個基礎上額外補充一下，使得這個語句更加健壯。

我們使用show create user 'webadmin'@'10.12.20.133'得到的結果如下：

| CREATE USER 'webadmin'@'10.12.20.133' IDENTIFIED WITH 'mysql_native_password' AS '*DA43F144DD67A3F00F086B0DA1288C1D5DA7251F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |

語句看起來豐滿了很多，但是似乎還是少了些許可權的資訊，

這是因為5.7裡面完整的資訊是透過show create user和show grants for 'xx'這兩種方式完成的，而在5.6中只需要透過show grants for 'xxx’即可。
明白了原委和解決方法，這個問題處理起來其實就很簡單了。

MySQL中如何得到許可權資訊

相關文章