Deep understanding of the MySQL help command (_linux)

In the process of installing, managing, and using MySQL, you need to memorize a lot of MySQL commands. And for the novice, very few commands do not know how to apply, for the veteran to have a lot of command time to forget the specific usage.

In fact, the help of MySQL information, has provided us with a very comprehensive way to use.

Here's a general introduction to how to use MySQL's help information.

Description: Here I just provide a way to learn MySQL, this article will not explain the use of each command too much.

First, MySQL database initialization

When installing MySQL for initialization, we use the mysql_install_db script to initialize the MySQL database, which we can then implement with the help command. As follows:

/usr/local/mysql/scripts/mysql_install_db--help

The 1, 2, and 3 figures in the diagram represent the bin path, MySQL data directory, and the users who are running MySQL when the MySQL installation is initialized.

Now we're going to initialize the database as follows:

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql/--datadir=/usr/local/mysql/data/--user= Mysql

From the above figure, we can also see that the MySQL database has been initialized successfully. At the same time the system also gives a way to start MySQL, Figure 5 marked out. The 6 marks in the figure are how to modify the root user's password.

Second, MySQL database management

2.1 Start MySQL using Mysqld_safe

After the MySQL database is installed, we will start MySQL. When we start MySQL, we can use the script provided by MySQL, or we can launch it through the Mysqld_safe command.

In fact, the MySQL script is also implemented by this command, as follows:

Cat/etc/init.d/mysqld

We can also view the Help documentation for the use of the Mysqld_safe command. As follows:

/usr/local/mysql/bin/mysqld_safe--help

We need to focus on defaults-file This parameter, which is loaded with MySQL's profile my.cnf. MySQL's multiple instance deployment is based on loading different my.cnf files to achieve the goal.

Now we're going to start MySQL, as follows:

/usr/local/mysql/bin/mysqld_safe--DEFAULTS-FILE=/ETC/MY.CNF &

Note that there is a parameter in the Help document we can not find, that is, skip password Authentication to start MySQL, this parameter is skip-grant-table. Use this parameter to start MySQL as follows:

/usr/local/mysql/bin/mysqld_safe--skip-grant-table &

2.2 Modify root password using mysqladmin

Just newly installed MySQL database, the root user password is empty. We can use Mysqladmin to see how to modify the root user password as follows:

/usr/local/mysql/bin/mysqladmin--help

Modify the root password as follows:

/usr/local/mysql/bin/mysqladmin-uroot Password 123456

The first time you modify the root password, because the root password is empty at this time, we can do so. If root already has a password, we will use the following command to modify it as follows:

/usr/local/mysql/bin/mysqladmin-uroot-p123456 Password 456789

Log in to MySQL with the new password as follows:

/usr/local/mysql/bin/mysql-uroot–p456789

Also note the-s parameter, which is used in multiple instances of MySQL.

2.3 Backup MySQL using mysqldump

When backing up the MySQL database, we typically use the MYSQLDUNMP command to view the help documentation for the use of the mysqldump command. As follows:

/usr/local/mysql/bin/mysqldump--help

As the mysqldump parameters are more, so we do not introduce. But there are a couple of parameters that need to be highlighted.

-A indicates backing up the entire database

-F indicates that the MySQL server will be refreshed Binlog before the export is performed

-E indicates that new multiline insert syntax is used. (Gives a tighter and faster insert statement)

--single-transaction This is when MySQL is InnoDB engine, use mysqldump Backup recommendations to add.

-S This is a MySQL multiple instance use, see "mud: mysql5.5 Multiple instance deployment."

2.4 Login MySQL using MySQL

Login or connect to MySQL we can use the MySQL command to view the help documentation for the use of MySQL commands. As follows:

/usr/local/mysql/bin/mysql--help

Here I mainly introduce the next-e and-s This parameter. The-e parameter is used when MySQL makes master-slave configuration to view the Binlog filename and POS location node of the Master library. -s parameter MySQL multiple instance use. As follows:

Mysql-uroot-p123456-s/data/3307/mysql.sock-e "Show Master Status"

Note that the above commands can be queried by the man to use the method. Like the MySQL command:

Mans MySQL

Third, the use of MySQL database

3.1 View MySQL database version

After logging in to the MySQL database, we can see the system's prompt command, as follows:

/usr/local/mysql/bin/mysql-uroot-p456789

Through the screenshot above, we can see that the current version of MySQL is 5.5.39. We also note that the system prompts: input help can view the assistance information, \c can empty the input.

3.2 View Help command

See the help command for information, as follows:

Help

We can see that the help command is simple in the diagram above and I will not introduce it in detail here. Describes only the system command, which executes related commands directly in the MySQL command. As follows:

System Ifconfig eth0

3.3 View the MySQL Help index

If you do not know the use of a MySQL command, it does not matter. MySQL provides me with a Help index. Now let's just type in a command to see what the MySQL hints like:

Help eth0;

We can see it through the diagram above. When we enter a command that does not exist, the system prompts you. Note The yellow section labeled "Help Contents" in the figure.

We can view all of the MySQL commands via Help contents, which is the MySQL assist index. As follows:

Help Contents;

Note that the figure is marked with the total list of MySQL master commands. For example, account management is responsible for managing MySQL user-related directories, administration is MySQL admin management mysql related directories.

All of our MySQL commands can be found under this index.

3.4 View MySQL How to create a user

We're going to create a new database user, but we don't know what command to use to create it, so we can use Help contents to see all of the MySQL command directories, and according to the directory hints we assume that the order should be in the Account management directory. Then we can look at the command's help first, as follows:

Help account Management;

From the figure above, we can see that the command to create the user is indeed in that directory, for the Create user command.

For a specific use of the Create User command, we can view it further through the help command. As follows:

Help create user;

With the above diagram, we can clearly see the specific use of create user, and the corresponding examples are given in the help information.

This way we know how to create a data user using the Creating user command.

Similarly, we can also see how to delete a user, using the Help drop User command, as follows:

Help drop user;

3.5 How to authorize the user

Once we have created the database user, we need to authorize the user. So how to authorize, what command should be used?

In fact, we can also look up in the Help account Management command as follows:

From the image above we can see that grant is the authorization command.

Let's take a look at how the grant command is used, as follows:

Help Grant;

The above diagram is the use of grant and an example. The system also shows how to view user permissions using show grants and how to remove user permissions using Revoke.

Similarly, we can also delete a user right and use the help revoke command to view the following:

Help revoke;

Here is a point of need to explain, when we give users authorization, sometimes do not know what the database on the user's permissions are, in fact, we can also be given through the system to query the database. The database is Information_schema, as follows:

Use INFORMATION_SCHEMA;

Desc user_privileges;

Select Privilege_type from User_privileges;

Table User_privileges's Privilege_type field stores all the permissions of the MySQL database user.

In addition, we can view all the permissions through the administration under the privileges, this method is the simplest. As follows:

Show privileges;

3.6 Modify User Password

To modify the password of the database user, we can also view it under Help account management. As follows:

Help account Management;

From the above figure, we can know that the command to modify the user's password is set password.

Let's look at the specific usage of set password, as follows:

Help set password;

By using the diagram above, we can know how to use the Set password and how to use the instance.

3.7 How to create a new MySQL database

How to create a new MySQL database, we can use Help contents to view what should be in data definition. As follows:

Help Contents;

Help data definition;

From the diagram above, we can see that the command to create the database is created. Now let's take a look at how to use the CREATE DATABASE command as follows:

Help create DATABASE;

The above illustration is the detailed usage of the CREATE DATABASE command.

Also note the section that we marked when we looked at the Help data Definition command, as follows:

We can see that there are create tables, create table spaces, delete databases, delete tables, delete table spaces, and so on commands, which we do not introduce. We only need to follow the CREATE DATABASE command to query its use method.

3.8 How to update a record in a database

Now we want to update the records of one of the fields in a table in the database, and we know we can use the Update command. But the exact use of the update command, we forgot.

Hey, it's okay, we can also see how the update command is used by help information.

By looking at the relevant information, we know that the update command is under the data manipulation directory. As follows:

Help data manipulation;

Now let's look at how the update is used, as follows:

Help update;

With the diagram above, we can know how to use the update command.

Also note that when we look at data manipulation, we can also view other actions about the table, query command Select, Delete command Delete, insert command inserts.

As follows:

Help data manipulation;

The way these commands are used is not covered here. We simply follow the update command to query its usage.

3.9 How to refresh the MySQL log

Now let's see how to refresh the MySQL log at the command line. We can see this in the administration under Help contents. As follows:

Help administration;

With the above figure, we can know that the command to refresh the MySQL log is flush. Now look at how the command is used. As follows:

Help flush;

With the image above, we can see that the refresh log uses the flush Logs command. As follows:

Show Master Status\g;

Flush logs;

Also in this help information, how to reset the log is given, using the Reset command. As follows:

Help reset;

In fact, we can see the reset command in Help administration. As follows:

3.10 Refresh Permissions

Sometimes when we update using the Update command, we find that the relevant permissions are not in effect. This time we need to use the flush privileges command to refresh.

As the following command:

Flush privileges;

In fact, flush help information, has been explained to us. As follows:

3.11 View Show Command

Through 3.9 We know that there is a special command show in administration.

We can see a lot of show related commands as follows:

Help administration;

This show command allows you to view the SQL statements that we use when users authorize, create databases, create tables, and so on. You can also view information such as the database engine, database State, table status, and so on.

View the SQL statements that were executed when the database Ilanni was created, as follows:

Show CREATE Database Ilanni;

Note that when we create the database Ilanni, we do not specify the language encoding used by the database. However, when executing SQL statements, the system automatically adds language encoding and uses the default language encoding latin1.

If you don't remember how to add language code, we can create a database. The show CREATE DATABASE command is then used to see how the system is encoded with language. We then delete the database again and execute it again as the system was created in the SQL language.

As follows:

Create DATABASE Ilanni default character set UTF8;

View the database in the system as follows:

show databases;

View the Master library Binlog related information as follows:

Show master status;

View the Ilanni user authorization SQL statement as follows:

Show grants for Ilanni;

View the tables for the MySQL database as follows:

Show tables;

3.12 View Master-Slave related commands

We use a lot of commands when it comes to MySQL master and slave configuration. These commands can also be obtained through help information. As follows:

Help Contents;

Help transactions;

Notice the part of the image we marked with yellow. The main library grants commands from the library's commands, lock-table commands, master library Master Reset database, open and close synchronization, and so on.

When we execute the authorization command for the main library from the library, we use the Change Master to command. See Help for specific usage of the Change Master to command. As follows:

Help change Master to;

With the above two graphs, we can easily know the change master to use method, and also gives the actual example in the Help information.

In order to maintain the consistency of the master-slave database when we are exporting the MySQL database, we will lock the table through the lock command. For the use of the lock command, we can also view it through Help information as follows:

Help lock;

Reset the main library master log as follows:

Help reset Master;

Reset Master;

On the sync from the library slave, as follows:

Help start slave;

Start slave;

Show Slave Status\g

To turn off synchronization from the library slave, as follows:

Help stop Slave;

Stop slave;

Show Slave Status\g