MySQL多数据库设置 [英] MySQL Multiple Database Setup

问题描述

我一直在寻找答案，我似乎能找到的问题是询问在单个数据库中使用多个数据库还是多个表是否更好.这不是我的问题.

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.
我想在当前数据库旁边建立一个新数据库，但不知道如何做.我想为用户提供对DB2的完全管理员访问权限，而不会看到DB1.这样，我可以托管一个朋友的站点，他们可以创建和删除所需的表，而不会干扰我自己的数据库.我还可以将其用于不安全且不应该存在于主数据库中的演示站点.

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.
如果这不可能，我想知道如何在续集专业版中正确设置对单个数据库的受限访问.我一直在搞弄它，到目前为止，它比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.

基本上，层次结构如下:

Essentially the heirarchy is as follows:

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. 添加用户，将他们限制为该数据库，仅允许您要授予该用户的特权，并且仅允许该数据库的特权.(此处指南)

这篇关于MySQL多数据库设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！