MySQL Commands

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

 

Majd Arbash

...

Leave a Reply