Connect to the server from the command line. How to check which MySQL version I'm using
Starting and stopping the MySQL server from the command line
You can manually start the MySQL server in one of two ways:
Double-click the mysqld-nt.exe file located in the bin subdirectory of the directory where MySQL is installed.
Open the window command line Windows. To do this, click the Start button, select Run from the menu, in the Run a program window that appears, in the Open field, enter the cmd command and click OK. A command line window will appear on the screen (Fig. 1.25).
Rice. 1.25. Command Prompt Window
At the command prompt, enter the command
mysqld-nt
and press Enter. The MySQL server will start.
If, when setting up the server, the path to the bin subdirectory was not added to the value of the Path system variable, then to start the server you must enter not only the file name, but also the full path to it, for example:
C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt
If you want to view diagnostic messages about the server in a command prompt window, instead of mysqld-nt, enter
mysqld-nt –console
Attention!
If you did not specify a root user password when setting up the MySQL server, you must set a password the first time you start the server (otherwise anyone can manage the server as root without a password).
To set the root password, open a new command prompt window and type next command:
mysqladmin -u root password<пароль>
(or C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin -u root password<пароль>, if the path to the bin subdirectory was not added to the Path system variable when setting up the server) and press Enter.
In the future, if you need to change the root user password, run the same command, only using the -p option:
mysqladmin -u root -p password<новый пароль>
When the Enter password prompt appears, enter your old password and press Enter.
Finally, if you need to stop the MySQL server, run the command
mysqladmin -u root -p shutdown
and at the Enter password prompt, enter the root user password. Press Enter. The MySQL server will be stopped.
You can also use the graphical MySQL Administrator utility to start and stop the MySQL server.
Starting and Stopping MySQL Server Using MySQL Administrator
To start the MySQL server using the graphical MySQL Administrator utility, follow these steps.
1. Launch the MySQL Administrator program (Start → All Programs → MySQL → MySQL Administrator). A server connection window will appear on the screen (Fig. 1.26).
Rice. 1.26.
2. Click Ctrl key and, while holding it, click on the Skip button that appears in the lower right corner of the window instead of the Cancel button. The main MySQL Administrator window will appear on the screen (Fig. 1.27).
Rice. 1.27. MySQL Administrator main window
4. If the MySQL server has not been configured as Windows service, then the Start Service button located in the right area of the window is unavailable. The following preliminary steps must be taken:
1) Go to the Configure Service tab. Find the Install new Service button at the bottom of the tab and click it;
2) in the dialog box that appears, specify the name of the service and click OK;
3) in the Config Filename field, enter the path to the my.ini configuration file (Fig. 1.28), for example C:\Program Files\MySQL\MySQL Server 5.0\my.ini. Red font color means the file was not found; if the color changes to normal, then the path is correct;
4) in the Path to binary field, enter the path to the mysqld-nt.exe file, for example C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt;
6) return to the Start/Stop Service tab.
Rice. 1.28. Configure Service Tab 5. Click the Start Service button. The MySQL server will start.
Attention!
If you did not specify a root user password when setting up the MySQL server, you must set it the first time you start the server (otherwise anyone can manage the server as root without a password). IN current version MySQL Administrator Setting the root password is not available, and to perform this operation you must use the mysqladmin command line utility (see the subsection “Starting and stopping the MySQL server from the command line”).
To stop the MySQL server with using MySQL Administrator, follow these steps.
1. Launch the MySQL Administrator program (Start → All Programs → MySQL → MySQL Administrator). A server connection window will appear on the screen (see Figure 1.26).
Server Host – value localhost (local computer);
Port – port number selected when setting up the server (default – 3306);
Usename – root value;
Password – root user password. Click OK.
3. In the main MySQL Administrator window, in the left pane, click Service Control.
4. In the right pane of the window, click the Stop Service button. The MySQL server will be stopped.
In the next subsection, you will learn how to start a MySQL server using Windows Administrative Tools.
Starting and stopping the MySQL server from the control panel
If the MySQL server was configured as a Windows service using the setup wizard (see subsection “Configuring the MySQL server”) or using the MySQL Administrator utility (see subsection “Starting and stopping the MySQL server using MySQL Administrator”), then start and stop it You can use the Services component of Control Panel.
To access the Services component, click the Start button, select Control Panel from the menu, then double-click Administrative Tools in Control Panel, and finally, double-click Services in the Administrative Tools window. The Services window will appear on the screen (Fig. 1.29) with a list of all local services.
Rice. 1.29. MySQL service in control panel
In the Services window, click on the name of the MySQL service (the name is determined when creating the service in the configuration wizard or in MySQL Administrator). Then click on the desired link under the service name: Start Service, Stop Service, or Restart Service.
Once the MySQL server is running, you can connect to it. In the following subsections you will learn how to do this.
Connecting to the server from the command line
To connect to the MySQL server from the command line, follow these steps.
1. Open a Windows Command Prompt window. To do this, click the Start button, select Run from the menu, in the Run a program window that appears, enter cmd in the Open field and click OK.
2. At the command line (see Figure 1.25), enter the command
mysql -h<Имя компьютера>-u<Имя пользователя>-p
(Where<Имя компьютера>is the name of the computer on which the server is running) and press Enter. When prompted to Enter password, enter the user's password.
If you need to connect to a MySQL server running on the same computer, the computer name (localhost) can be omitted, for example
mysql -u root -p
After connecting to the server, the command line prompt will change to mysql> (Figure 1.30). Now you can start working with the database: add tables, enter and query data, register new users, etc.
Rice. 1.30. Connecting to the MySQL server from the command line
To disconnect from the server, simply type the command in the command line
and press Enter.
An alternative way to connect to the MySQL server is provided by the graphical MySQL Query Browser utility.
Connecting to the server using MySQL Query Browser
The MySQL Query Browser utility is an interface for creating, editing and executing SQL statements. It is more convenient than the command line. If you decide to use the MySQL Query Browser to work with the database, then follow these steps to connect to the server.
1. Launch the MySQL Query Browser program (Start → All Programs → MySQL → MySQL Query Browser). A server connection window will appear on the screen (Fig. 1.31).
Rice. 1.31. MySQL server connection window
2. In the fields of the server connection window, enter the connection parameters:
Server Host – the name of the computer on which the MySQL server is running;
Port – port number selected when setting up the server (default – 330 6);
Username – user name;
Password – user password;
Default Schema - The name of the database you will be working with (this can be an existing database or a new one).
3. Click OK. If you entered a name for the new database, then in the dialog box that appears, click Yes to create this database.
After connecting to the server, the main MySQL Query Browser window will appear on the screen (Fig. 1.32). In it you can perform any operations with the database: add tables, enter and query data, register new users, etc.
Rice. 1.32. MySQL Query Browser main window
Attention!
The default font used in MySQL Query Browser to display SQL queries does not support Russian letters. To enter Russian letters in query texts, you must select a different font (for example, Arial or Book Antiqua). To do this, in the main MySQL Query Browser window, open the Tools menu and select Options. In the Options window that appears, in the left pane, click General Options ( Common parameters) and in the right area in the Code Font field, select the desired font from the list. Click the Apply button.
To disconnect from the server, simply close the MySQL Query Browser window.
This concludes our introduction to MySQL and moves on to summing up the results.
1.6. Summary
This chapter covered MySQL DBMS and graphical utilities MySQL Administrator and MySQL Query Browser. You have mastered a fairly complex procedure for installing and configuring a MySQL server, learned how to manage the server and connect to it. You also learned how a relational database works and how to design your own database.
So, the next step is to build a database in MySQL. The second chapter is devoted to this. It will show you how to create tables, enter information into them, and find the information you need in the database.
Chapter 2
Database Management with SQL
In this chapter, you will learn how to work with data in the MySQL DBMS, how to determine its structure, and how to add, change, and delete data. These operations are performed by SQL, a universal structured query language that is standard means access to relational databases data.
You can use any of the many MySQL server client applications to execute SQL commands. This chapter will not cover third party applications. You will only be introduced to applications created by MySQL AB: the mysql command line utility and the graphical MySQL Query Browser utility.
All data operations are available in both utilities. MySQL Query Browser is convenient to work with the database: its components are clearly presented, you can directly edit data (without using the SQL UPDATE operator), work with queries, for example, build them using a special tool (and you do not need to enter the names of tables and columns manually ), save queries to a file, export query results and much more. You can learn about all the capabilities of MySQL Query Browser by referring to the documentation in Russian, which can be found at http://dev.mysql.com/doc/query-browser/ru/index.html.
You'll first learn how to run SQL commands in the MySQL Query Browser and the command line, and then we'll cover only the syntax of the SQL commands.
2.1. Executing SQL Commands
Before executing SQL commands, you need to connect to a running MySQL server (how to do this was described in Chapter 1). In this section, you will learn how to create SQL commands and submit them to the server for execution.
If you are using the command line, then to execute the SQL command, enter its text in the command line window and press the Enter key to send the command to the server. To avoid problems with encoding Russian-language data, before starting to work with the data, run the command
SET NAMES cp866;
You can see the result of executing this command in Fig. 2.1.
Rice. 2.1. Setting the encoding on the command line
The SET NAMES command must be repeated when everyone connecting to the server using the command line. This command tells the server that this client application(mysql utility) uses CP-866 encoding (this is the Windows command line encoding), and the server will automatically perform encoding conversion when communicating with the client application.
After changing the encoding, you can enter any SQL commands on the command line. A message about the result of the command, as well as the requested data, is displayed directly in the command line window (Fig. 2.2).
Rice. 2.2. Executing an SQL Query on the Command Line
The mysql utility allows you to enter multi-line commands (in Fig. 2.3 the SHOW DATABASES command was entered in this way). If a semicolon is not entered - a sign of the end of the command, then when you press Enter keys The utility does not send the command to the server, but prompts you to continue entering the command. If you want to cancel a multi-line command, type \c (Figure 2.3).
Rice. 2.3. Multiline command
If you use MySQL Query Browser, then you do not need to set the encoding - this program works in UTF-8 encoding and itself reports this to the server. However, in MySQL Query Browser there is a problem with displaying Russian letters in the query area (the area where the text of SQL commands is entered, Fig. 2.4). To solve this problem, you need to change the font used in the query area (how to do this was described at the end of the previous chapter). You only need to change the font once.
Rice. 2.4. Executing an SQL Query in MySQL Query Browser
In the query area, you can enter several SQL commands at once, as shown in Figure. 2.4. The current command (the cursor is on one of its lines) is highlighted with a white background, the remaining commands are displayed on a light gray background. To execute the current command, you can click either the Execute button located to the right of the prompt area or the Ctrl+Enter key combination. After executing the command, the requested data is displayed in the results area, and a message about the result of the command is displayed at the bottom of this area.
Now that you have learned how to enter SQL commands, let's start managing data using these commands. First of all, we will look at commands designed to work with the database as a whole.
2.2. Database creation
In this section, you will learn how to create and delete a database, change the default encoding for it, select the current database, and also view a list of all databases on this server MySQL.
To create a database, run the command
CREATE DATABASE<Имя базы данных>;
For example, the command
CREATE DATABASE SalesDept;
creates a database named SalesDept.
If for some reason you need to set a default encoding for the new database that is different from the encoding specified when MySQL setup, then when creating the database you can specify the required encoding(character set) and/or comparison (sorting) rule for character values:
CREATE DATABASE<Имя базы данных>
CHARACTER SET<Имя кодировки>
COLLATE<Имя правила сравнения>;
For example, if you are in new base import data that is in the CP-1251 encoding, then we will indicate this encoding when creating the database in this way:
CREATE DATABASE SalesDept
CHARACTER SET cp1251 COLLATE cp1251_general_ci;
Advice
To view the list of encodings used in MySQL, run the SHOW CHARACTER SET command; and to see a list of rules for comparing symbolic values, use the SHOW COLLATION; command. In this case, you can use the LIKE operator: for example, to see all comparison rules for the CP-1251 encoding, run the command SHOW COLLATION LIKE %1251%;. The ending “_ci” (case insensitive) in the name of the comparison rules means that when comparing and sorting the case of characters is not taken into account, the ending “_cs” (case sensitive) – case is taken into account, the ending “_bin” (binary) – comparison and sorting are performed by numeric character codes. For most comparison rules, you can find a description (that is, the order of characters according to which the text values will be ordered) on the web page http://www.collation-charts.org/mysql60/.
The encoding specified when creating the database will be used by default for tables in this database, however, you can specify a different encoding.
You can change the encoding and/or comparison rule for character values for the database using the command
ALTER DATABASE<Имя базы данных>
CHARACTER SET<Имя кодировки>
COLLATE<Имя правила сравнения>;
In this case, the encoding used in existing database tables remains the same; Only the default encoding for newly created tables changes.
To remove an unnecessary or erroneously created database, run the command
DROP DATABASE<Имя базы данных>;
Attention!
Deleting a database is a very important operation because it deletes all the tables in the database and the data stored in the tables. Before deleting, it is recommended to create backup copy Database.
You can select one of the databases created on this MySQL server as the current database using the command
USE<Имя базы данных>;
For example,:
USE SalesDept;
You can then perform operations on tables in that database without prefixing the table name with the database name. For example, to access the Customers table in the SalesDept database, you can simply write Customers instead of SalesDept.Customers. By specifying the current database, you can access tables in other databases, but you must use the database name as a prefix. The selection of the current database is saved until you disconnect from the server or until you select another current database.
To see a list of all databases existing on a given MySQL server, run the command
SHOW DATABASES;
Even if you haven't created any databases yet, you will see three in the resulting list system bases data.
INFORMATION_SCHEMA is an information database from which you can get information about all other databases, the data structure in them and all kinds of objects: tables, columns, primary and foreign keys, access rights, stored procedures, encodings, etc. This database is available It is read-only and virtual, meaning it is not stored as a directory on disk: all information requested from this database is provided dynamically by the MySQL server.
Mysql is a utility database used by the MySQL server. It stores information about registered users and their access rights, reference Information and etc.
Test is an empty database that can be used to try things out or simply deleted.
So, you have mastered the basic operations performed with the database as a whole: the commands CREATE DATABASE (creation), ALTER DATABASE (change), DROP DATABASE (delete), USE (select the current database) and SHOW DATABASES (view a list of databases) . Next we'll look at table operations. In this case, we will assume that you have selected a database as the current one and are working with its tables.
2.3. Working with tables
In this section, you will learn how to create, modify, and delete a table, how to view table information, and a list of all tables in the current database. Let's start with the most difficult command - creating a table.
Creating a table
To create a table, run the command shown in Listing 2.1.
Listing 2.1. Create table command
CREATE TABLE<Имя таблицы>
(<Имя столбца 1> <Тип столбца 1> [<Свойства столбца 1>],
<Имя столбца 2> <Тип столбца 2> [<Свойства столбца 2>],
[<Информация о ключевых столбцах и индексах>])
[<Опциональные свойства таблицы>];
As you can see, the table creation command can include many parameters, but many of them are optional (in Listing 2.1, such parameters are enclosed in square brackets). In fact, to create a table, you just need to specify its name and the names and types of all the columns; other parameters are used if necessary.
Let's first look at a few examples that will help you master the CREATE TABLE command and immediately, without studying its many parameters, start creating your own (simple in structure) tables.
Let's assume that we are building the database that we designed in Chapter 1. Using the commands from the previous section, we created an empty SalesDept database and selected it as the current one. Now let's create three tables: Customers, Products and Orders. Listing 2.2 shows the command to create the Customers table.
Listing 2.2. Command to create the Customers table
CREATE TABLE Customers
(id SERIAL,
name VARCHAR(100),
phone VARCHAR(20),
address VARCHAR(150),
rating INT,
PRIMARY KEY (id))
This command used parameters: firstly, the name of the table and, secondly, the names and types of the columns that the table will consist of (see also Table 1.1 in Chapter 1).
Id – record identifier. You assigned the type SERIAL to this column, which allows you to automatically number the table rows. The SERIAL keyword stands for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. This means that large integer (BIGINT) positive (UNSIGNED) numbers can be entered into the column, while the absence of undefined and duplicate values (NOT NULL UNIQUE) is automatically controlled. If, when adding a row to a table, you do not specify a value for this column, then the MySQL program will add another value to this column serial number(AUTO_INCREMENT).
Note
NULL is a constant indicating the absence of a value. If a column contains a NULL value, then it is assumed that there is no defined value for that column (which is why we also call NULL a null value). NULL should not be confused with the empty string ("") or the number 0. NULL values are treated differently: most functions and operators return NULL if one of the arguments is NULL. For example, the comparison result 1 = 1 is a true value (TRUE), and the comparison result NULL = NULL is an undefined value (NULL), that is, two undefined values are not considered equal.
Nam is the client’s name, phone is the phone number and address is the address. You assigned the type VARCHAR to these columns because they will contain character values. The maximum allowed number of characters in the column value is indicated in parentheses.
Third, you specified that the id column would be the table's primary key by including a PRIMARY KEY (id) clause in the table creation command.
Fourth, you have specified two optional parameters for this table. The ENGINE parameter specifies the table type. You assigned the Customers table the InnoDB type, since only this type ensures maintaining the integrity of relationships between tables (more details about table types will be discussed in the “Optional table properties” section). The CHARACTER SET parameter specifies the default encoding for the data in the table. Because you did not set the encoding separately for the name, phone, and address columns, the data in these columns will be stored in UTF-8, which is the default encoding for the Customers table.
The next example we'll look at is the Products table creation command shown in Listing 2.3.
Listing 2.3. Products table creation command
CREATE TABLE Products
(id SERIAL,
description VARCHAR(100),
details TEXT,
price DECIMAL(8,2),
PRIMARY KEY (id))
ENGINE InnoDB CHARACTER SET utf8;
This command is very similar to the command to create the Customers table and differs only in the name of the table and the set of columns. The id (product number) and description (product name) columns of the Products table have types that are already familiar to us. The details column is of type TEXT. This type is convenient to use instead of the VARCHAR type if the column will contain long values: the total length of the values of all VARCHAR columns is limited to 65,535 bytes for each table, and there is no limit on the total length of the TEXT columns. The disadvantage of the TEXT type is the inability to include such columns in a table's foreign key, that is, to create a relationship between tables based on these columns.
The price column is of type DECIMAL, designed to store monetary amounts and other values for which it is important to avoid rounding errors. We have indicated two numbers in brackets: the first of them determines maximum amount digits in the column value, the second is the maximum number of digits after the decimal separator. In other words, the price of a product can contain up to six digits in the integer part (6 = 8–2) and up to two digits in the fractional part.
And finally, the last example is the command to create the Orders table, shown in Listing 2.4.
Listing 2.4. Orders table creation command
CREATE TABLE ORDERS
(id SERIAL,
date DATE,
product_id BIGINT UNSIGNED NOT NULL,
qty INT UNSIGNED,
amount DECIMAL(10,2),
customer_id BIGINT UNSIGNED,
PRIMARY KEY (id),
FOREIGN KEY (product_id) REFERENCES Products (id)
ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (customer_id) REFERENCES Customers (id)
ON DELETE RESTRICT ON UPDATE CASCADE)
ENGINE InnoDB CHARACTER SET utf8;
A special feature of the Orders table is the presence of foreign keys: the product_id column contains product numbers from the Products table, and the customer_id column contains customer numbers from the Customers table (see also Table 1.2 in Chapter 1). Because product and customer numbers are large positive integers, we assigned the product_id and customer_id columns to type BIGINT UNSIGNED.
Next, to ensure that the integrity of relationships is automatically maintained (we talked about integrity in Chapter 1), we told MySQL which primary key each foreign key corresponds to. Thus, the FOREIGN KEY (customer_id) REFERENCES Customers (id) construct means that the customer_id column can only contain values from the id column of the Customers table and null values, and other values are prohibited. For the product_id column, we set a similar constraint and assigned the NOT NULL property to this column to prevent registering orders with an undefined product. Additionally, we indicated the rules for maintaining integrity for each of the connections (we also discussed them in Chapter 1). The ON DELETE RESTRICT rule means that you cannot delete a customer record if that customer has a registered order, and you cannot delete a product record if that product was ordered by someone. The ON UPDATE CASCADE rule means that when a customer number in the Customers table or an item number in the Products table changes, the corresponding changes are made in the Orders table.
Note
Note that we created the Orders table last because the primary keys in the Customers and Products tables must be created before the foreign keys in the Orders table that reference them. However, it would be possible to create tables without foreign keys in any order, and then add foreign keys using the ALTER TABLE command, which we will look at in the subsection “Changing the table structure.”
In our examples, we looked at only some of the parameters of the table creation command. Now we will list all the main parameters that may be useful to you when creating tables. In the “Data Types in MySQL” section we will talk about column types, in the “Column Properties” section we will talk about setting up key columns, and finally in the “Key Columns and Indexes” section we will talk about optional table properties.
Data types in MySQL
As you already know, when you create a table, you need to specify a data type for each column. MySQL provides many data types to store numbers, dates/times, and character strings (texts). In addition, there are data types for storing spatial objects, which will not be discussed in this book.
Let's look at numeric data types.
BIT[(<Количествобитов>)].
A bit number containing a specified number of bits. If the number of bits is not specified, the number consists of one bit.
An integer in the range of either -128 to 127, or (if the UNSIGNED property is specified) 0 to 255.
BOOL or BOOLEAN.
They are synonyms for the TINYINT(1) data type (the number in parentheses is the number of digits to display, see note below). In this case, a non-zero value is considered true (TRUE), a zero value is considered false (FALSE).
An integer in the range of either -32,768 to 32,767, or (if the UNSIGNED property is specified) 0 to 65,535.
An integer in the range of either -8,388,608 to 8,388,607, or (if the UNSIGNED property is specified) 0 to 16,777,215.
INT or INTEGER.
An integer in the range of either -2,147,483,648 to 2,147,483,647, or (if the UNSIGNED property is specified) 0 to 4,294,967,295.
An integer in the range of either -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, or (if the UNSIGNED property is specified) from 0 to 18,446,744,073 70 9,551,615.
Synonymous with the expression BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE (a large unsigned integer that accepts automatically incremented unique values; NULL values prohibited). Used to automatically generate unique values in a column primary key. You will find a description of the UNSIGNED and AUTO_INCREMENT properties in this subsection, and the NOT NULL and UNIQUE properties in the “Column Properties” section.
Note
For all integer data types, except BOOL (BOOLEAN) and SERIAL, you can specify in parentheses the number of displayed digits, which is used in conjunction with the ZEROFILL parameter: if the number contains fewer digits, then when output it is padded on the left with zeros. For example, if a table column is defined as INT(5) ZEROFILL, then the values "1234567" and "12345" are displayed as is, and the value "123" is displayed as "00123". For the BIT data type, the size of the number, that is, the maximum number of bits stored, is indicated in parentheses.
mysqladmin is a command line utility that comes with MySQL server om and is used by database administrators to perform some simple MySQL tasks such as setting root or other user's password, changing root or other user's password, monitoring mysql processes, resetting privileges, checking server status, etc.
This article contains some very useful commands mysqladmin which is used system administrators and database administrators in their daily work. You must already have a MySQL server installed to be able to follow these examples.
1. How to set MySQL Root password?
If yours is fresh MySQL installation server, it does not require any password to connect as the root user. To set the root user password in MySQL, use the following command:
Mysqladmin -u root password YOUR_NEW_PASSWORD
2. How to change MySQL Root password?
If you want to change or update the MySQL root password, then you need to type the following command. Let's say your Old Password it is 123456 and you want to change it to the new password xyz123:
Mysqladmin -u root -p123456 password "xyz123"
The same operation is performed for other users; instead of root, insert the username for which you want to change the password
3. How to check if the MySQL server is running?
To find out if the MySQL server is running, use the following command:
Mysqladmin -u root -p ping
4. How can I check which version of MySQL I am using?
The following command will show the MySQL version as well as the current running status:
Mysqladmin -u root -p version
5. How to find out the current status of the MySQL server?
To determine the status of the MySQL server, use the following command. Mysqladmin will show the operating time with running molasses and queues.
Mysqladmin -u root -p status
6. How to check the status of all variables and values of the MySQL server?
To check all variables and values of a running MySQL server, type the following command. The output should be something like this:
Mysqladmin -u root -p extended-status
7. How to view all MySQL status variables and values?
To view all the variables and values of a running MySQL server, use the command as shown below:
Mysqladmin -u root -p variables
8. How to check all processes of a working MySQL server?
The following command display all running processes MySQL database queries:
9. How to create a database on a MySQL server?
For creating new team on MySQL server use the command shown below:
Mysqladmin -u root -p create data_name
10. How to delete a database on a MySQL server?
To remove a database from the MySQL server, use the following command. Press ‘y’ to confirm.
Mysqladmin -u root -p drop data_name
11. How to reload/reset MySQL privileges?
The reload command tells the server to reload the grant tables. The refresh command resets all tables and reopens the log files.
Mysqladmin -u root -p reload; mysqladmin -u root -p refresh;
12. How to safely shut down a MySQL server?
To safely shut down the MySQL server, use the following command:
Mysqladmin -u root -p shutdown
You can also use the following commands to start/stop MySQL server:
Sudo systemctl stop mysql.service sudo systemctl start mysql.service
13. Some Useful MySQL Flush Commands
Below are some useful flush commands with descriptions.
- flush-hosts: Clear all information from the host cache.
- flush-tables: Reset all tables.
- flush-threads: Clear the cache of all threads.
- flush-logs: Clear all information logs.
- flush-privileges: Reload grant tables (same as reload).
- flush-status: Clear status variables.
14. How to terminate a sleeping MySQL client process?
Use the following command to detect a sleeping MySQL client process:
Mysqladmin -u root -p processlist
Now run the command with kill and process ID as shown below:
Mysqladmin -u root -p kill 5
If you need to terminate multiple processes, then pass the process IDs as a comma separated list:
Mysqladmin -u root -p kill 5.10
15. How to run multiple mysqladmin commands together?
If you want to run multiple mysqladmin commands at the same time, the command should look something like this:
Mysqladmin -u root -p processlist status version
16. How to connect to a remote mysql server?
To connect to a remote MySQL server, use -h (host) with the IP address of the remote machine:
Mysqladmin -h 172.16.25.126 -u root -p
17. How to execute commands on a remote MySQL server?
Let's say you want to see the status of a remote MySQL server, then the command will be:
Mysqladmin -h 172.16.25.126 -u root -p status
18. How to start/stop copying on a remote secondary MySQL server?
To start/stop MySQL replication on a secondary (salve) server, use the following commands:
Mysqladmin -u root -p start-slave mysqladmin -u root -p stop-slave
19. How to save MySQL debugging information to log files?
The command tells the server to write debugging information about locks used, memory used, and query usage to the MySQL log file, and also includes information about scheduler events.
Mysqladmin -u root -p debug
20. Options and usage of mysqladmin
All options and available commands you can find mysqladmin by typing:
Mysqladmin --help
We have tried to include almost all mysqladmin commands with examples in the article. If we missed something, write in the comments.
Quite a few Internet projects use MySQL DBMS as database storage. With all this, there are other options to choose from, such as: MS SQL, mSQL, PostrgreSQL, Oracle, etc., but almost every hoster provides services that most likely include the ability to use MySQL databases. There could be many good reasons for this popularity, one of which is that the product has open source , in other words Open Source , which anyone can get (there are some reservations for Windows versions). There is also an opinion that a combination of PHP/MySQL, or Perl/MySQL can give a great result speed, which in other cases is much more difficult to achieve. In addition, PHP in its standard build has built-in support for MySQL.
To create queries on the MySQL server, as in most other DBMSs, the language is used SQL. SQL is the dominant language for working with databases, but each database can also have its own “dialects” SQL language, which are inherent in a particular type of DBMS.
Just like in any database, there are certain standards under which the database server functions properly. Can become one of the reasons for system braking complex SQL request that takes long time for execution. Heavy SQL queries create a heavy load on the MySQL server, which generally affects the performance of the system as a whole. This can sometimes be extremely critical on a “combat” server, when system slowdown can cause a bunch of additional problems.
Such requests, as a rule, should be done, but what to do if you need to remove a request from the queue, which, due to its intricacy, has frozen.
There is a method, popularly called a “clumsy” solution to the problem, this is restarting the MySQL server.
There is another way, which is often more rational, this is to “kill” the problematic MySQL query. This can be done via SSH, or with some utility, for example, phpMyAdmin.
Removing a heavy MySQL query via SSH:
1. Log into the server via SSH
2. Execute the command:
$ mysql –u USER –p
Instead of USER we specify the MySQL user
3. Enter the password from account USER
4. Execute the command
Show processlist;
(don't forget to include a semicolon at the end)
5. Find out the ID of the problematic request
6. Execute the command
Kill query 11223
Instead of 11223, indicate the ID of the request that needs to be removed.
phpMyAdmin makes this even easier.
Removing a heavy MySQL query in phpMyAdmin:
Show processlist
4. A list of requests appears, on the desired request, click on the “complete” link