MySQL管理工具MySQL Utilities — mysqluserclone (30)

mysqluserclone  以现有数据库上的用户作为模板创作一个或多个具有相同权限的账户。新的账号可以创建在原始服务器或一个不同的服务器上。

如果想列出所有用户，指定--list 选项。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqluserclone/

输出格式

• grid (default)
• csv
• tab
• vertical

选项

MySQL Utilities mysqluserclone version 1.5.3 
License type: GPLv2
Usage: mysqluserclone --source=user:pass@host:port:socket --destination=user:pass@host:port:socket joe@localhost sam:secret1@localhost

mysqluserclone - clone a MySQL user account to one or more new users

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --ssl-ca=SSL_CA       The path to a file that contains a list of trusted SSL
                        CAs.
  --ssl-cert=SSL_CERT   The name of the SSL certificate file to use for
                        establishing a secure connection.
  --ssl-key=SSL_KEY     The name of the SSL key file to use for establishing a
                        secure connection.
  --source=SOURCE       connection information for source server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  --destination=DESTINATION
                        connection information for destination server in the
                        form: <user>[:<password>]@<host>[:<port>][:<socket>]
                        or <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  -d, --dump            dump GRANT statements for user - does not require a
                        destination。显示GRANT语句而不是执行。
  --force               drop the new user if it exists。如果有相同账号没有指定该选项将报错
  --include-global-privileges
                        include privileges that match base_user@% as well as
                        base_user@host
  -l, --list            list all users on the source - does not require a
                        destination
  -f FORMAT, --format=FORMAT
                        display the list of users in either grid (default),
                        tab, csv, or vertical format - valid only for --list
                        option
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.

实例

列出所有用户文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqluserclone/

# mysqluserclone --source=instance_3306 --list -vvv --format=v
# Source on localhost: ... connected.
# All Users:
*************************       1. row *************************
     user: root
     host: 127.0.0.1
 database: None
*************************       2. row *************************
     user: root
     host: ::1
 database: None
*************************       3. row *************************
     user: admin
     host: localhost
 database: None
*************************       4. row *************************
     user: root
     host: localhost
 database: None
*************************       5. row *************************
     user: root
     host: localhost.localdomain
 database: None
5 rows.

复制admin用户到3308实例，用户名为user1，密码为passwd1，主机为10.%文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqluserclone/

# mysqluserclone --source=instance_3306 --destination=instance_3308 admin@localhost  user1:passwd1@10.% -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 1 users...
# Cloning admin@localhost to user user1:passwd1@10.% 
CREATE USER 'user1'@'10.%' IDENTIFIED BY 'passwd1'
GRANT SHUTDOWN ON *.* TO 'user1'@'10.%' 
# ...done.

# mysqluserclone --source=instance_3308 --list -vvv --format=v 
# Source on localhost: ... connected.
# All Users:
*************************       1. row *************************
     user: user1
     host: 10.%
 database: None
*************************       2. row *************************
     user: root
     host: 127.0.0.1
 database: None
*************************       3. row *************************
     user: root
     host: ::1
 database: None
*************************       4. row *************************
     user: admin
     host: localhost
 database: None
*************************       5. row *************************
     user: root
     host: localhost
 database: None
*************************       6. row *************************
     user: root
     host: localhost.localdomain
 database: None
6 rows.

权限

需要对mysql数据库SELECT权限和访问数据目录的权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqluserclone/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqluserclone/