mysql cluster 共享权限_mysql cluster统一用户权限

统一用户权限

使用mysql cluster由于存放用户信息的表user是指mysql库里面的，而且这些表是MyISAM存储引擎的。如果希望所有的sql节点都有统一的用户权限，可以把创建用户的脚本在每个sql节点都执行一下，这样比较麻烦。可以使用mysql cluster的一个新特性来完成这些操作。就是把mysql cluster的sql节点的用户权限表转换成NDB数据库引擎的表。

下面是在其中一个sql节点的操作

[root@vmhost02 local]# mysql -u root -ppassword

Welcome to the MySQL monitor.Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.5.27-ndb-7.2.8-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database|

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

| information_schema |

| cacti|

| data|

| db_nagiosql_v32|

| egroupware|

| events|

| fanwe|

| jiqun|

| joffice131|

| jofficev1_2|

| mysql|

| phpgroupware|

| test|

| ultrax|

| vmdb|

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

15 rows in set (0.00 sec)

mysql> grant all on jiqun.* to 'user1'@'%' identified by 'password';

Query OK, 0 rows affected (0.00 sec)

mysql> use jiqun

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_jiqun |

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

| tb1|

| tb2|

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

[root@vmhost02 ~]# mysql -u user1 -ppassword -h 192.168.163.110

Welcome to the MySQL monitor.Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.5.27-ndb-7.2.8-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from jiqun.tb1;

+-------+

| colid |

+-------+

|3 |

|2 |

|1 |

+-------+

3 rows in set (0.00 sec)

mysql>

这些是最另外一个节点操作的

[root@vmhost06 ~]# mysql -u user1 -ppassword

ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

[root@vmhost06 ~]# mysql -u user1 -ppassword -h 192.168.163.160

ERROR 1045 (28000): Access denied for user 'user1'@'192.168.163.160' (using password: YES)

下面是转换用户权限表的操作，就两步，简单吧！

[root@vmhost02 ~]# mysql -u root -ppassword

Welcome to the MySQL monitor.Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.5.27-ndb-7.2.8-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source /usr/share/mysql/ndb_dist_priv.sql

Query OK, 0 rows affected, 1 warning (0.04 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call mysql.mysql_cluster_move_privileges();

Query OK, 1 row affected (5.08 sec)

mysql> show create table mysql.user\G;

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

Table: user

Create Table: CREATE TABLE `user` (

`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',

`ssl_cipher` blob NOT NULL,

`x509_issuer` blob NOT NULL,

`x509_subject` blob NOT NULL,

`max_questions` int(11) unsigned NOT NULL DEFAULT '0',

`max_updates` int(11) unsigned NOT NULL DEFAULT '0',

`max_connections` int(11) unsigned NOT NULL DEFAULT '0',

`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',

`plugin` char(64) COLLATE utf8_bin DEFAULT '',

`authentication_string` text COLLATE utf8_bin,

PRIMARY KEY (`Host`,`User`)

) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

1 row in set (0.01 sec)

ERROR:

No query specified

mysql>

至此，用户在两个sql几点都出现了。而且以后新添加到用户无论是在哪个节点执行的命令，用户都可以在其他节点登录。