MySQL 多数据库设置

2022-01-05 00:00:00 database mysql phpmyadmin multiple-databases

我已经搜索了这个问题的答案，但我似乎能找到的只是询问在单个数据库中使用多个数据库还是多个表更好的问题.不过这不是我的问题.

I've searched for an answer to this and all I can seem to find are questions asking whether it is better to use multiple databases or multiple tables in a single database. This isn't my question though.

问题 1.
我想在我当前的数据库旁边建立一个新数据库，但不知道如何.我想在不查看 DB1 的情况下授予用户对 DB2 的完全管理员访问权限.通过这种方式，我可以托管朋友的站点，他们可以根据需要创建和删除任意数量的表，而不会干扰我自己的数据库.我还可以将它用于不安全且不应该存在于我的主数据库中的演示站点.

QUESTION 1.
I want to set up a new database alongside my current DB and don't know how. I want to give a user full admin access to DB2 without seeing DB1. This way I can host a friend's site and they can create and delete as many tables as they want without disturbing my own DB. I could also use it for demo sites that aren't secure and shouldn't exist inside my primary DB.

我想我可以用虚拟机很容易地做到这一点，并运行一个单独的 MySQL 实例，但我的资源有限，所以这不是一个真正的选择.

I figure I could do this pretty easily with a virtual machine and run a separate instance of MySQL but my resources are limited so that isn't really an option.

我想通过以下两种方式之一进行设置.我更愿意让server.example.com"托管两个数据库并根据用户登录打开正确的数据库.如果没有，我可以将其路由到 server1.example.com 和 server2.example.com.

I'd like to set this up in one of 2 ways. I would prefer to have "server.example.com" host both DBs and open the proper DB based on user login. If not I could do it routing to server1.example.com and server2.example.com.

问题 2.
如果这是不可能的，我想知道如何在 sequel pro 中正确设置对单个数据库的受限访问.我一直在弄乱它，到目前为止，我更喜欢它而不是 PHPMyAdmin.出于某种原因，如果我设置了一个没有权限的新用户，他们可以完全访问我的information_schema"和test"表，但不能创建新表.我不希望其他用户访问这些表，但我希望他们能够设置自己的表.我想对其进行设置，以便新用户可以创建有限数量的表，并且只能查看和编辑这些表.我似乎也找不到这方面的信息.

QUESTION 2.
If this isn't possible I'd like to know how to properly set up restricted access to a single DB in sequel pro. I have been messing around with it and so far prefer it to PHPMyAdmin. For some reason if I set up a new user with no permissions they have full access to my 'information_schema' and 'test' tables but can't create new tables. I don't want other users to access these tables though and I want them to be able to set up their own tables. I'd like to set it up so a new user can create a limited number of tables and only see and edit those tables. I can't seem to find information on this either.

即使我的第一个问题是可能的，我也想知道问题 2 的答案.我已经搜索了很长时间，但在任何地方都找不到可靠的信息.也许我的大脑只是累了...

Even if my first question is possible I'd like to know the answer to question 2. I've been searching for a long time and can't find reliable information anywhere. Maybe my brain is just tired...

推荐答案

您可以设置多个 mysql 实例，但对于您的情况，最好在同一实例中创建不同的数据库.

You can set up multiple instances of mysql but for your situation you are better off creating different databases within the same instance.

您可以创建数据库，然后添加用户，这些用户只能操作他们被授予的数据库，而没有其他权限.

You can create databases and then add users that only have access to manipulate the database they are given and nothing else.

基本上层次结构如下:

Mysql (root or any other super user can see everything)
- Your DB
  - Your Users
    - Your tables/functions/Procedures/etc
- Their DB
  - Their Users
    - Their tables/functions/procedures/etc.

您基本上将每个访问权限分开，在 PHPMyAdmin 中这很容易.步骤是:

You basically separate the access for each, and in PHPMyAdmin it is very easy. The steps are:

  1. 添加数据库 )
  2. 添加用户，将他们限制在该数据库中，只允许您想授予该用户和该数据库的权限.(此处指南)

相关文章