MySQL Administrator’s Bible- P16

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

Thêm vào BST

Báo xấu

97
lượt xem 9
download

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

MySQL Administrator’s Bible- P16: Welcome to the MySQL Administrator’s Bible. Like all books in the Bible series, you can expect to find both hands-on tutorials and real-world practical application information, as well as reference and background information that provide a context for what you are learning.

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- P16

The MySQL Data Dictionary 21 mysql_version.h is not in the source package, but it is in the mysql-devel package (the compilation process creates mysql_version.h later on). Unfortunately mysql_priv.h is only in the source package, not the mysql-devel package, so you need to download both the source and the mysql-devel packages. On an abstract level, making a custom system view in the INFORMATION_SCHEMA database requires: ■ The ﬁeld deﬁnitions of the system view (that is, the structure of the system view). In our example, we deﬁne a system view named MYSQL_HELLO with a ﬁeld deﬁned as HELLO VARCHAR(64) NOT NULL DEFAULT `’. ■ The function that populates the system view upon request (that is, the values in the system view). We will deﬁne a function to store the string plugin: hello, information_schema!!! into the HELLO ﬁeld of our MYSQL_HELLO system view. On a more technical level, to create the custom system view, you need to instantiate the ST_SCHEMA_TABLE struct and deﬁne two members. One member, field_info, is an array of ST_FIELD_INFO structures, which deﬁne the ﬁelds in your system view. The values in the ST_FIELD_INFO structure are deﬁned in the source code in the sql/table.h header ﬁle and explained in Table 21-3. TABLE 21-3 ST_FIELD_INFO Structure Code Explanation Example Value const char* Field name ‘‘HELLO’’ field_name; uint field_length; In string-type ﬁelds, the maximum 64 in our example, a number of characters. In other ﬁelds, string-type ﬁeld the display length. enum enum_field_types Field data type MYSQL_TYPE_VARCHAR field_type; int value; Field value 0 uint field_flags; 0 means NOT NULL and SIGNED. It 0 can be overridden by MY_I_S_MAYBE_NULL, MY_I_S_UNSIGNED, or both constants, separated by | (bitwise or). const char* old_name; Old ﬁeld name ‘‘Hello’’ uint open_method; How the table is opened. One of SKIP_OPEN_TABLE SKIP_OPEN_TABLE, OPEN_FRM_ONLY, or OPEN_FULL_TABLE. 717
Part IV Extending Your Skills Our example contains the following code to deﬁne the custom system view: 8. static ST_FIELD_INFO mysql_is_hello_field_info[]= 9. { 10. {"HELLO", 64, MYSQL_TYPE_VARCHAR, 0, 0, "Hello", SKIP_OPEN_TABLE}, 11. {NULL, 0, MYSQL_TYPE_NULL, 0, 0, NULL, 0} 12. }; The ﬁrst structure in the array is the ﬁeld we are creating. The last structure is an indication that the array is complete, and must be present. We named the array mysql_is_hello_field_ info, which stands for ‘‘the ﬁeld information of the hello system view in the mysql information schema.’’ The next line of code is: 13. int schema_table_store_record(THD *thd, TABLE *table); This line declares the schema_table_store_record function, which we will use later to store a row in a system view. The second member of ST_SCHEMA_TABLE that we need to deﬁne is the function that populates the system view, called the fill_table: 14. int mysql_is_hello_fill_table( 15. THD *thd 16. , TABLE_LIST *tables 17. , COND *cond 18. ) 19. { 20. int status; 21. CHARSET_INFO *scs= system_charset_info; 22. TABLE *table= (TABLE *)tables->table; 23. const char *str = "plugin: hello, information_schema!!!"; 24. table->field[0]->store( 25. str 26. , strlen(str) 27. , scs 28. ); 29. status = schema_table_store_record( 30. thd 31. , table 32. ); 33. return status; 34. } In lines 15–17 we see THD *thd again, pointing to the current session, and TABLE_LIST *tables, which is an array of table instantiations of our ‘‘Hello’’ system view. COND *cond is a condition that could be used by our plugin for ﬁltering or optimization, though we will not use it in this example. 718
The MySQL Data Dictionary 21 The status variable is initialized as an int on line 20. At the end of the function (line 33), status is returned; a value of 1 indicates an error, and a value of 0 indicates success. Then on line 21, CHARSET_INFO *scs is initialized with the character set information. This is needed to properly store the ﬁeld of our system view. On line 22, TABLE *table is set as the handler to the instantiation of our system view. We ini- tialize the char *str on line 23 with the string we will store in our system view. This means that when we ﬁnally install the plugin, we will see str in our system view: mysql> SELECT * FROM INFORMATION_SCHEMA.MYSQL_HELLO; +--------------------------------------+ | HELLO | +--------------------------------------+ | plugin: hello, information_schema!!! | +--------------------------------------+ 1 row in set (0.00 sec) Lines 24–28 store the string str, its length, and the character set scs (deﬁned on line 21) into the ﬁrst ﬁeld (field[0]) of the row. Lines 29–32 store the row into the instantiation of our system view for the current session. If the function that stores the row is successful, status gets a value of 0. If there was an error, status gets a value of 1. status is returned on line 33, thus giving the mysql_is_hello_fill_table function a return value of 0 if it is successful and 1 if there was an error. So far we have deﬁned a system view in an ST_FIELD_INFO array and a fill_table function to populate the system view. We now need to create a plugin function to use these: 35. static int mysql_is_hello_plugin_init(void *p) 36. { 37. ST_SCHEMA_TABLE *schema= (ST_SCHEMA_TABLE *)p; 38. schema->fields_info= mysql_is_hello_field_info; 39. schema->fill_table= mysql_is_hello_fill_table; 40. return 0; 41. } The plugin_init function initializes the plugin as an INFORMATION SCHEMA plugin with the fields_info and fill_table we deﬁned previously. Because we have a simple plugin_init function, we have a simple plugin_deinit function as well: 42. static int mysql_is_hello_plugin_deinit(void *p) 43. { 44. return 0; 45. } In our example there is nothing that needs to be done during the plugin_deinit, so we sim- ply return 0, indicating the function was successful. In a more complex example, there may be memory or other resources allocated in the plugin_init function that should be deallocated in the plugin_deinit function. 719
Part IV Extending Your Skills At this point, we still have not created the plugin. However, we have most of the parts we need to make an INFORMATION_SCHEMA plugin, and can now create the code to deﬁne the plugin itself: 46. struct st_mysql_information_schema mysql_is_hello_plugin= 47. { MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION }; 48. mysql_declare_plugin(mysql_is_hello) 49. { 50. MYSQL_INFORMATION_SCHEMA_ PLUGIN, /* type constant */ 51. &mysql_is_hello_plugin, /* type descriptor */ 52. "MYSQL_HELLO", /* Name */ 53. "Roland Bouman (http:// rpbouman.blogspot.com/)", /* Author */ ___________________________________________________________________ 54. "Says hello.", /* Description */ 55. PLUGIN_LICENSE_GPL, /* License */ 56. mysql_is_hello_plugin_init, /* Init function */ 57. mysql_is_hello_plugin_deinit, /* Deinit function */ 58. 0x0010, /* Version (1.0) */ 59. NULL, /* status variables */ 60. NULL, /* system variables */ 61. NULL /* config options */ 62. } 63. mysql_declare_plugin_end; Lines 46–47 deﬁne the type descriptor for the plugin, which is set as part of the plugin in line 51. Line 50 deﬁnes the type of plugin as an INFORMATION_SCHEMA plugin. Lines 52–55 deﬁne metadata that appear after plugin installation in the PLUGINS system view as the PLUGIN_NAME, PLUGIN_AUTHOR, PLUGIN_DESCRIPTION, and PLUGIN_LICENSE ﬁelds, respectively. Lines 56–57 point to the previously created plugin_init and plugin_deinit functions, and line 58 deﬁnes the version metadata, which corresponds to the value of PLUGIN_VERSION in the PLUGINS system view. Lines 59–61 are pointers to structures containing the status variables, system variables, and conﬁguration options. Our plugin does not have any of these, so we deﬁne them as NULL. The entirety of the code is: #include #include #include 720
The MySQL Data Dictionary 21 #include #include #include #include static ST_FIELD_INFO mysql_is_hello_field_info[]= { {"HELLO", 64, MYSQL_TYPE_VARCHAR, 0, 0, "Hello", SKIP_OPEN_TABLE}, {NULL, 0, MYSQL_TYPE_NULL, 0, 0, NULL, 0} }; int schema_table_store_record(THD *thd, TABLE *table); int mysql_is_hello_fill_table( THD *thd , TABLE_LIST *tables , COND *cond ) { int status; CHARSET_INFO *scs= system_charset_info; TABLE *table= (TABLE *)tables->table; const char *str = "plugin: hello, information_schema!!!"; table->field[0]->store( str , strlen(str) , scs ); status = schema_table_store_record( thd , table ); return status; } static int mysql_is_hello_plugin_init(void *p) { ST_SCHEMA_TABLE *schema= (ST_SCHEMA_TABLE *)p; schema->fields_info= mysql_is_hello_field_info; schema->fill_table= mysql_is_hello_fill_table; return 0; } static int mysql_is_hello_plugin_deinit(void *p) { return 0; } 721
Part IV Extending Your Skills struct st_mysql_information_schema mysql_is_hello_plugin= { MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION }; mysql_declare_plugin(mysql_is_hello) { MYSQL_INFORMATION_SCHEMA_ PLUGIN, /* type constant */ &mysql_is_hello_plugin, /* type descriptor */ "MYSQL_HELLO", /* Name */ "Roland Bouman (http:// rpbouman.blogspot.com/)", /* Author */ "Says hello.", /* Description */ PLUGIN_LICENSE_GPL, /* License */ mysql_is_hello_plugin_init, /* Init function */ mysql_is_hello_plugin_deinit, /* Deinit function */ 0x0010, /* Version (1.0) */ NULL, /* status variables */ NULL, /* system variables */ NULL /* config options */ } mysql_declare_plugin_end; Compiling the plugin Now that the code has been created, we need to compile the plugin as a dynamic shared object and then load it into mysqld. In our example we will save the source code in a ﬁle named mysql_is_hello.cc, and use g++ to compile the source. Table 21-4 shows the compile ﬂags we will use and their meanings. So the complete compile statement is: shell> g++ -DMYSQL_DYNAMIC_PLUGIN –Wall –shared \ -I/path/to/mysql-6.0.x-source/include \ -I/path/to/mysql-6.0.x-source/sql \ -I/usr/include/mysql \ -o mysql_is_hello.so mysql_is_hello.cc The include paths were not speciﬁed for stdlib.h and ctype.h, because they are likely in your default include path. On our machines, we found these ﬁles in /usr/include, which was in the default include path. If you get errors such as: 722
The MySQL Data Dictionary 21 error: stdlib.h: No such file or directory error: ctype.h: No such file or directory you should specify the include path for stdlib.h and ctype.h with another -I ﬂag. Some architectures, such as AMD64, require shared libraries to be compiled with the –fPIC ﬂag. If your architecture requires this, you will see output similar to the following when you try to compile: /usr/bin/ld: /tmp/ccNXOEqH.o: relocation R_X86_64_32S against `a local symbol’ can not be used when making a shared object; recompile with –fPIC /tmp/ccNXOEqH.o: could not read symbols: Bad value collect2: ld returned 1 exit status In this case, simply add –fPIC to the list of compile ﬂags and try to compile again. TABLE 21-4 g++ Compile Flags for Compiling a Plugin as a Dynamic Shared Object Compile Flag Meaning -DMYSQL_DYNAMIC_ -D indicates a constant; the constant is PLUGIN MYSQL_DYNAMIC_PLUGIN, which is required to compile the plugin as a dynamic shared object for mysqld. -Wall -W indicates warnings should be shown; all speciﬁes showing all warnings. -shared Compile as a shared, dynamically linked library. -I/path/to/mysql- The include path for the my_global.h, my_dir.h, and 6.0.x-source/include mysql/plugin.h header ﬁles. -I/path/to/mysql- The include path for the mysql_priv.h header ﬁle. 6.0.x-source/sql -I/usr/include/mysql The include path for the mysql_version.h header ﬁle. -o mysql_is_hello.so The output ﬁle. The extension .so is traditionally used for shared objects. If all goes well, a dynamic shared plugin will be created with a ﬁlename of mysql_is_ hello.so. 723
Part IV Extending Your Skills Installing the plugin MySQL plugins are installed in mysqld using a MySQL extension to SQL. Thus, mysqld must be running, and no downtime is required to install a plugin. For security purposes, mysqld only looks for plugin libraries in the directory speciﬁed by the system variable plugin_dir: mysql> SHOW VARIABLES LIKE ’plugin_dir’; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | plugin_dir | /usr/lib64/mysql/plugin | +---------------+-------------------------+ 1 row in set (0.01 sec) For our example, we make sure the plugin directory exists, copy the plugin to the mysqld plu- gin directory, and ensure that the directory and plugin have appropriate permissions so mysqld can load the plugin: shell> mkdir /usr/lib64/mysql/plugin/ shell> cp mysql_is_hello.so /usr/lib64/mysql/plugin shell> sudo chown –R mysql:mysql /usr/lib64/mysql/plugin/ Now we use the INSTALL PLUGIN statement to install our plugin. The syntax of INSTALL PLUGIN is: INSTALL PLUGIN plugin_name SONAME ’plugin_library’; The plugin_name was set in the plugin metadata (on line 52 of our code). The plugin_library is the name of the compiled plugin ﬁle (the complete path to the plugin ﬁle is plugin_dir/plugin_library): mysql> INSTALL PLUGIN MYSQL_HELLO SONAME ’mysql_is_hello.so’; Query OK, 0 rows affected (0.00 sec) And our plugin has successfully been installed: mysql> SELECT * FROM INFORMATION_SCHEMA.MYSQL_HELLO; +--------------------------------------+ | HELLO | +--------------------------------------+ | plugin: hello, information_schema!!! | +--------------------------------------+ 1 row in set (0.00 sec) We can now see our plugin in the mysql.plugin system table and the INFORMATION_ SCHEMA.PLUGINS system view: mysql> SELECT * FROM mysql.plugin -> WHERE name=’MYSQL_HELLO’\G 724
The MySQL Data Dictionary 21 *************************** 1. row *************************** name: MYSQL_HELLO dl: mysql_is_hello.so 1 row in set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS -> WHERE PLUGIN_NAME=’MYSQL_HELLO’\G *************************** 1. row *************************** PLUGIN_NAME: MYSQL_HELLO PLUGIN_VERSION: 0.21 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: INFORMATION SCHEMA PLUGIN_TYPE_VERSION: 60006.0 PLUGIN_LIBRARY: mysql_is_hello.so PLUGIN_LIBRARY_VERSION: 1.0 PLUGIN_AUTHOR: Roland Bouman (http://rpbouman.blogspot.com/) PLUGIN_DESCRIPTION: Says hello. PLUGIN_LICENSE: GPL 1 row in set (0.00 sec) To uninstall the plugin, run UNINSTALL PLUGIN plugin_name: mysql> UNINSTALL PLUGIN MYSQL_HELLO; Query OK, 0 rows affected (0.00 sec) The plugin is loaded when the INSTALL PLUGIN is run. If you want to change the plugin you have to uninstall the plugin, change the plugin ﬁle, and re-install the plugin. If the plugin ﬁle changes or is removed while installed on a running server, mysqld will most likely crash. Summary This chapter described the information contained in the MySQL data dictionary, including: ■ All of the metadata in the system views of the INFORMATION_SCHEMA database ■ Many SHOW statements (see Chapter 4 for the remainder of the SHOW statements that show metadata) ■ Many of the system tables in the mysql database ■ Creating custom metadata as INFORMATION_SCHEMA plugins 725
Scaling and High Availability Architectures I n these days of an online world where applications need to run all the time, and need to run quickly, scaling and high availability are very IN THIS CHAPTER important. MySQL’s cost and three basic tenets of fast, reliable, and Scaling MySQL easy-to-use have redeﬁned the database market. These days, those without advanced training and large amounts of money can create successful appli- Synchronizing data cations with database backends. In many ways, you can scale more and have a higher availability just by using MySQL instead of another database Automating failover product. Scaling and high availability are both very important to a successful appli- cation. Scaling means an application works with acceptable speed when there are one hundred users or one hundred million users. High availabil- ity means that the application is up and running for much of the time. Not all architectures that provide scaling will provide high availability, and vice versa. A simple way to scale is to upgrade your hardware to a conﬁguration with faster CPUs and disk, and more CPUs and RAM. This is costly, and only scales to a certain point. This is called scaling up, or vertical scaling, because you are upgrading the hardware. Horizontal scaling, or scaling out, increases the maximum capacity of your database architecture by adding more database servers. One of the best ways to scale is to make sure your queries are running as quickly as possible. If a database can handle 2,000 writes per second, there is no architectural solution that will allow the database to handle more than that. If a write query can run faster, the same database will be able to handle more writes per second. Query optimization, schema optimization, 727
Part IV Extending Your Skills data reduction, and server tuning are ways to make a query run faster. We cover data reduc- tion and query and schema optimization in Chapter 18. Server tuning is covered in Chapter 10. If your queries are running as fast as they possibly can, but there is a large amount of data to wade through, you may want to partition your data. We discuss different ways to partition in Chapter 15. This chapter goes through a number of technologies, which can be used for high availability or scalability: ■ Scalability ■ Replication ■ MySQL Proxy ■ MySQL Cluster ■ Connection pooling ■ memcached ■ High Availability (Data Synchronization) ■ Replication ■ SAN ■ DRBD ■ MySQL Cluster ■ High Availability (Automated Failover) ■ MySQL Proxy ■ Linux-HA Heartbeat ■ MySQL Cluster As with all difﬁcult problems, there is no easy solution that will solve every different type of scaling and high availability issue for most people. Many web-based application administrators have found that users are using the application differently than they had anticipated. Changing schema and adding features causes the architecture design to change, so even if you plan the best solution for your application, the requirements may change based on how the application is used. Through measurement, planning, ﬂexibility, and risk assessment, high availability and scalability can be achieved and maintained. Replication The basics of replication, and how to set up replication, are discussed in detail in Chapter 16. Replication allows copies of data to be kept asynchronously on different servers. In traditional master/slave replication, where a master database server replicates to a slave database server, all writes must occur on the master database server. A write to a master server needs to propagate 728
Scaling and High Availability Architectures 22 to any slaves, so there is no real way to reduce the number of writes to a machine using repli- cation. However, reads can be done on either the master or the slave, and because no data is changed, nothing needs to propagate. If your application has a high ratio of reads to writes, you can move some or all of the load gen- erated by reads a different server. Thus, replication can be a scaling technique. Replication can also be a technique for high availability. If a master server cannot be reached, a slave can be promoted to be a master server. The slave is already loaded with the data, so there is no need to spend time restoring from a backup. One read slave A machine can have many slaves, but a machine can have only one master. The traditional mas- ter/slave replication architecture can be represented by Figure 22-1. FIGURE 22-1 db1 db2 If db1 cannot be accessed, an administrator will promote db2 to a master server. This is often called failover — a server failed, so the slave will be promoted. Some administrators use the term switchover when they are promoting a slave when the master has not failed, for instance if the master needs to be taken ofﬂine for maintenance. Unfortunately, MySQL does not ship with a tool to automate slave promotion; however, automation is possible using scripts and heartbeat tools. MySQL Proxy or Linux-HA Heartbeat (described later in this chapter) can be used with replication to achieve an automated failover solution. Promoting a new master Promoting a slave is not a difﬁcult task, though many steps are involved. Gather information ■ Determine if the slave to be promoted is read-only ■ Determine if the slave to be promoted has binary logging enabled ■ Decide if you want to reset the binary logs on the slave to be promoted — this will delete any previous binary logs and reset the binary log to the ﬁrst binary log (default is mysql-bin.000001) ■ Check for other slaves that will need to be changed to replicate the new master 729
Part IV Extending Your Skills Make sure the slave is caught up applying its relay logs When the information has been gathered, make sure the slave’s SQL thread has caught up to the slave’s I/O thread. That is, make sure all the statements in the relay logs have been applied. Compare the parameters Read_Master_Log_Pos, the position at which the I/O thread has read from the master’s binary logs, and Exec_Master_Log_Pos, which is the position at which the SQL thread has ﬁnished applying the logs: mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Reconnecting after a failed master event read Master_Host: 192.168.1.1 Master_User: master_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000051 Read_Master_Log_Pos: 3253467 Relay_Log_File: slave-relay-bin.000072 Relay_Log_Pos: 3253613 Relay_Master_Log_File: mysql-bin.000051 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3253467 Relay_Log_Space: 3253782 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2013 Last_IO_Error: error reconnecting to master ’master_user@192.168.1.1:3306’ - retry-time: 60 retries: 86400 730
Scaling and High Availability Architectures 22 Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) In this sample, Read_Master_Log_Pos and Exec_Master_Log_Pos are the same value (3253467). This shows that the slave has applied all of the logs it has read from the mas- ter, and is ready to become a master. If the Exec_Master_Log_Pos is less than the Read_Master_Log_Pos, you will need to wait until the slave SQL thread catches up before you promote the slave to a master; otherwise there will have been transactions that completed on the old master that do not appear on the new master. Stop and reset the slave process Stop the slave with the STOP SLAVE command, and then ensure the slave will not accidentally become re-enabled and try to read from the master by clearing out the slave information. You can do this with the RESET SLAVE command: mysql> RESET SLAVE; Query OK, 0 rows affected (0.52 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.1 Master_User: master_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: slave-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 126 Until_Condition: None Until_Log_File: 731
Part IV Extending Your Skills Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2013 Last_IO_Error: error reconnecting to master ’master_user@192.168.1.1:3306’ - retry-time: 60 retries: 86400 Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) Note that though the connection parameters (Master_Host, Master_User, and Master_ Port) have not changed, the Slave_IO_State has been reset, and so have the master log and relay log parameters (Master_Log_File, Read_Master_Log_Pos, Relay_Log_File, Relay_Log_Pos, Relay_Master_Log_File, Exec_Master_Log_Pos, and Relay_Log_ Space). If you look at the data directory, you will note that the master.info ﬁle has disap- peared, and if you search where the relay logs are kept, you will note that the relay-log.info ﬁle and relay logs are gone. Although those ﬁles have been deleted, MySQL has the connection information cached in memory. To make the slave information truly disappear, the mysqld process needs to be restarted. You can ﬁnd more information on the how the master.info and relay-log.info ﬁles relate to replication in Chapter 16. Change conﬁguration parameters If the slave is set as read_only, you will want to change that so writes can occur once it is pro- moted to be the new master. If the slave does not have binary logging enabled, you will need to enable it with the bin-log parameter. Though you can change the read_only system vari- able dynamically, changing the bin-log parameter requires a mysqld restart after updating the my.cnf or my.ini conﬁguration ﬁle. The read_only parameter is global, so only a user with the SUPER privilege can modify it. If there are transactions that have locked tables (explicit locks or implicit locks because they are changing data or schema), setting read_only dynamically will wait until the locks are released before it is successful. If you hold a table lock and try to set the read_only parameter, an error will occur. Restart If necessary, restart the mysqld process, checking the error logs to ensure the process starts without any problems. Note that the slave conﬁguration is truly gone now: 732
Scaling and High Availability Architectures 22 mysql> show slave status\G Empty set (0.00 sec) RESET MASTER If you prefer, you can reset the binary logs for your new master. This deletes all cur- rent and previous binary logs, and resets the binary log to the ﬁrst binary log (default is mysql-bin.000001), log position 4. Change any remaining slaves to replicate off the new master Any other slaves that were replicating the old master now need to be changed to replicate the new master. This involves a STOP SLAVE and CHANGE MASTER TO command. In the event that the logs have been reset, the MASTER_LOG_FILE and MASTER_LOG_POS variables are already known as the ﬁrst binary log ﬁle, log position 4. In the event that the logs have not been reset, run the SHOW MASTER STATUS command on the new master: mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000235 | 4 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) In this case, the value of MASTER_LOG_FILE is mysql-bin.000235 and the value of MASTER_ LOG_POS is 4. The log position value should be fairly low, because the mysqld process on the new master was just restarted, which rotates the binary logs. You may also choose to have the old master replicate the new master. In this case, the same procedure applies: use the CHANGE MASTER TO command to replicate off the new master. Chapter 16 has more information about setting up replication. Slave promotion summary To promote a slave to a master: 1. Gather information. 2. Make sure the slave is caught up applying its relay logs. 3. Stop the slave process with STOP SLAVE. 4. Ensure the old slave is no longer a slave by issuing RESET SLAVE. 5. If necessary: Remove the read_only restriction and enable log-bin in the conﬁguration ﬁle. 6. If necessary: Restart the mysqld process and double-check that the slave parameters have disappeared. 733
Part IV Extending Your Skills 7. If desired: RESET MASTER. 8. Change any remaining slaves to replicate off the new master (if using many read slaves). Many read slaves With only one slave, there is a decision to be made. Is db2 meant for scaling, or for high avail- ability? If db2 is meant for high availability, it should be unused by the application until it is time to be promoted to be the master. If db2 is meant for scalability, there is no failover server, because both the master and slave are necessary to handle the volume of read queries. As well, there may be so many read queries that one slave cannot process them all. For these cases, more than one read slave can be deﬁned. One master database server can have many slaves replicating directly from it, as in Figure 22-2. FIGURE 22-2 db2 db1 db3 This is the simplest method for having more than one slave of a master. Read queries can go to either db1, db2, or db3, whereas write queries remain on db1 and propagate to db2 and db3. There are no tricks to setting up this sort of replication architecture; merely go through the pro- cess of creating db2 as a slave of db1 as described in Chapter 17, and then repeat the process to create db3 as a slave of db1. Perhaps db2 and db3 are both for scalability, accepting many read queries to take the load off db1. Alternatively, db2 could be for scalability, accepting read queries, and db3 could be for high availability, ready to be promoted to the master should the need arise. Some applications use a replication relay slave, as in Figure 22-3. FIGURE 22-3 db3 db1 db2 db4 One reason for using this architecture is that if there are 10 or more slaves, it can be easier to have the slaves connect to a non-master server to avoid the small extra bit of load that each 734
Scaling and High Availability Architectures 22 slave produces. For large setups with hundreds of slaves in a replication farm, replication relay slaves are needed. Another reason for this type of setup is that if something happens to db1, and db2 is promoted to be a master, there is no need to change the replication setup for db3 and db4, because they are already replicating db2. One of the biggest drawbacks of this architecture is that problems with slave lag may be compounded. For this setup, db3 and db4 are set up as slaves of db2 in the usual way. To set up db2 as a relay slave, it is set up as a slave of db1 in the usual way, with one addition in the conﬁguration setup: log-slave-updates. This will ensure that the binary logs for db2 contain the changes that replicate from db1. Master/master replication Master/master replication, also called dual-master replication, is a way to scale reads. As with all replication architectures, it is not a way to scale writes. However, with master/master replica- tion, either of the mysqld processes can accept writes, which will propagate to the other master. Figure 22-4 shows what a master/master replication architecture looks like. FIGURE 22-4 db1 db2 You set up master/master replication by setting up db1 to be a slave of db2, and setting up db2 to be a slave of db1. In addition, some extra parameters need to be set to ensure data integrity: ■ auto_increment_offset ■ auto_increment_increment Having writes to more than one server can cause problems with auto_increment ﬁelds. If an auto_increment ﬁeld has used up to number 100, and there’s a write to db1 and db2 at the same time, both will use 101 as the ﬁeld value. Replication will break on both servers because of duplicate key errors. The auto_increment_offset parameter is set with a number indicating at what number the auto_increment ﬁeld should start. The auto_increment_increment is set with a number indicating by how much the auto_increment value should be incremented. For example, if db1 is conﬁgured with: auto_increment_offset=1 auto_increment_increment=10 and db2 is conﬁgured with: auto_increment_offset=2 auto_increment_increment=10 735
Part IV Extending Your Skills the ﬁrst ﬁve consecutive inserts into auto_increment ﬁelds on db1 will have the values 1, 11, 21, 31, 41, and the ﬁrst ﬁve consecutive inserts into auto_increment ﬁelds on db1 will have the values 2, 12, 22, 32, 42. The advantage of this setup is that a load balancing solution can point to both db1 and db2, and if one of the servers is unavailable, the other can be used with no change in setup. How- ever, the biggest disadvantage of this is that replication can get out of sync — that is, the data on a slave may not match its master. If there are writes to both db1 and db2, and they are out of sync with each other, which has the ‘‘correct’’ data set? It is very likely that neither data set is complete and correct. The safest way to use this setup is to have one database be the primary database, and the other to be a failover server. This is still not completely safe — as long as writes are allowed on the failover server when the primary server is running, there is the possibility of having different data sets that cannot be resolved with each other (the ‘‘which has the correct data set?’’ problem). To get around this, put the read_only parameter on the failover database. However, this turns the property of ‘‘using the failover with no change in setup’’ to ‘‘using the failover but having to change a server variable.’’ In general, the only savings in a master/master replication setup are that both servers are set up appropriately to be a master and a slave — for example, both servers already have binary logging enabled and are already replicating each other. We recommend having a master/slave setup where binary logging is enabled on both the master and slave — by doing that, the only additional step in a master/slave setup (instead of a master/master setup) is having to run the CHANGE MASTER TO command. This extra step is well worth it to know which data set is the correct data set in case the two machines have different data. Circular replication In circular replication, you have a ring of master/slave connections, as in Figure 22-5. FIGURE 22-5 db2 db1 db3 Again, though it does allow the ability to write to more than one server, it does not scale writes — remember, eventually all writes will have to be done on all servers. Reads can be scaled this way. 736