![Page 1: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/1.jpg)
CSC 411/511: DBMS Design
Dr. Nan Wang 1
Database Administration
![Page 2: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/2.jpg)
Dr. Nan Wang 2
Account Management Statements
• Create user– CREATE USER user [IDENTIFIED BY [PASSWORD]
'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']]…
• Drop user– DROP USER user [, user] ...
• Grant• Rename user
– RENAME USER old_user TO new_user [, old_user TO new_user] ...
• Revoke• Set password
– SET PASSWORD [FOR user] = { PASSWORD('some password') | OLD_PASSWORD('some password') | 'encrypted password' }
![Page 3: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/3.jpg)
Dr. Nan Wang
Grant-give privileges on an object to a user
3
![Page 4: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/4.jpg)
Dr. Nan Wang
examples
4
![Page 5: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/5.jpg)
Dr. Nan Wang
Revoke-remove privileges on an object from a user
5
GRANT OPTION FOR option revokes the ability of the identified users to grant the specified privilege(s) to other users
![Page 6: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/6.jpg)
Dr. Nan Wang
PUBLIC-grant a privilege to all users
6
![Page 7: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/7.jpg)
Dr. Nan Wang
ROLE/GROUP
• create a role that represents a type of database user and assign privileges to that role.
• CREATE ROLE <role name> [WITH ADMIN OPTION]
7
![Page 8: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/8.jpg)
Dr. Nan Wang
Revoke
8
DROP ROLE <role>
![Page 9: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/9.jpg)
Dr. Nan Wang
Practise
9
![Page 10: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/10.jpg)
Dr. Nan Wang
MySQL Administrator
10
![Page 11: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/11.jpg)
Dr. Nan Wang
User Account
11
![Page 12: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/12.jpg)
Dr. Nan Wang
Connecting to and Disconnecting from the Server• shell>mysql -h host -u user -p • mysql> QUIT
• MySQL Server:– Mysqld: mysql server– Mysqld_safe: mysql server startup script– Mysql.server: mysql server startup script– Mysqld_multi: manage multiple mysql servers
– http://dev.mysql.com/doc/refman/5.1/en/programs-server.html
12
![Page 13: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/13.jpg)
Dr. Nan Wang
Server information
13
![Page 14: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/14.jpg)
Dr. Nan Wang
Server Control
14
![Page 15: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/15.jpg)
Dr. Nan Wang
Server Control
15
![Page 16: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/16.jpg)
Dr. Nan Wang
Backup
• SELECT * INTO OUTFILE 'file_name' FROM
tbl_name.
• mysqldump --tab=/path/to/some/dir --opt
db_name
• mysqlhotcopy db_name /path/to/some/dir
16
![Page 17: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/17.jpg)
Dr. Nan Wang
Backup
17
![Page 18: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/18.jpg)
Dr. Nan Wang
Restore
18
![Page 19: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/19.jpg)
Dr. Nan Wang
Using mysql in Batch Mode
• shell> mysql < batch-file
• C:\> mysql -e "source batch-file"
• shell> mysql -h host -u user -p < batch-file
Enter password: ********
• shell> mysql < batch-file > mysql.out
• mysql> source filename
• mysql> \. filename 19
![Page 20: CSC 411/511: DBMS Design Dr. Nan Wang 1 Database Administration](https://reader035.vdocuments.us/reader035/viewer/2022062423/5697bfef1a28abf838cba510/html5/thumbnails/20.jpg)
Dr. Nan Wang
Load data
• LOAD DATA INFILE '/tmp/test.txt'
INTO TABLE test
FIELDS TERMINATED BY ','
LINES STARTING BY ‘\n\r';
20