ITN270 Advanced Internet Databases Lecture 15. General MySQL Administration

1. ITN270 Advanced Internet DatabasesLecture 15. General MySQL Administration Topics: Securing a New MySQL Installation MySQL Server Startup & Shutdown Managing User Accounts Maintaining Log files Multiple Servers Updating MySQL ITN270.001 Wake Tech

2. Securing a New MySQL Installation • After MySQL installation • mysql database • user table • include root & anonymous accounts • all the accounts are listed in the user table • test database • No passwords are set up for any user • To Do list: • Establish password for root & anonymous accounts • Set up accounts for other users ITN270.001 Wake Tech

3. Establishing Password for the Initial MySQL Accounts • Unix OS: • First Method: run the commands without server started mysqladmin -h localhost -u root password “password” • Second Method: mysql -u root mysql> SET PASSWORD FOR ‘root’@’localhost’ = password(‘password’); • Third Method: mysql -u root mysql>USE mysql; UPDATE user SET Password = PASSWORD(‘password’) WHERE User =‘root’; FLUSH PRIVILEGES; ITN270.001 Wake Tech

4. Establishing Password for the Initial MySQL Accounts • Delete Anonymous Users • Or • REVOKE ALL ON *.* FROM ‘’@’localhost; • REVOKE GRANT OPTION ON *.* FROM ‘’@’localhost’; ITN270.001 Wake Tech

5. Setting up Password for a Second Server • Two Servers Installed on one Machine • %mysql -u root -p • Enter password  just enter here ITN270.001 Wake Tech

6. Arranging for MySQL Server Startup & Shutdown • UNIX OS: • Server to run as some user other than root • Server to run as the same user all the time • Shutdown • mysqladmin -u root -p shutdown • Starup • Manually • mysqld_safe • mysql.server • Automatically ITN270.001 Wake Tech

7. Arranging for MySQL Server Startup & Shutdown • Windows OS: • mysqld • mysql --console • error message will go to the console window instead of to the error log • Running the Server as a Service • mysql-nt --install ~ automatically • mysql-nt --install ~ manual • Start or stop the service • net start MySql • net stop MySql / mysqld-nt --remove ITN270.001 Wake Tech

8. Specifying the Startup Options • UNIX: File: /etc/my.cnf • Windows: my.int or my.cnf ITN270.001 Wake Tech

9. Shutting Down the Server • /etc/rc.d/init.d/mysql stop • mysqladmin -p -u root shutdown • net stop MySql • kill -TERM PID • kill -9 PID (last choice) ITN270.001 Wake Tech

10. Managing MySQL User Accounts • To specify • which user can connect to the server • where they can connect from • what they can do while connected • GRANT • creates accounts & specifies privileges • REVOKE • remove privileges from existing accounts ITN270.001 Wake Tech

11. Creating New Users & Granting Privileges • Syntax grant privileges (columns) on what to account IDENTIFIED BY ‘password’ REQUIRE encription requirements WITH grant or resource management options ITN270.001 Wake Tech

12. Creating New Users & Granting Privileges • Who can connect & from where • example: GRANT ALL ON alwoodsampdb.* TO ‘alwood’@’localhost’ IDENTIFIED BY ‘password’ or GRANT ALL ON alwoodsampdb.* TO ‘alwood’@’et219-3’ IDENTIFIED BY ‘password’ or GRANT ALL ON alwoodsampdb.* TO ‘alwood’@’%’ IDENTIFIED BY ‘password’ ITN270.001 Wake Tech

13. Creating New Users & Granting Privileges • Privileges • Administrative: Privilege Specifier Operation Allowed by Privilege CREATE TEMPORARY TABLES Create temporary tables EXECUTE Execute stored procedures (reserved for future use) FILE Read and write files on the server host GRANT OPTION Grant the account’s privileges to other accounts LOCK TABLES Explicitly lock tables with LOCK TABLES statements PROCESS View information about the threads executing within the server RELOAD Reload the grant tables or flush the logs or caches REPLICATION CLIENT Ask about master and slave server locations REPLICATION SLAVE Act as a replication slave server SHOW DATABASES Issue SHOW DATABASES statements SHUTDOWN Shut down the server SUPER Kill threads and perform other supervisory operations ITN270.001 Wake Tech

14. Creating New Users & Granting Privileges • Privileges Privilege Specifier Operation Allowed by Privilege • ALTER Alter tables and indexes • CREATE Create databases and tables • DELETE Delete existing rows from tables • DROP Drop (remove) databases and tables • INDEX Create or drop indexes • INSERT Insert new rows into tables • REFERENCES Unused (reserved for future use) • SELECT Retrieve existing rows from tables • UPDATE Modify existing table rows ITN270.001 Wake Tech

15. Creating New Users & Granting Privileges • Secure connection: • GRANT ALL ON alwoodsampdb.* TO ‘alwood’@’localhost’ IDENTIFIED BY ‘password’ REQUIRE SSL • SSL - SQCURE SOCKETS LAYER • Administrative privilege • GRANT ALL ON alwoodsampdb.* to ‘alwood’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION • or • GRANT GRANT OPTION ON alwoodsampdb.* to ‘alwood’@’localhost’ IDENTIFIED BY ‘password’ ITN270.001 Wake Tech

16. Creating New Users & Granting Privileges • Limit user’s resource GRANT ALL ON alwoodsampdb.* to to ‘alwood’@’localhost’ IDENTIFIED BY ‘password’ WITH MAX_CONNECTION_PER_HOUR 10 MAX_QUERIES_PER_HOUR 200 MAX_UPDATE_PER_HOUR 50 ITN270.001 Wake Tech

17. Revoking Privileges and Removing Users • Syntax: • REVOKE privileges (columns) ON what FROM account; • Example: • REVOKE DELETE, UPDATE ON sampdb.* FROM ‘userid’@’localhost’; • REMOVE USER: • %mysql -u root • mysql>USE mysql; • mysql>DELETE FROM user WHERE User = ‘userid’ and Host = ‘%’; • mysql>FLUSH PRIVILEGES; ITN270.001 Wake Tech

18. Changing Passwords or Resetting Lost Password 1: mysql>UPDATE user SET Password=PASSWORD(‘password’) -> WHERE User=‘userid’ and Host = ‘localhost’; mysql> FLUSH PRIVILEGES; 2: mysql> SET PASSWORD FOR ‘userid’@’localhost’ = PASSWORD(‘password’); 3: mysql> GRANT USAGE ON *.* TO ‘userid’ IDENTIFY BY ‘password’; ITN270.001 Wake Tech

19. Maintaining Log Files • The general query log • The slow-query log • The update log • The binary update log & the binary log index file • Error log ITN270.001 Wake Tech

20. Maintaining Log Files • To enable logging, use the options • please refer to MySQL Manual for more information ITN270.001 Wake Tech

21. Running Multiple Servers • Configuring & Compiling Different Servers: • Different locations • Startup Options • using mysqld_multi • Check /etc/my.cnf file • Commands: • mysqld_multi --nolog start 32351 • mysqld_multi --nolog --user=root --password=‘password’ stop 32351 ITN270.001 Wake Tech

22. Updating MySQL ITN270.001 Wake Tech