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

0
58
lượt xem
5

Mô tả tài liệu

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)

Lưu

## Nội dung Text: MySQL Administrator’s Bible- P16

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