MySQL is probably the most striking software project after the release of Linux. While the mSQL deserves respect for havingpushed the case management database off the ground, MySQL skillfully took advantage of the moment. Now she is no more and no less, as a serious competitor to large DATABASE MANAGEMENT SYSTEMS in the field of database development of small and medium scale. At the very beginning of MySQL just began replacing the decaying core of mSQL version 1. As noted in Chapter 1, the signs of aging as manifested in the mSQL stability problems and the inability to meet the growing demands of which struckher with the success. MySQL used the basics of mSQL and now surpasses its feature set and performance at the same time.
Building on the heritage received from mSQL, TcX decided that MySQL should not be slower than mSQL, having a great set offeatures. While the crucible in the mSQL database performance, so the task of a Tlc has put difficult. Special design goals of MySQL are speed, reliability, and ease of use. To achieve such a performance, the Tlc decided to make highly threaded MySQLinternal mechanism. Multithreaded application performs several tasks at the same time–as if simultaneously run multiple instances of an application.
Making a MySQL thread, Tlc has given users many benefits. Each incoming connection is handled by a separate thread whileanother is always a running thread manages connections, so clients don’t have to wait for requests from other clients. At the same time can be run any number of requests. While a thread writes data to a table, all other queries that require access to this table, just wait until it is released. The client can perform all the valid operation, paying no attention to the other simultaneous connections. Control thread prevents simultaneous entry by two threads in the same table.
This architecture, of course, more complex than single-threaded architecture mSQL. However, the gain in speed thanks to the simultaneous execution of multiple queries exceeds the speed loss due to increase in complexity.
Another advantage of multi-threading is inherent in all multithreaded applications. Despite the fact that threads share the process memory, they are run separately. Due to this Division of the threads on multiprocessor machines can be distributedacross multiple CPUs. In Figure 1. 4–1 shows the multithreaded nature of the MySQL server.
Figure. 4–1. the MySQL client-server architecture
In addition to performance gains obtained through multithreading, MySQL supports a larger subset of SQL than mSQL. MySQL supports more than a dozen types of data, and SQL functions. Your app can access these functions through ANSI SQLcommands.
MySQL actually extends the ANSI SQL multiple new features. Among them are new functions (ENCRYPT, WEEKDAY, IF andothers), the ability to increment fields (AUTO_INCREMENT, LAST_INSERT_ID), as well as the ability to distinguish between upper and lower case.
TLC deliberately dropped some features of SQL that occur in large databases. The most noticeable absence transactions andbuilt-in procedures. As David Hughes in mSQL, Tlc decided that these opportunities will cause too much performance hit.However, Tlc continues to work in this direction, but so that performance suffered from the loss of only those users who reallyneed such opportunities.
Since 1996, the Tlc uses MySQL in an environment where there are more than 40 databases containing 10000 tables. Of thesemore than 10000 500 tables are, in turn, more than 7 million records of about 100 Gb of data.
Before you can use MySQL, you need to install it. MySQL runs on almost any Unix platform, known as well as running Windows 9 x, Windows NT, and OS/2. For Windows 9 x and Windows NT, you must purchase a license. If you just want to test the performance of the DATABASE ENGINE, you have an older free version.
MySQL distro exists in both binary and source code form. If you would like to contribute to the MySQL project, add theirimprovements or simply cannot find a binary distribution for your platform, then you must take the source code distribution.Most users, however, should take a binary distribution. The latest distributions–binary and source–can be found athttp://www.mysql.com
If you receive a distribution in the form of source code, you need to compile it before installing. In any case, should be guided by the instructions provided in the distribution.
Typically, the database server runs continuously. After all, what’s the point of having a database, if you cannot get the data. TLCcreated MySQL, with that in mind, so MySQL is running as a daemon under Unix, and as a service under Windows NT. Windows95 is only a rough equivalent, implemented by placing a shortcut in the startup folder, an executable application (StartUp). An important feature of Windows 95 is that each thread creation leak about 200 bytes of RAM. Therefore, under Windows 95 should not be to keep the MySQL running. For Windows 98 and Windows NT, this is not the case.
Start MySQL the mysqld_safe script is performed. On Unix, this file is installed by default as/usr/local/bin/mysqld_safe. This is aBourne shell script, and you can edit it to change the default settings. All of the settings that you specify in the mysqld_safe will be passed directly to MySQL daemon.
MySQL — branching daemon. When you run, the program creates a copy of itself and runs as a background process. So you don’t need to do anything to make MySQL run in the background. If you are using MySQL to start mysqld_safe script, then you should put it in the background:
The reason you should start mysqld_safe in the background (and on which you want to run the mysqld_safe instead of mysqld)that mysqld_safe mysqld runs the script, and then continuously checks whether mysqld. If execution stops unexpectedly,mysqld_safe-MySQL it will restart.
Now that you know how to run MySQL must do so that MySQL kicked off and finished the work with the computer on which it is running. Under Windows NT, of course, simply install MySQL as a service. Under Windows 9 x start-up script label you want to put MySQL in the Startup folder. Under Unix, as almost always happens in this world, make MySQL start and end with asomewhat complicated. UNIX systems typically are looking for startup scripts in/etc or/etc//etc/rc.d in init.d. You should ask your system administrator or in the documentation, where you want to place the startup/shutdown scripts. MySQL distro contains the file support_j’lies in the directory mysql.ser–ver. This script and will serve as a startup/shutdown script.
Now that you have a freshly installed and running MySQL, you must first change the root password of the server by runningfrom the directory in which you installed the MySQL command:
./bin/mysqladmin–u root password ‘ mynewpasswd ‘
While running and secure MySQL you can do some basic tasks of administration, MySQL was able to begin to serve you.
The main tool for MySQL database administration and development in a utility mysqladmin. With it you can create, destroy andcontrol your server and database supported by the server.
Creation of databases
Your server is useless, as long as there is no database, which it serves. Using mysqladmin to create new database:
mysqladmin-p create DATABASENAME
–R option specifies that you want to be prompted to enter the root password you have set before. If you enter the correctpassword, mysqladmin will create a new blank database with the name you specified. Because MySQL database is a directory with the files, the command mysqladmin create creates a new directory that will contain the database files. For example, if you create adatabase named “mydata“, in the catalogue of the data contained in the directory into which you installed MySQL, will create adirectory called mydata.
Since the MySQL databases and tables are stored as files on the file system, you will encounter unpleasant differences in behavior of implementations for Unix and Win32. That is, all file systems for Win32 are case insensitive, while Unix file systems are case sensitive. As a result of database and table names are case sensitive on Unix and Win32 are not.
During application development, you will probably need to create multiple databases to support development efforts. For example, a common practice in developing database applications is to create separate databases for development, testing andoperation. Once you get rid of these staging databases. The mysqladmin utility allows you to delete the database by using the“drop”:
mysqladmin-p DATABASENAME drop
As in the create DATABASENAME mysqladmin command is the name of the database you want to delete. MySQL will prevent you from accidentally deleting the database. After you enter this command, it will warn you that the uninstall database of potentially very dangerous and will ask you to confirm your intention to. When a database is deleted, you can make sure thatthere are no more in the data directory the directory previously serving the database.
Renaming and copying databases
In MySQL there is a utility for renaming and copying databases. Because the database is just the files in a certain directory, you can carefully, rename or copy a database by using the operating system. Although the use of operating system commands can be used to rename and copy databases, the result will not save the security settings of the source tables, since MySQL stores the data protection system database table. To copy a database, you also need to duplicate the information on protection, stored in the system database MySQL. We take a closer look at the MySQL protection system later in this chapter.
The status of the server
The mysqladmin utility a lot of commands that allow you to monitor the status of the MySQL server. Enter the commandmysqladmin status provides server status in one line that looks like this:
Uptime: 395 Threads: 1 Questions: 14 Slow queries: 0
Opens: 10 Flush tables: 1 Open tables: 6
The displayed values are: Uptime
The number of seconds that the server is up and running. Threads
The number of threads that interact with the database at this time. You always see at least one thread is the one that counts, all other threads. The server also has three other flow, of invisible this is one team handles signals, another manages all the other threads and a third waits for incoming connections.
The number of requests that are submitted to the database since the start.
The number of requests that required more time than the time specified in the configuration. The key property of theconfiguration–long_query_tiroe. We take a look at the configuration settings later in this chapter.
The number of tables that are open since the server was started.
The number of flush, refresh and reload.
The number of tables that are currently open. Since MySQL is gopotočna, each table can be opened multiple times. For example, you can run any number of SELECT commands that are used on the same table. For this reason, the number of open tables maybe greater than the total number of tables in the system.
If MySQL was compiled with the-with-debug, the mysqladmin status command shows the current and maximum amount ofmemory used.
If you are interested in more general static information, then run the command mysqladmin version. It displays the following:
bin/mysqladmin Ver 7.8 Distrib 3.22.17, for sun-solaris 2.6 on spare TCX Datakonsult AB, by Monty
Server version 3.22.17
Protocol version 10
Connection Localhost via Unix socket
Unix socket/tmp/; ny3ql. sock
Uptime: 23 mm 58 sec
Threads: 1 Questions: 15 Slow queries: 0 Opens: 10 Flush tables: 1 Open tables: 6
The last line is, of course, with those data, which shows the mysqladmin status command. The rest of the data is completely different.
The released version of the MySQL server. Protocol version
MySQL communication protocol version that the server supports. If you have any difficulties with tools that uses MySQLcommunication protocol, you can compare that value with the one that waits for your program.
Method of connecting to the server. In our example, the client communicates with the MySQL Unix socket. If you choose to access a remote server in this position will be the name of the machine to which you are connected.
The name of the socket file that you use to Exchange data with the server. If you are contacting MySQL via TCP/IP instead of TCPport item will be listed with the port number of MySQL.
The total time of the server.
Two other teams, mysqladmin variables and mysqladmin extended-status, offer additional information.
Since MySQL mnogopotočna, track the activity of the Unix ps command is not easy. Despite the fact that multiple threads in the process list will be indicated only one process. MySQL allows you to handle it with the command mysqladmin-u root processlist, which lists all active threads in a wonderfully presented plaques:
Here is exactly what busy every process. Issued by the values have the following meanings:
The internal ID number of the flow. This value is not related to any system process ID. You can use this number in the commandmysqladmin kill to terminate a thread.
The user connected to the server via this thread.
The name of the host from which the user has connected.
The database to which you are connected.
Type the command to execute the thread. The team can be one of the following types:
The thread is waiting for user input. Most of the processes should be in this State.
The flow in the process of completion.
Stream prepares base interaction. The client can only communicate with one base, but switching between them can be done at any time.
Thread performs the actual query. Although most of the interaction with the database in the form of queries, these commandsare made very quickly and rarely appear in extradition.
Thread generates the list of fields in the table.
Thread creates a new database.
Thread deletes the database.
The thread restarts the MySQL tables. After a reboot, all new threads will use the updated access table.
The stream is in the process of completing all other accounts on the server.
Thread generates statistics.
This thread discusses other threads. With this value will show the thread executing this command.
The flow in the process of setting the incoming client connection.
This thread another thread completes.
The stream clears all buffers and resets the log files.
The MySQL log files provide another way to obtain useful information for server administration. MySQL creates the master log, if mysqld is started with the-log. This log book is kept in the file/usr/local/var/HOSTNAME.log, where HOSTNAME is the hostname of the machine running MySQL. Records server connection and commands sent to it customers.
Setting the parameter to mysqld-debug (or mysqld_safe), you force MySQL to send additional information in the log. Debugpackage used by MySQL, has dozens of options, most of which you‘ll never use. The most commonly used installation-d:t: o, FILENAME, where FILENAME is the name of the debug log that you want to use. If you set this parameter to log all actions will be written to the server, step by step.
MySQL supports another magazine, readable. If you run MySQL with the option-log–update, this will create a file namedHOSTNAME. #, where HOSTNAME is the name of the machine, a # is a unique number. This log contains all changes made to thedatabase tables. This log file is created in the form of SQL, so all operations can be repeated on a different database server.
The following command performs graceful shutdown the MySQL server:
This command is the proper way to shut down the server. If you run MySQL using mysqld_safe and are trying to shut down the server in any other way, mysqld_safe just starts another instance of the server. You can safely close the traditional Unix Server–team kill, but never use kill-9.
Mysqladmin command-line options
Utility mysqladmin-a very rich tool with lots of command line options. General format
mysqladmin OPTIONS COMMAND1 COMMAND2 … .… COMMANDn
In other words, you can specify multiple commands. Will be made even such a sequence of commands like, say,
mysqladmin-p create drop silly silly
This command will create and destroy a database “silly“. Here is a list of commands that you can pass to mysqladmin:
Creates a new database with the specified name.
Deletes the database with the specified name.
Displays the extended status message server.
Immediately writes all buffered changes on remote computers.
Immediately writes all buffered changes in magazines.
Immediately writes all buffered changes to tables.
Same as reload.
killID1, ID2 … ... … ….Idn
Terminates the threads with the specified IDs.
Replaces the password to a new value.
Check whether another mysqld.
Provides a list of active threads.
Reloads all the tables to access.
Writes buffered changes in all tables and closes and opens all the logs.
Shuts down the server.
Gives a briefing on the status of the server.
Displays the values of the variables.
Displays information about the version of the server.
In addition, the following options are supported:
Issue the debug information to the log. Often this ‘ d:t: o, FILENAME ‘.
Do not prompt for confirmation to delete the table and move on to the next team even if it is not possible to do this.
Giving tips on how to use the utility msqladmin.
Use compression in the client/server protocol.
To connect to the specified computer.
Use the specified password to validate the user’s rights.
If you do not specify a password, the user will be prompted for a password.
Use to connect to the specified port.
Repeat the command after a specified period of time.
Exit without message, if the connection to the server cannot be established.
File to use as the Unix socket.
The timeout for the connection.
The name for user registration, if other than the current one.
Provide version information and exit.
Wait and retry the specified number of times, if the server is not currently available.
We cannot overemphasize the importance of regular data backups. Without a good backup power as a result of the accident can be lost in the months and years of work. At the same time, when you can reserve planned well for a short time to recover almostin any accident.
Chapter 5 “mSQL“ provides a detailed description of the role of the command msqldump with mSQL data redundancy. MySQL supports almost identical functionality as mysqldump. We recommend you to study to understand the role of mysqldump with full database backup. In this section we will focus on the next most important form of backup–interface–cement reservation.
Although technically full backup data is enough to recover from data loss, it is sometimes difficult to implement. When you have many data files necessary to complete the reservation may take up too much disk space. Therefore, it is generally accepted to full backup every week or through small intervals, and perform daily backup of data that has changed since the last full backup. This is called an incremental backup.
When you use the incremental MySQL backup can be performed by using a database server as the update log “–MySQL change history. If the database server mysqld is started with the-log-update, all changes in the database will be stored in a file as SQL commands. Changes will be saved in the order of their production. The result is a file that is being processed by the mysqlmonitor, will play all of the activity on the database. If the log is stored in the database, the education will be restored to the entire lifecycle of the database, which will lead her in the current state.
With greater use of the magazine maintained with some time, for example, from the last database backup, you can use the backup to bring the current state. This is an incremental backup technology. Are regular (say, once a week) full backup of the database. Then each day copy the change log to tape or hard disk selection. Keep a copy of all the daily logs of changes, startingfrom the date of the last full backup. This allows you to recover the database in the event of an accident, as well as all data is lostsince the last backup. Because the change log is a text file, you can view the SQL commands to search for specific data.
Whatever method you use for backup, make it frequently and periodically check the possibility of a real recovery of their data.Many database administrators have retained backup data only to one day make sure that the error–operator, software or media–their backups are absolutely worthless.
You not only need to have reliable access to your data, but also be sure that others have no access to them. MySQL uses its owndatabase server for security. When you first install MySQL database is created under the name “mysql”. In the base there are five tables: db, host, user, tab–les_priv, and columns_priv. Newer versions of MySQL, create a database called func, but it has nothing to do with security. MySQL uses this table to determine who is allowed to do. The user table contains data for security related to the server as a whole. Table host provides server access permissions for remote computers. And finally, db, tables_priv and co-lumns_priv control access to individual databases, tables, and columns.
We take a quick look at all of the tables that support security in MySQL, and then consider the use of technology while protecting the core MySQL.
Host and User columns, you can use the “%” symbol in place of the random sequence of characters. For example, the host name“chem lab“% include “chembiolab“, “chemtestlab” and so on the special username “nobody acts like a single“% “, that is, covers all users, not mentioned elsewhere. The following explains the meaning of the different access rights:
Ability to execute the SELECT command.
To perform an INSERT command.
Ability to execute the UPDATE command.
To perform a DELETE command.
The ability to CREATE or to create the database.
The ability to DROP command to remove the database.
Ability to reload information on access by using mysqladmin reload.
To stop the server using mysqladmin shutdown.
The ability to manage server processes.
Возможность читать и записывать файлы с помощью команд типа SELECT INTO OUTFILE и LOAD DATA INFILE.
Возможность давать привилегии другим пользователям.
Возможность создавать и уничтожать индексы.
Возможность выполнять команду ALTER TABLE.
В MySQL есть специальная функция, позволяющая скрыть пароли от любопытных глаз. Функция password () зашифровывает пароль. Ниже показано, как использовать функцию password () в процессе добавления пользователей в систему.
INSERT INTO user (Host, User, Password, Select_priv,
Insert_priv, Update_priv, Dclete_priv)
VALUES (‘% ‘, ‘ bob ‘, password (‘ mypass ‘), ‘ Y ‘, ‘ Y ‘, ‘ Y ‘. ‘Y ‘)
INSERT INTO user (Host, User, Password, Select_priv)
VALUES (‘athens.imaginary.com’, ‘ jane ‘, ‘ ‘, ‘ Y ‘)
INSERT INTO user (Host, User, Password)
VALUES (‘% ‘, ‘ nobody ‘, “)
INSERT INTO user (Host, User, Password, Select_pnv,
Insert_priv, Updatejriv, Delete_priv)
VALUES (‘athens.imaginary.com’, ‘ nobody ‘,
password (‘ thispass ‘), ‘ Y ‘, ‘ Y ‘, ‘ Y ‘, ‘ Y ‘)
MySQL usernames typically are not associated with the user name of the operating system. By default, the KLI–client MySQLtools use when registering user names for your operating system. For them, however, do not require the assertion of conformity.Most MySQL client application by using the-and connect to MySQL using any name. The same name as the user’s operating system does not appear in the user table, unless specifically included in the assignment of rights.
First we created the user “bob“, can connect to the database from any computer and run commands SELECT, INSERT, UPDATE, and DELETE statements. The second person, “jane“, can connect with the “athens.imaginary.com“, does not have a password andcan only run the SELECT statement. The third user–“nobody“–with any machine. “this user can do nothing at all. Last user“nobody“–from the machine “athens.imaginary.com“, it can execute SELECT, INSERT, UPDATE, and DELETE, as user “bob.“
How MySQL performs comparisons? You may have noticed that a name can be in fact multiple entries. For example,“email@example.com” matches and “nobody @%“, and “firstname.lastname@example.org“. Before you search in the user table, MySQL sorts data as follows:
At first matched for nodes that do not contain the “%” masks with blank Host name is interpreted as “%“.
For the same node is first checked against names that do not contain masks. User field is empty is treated as containing the “%“.
The first match it finds is considered final.
In the previous example, the user will first be compared with “nobody” from the “athens.imagmary.com” as“athens.imaginary.com“ in the sort order is above the “%“. Because the computer names are sorted before user names, values of privileges for the computer from which you are connecting, takes precedence over any specific rights that you may have. For example, if the user table contains the entry: