| There is no undo feature in MySQL. If you issue a query such as DROP DATABASE test, all of the tables stored in test are instantly deleted. If this query was issued by mistake, you can only recover the data from logs, from backups, or (in some instances) by using a data recovery tool. Here are a few tips to help you work more safely with your MySQL databases. Most of these tips are geared for data on the scale of thousands of rows making up tens of megabytes in size or less. For larger installations, get expert help! Don't grant users (including yourself) more permissions than they need. For example, if you are running some ad hoc queries on a database for the finance department, don't use the MySQL root account. Instead, create a user who only has permission to run the needed SELECT queries and then log in as that user. This might seem paranoid, but it can save a lot of hassle in the long run. For more on managing users and accounts, see Chapter 6, "User Management and Security." Keep up-to-date backups. Although the topic of backups is outside of the scope of this book, it is important that your server administrator keep daily backups of the data stored in MySQL. Make ad hoc backups as needed. Even if your system administrator does make frequent backups of MySQL, there might be times when you want to make your own backup of the data. Consider making your own backup before you make any radical changes to the data or when you are using unfamiliar features. See Chapter 10, "Troubleshooting and Emergencies," to learn how to use the mysqldump utility to make ad hoc backups. Ask your server administrator to start your MySQL server with the --safe-updates option. Alternatively, you can set this option yourself (but only temporarily) by running the following query: SET SQL_SAFE_UPDATES=1; Amusingly, a synonym for this option is --i-am-a-dummy. This option prevents DELETE and UPDATE queries that do not contain a LIMIT or WHERE clause from being run. The danger of DELETE and UPDATE queries without LIMIT or WHERE clauses is that these kinds of queries are applied to the entire table. For example, DELETE FROM foo; deletes every row in table foo. If working on a table managed by MyISAM or another non-transactional storage engine, test your queries on a copy of the table before running them on the real table. For example, you could use this set of commands to copy a table: CREATE TEMPORARY TABLE test LIKE book; INSERT test SELECT * FROM book; Then run your commands on the test table, double-check that they work as anticipated, and then run them on table book. This approach only works with a moderate-sized database. If you have hundreds of thousands or millions of rows of data, it might take too much of your server's resources to test in this fashion. If you are using tables managed by a transactional storage engine such as InnoDB or BerkeleyDB, see the following phrase for instructions on how to safely test queries inside of a transaction.
If you do accidentally destroy valuable data, see Chapter 10. |