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

0
83
lượt xem
24

Mô tả tài liệu

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)

Lưu

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

1. 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
2. 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
3. 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
4. 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 5. 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
6. 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
10. 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
20. 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