{"id":2518,"date":"2015-08-01T18:31:11","date_gmt":"2015-08-01T18:31:11","guid":{"rendered":"https:\/\/qbytes.cloud\/?p=2518"},"modified":"2015-08-01T18:31:11","modified_gmt":"2015-08-01T18:31:11","slug":"plesk-how-to-create-multiple-mysql-dbs-with-a-single-user","status":"publish","type":"post","link":"https:\/\/www.qbytes.cloud\/index.php\/2015\/08\/01\/plesk-how-to-create-multiple-mysql-dbs-with-a-single-user\/","title":{"rendered":"PLESK: How to create multiple Mysql DB\u2019s with a Single User"},"content":{"rendered":"<p>Login with ssh<\/p>\n<p>Login to mysql<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# mysql -uadmin -p`cat \/etc\/psa\/.psa.shadow`\n<\/pre>\n<p>use the mysql db.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\nmysql&gt; use mysql;\n<\/pre>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\nmysql&gt; SELECT* FROM db;\n<\/pre>\n<p>To add same user to another database, you have to insert that user into db table and give him same privileges he already has for his existing database.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\nmysql&gt; INSERT INTO db VALUES(\u2018localhost\u2019,\u2019second_db\u2019,\u2019same_username_you_used_for_first_db\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019N\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019);\n<\/pre>\n<p>The second database is accessible to the same user as the first database, from command line but it will not appear yet in your Plesk admin panel. In order to have access to second database from Plesk interface you will have to link your database user to this second database in plesk table:<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n\nmysql&gt; use psa;\n\n<\/pre>\n<p>First you must find out the database id Plesk internally assigned to your second database which we do with the following:<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n\nmysql&gt; SELECT * FROM data_bases;\n\n    +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014\u2014-+\n    | id | name                             | type  | dom_id              | db_server_id    | default_user_id    |\n    +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014+\n    |  1 | first_db                         | mysql |      1              |            1 |               1       |\n    |  2 | second_db                        | mysql |      1              |            1 |               0       |\n    |  3 | test                             | mysql |      1              |            1 |               0       |\n    |  4 | wordpress                        | mysql |      1              |            1 |               3       |\n    +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\n\n<\/pre>\n<p>Note: We are going to get second_db to use first_db\u2019s user so we are going to need the id &#8220;2&#8221;. Now we have to find out what id plesk has signed internally to the Database user:<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n\nmysql&gt; SELECT * FROM db_users;\n\n    +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014+\n    | id | login          | account_id    | db_id     |\n    +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014+\n    |  1 | first_db_user  |       3       |    1      |\n    |  2 | worpress_user  |       4       |    4      |\n    +\u2014-+\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014+\n\n<\/pre>\n<p>Note: We are going to use  first_db\u2019s  account ID above which is &#8220;3&#8221;. Now you have to link the user to the second database using the command below<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n\nmysql&gt; INSERT INTO db_users VALUES(\u201d,\u2019first_db_user\u2019,\u20193\u2032,\u20192\u2032);\n\n<\/pre>\n<p>Note: For people who do not like to copy an paste those are single quotes at the start.<br \/>\nYou should run the select db_users command again to check for your changes then exit mysql and restart MySQL:<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n\n# service mysqld restart\n\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Login with ssh Login to mysql # mysql -uadmin -p`cat \/etc\/psa\/.psa.shadow` use the mysql db. mysql&gt; use mysql; mysql&gt; SELECT* FROM db; To add same user to another database, you have to insert that user into db table and give him same privileges he already has for his existing database. mysql&gt; INSERT INTO db VALUES(\u2018localhost\u2019,\u2019second_db\u2019,\u2019same_username_you_used_for_first_db\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019N\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019,\u2019Y\u2019); &#8230; <a title=\"PLESK: How to create multiple Mysql DB\u2019s with a Single User\" class=\"read-more\" href=\"https:\/\/www.qbytes.cloud\/index.php\/2015\/08\/01\/plesk-how-to-create-multiple-mysql-dbs-with-a-single-user\/\" aria-label=\"Read more about PLESK: How to create multiple Mysql DB\u2019s with a Single User\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70,86],"tags":[],"class_list":["post-2518","post","type-post","status-publish","format-standard","hentry","category-mysql","category-plesk"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/2518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=2518"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/2518\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=2518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=2518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=2518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}