Category: Database

MySQL: How to Copy tables into seperate Schemas

To Copy a single table in a Schema into another existing Schema

CREATE TABLE myschema.mytbl LIKE myschema2.mytbl;
INSERT INTO myschema.mytbl SELECT * FROM myschema2.mytbl;

To Copy all the tables in a Schema into another existing Schema

 
In cmd/terminal, dump the database

mysqldump -u root -ppass myschema > /path/to/myschema.sql

In cmd/terminal, use the new database and import

mysql -u root -ppass

USE newschema;

source /path/to/myschema.sql

Create a New MySQL User that only has access to a Particular Schema and Table

Create New MySQl User with Privileges

GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';

So for example:

Log in to Mysql…

mysql -u [root] -p

Then type:

GRANT ALL PRIVILEGES ON [schema].[table “” not found /]
To '[user]'@'[hostname]' IDENTIFIED BY '[password]';

How to View Current Users and Privileges

SHOW GRANTS;

or

select user,host from mysql.user;