MySQL database on Linux Tutorial

Create password root the first

# mysqladmin -u root password NEWPASSWORD

Change password user root

# mysqladmin -u root -p'oldpassword' password newpass

Create a MySQL Database

# mysql -u root -p

mysql> CREATE DATABASE myDB;

How To Create a New User and Grant Permissions in MySQL

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
mysql> FLUSH PRIVILEGES;

How To Grant Different User Permissions

• ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
• CREATE- allows them to create new tables or databases
• DROP- allows them to them to delete tables or databases
• DELETE- allows them to delete rows from tables
• INSERT- allows them to insert rows into tables
• SELECT- allows them to use the Select command to read through databases
• UPDATE- allow them to update table rows
• GRANT OPTION- allows them to grant or remove other users' privileges

mysql> GRANT ALL PRIVILEGES  ON [database name].[table name] TO ‘[username]’@'localhost’; 

Note: All permission user for database name

mysql> GRANT [type of permission] UPDATE,INSERT ON [database name].[table name] TO ‘[username]’@'localhost’;

Backup and restore mysql database

# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

For example

Backup

# mysqldump -u user01 -p123 mydb  > dumpfilename.sql

Restore

# mysql -u user01 -p123 mydb  < dumpfilename.sql

Query to determine the size of tables in a database

mysql> SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Copyright by: www.linuxoperatingsystem.info http://goo.gl/kMscJ4