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.sqlFor example
# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
Backup
# mysqldump -u user01 -p123 mydb > dumpfilename.sqlRestore
# 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