Rotten mud: mysql HELP command instructions

Rotten mud: mysql HELP command instructions

Do you need to remember many mysql commands when installing, managing, and using mysql. In addition, for beginners, there are not many commands that do not know how to apply them. For veterans, many commands have long time to forget the specific usage.

In fact, mysql's help information has provided us with comprehensive usage methods.

Next I will give a general introduction to how to use mysql help information.

Note: I am here to provide an idea for learning mysql. This article will not explain too much about how to use each command.

I. mysql database Initialization

When installing mysql for initialization, we need to use the mysql_install_db script to initialize the mysql database. At this time, we can use the help command. As follows:

/Usr/local/mysql/scripts/mysql_install_db -- help

The values 1, 2, and 3 in the figure indicate the bin path, mysql data directory, and users used to run mysql during initialization.

Now we initialize the database as follows:

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

We can also see that the mysql database has been initialized successfully. The system also shows how to start mysql, marked as 5 in the figure. Figure 6 shows how to change the password of the root user.

Ii. mysql Database Management

2.1 start mysql using mysqld_safe

After the mysql database is installed, start mysql. When starting mysql, we can use the script provided by mysql or the mysqld_safe command to start mysql.

In fact, the mysql script is also implemented through this command, as follows:

Cat/etc/init. d/mysqld

For how to use the mysqld_safe command, you can also view the help documentation. As follows:

/Usr/local/mysql/bin/mysqld_safe -- help

We need to pay attention to the defaults-file parameter. This parameter loads the mysql configuration file my. cnf. Mysql multi-instance deployment is achieved by loading different my. cnf files.

Start mysql as follows:

/Usr/local/mysql/bin/mysqld_safe -- defaults-file =/etc/my. cnf &

Note that we cannot find a parameter in the help document, that is, skip password verification 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 change the root user password using mysqladmin

For the newly installed mysql database, the root user password is blank. You can use mysqladmin to view how to change the root user password, as shown below:

/Usr/local/mysql/bin/mysqladmin -- help

Modify the root password as follows:

/Usr/local/mysql/bin/mysqladmin-uroot password 123456

Change the root password for the first time because the root password is empty. If the root user already has a password, run the following command to modify the password:

/Usr/local/mysql/bin/mysqladmin-uroot-p123456 password 456789

Use the new password to log on to mysql as follows:

/Usr/local/mysql/bin/mysql-uroot-p456789

Pay attention to the-S parameter, which will be used for mysql multi-instance deployment. For details, see "rotten mud: mysql5.5 multi-instance deployment".

2.3 back up mysql using mysqldump

When backing up a mysql database, we generally use the mysqldunmp command. For how to use the mysqldump command, see the help documentation. As follows:

/Usr/local/mysql/bin/mysqldump -- help

Because mysqldump has many parameters, we will not describe them one by one. However, there are several parameters that need to be highlighted.

-A indicates backing up the entire database

-F indicates that the binlog of the MySQL server will be refreshed before the export is executed.

-E indicates that the new multiline INSERT syntax is used. (A more compact and faster insert statement is provided)

-- Single-transaction: when mysql is an innodb engine, we recommend that you add mysqldump for backup.

-S is used by multiple mysql instances. For details, see "mud: mysql5.5 multi-instance deployment".

2.4 log on to mysql and use mysql

To log on to or connect to mysql, you can use the mysql Command. For more information about how to use the mysql Command, see the help documentation. As follows:

/Usr/local/mysql/bin/mysql -- help

Here I will mainly introduce the-e and-S parameters. -E parameter is used in mysql for master-slave configuration to view the binlog File Name of the master database and the pos position node. -S parameter mysql multi-instance usage. For details, see "mud: mysql5.5 multi-instance deployment". As follows:

Mysql-uroot-p123456-S/data/3307/mysql. sock-e "show master status"

Note that you can use man to query the usage of the preceding commands. For example, mysql command:

Man mysql

Iii. mysql database usage

3.1 view the mysql database version

After logging on to the mysql database, we can see the system prompts:

/Usr/local/mysql/bin/mysql-uroot-p456789

Through the above, we can see that the current mysql version is 5.5.39. At the same time, we also noticed the system prompt: Enter help to view help information, and \ c to clear the input.

3.2 view the help Command

View the help command information as follows:

Help

We can see that the help Command is very simple, and I will not detail it here. Only system commands are introduced. This parameter can be used to directly execute system-related commands in mysql commands. As follows:

System ifconfig eth0

3.3 view mysql Help Index

It does not matter if you do not know or use any mysql command. Mysql provides me with a help index. Now we can enter a command to check the mysql prompt as follows:

Help eth0;

We can see through. When we enter a command that does not exist, the system will prompt. Pay attention to the help contents section marked as *** in the figure ".

We can view all mysql commands through help contents. This is the mysql help index. As follows:

Help contents;

Note that the marked directory is the total directory of each mysql command. For example, Account Management is used to manage mysql user-related directories, and Administration is used by the mysql administrator to manage mysql-related directories.

All our mysql commands can be found under this index.

3.4 view how mysql creates a user

We want to create a new database user, but we don't know what command to use to create it. Then we can use help contents to view all mysql Command directories, according to the information prompted by the Directory, we guess the command should be in the Account Management directory. Then we can first view the help of this command, as shown below:

Help Account Management;

Through the command, we can see that the command to create a user is indeed in this directory, it is the create user command.

For more information about how to use the create user command, see the help Command. As follows:

Help create user;

Through this, we can clearly see the specific usage of the create user, and the corresponding examples are also given in the help information.

Now we know how to use the create user command to create a data user.

Similarly, we can also view how to delete a user and use the help drop user command, as shown below:

Help drop user;

3.5 how to authorize a user

After creating a database user, we need to authorize the user. So how can I authorize it? What command should I use?

In fact, we can also see in the help Account Management command, as shown below:

We can see that grant is the authorization command.

Let's take a look at how to use the grant Command, as shown below:

Help grant;

Is the use method and instance of grant. At the same time, the system also shows how to view the user's permissions using show grants and how to delete the user's permissions using revoke.

Similarly, you can delete a user permission and use the help revoke command to view the permission, as shown below:

Help revoke;

In this case, we need to note that when authorizing users, sometimes we do not know what database permissions are for users. In fact, we can also query them through the database provided by the system. The database is information_schema, as follows:

Use information_schema;

Desc user_privileges;

Select privilege_type from user_privileges;

The privilege_type field of table user_privileges stores all permissions of mysql database users.

In addition, we can also view all permissions through privileges under Administration. This method is the simplest. As follows:

Show privileges;

3.6 Change User Password

You can modify the password of a Database User in help Account Management. As follows:

Help Account Management;

The command to change the user password is set password.

The following describes how to use set password:

Help set password;

We can know how to use the set password and how to use the instance.

3.7 how to create a mysql database

To create a new mysql database, we can use help contents to check whether it is in data definition. As follows:

Help contents;

Help data definition;

We can see that the command for creating a database is create database. Now let's take a look at the usage of the create database Command as follows:

Help create database;

Is the detailed usage of the create database Command.

At the same time, pay attention to the parts marked when we view the help data definition Command, as follows:

We can see that there are also commands such as creating tables, creating tablespaces, deleting databases, deleting tables, and deleting tablespaces. We will not discuss these commands one by one. You only need to follow the create database Command to query its usage.

3.8 how to update a database record

Now we want to update the record of a field in a table in the database. We know that we can use the update command. However, we forget the specific usage of the update command.

Hey, it's okay. You can also view the update command usage through the help information.

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

Help data manipulation;

Now let's check how to use update as follows:

Help update;

Through the update command, we can know how to use the update command.

At the same time, you must note that when viewing data manipulation, you can also view other operations on the table, query command select, delete command delete, insert command insert.

As follows:

Help data manipulation;

The usage of these commands is not described here. We only need to follow the update command to query its usage.

3.9 how to refresh mysql logs

Now let's see how to refresh mysql logs under the command line. This can be viewed in the Administration under help contents. As follows:

Help Administration;

The command for refreshing mysql logs is flush. Now you can view the specific usage of this command. As follows:

Help flush;

You can use the flush logs command to refresh the log. As follows:

Show master status \ G;

Flush logs;

The help information also shows how to reset logs and use the reset command. As follows:

Help reset;

In the help Administration, we can also see the reset command. As follows:

3.10 refresh Permissions

Sometimes, when we use the update command for updates, we will find that the related permissions do not take effect. In this case, we need to use the flush privileges command for Refresh.

Run the following command:

Flush privileges;

In fact, the flush help information has been described for us. As follows:

3.11 view the show command

Through 3.9, we know that there is a special command show in Administration.

We can see many show-related commands as follows:

Help Administration;

The show command shows the SQL statements used for user authorization, database creation, and table creation. You can also view the database engine, database status, table status, and other information.

View the SQL statement executed when the database ilanni is created, as follows:

Show create database ilanni;

Note that the language encoding used by the database is not specified when we create the database ilanni. However, when executing an SQL statement, the system automatically adds the language encoding and uses the default language encoding latin1.

If you cannot remember how to add the language encoding, we can create a database. Then, run the show create database Command to check how the system is encoded with the language. Then we will delete the database again and execute it again in the SQL language when the system was created.

As follows:

Create database ilanni default character set utf8;

View the database in the system as follows:

Show databases;

View the binlog information of the master database as follows:

Show master status;

Check the SQL statement authorized by the ilanni user as follows:

Show grants for ilanni;

View the mysql database table as follows:

Show tables;

3.12 view master-slave commands

Many Commands are used for mysql master-slave configuration. These commands can also be obtained through help information. As follows:

Help contents;

Help transactions;

Note: The part marked. Commands granted to the master database, such as commands from the slave database, the lock table command, the master database master resets the database, and enabling and disabling synchronization.

When we execute the authorization command of the master database from the slave database, we use the change master to command. For more information about how to use the change master to command, see help. As follows:

Help change master;

Through the above two figures, we can easily know how to use the change master to, and provide the actual example in the help information.

When exporting a mysql database, we use the lock command to lock the table to maintain consistency between the master and slave databases. For the use of the lock command, you can also view the help information as follows:

Help lock;

Reset the master log of the master database as follows:

Help reset master;

Reset master;

Enable synchronization on the slave database slave as follows:

Help start slave;

Start slave;

Show slave status \ G

Disable synchronization on the slave database slave as follows:

Help stop slave;

Stop slave;

Show slave status \ G

For details about how to configure mysql master-slave synchronization, see mud: mysql5.5 master-slave synchronization replication configuration.