Couple of useful sql queries we always search about for user management in mySQL:

# creating a user in mysql
CREATE USER '_username_'@'%' IDENTIFIED BY '_password_';

# give user select access on all tables in the database
GRANT SELECT ON _database_.* TO '_username_'@'%';

# give user full access on all tables in the database
GRANT ALL PRIVILLEGES ON _database_.* TO '_username_'@'%';

# grant special access to user for specific tables
GRANT UPDATE ON _database_._tablename_ TO '_username_'@'%';
GRANT DROP, INSERT, DELETE, UPDATE ON _database_._table_ TO '_username_'@'%';
GRANT SELECT ON _database.table_ TO '_username_'@'%';

Determining the size of the database:

SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;

Dumping Data:

# dumping specific database
mysqldump [database] -u[username] -p[password] -h[hostname] > /tmp/database.sql

# dumping certain tables
mysqldump [database] --tables [table1] [table2] -u[username] -p[password] -h[host] > /tmp/database_table1_table2.sql

# dumping based on query
mysqldump [database] -e "[query]" -u[username] -p[password] -h[host] > /tmp/data.sql

[query] - example: select * from users where id > 10

Other options for dumping: –no-data –skip-comments –compact –add-drop-database –no-create-db

Publish Date: 2014-09-10