Chapter 14

1. Chapter 14 Database Maintenance, Backups, and Replication Mysql Developers Library. Dubois 4th ed

2. Principles of Preventive Maintenance • Prepare in advance against database problems • Enable Auto-recovery capabilities • Set up scheduled preventive maintenance to perform table checking periodically. • Set up a database backup schedule. Mysql Developers Library. Dubois 4th ed

3. Utilities included in MySQL • When the server starts, transactional storage engines can perform auto-recovery. • You can also enable automatic table repair for the MyISAM storage engine. • These capabilities are useful when the server restart follows a crash. Mysql Developers Library. Dubois 4th ed

4. Utilities included in MySQL • Use the mysqldump or mysqlhotcopy program to make backups of your databases • Perform table maintenance operations on demand, use SQL statements such as CHECK TABLE and REPAIR TABLE. • For a command-line interface to these statements, use the mysqlcheck program. The myisamchk utility also can check tables for problems and perform various corrective actions. Mysql Developers Library. Dubois 4th ed

5. Utilities included in MySQL • Mysqlcheck and mysqldump, work in cooperation with the server. • They connect as clients to the server and issue SQL statements that instruct the server what kind of table maintenance operation to perform. • myisamchk is an independent standalone program that operates directly on the files used to represent tables. Mysql Developers Library. Dubois 4th ed

6. Utilities included in MySQL • You must take steps to prevent myisamchk and the server from interfering with each other. • For example, if you're repairing a table with myisamchk, it's necessary to keep the server from trying to write to the table at the same time. • Failure to do so can result in much worse problems than those you're trying to correct! Mysql Developers Library. Dubois 4th ed

7. Performing Database Maintenance with the Server Running • To perform consistency checks or table repairs on a MyISAM table, • issue a CHECK TABLE or REPAIR TABLE statement (or invoke the mysqlcheck program) and let the server do the work. • The server will access the .frm, .MYD, and .MYI files that represent the table. • This is the best approach to take if possible Mysql Developers Library. Dubois 4th ed

8. Performing Database Maintenance with the Server Running • On MyISAM table is to invoke the myisamchk utility, • Opens the table files directly without going through the server. Mysql Developers Library. Dubois 4th ed

9. Performing Database Maintenance • When using the MyISAMchk utility • The most effective way to prevent the server from interfering with you is to shut it down. Mysql Developers Library. Dubois 4th ed

10. Locking protocols • When performing operations on a running server use a locking protocol. The server has two kinds of locking: Mysql Developers Library. Dubois 4th ed

11. Locking protocols • The server uses internal locking to keep requests from different clients from getting mixed up with each other • to keep one client's SELECT query from being interrupted by another client's UPDATE statement. Mysql Developers Library. Dubois 4th ed

12. Locking – operating systems • The server can use external locking to prevent other programs from modifying table files while it's using them. • This is based on the locking capabilities available for your operating system at the filesystem level. Mysql Developers Library. Dubois 4th ed

13. Locking Individual Tables for Read-Only • The locking protocol to use depends on whether you need read-only access or read/write access to the table's files: • For operations that just check or copy the files, read-only access is sufficient. • For operations that modify the files, such as table repair or replacing damaged files with good ones, you need read/write access. Mysql Developers Library. Dubois 4th ed

14. Locking protocols • You must perform all the LOCK, FLUSH, and UNLOCK statements from within a single session with the server. Mysql Developers Library. Dubois 4th ed

15. Locking tables • One easy way to perform the locking procedures is to keep two windows open. This enables you to leave mysql running in one window while you work with the table files in the other. Mysql Developers Library. Dubois 4th ed

16. Locking only on MyIsam tables • The internal-locking techniques described here for locking individual tables apply only when working with table files for storage engines such as MyISAM that represent each table with its own unique files. • They do not apply to storage engines such as InnoDB or Falcon that store information about multiple tables in a given file. Mysql Developers Library. Dubois 4th ed

17. Locking table – Read-only • Used when coping of files or checking them for inconsistencies. • The server will prevent other clients from modifying the table. Mysql Developers Library. Dubois 4th ed

18. Syntax for lock read-only – step 1 • % mysqldb_name mysql> LOCK TABLE tbl_name READ; mysql> FLUSH TABLE tbl_name; Mysql Developers Library. Dubois 4th ed

19. lock read-only – step 2 • % myisamchktbl_name • current directory is the table's database directory Mysql Developers Library. Dubois 4th ed

20. lock read-only – step 3 • mysql> UNLOCK TABLE; Mysql Developers Library. Dubois 4th ed

21. Locking All Databases for Read-Only Access • mysql> FLUSH TABLES WITH READ LOCK; mysql> SET GLOBAL read_only = ON; Mysql Developers Library. Dubois 4th ed

22. Locking All Databases for Read-Only Access • changes to be made once again, use these statements • mysql> SET GLOBAL read_only = OFF; mysql> UNLOCK TABLES; Mysql Developers Library. Dubois 4th ed

23. Locking All Databases for Read-Only Access • While the tables are locked this way, other clients can read from them but cannot make changes. Mysql Developers Library. Dubois 4th ed

24. Replication – master slave relationship • In a replication relationship, • one server acts as the master • another server acts as the slave. • Each server must be assigned a unique replication ID. • There can be multiple slaves per master. • A slave can serve as a master to another slave, thus creating a chain of replication servers. • Multiple-master replication to a slave is also possible, but is trickier to set up Mysql Developers Library. Dubois 4th ed

25. Replication – slave server • Each slave must begin with its databases synchronized to those of its master. • That is, any database to be replicated on the slave must be an identical copy of the master database when replication begins. • After that, updates that are made on the master server propagate to the slave. • Updates should not be made directly to the replicated databases on the slave. Mysql Developers Library. Dubois 4th ed

26. Replication – bin logs • Communication of updates is based on the master server's binary logs, which is where updates are recorded that are to be sent to the slaves. • Binary logging therefore must be enabled on the master server. Stored updates in the binary log are called "events." Mysql Developers Library. Dubois 4th ed

27. Replication - permissions • Each slave server must have permission to connect to the master and request updates. • When a slave connects to its master, it tells the master how far into the master's binary log it had progressed when it last connected. • This progress is measured in terms of replication coordinates: A binary log filename and position within that file. • The master then begins sending to the slave those events in the binary log that occurred after the given coordinates. • When the slave has read all available events, it pauses and waits for more. Mysql Developers Library. Dubois 4th ed

28. Replication – Master Bin logs • As new updates occur on the master server, it writes them to its binary log for later transmission to its slaves. • The master server handles connected slaves much as it handles regular clients, and connected slaves count against the limit set by the max_connections system variable. Mysql Developers Library. Dubois 4th ed

29. Repliaction – Slave server • On the slave side, the server uses two threads to handle replication duties. • The I/O thread receives events to be processed from the master server and writes them to the slave's relay log. • The SQL thread reads events from the relay log and executes them. • The relay log serves as the means by which the I/O thread communicates changes to the SQL thread. • As each relay log file is processed completely, the slave removes it automatically. • The I/O and SQL threads operate independently, so each can be stopped or started separately from the other. • I/O thread can continue to read events from the master server while you stop the SQL thread so that no updates occur in the slave's databases while you make a backup. Mysql Developers Library. Dubois 4th ed

30. Mysql Developers Library. Dubois 4th ed

31. Mysql Developers Library. Dubois 4th ed

32. Mysql Developers Library. Dubois 4th ed

33. Mysql Developers Library. Dubois 4th ed

34. Mysql Developers Library. Dubois 4th ed

35. Mysql Developers Library. Dubois 4th ed

36. Mysql Developers Library. Dubois 4th ed

37. Mysql Developers Library. Dubois 4th ed

38. Mysql Developers Library. Dubois 4th ed

39. Mysql Developers Library. Dubois 4th ed

40. Mysql Developers Library. Dubois 4th ed