MySQL Administrator's Bible- P11

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

133
lượt xem 25
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL Administrator's Bible- P11: Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MySQL Administrator's Bible- P11

Backups and Recovery 13 R1Soft is that it provides what it calls near-Continuous Online Backups. It does this by perform- ing backups very frequently (every 15 minutes or less). This provides for a very small window of time that data can be lost. In addition, the R1Soft software also provides for complete bare-metal restore for MySQL servers. The homepage of R1Soft is: www.r1soft.com. Copying Databases to Another Machine You can copy the .frm, .MYI, and .MYD ﬁles for MyISAM tables and the .frm and data ﬁles (.ibd or ibdata) for InnoDB between different hardware architectures that support the same ﬂoating-point format (Endianness). This means that you can transfer InnoDB and MyISAM tables from Windows to Linux without doing a logical export and import. Simply shut down the database (or lock the tables involved) and use scp to copy the database. Or, restore a physical backup to a different machine. In cases where you need to transfer databases between different architectures, you can use mysqldump to create a ﬁle containing SQL statements. You can then transfer the dump ﬁle to the second machine (the destination host) and feed it as input to the mysql client. To move a database from one machine to another, run the following from the machine currently holding the database (the target host): shell> mysqldump --databases sakila | mysql -h destination_host sakila For large tables, exporting a tab-delimited ﬁle and using mysqlimport is much faster than using mysqldump to export INSERT statements and restoring with source or the redirection operator ( mkdir /path/to/backup shell> mysqldump --tab=/path/to/backup --databases sakila Then copy the ﬁles in /path/to/backup directory to the destination machine and load the ﬁles into mysqld there: shell> cat /path/to/backup/*.sql | mysql sakila shell> mysqlimport sakila /path/to/destination/copy/*.txt The grant tables (user permissions) are stored in the mysql database. If you do not have a mysql database, mysqld may not start up on the new machine. Make sure to FLUSH PRIVILEGES or restart mysqld when the grant tables are imported. 467
Part III Core MySQL Administration Recovering from Crashes Many administrators spend a signiﬁcant amount of time on backups and then do not spend time on their recovery strategies. However, they make a serious mistake by not planning for how they will recover or ever testing backups and the recovery process by performing a recovery. The recovery process is going to vary depending on your objectives. It will always begin with the restoration of a backup. With physical backups you just copy the ﬁles to the server where the recovery is taking place and restart the server. For a logical backup the techniques used for recovery are going to vary — recovery may consist of loading of ﬁles with the source command, redirecting ﬁles with the < operator, or using mysqlimport. Often after the backup is restored you will need to restore the server to a point-in-time after the last backup. If this is the case you need to perform what is called a point-in-time recovery. You can perform a point-in-time recovery with any backup process because you are using incre- mental backups (such as the binary log ﬁles) to bring the server up to a certain point-in-time after restoring a previous backup. MySQL server uses a binary format for the log ﬁles to save space. This means you cannot view it directly. MySQL supplies a utility called mysqlbinlog to convert these logs to a text format that you can view. For more on binary logging, see Chapter 16. The process for performing a point-in-time restore is as follows: ■ Restore the database using the last backup ■ Determine the ﬁrst binary log and starting position needed ■ Determine the last binary log needed ■ Convert the binary log(s) to text format with the mysqlbinlog utility, using options to specify the start and stop time ■ Import the converted binary log(s) As with any recovery process, the ﬁrst step is to restore the last backup performed. This restora- tion will vary depending on how the backup was performed. For this example assume a ﬁle sys- tem snapshot was performed at midnight of the 16th of September and the logs were ﬂushed at the same time. This means you have a physical backup and the restoration should just be copy- ing the ﬁles to the server and starting up mysqld again. Once the basic restoration is complete it is time to restore the data changes since the backup was performed. 468
Backups and Recovery 13 Here is a listing of the binary log directory: $ ls -lh mysql-bin* -rw-rw---- 1 mysql mysql 257M Sep 16 23:48 mysql-bin.010309 -rw-rw---- 1 mysql mysql 257M Sep 17 00:02 mysql-bin.010310 -rw-rw---- 1 mysql mysql 257M Sep 17 03:48 mysql-bin.010311 -rw-rw---- 1 mysql mysql 257M Sep 17 19:01 mysql-bin.010312 -rw-rw---- 1 mysql mysql 162M Sep 17 19:03 mysql-bin.010313 -rw-rw---- 1 mysql mysql 8.3K Sep 17 19:01 mysql-bin.index This means that mysql-bin.010310 is the ﬁrst binary log created after the backup was per- formed. This was determined by looking at the timestamp of the log ﬁles, which shows the last time the log ﬁle was modiﬁed. Knowing the backup was performed at midnight you can see that mysql-bin.010309 was the last log written before midnight. Therefore the next log ﬁle is the one with which you want to start your restoration. For this example, you need to restore the server through the last log listed, which is mysql-bin.010313. If you have a large number of binary logs (such as in this case) to convert it would probably be beneﬁcial to script this process. The command to convert an entire binary ﬁle will look similar to this: $ mysqlbinlog mysql-bin.010310 > mysql-bin.010310.sql This would convert the mysql-bin.010310 log to text format and store it in the mysql-bin.010310.sql ﬁle. You will have to do this for each log ﬁle needed. The ﬁnal part of the process is the import of the log ﬁles into the database server: $ mysql --user=root --pasword < mysql-bin.010310.sql This would need to be done for each converted binary log. Once again, scripting might be helpful. To create text ﬁles from parts of binary logs using mysqlbinlog, specify a starting place with either --start-datetime=’YYYY-MM-DD’ or --start-position=# and ending place with either --stop-datetime=’YYYY-MM-DD’ or --stop-position=#. To determine the exact position to start or stop you have to examine the binary log contents. The problem is that this can be a large ﬁle. To start you have to convert the log to text format: $ mysqlbinlog mysql-bin.010312 > mysql-bin.010312.sql 469
Part III Core MySQL Administration Once you convert the log ﬁle you can view the text-format log with a text editor. With a binary log of 162 MB in size this may be tricky. If you are looking to end at a speciﬁc time you can specify a stopping time: $ mysqlbinlog --stop-datetime=’2008-09-17 18:42:48’ mysql-bin.010312 > mysql-bin.010312.sql Once you have trimmed the ﬁle it becomes much easier to view with the tail command. Now you will still have to potentially look through a number of entries because a busy database server is going to be executing hundreds, if not thousands, of queries a second. Here are the last 25 lines after trimming: $ tail -25 mysql-bin.010312.sql use usersession/*!*/; SET TIMESTAMP=1221702167/*!*/; UPDATE XXXXX /*!*/; # at 185118382 #080917 18:42:47 server id 16 end_log_pos 185118409 Xid = 9731310851 COMMIT/*!*/; # at 185118409 #080917 18:42:47 server id 16 end_log_pos 185118473 Query thread_id=1273437368 exec_time=1 error_code=0 SET TIMESTAMP=1221702167/*!*/; BEGIN/*!*/; # at 185118473 #080917 18:42:47 server id 16 end_log_pos 185118508 Rand SET @@RAND_SEED1=700138339, @@RAND_SEED2=45664511/*!*/; # at 185118508 #080917 18:42:47 server id 16 end_log_pos 185119173 Query thread_id=1273437368 exec_time=1 error_code=0 use usersession/*!*/; SET TIMESTAMP=1221702167/*!*/; UPDATE XXXXX /*!*/; # at 185119173 #080917 18:42:47 server id 16 end_log_pos 185119200 Xid = 9731310854 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; $ In this case you want to execute the ﬁrst COMMIT statement and then stop. The line after the COMMIT statement shows the log position. The log position is 185118473. Now you can create your ﬁnal text format ﬁle with exactly the right information: 470
Backups and Recovery 13 $ mysqlbinlog --stop-position=185118473 mysql-bin.010312 > mysql-bin.010312.sql This ﬁle (mysql-bin.010656.sql) is what you will want to import. $ mysql --user=root --password < mysql-bin.010656.sql It would be wise to examine the resulting ﬁle to ensure it is correct before execution of the log ﬁle. Table 13-6 lists common options for the mysqlbinlog program. TABLE 13-6 mysqlbinlog Options Option Description --start-datetime= Begins reading the binary log ﬁle at a timestamp equal to or "date_time" greater than the datetime argument. --stop-datetime= Ends reading the binary log ﬁle at a timestamp equal to or "date_time" greater than the datetime argument. --start-position= Begins reading the binary log ﬁle beginning at the ﬁrst log start_log_position position equal to or greater than start_log_position. --stop-position=stop_ Ends reading the binary log ﬁle at the ﬁrst event having a log_position log position equal to or greater than stop_log_position. Planning for Disasters Database recovery is part of the disaster planning process. What to do, who does it, and how long the recovery process takes when things break requires thought, planning, and usually coor- dination with other people and departments. It is important that you rehearse plans and perform drills to make sure that the proper preparations are in place. A backup plan and corresponding periodic restores of your backups should be part of the disas- ter preparation. An incomplete list of issues covered could include: ■ Power ■ Employee termination process ■ Data center failover plan ■ Data retention strategies 471
Part III Core MySQL Administration A disaster plan should be written down and approved by everyone involved, including manage- ment. It should include checklists and processes to carry out for various scenarios. Summary You have multiple methods of backing up your data, and depending on your situation, some options are going to be better than others. Do not underestimate the importance of performing backups and testing the recovery procedure. Ensure the backups and recovery processes are actually working and current by testing frequently, preferably at least once per quarter. Other periodic tasks may include a test of the backups and recovery processes, such as periodically refreshing a QA server by recovering a production backup to it. The following topics were covered in this chapter: ■ Backup and recovery terminology ■ Why backups are necessary ■ Backup methodology ■ The recovery process ■ Disaster planning 472
User Management M anaging the users for a MySQL server is one of the most impor- tant tasks of a MySQL database administrator. Because of the IN THIS CHAPTER ﬂexibility of the permissions system, it is not necessarily a trivial task. There are many tips to help manage users. Learning about MySQL users Managing user accounts Learning about MySQL Users Resetting the root password Debugging user account A user in MySQL is a combination of a username and host string. problems A host string can be an IP address, hostname, fully qualiﬁed domain name, or netmask. This means that even though they share a username, admin@192.168.2.10 is different from admin@’192.168.2.%’, and both users can have different passwords and permissions. In the following example, we set up two users with the same username and different passwords and permissions: shell> mysql -u root -prootpass Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 6.0.8-alpha-community MySQL Community Server (GPL) Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer. mysql> GRANT USAGE ON *.* TO admin@’192.168.2.10’ IDENTIFIED BY ’easytoguess’; Query OK, 0 rows affected (0.22 sec) mysql> GRANT ALL ON sakila.* TO admin@’192.168.2.20’ IDENTIFIED BY ’anotherpassword’; 473
Part III Core MySQL Administration Query OK, 0 rows affected (0.41 sec) mysql> select user,host,password from mysql.user where user=’admin’; +-------+--------------+-------------------------------------------+ | user | host | password | +-------+--------------+-------------------------------------------+ | admin | 192.168.2.10 | *2F9A309FBEA7337E61AA2953EB48179BF9300B7C | | admin | 192.168.2.20 | *4CBC947A0D5CF017233C027F4597C92A92D02F92 | +-------+--------------+-------------------------------------------+ 2 rows in set (0.05 sec) mysql> exit Bye This allows for a ﬂexible control system but can also cause confusion. How the server deter- mines who a user is and what permissions are allowed for that user will be discussed in the next section. Access Control Lists An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and once you understand this it helps greatly when troubleshooting problems with users not being able to connect. MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order. If you had two users, admin@’192.168.2.%’ and then admin@192.168.2.10, the user admin@’192.168.2.%’ user comes before admin@192.168.2.10 in the Access Control List. When MySQL checks authentication, the admin@’192.168.2.%’ user is the ﬁrst user whose credentials match the credentials provided. Remember how users with the same username but different host strings can have different passwords? The following example shows what happens in this case; the computer used by the user has an IP address of 192.168.2.20: shell> mysql -u admin –peasytoguess –h 192.168.1.5 ERROR 1045 (28000): Access denied for user ’admin @’192.168.2.20’ (using password: YES) What happened was the account attempted to connect using the account admin@192.168. 2.10, which was conﬁgured with the password of easytoguesss. When attempting to connect the server authenticated against the user account admin@192.168.2.20, which has a password of anotherpassword. If they had same passwords the connection would be allowed — but the connection may be using an account with different privileges than expected. If you are not sure what user you are actually logged in as you can use the USER() and CURRENT_USER() functions to determine how you are connected. 474
User Management 14 The USER() function shows which username and host the MySQL server sees the connection as coming from. The CURRENT_USER() function shows which username and host the con- nection is actually authenticated. Note that the SHOW GRANTS statement with no arguments shows the privileges for the user the connection was authenticated — the privileges for the CURRENT_USER(). Wildcards Wildcard characters (% and _) are allowed in host strings. This is another source of confusion as admin@192.168.2.10 is a completely different user than admin@’192.168.2.%’. As stated above, MySQL checks the access control list in order. However, we did not reveal how the MySQL server orders the access control list. MySQL orders the access control list with the least speciﬁc hosts last. This means that hostnames and IPs without wildcards or netmasks are placed before hostnames and IPs with wildcards and netmasks. MySQL matches the most speciﬁc user and hostname. In the following example, after deleting the users from the previous example, admin@192.168. 2.10 is given full read/write permissions to the sakila database, and admin@’19.168.2.%’ is given read-only permissions to the sakila database: mysql> DROP USER admin@192.168.2.20; Query OK, 0 rows affected (0.01 sec) mysql> DROP USER admin@192.168.2.10; Query OK, 0 rows affected (0.01 sec) mysql> SELECT USER, HOST, PASSWORD FROM MYSQL.USER WHERE USER=’admin’; Empty set (0.01 sec) mysql> GRANT SELECT ON sakila.* TO admin@’1921.68.2.%’ identified by ’adminpass’; Query OK, 0 rows affected (0.39 sec) mysql> GRANT ALL ON sakila.* TO admin@’192.168.2.10’ identified by ’adminpass’; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye shell> mysql -u admin Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 6.0.8-alpha-community MySQL Community Server (GPL) Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer. 475
Part III Core MySQL Administration mysql> SHOW GRANTS\G *************************** 1. row *************************** Grants for admin@192.168.2.10: GRANT USAGE ON *.* TO ’admin’@’192. 168.2.10’ IDENTIFIED BY PASSWORD ’*2C6396ADEEF1AF865672D48735 C0E3EC8B1A9CEC’ *************************** 2. row *************************** Grants for admin@192.168.2.10: GRANT ALL PRIVILEGES ON `sakila`.* TO ’admin’@’192.168.2.10’ 2 rows in set (0.00 sec) mysql> exit Bye The connection was authenticated as the user admin@192.168.2.10 because it has a more speciﬁc host than the user admin@’192.168.2.%’ and, therefore, appeared earlier in MySQL’s access control list. This would only happen if the user connected from the IP address 192.168.2.10. If they connected from 192.168.2.20, it would use the more general host of ’192.168.2.%’. If they attempted to connect from 192.168.3.10, they would not be authenticated. System tables All the user and permission information is stored in the mysql database in a set of tables known as the grant tables. If you execute ’SHOW DATABASES’ on a typical default install of MySQL it will look like the following: mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.02 sec) The information_schema database really is not a database but an interface to various system metadata (see Chapter 21 for more information about the information_schema database). The test database is an empty database used for testing purposes and as mentioned the mysql database stores the user information. In addition to the grant tables, the mysql database has tables containing other system information. For example, a table called event is used by the 476
User Management 14 event scheduler (see Chapter 7 for more information about events). Because of new additions such as this, the tables in the mysql database vary from version to version. Here are the tables in a server running mysqld 6.0.8-alpha: mysql> SHOW TABLES; +---------------------------+ | Tables_in_mysql | +---------------------------+ | backup_history | | backup_progress | | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 25 rows in set (0.18 sec) The tables that are of interest when it comes user management are columns_priv, db, host, procs_priv, tables_priv, and user. It is possible to directly manipulate these tables using SQL to add, delete, or update user information. In fact, that used to be the only way privileges were managed. These days, however, it is much easier and less error prone to use the GRANT, REVOKE, CREATE USER, DROP USER, and RENAME USER commands designed for user manage- ment. We will cover the commands used to manipulate users in the next section. 477
Part III Core MySQL Administration One of the more common problems of a database administrator is seeing what users are already exist and what privileges they have. If you are logged in to the server with appropriate privi- leges, the following will show all usernames, hosts, and password hashes on the system: mysql> SELECT user,host,password FROM mysql.user; +-------------+--------------+-----------------------------------+ | user | host | password | +-------------+--------------+-----------------------------------+ | root | % | *ACC4836009D0D7911EFE143E154D3E7C | | | | 32AB8EEB | | root | localhost | *ACC4836009D0D7911EFE143E154D3E7C | | | | 32AB8EEB | | developer | localhost | *50C0E8BEE396F2367258EC80901409C4 | | | | BE300238 | | production_ | slave. | *891A44E50A5E8286F04BC1EFB0292BE3 | | slave | company.com | AFE74D5E | | production_ | 192.168.2.191| *891A44E50A5E8286F04BC1EFB0292BE3 | | slave | | AFE74D5E | | ops | localhost | *99FFA08BDD2C5D80552F52F441AA632D | | | | FA1DE9E3 | | cto | 192.% | *B81134DE91B9BE86259180DC8446A254 | | | | 008A1D9E | +-------------+--------------+-----------------------------------+ 7 rows in set (0.00 sec) If a user has a blank password, the password ﬁeld will be empty. Managing User Accounts MySQL server provides a number of commands used for managing users. To create a user, you can use the CREATE USER command. To drop a user, you should use the DROP USER command. In the following example, we create a user and give them privileges and ﬁnally drop the user. mysql> CREATE USER ’ops’@’192.168.%’ IDENTIFIED BY ’password’; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON test.* TO ’ops’@’192.168.%’; Query OK, 0 rows affected (0.00 sec) mysql> DROP USER ’ops’@’192.168.%’; Query OK, 0 rows affected (0.00 sec) 478
User Management 14 mysql> select User,Host,Password from user; +------+-----------+-------------------------------------------+ | User | Host | Password | +------+-----------+-------------------------------------------+ | root | localhost | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C53366A1 | | root | % | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C53366A1 | +------+-----------+-------------------------------------------+ 2 rows in set (0.00 sec) The CREATE USER and GRANT USER commands (covered in the next section) can both be used to create users without passwords. This is very insecure and should be avoided! Always use the IDENTIFIED BY clause when using these commands. Dropping the user removes all their privileges. Even if you recreate the exact same username and host the new user does not retain the privileges of the previous user. You are starting from scratch. Here is an example showing this: mysql> CREATE USER ’ops’@’192.168.%’ IDENTIFIED BY ’password’; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON test.* TO ’ops’@’192.168.%’; Query OK, 0 rows affected (0.00 sec) mysql> DROP USER ’ops’@’192.168.%’; Query OK, 0 rows affected (0.00 sec) mysql> SELECT user,host,password FROM mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C53366A1 | | root | % | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C53366A1 | +------+-----------+-------------------------------------------+ 2 rows in set (0.00 sec) mysql> CREATE USER ’ops’@’192.168.%’ IDENTIFIED BY ’password’; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR ’ops’@’192.168.%’; +-----------------------------------------+ | Grants for ops@192.168.% | +-----------------------------------------+ | GRANT USAGE ON *.* TO ’ops’@’192.168.%’ | +-----------------------------------------+ 1 row in set (0.00 sec) 479
Part III Core MySQL Administration The RENAME USER command renames an existing account. The RENAME COMMAND will return an error if the new user already exists. mysql> CREATE USER ’ops’@’192.168.%’ IDENTIFIED BY ’password’; Query OK, 0 rows affected (0.00 sec) mysql> SELECT user,host,password FROM mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C53366A1 | | ops | 192.168.% | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | root | % | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C53366A1 | +------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec) mysql> CREATE USER ’support’@’192.168.%’; Query OK, 0 rows affected (0.00 sec) mysql> SELECT user,host,password FROM mysql.user; +---------+-----------+-------------------------------------------+ | user | host | password | +---------+-----------+-------------------------------------------+ | root | localhost | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C53366A1 | | ops | 192.168.% | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | root | % | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C53366A1 | | support | 192.168.% | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | +---------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec) mysql> RENAME USER ’ops’@’192.168.%’ TO ’support’@’192.168.%’; ERROR 1396 (HY000): Operation RENAME USER failed for ’ops’@ ’192.168.%’ mysql> RENAME USER ’ops’@’192.168.%’ TO ’over_lords’@’192.168.%’; Query OK, 0 rows affected (0.00 sec) mysql> SELECT user,host,password FROM mysql.user; +------------+-----------+--------------------------------------+ | user | host | password | +------------+-----------+--------------------------------------+ | root | localhost | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C | | | | 53366A1 | | over_lords | 192.168.% | *2470C0C06DEE42FD1618BB99005ADCA2 | | | | EC9D1E19 | | root | % | *0AFF2E05C6A513A4FF86D9EBE1D7F8C4C | | | | 53366A1 | | support | 192.168.% | *2470C0C06DEE42FD1618BB99005ADCA2 | | | | EC9D1E19 | +------------+-----------+--------------------------------------+ 4 rows in set (0.00 sec) 480
User Management 14 When a user is renamed, the password is retained by the new user. The user privileges are not migrated. Also RENAME USER does not change any database object properties (tables, views, stored routines, and triggers) that the user created. GRANT and REVOKE commands There are two commands that are used to control a user’s privileges. The GRANT command is used to give an existing user privileges, and REVOKE is used to remove privileges. If a user does not exist, GRANT will create a new user at the same time you are giving them privileges. It is not recommended that you use GRANT to create a user, because it is too easy to forget to specify a password when using the GRANT syntax. Users should be created with CREATE USER ﬁrst, then given permissions with GRANT. There are ﬁve levels that privileges can be granted. Global Global privileges apply to all databases on a MySQL server. These privileges are stored in the mysql.user table. You use the GRANT privilege_list ON *.* and REVOKE privilege_list ON *.* statements to grant and revoke only global level privileges. The following example will grant all privileges (except the GRANT PRIVILEGES privilege) to the ’ops’@’192.168.%’ user. These privileges apply for all databases on the server: GRANT RELOAD,SHUTDOWN ON *.* TO ’ops’@’192.168.%’; The next example will grant only SELECT, INSERT, UPDATE, and DELETE privileges to the user ’ops’@’192.168.%’ on all databases on the server: GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO ’ops’@’192.168.%’; The users with username of root created by default is only special because of the permissions it has. The root username has no signiﬁcance and can be deleted from a fresh installation with no issues (servers currently in use may be depending on the root user for backups or some other important task). To create a new user with all privileges: CREATE USER superuser@localhost IDENTIFIED BY ’superpass’; GRANT ALL ON *.* TO superuser@localhost; Database Database privileges apply to all objects of a speciﬁed database. These privileges are stored in the mysql.db and mysql.host tables. The GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* commands grant and revoke only database level privileges. The following example will grant all privileges (except the GRANT PRIVILEGES privilege) to the ’ops’@’192.168.%’ user. These privileges apply only to the database user_db: GRANT ALL ON user_db.* TO ’ops’@’192.168.%’; 481
Part III Core MySQL Administration The next example will grant only SELECT, INSERT, UPDATE, and DELETE privileges to the user ’ops’@’192.168.%’ on the database user_db: GRANT SELECT, INSERT, UPDATE, DELETE ON user_db.* TO ’ops’ @’192.168.%’; Table Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. The GRANT ALL ON db_name.table_name and REVOKE ALL ON db_name.table_name commands grant and revoke only table level privileges. The following example will grant all privileges (except the GRANT PRIVILEGES privilege) to the ’ops’@’192.168.%’ user. These privileges apply only to the table table_name of the database user_db: GRANT ALL ON user_db.table_name TO ’ops’@’192.168.%’; The next example will grant only SELECT, INSERT, UPDATE, and DELETE privileges to the user ’ops’@’192.168.%’ on to the table table_name of the database user_db: GRANT SELECT, INSERT, UPDATE, DELETE ON user_db.table_name TO ’ops’@’192.168.%’; If you had only speciﬁed table_name rather than db_name.table_name, the GRANT or REVOKE statement applies to the table table_name in the default database. To keep from hav- ing unexpected results, we would recommend you use the "full" database_name.table_name format instead. Column Column level privileges apply to one or more columns in a given table. These privileges are stored in the mysql.columns_priv table. When using the REVOKE command to remove column level privileges, you must specify the same columns that were granted. The column or columns for which the privileges are to be granted are enclosed within parentheses. The following example will grant SELECT, INSERT, and UPDATE privileges to the user ’ops’@’192.168.%’ on the columns col1 and col2 of the table table_name located in the database user_db: GRANT SELECT (col1,col2), INSERT (col1,col2), UPDATE (col1,col2) ON user_db.table_name TO ’ops’@’192.168.%’; 482
User Management 14 Routine The CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT privileges apply to stored rou- tines (functions and procedures). They can be granted at the global and database levels. Also, except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines. The privileges are stored in the mysql.procs_priv table. GRANT CREATE ROUTINE ON database.* TO ’ops’@’192.168.2.%’; GRANT EXECUTE ON PROCEDURE database.backup_proc TO ’backup’@ ’192.168.2.%’; Table 14-1 lists all of the privilege options available. TABLE 14-1 MySQL User Privileges Privilege Description ALL Grants all privileges to speciﬁed user except the GRANT OPTION. ALTER Allows user to ALTER TABLE. ALTER ROUTINE Allows user to alter or drop stored routines. CREATE Allows user to execute the CREATE TABLE command. CREATE ROUTINE Allows user to create stored routines. CREATE TEMPORARY Allows user to execute the CREATE TEMPORARY TABLE command. TABLES CREATE USER Allows user to execute CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES statements for user creation. CREATE VIEW Allows user to execute the CREATE VIEW command to create views. DELETE Allows user to execute the DELETE command. DROP Allows user to execute the DROP command. EXECUTE Allows user to run stored routines. FILE Allows user to execute both SELECT INTO OUTFILE and LOAD DATA INFILE. GRANT OPTION Allows user to grant other users privileges. INDEX Allows user to execute CREATE INDEX and DROP INDEX. continued 483
Part III Core MySQL Administration TABLE 14-1 (continued ) Privilege Description INSERT Allows user to execute the INSERT command. LOCK TABLES Allows user to execute LOCK TABLES (user must also have SELECT privileges on the table). PROCESS Allows user to see all processes when executing SHOW PROCESSLIST. REFERENCES This privilege is not currently implemented. RELOAD Allows user to execute FLUSH. REPLICATION Allows user to execute both SHOW MASTER STATUS and SHOW CLIENT SLAVE STATUS commands. REPLICATION Needed by the replication slave to read binary logs from the master. SLAVE SELECT Allows users to execute SELECT statement. SHOW DATABASES When user executes SHOW DATABASES command will return a list of all databases. SHOW VIEW Allows user to execute the SHOW CREATE VIEW command. SHUTDOWN Allows user to execute ’mysqladmin shutdown’. SUPER Allows user to execute CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL commands. Also will allow user to always connect even if max_connections has been reached. UPDATE Allows user to execute UPDATE command USAGE Allows user to connect. As you can see there are quite a few allowable privileges. This, in combination with the ﬁve privilege levels (global, database, table, column, and routine), allow for any level of granularity needed by a database administrator. This granularity creates complexity, but the end result is a more controllable and secure system. Privileges are checked until either access is allowed or the end of the ACL is reached. If you want to query the table production.employee, then MySQL server ﬁrst checks to see if you have global access privileges. If so, the query is executed. If you do not have global access then MySQL server checks for privileges at the database level (production). If you do not have privileges at the database level, then the table level (employee) privileges are checked. If this fails the column level privileges are checked and if this fails the user is denied access. If a check returns positive at any level mysqld stops checking privileges. 484
User Management 14 REVOKE The REVOKE statement is used to remove privileges from a user account. Just as with the GRANT statement there are ﬁve levels that you can revoke privileges from: global, database, table, col- umn, and routine. The following example would revoke all privileges for the user ’ops’@’localhost’: mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’ops’@’localhost’; Even if you revoke all privileges, the user is not dropped (they are still visible in the mysql.user system table). At this point, the user has the USAGE privilege, which means they can still connect to the server and execute a few commands such as SHOW VARIABLES and SELECT NOW(). To drop a user, you must use the DROP USER. It is a best practice to always drop users after revoking all their privileges. What if the ’ops’@’localhost’ had global SELECT, INSERT, UPDATE, DELETE, and DROP privileges but you wanted to only remove the DROP privilege? The following would accomplish this: mysql> REVOKE DROP ON *.* FROM ’ops’@’localhost’; If the user ’ops’localhost’ had SELECT, INSERT, UPDATE, and DELETE privileges on the table user_accounts of the database production, you could revoke the DELETE privileges on this one table like this: mysql> REVOKE DELETE ON production.user_accounts FROM ’ops’@’localhost’; As you have probably noticed the REVOKE command very similar of the GRANT command. SHOW GRANTS and mk-show-grants The SHOW GRANTS command is used to show a user’s privileges. This is done by displaying a list of all the GRANT statement(s) that could then be used to duplicate the privileges of a user. If the user has the GRANT PRIVILEGES privilege, then the user can also view the grants of other users. Here is a simple example which shows the grants for the current user: mysql> SHOW GRANTS\G *************************** 1. row *************************** Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO ’root’@ ’localhost’ IDENTIFIED BY PASSWORD ’*3800D13EE735ED411CBC3F23B2 A2E19C63CE0BEC’ WITH GRANT OPTION 1 row in set (0.00 sec) 485
Part III Core MySQL Administration This was done with the root user who has all privileges, including the GRANT OPTION. Because this user has the GRANT OPTION, it can grant privileges to other users, and use the SHOW GRANTS command to display grants for other users. Remember, if you need to see a list of users on the server SELECT user,host FROM mysql.user will return all users. Now to take a look at the privileges for ’over_lords’@’%’: mysql> SHOW GRANTS FOR ’over_lords’@’%’\G *************************** 1. row *************************** Grants for over_lords@’%’: GRANT USAGE ON *.* TO ’over_lords’@’%’ IDENTIFIED BY PASSWORD ’*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19’ This user has no privileges. If you are running Unix-based servers the Maatkit toolkit (http://www.maatkit.org) has a very useful command for systems that have more than a few users. The mk-show-grants command allows you display a list of all the users on the system. In addition, it is very easy to pipe the output to a ﬁle and then store the ﬁle in a version control system or use just simply copy it to another server and use the ﬁle to set up the same permissions on another server. Here is a sample of the mk-show-grants command on a system with more users. Password hashes have been removed: shell> ./mk-show-grants -u root -ppassword -- Grants dumped by mk-show-grants @VERSION@ -- Dumped from server Localhost via UNIX socket, MySQL 6.0.8-alpha at 2009-01-06 01:48:50 -- Grants for ’monitoring’@’10.%’ GRANT REPLICATION SLAVE ON *.* TO ’monitoring’@’10.%’ IDENTI- FIED BY PASSWORD ’PASSWORD_HASH’; -- Grants for ’monitoring’@’localhost’ GRANT ALL PRIVILEGES ON *.* TO ’monitoring’@’localhost’ IDENTI- FIED BY PASSWORD ’PASSWORD_HASH’; GRANT USAGE ON *.* TO ’company’@’%.company.com’ IDENTIFIED BY PASSWORD ’PASSWORD_HASH’; GRANT ALL PRIVILEGES ON `company_production`.* TO ’company’@’%. company.com’ WITH GRANT OPTION; -- Grants for ’webuser’@’10.%’ GRANT USAGE ON *.* TO ’webuser’@’10.%’ IDENTIFIED BY PASSWORD ’PASSWORD_HASH’; GRANT ALL PRIVILEGES ON `company_production`.* TO ’webuser’@’10.%’ WITH GRANT OPTION; -- Grants for ’webuser’@’localhost’ GRANT USAGE ON *.* TO ’webuser’@’localhost’ IDENTIFIED BY PASSWORD ’PASSWORD_HASH’; GRANT ALL PRIVILEGES ON `webuser_load_test`.* TO ’webuser’@ 486