Basic usage of mysql in linux

1] how to create a management user for the mysqld database?

After the database is installed, we should create a management account for the mysql database. To set the root user as the administrator, we should run the following command;

[Root @ linuxsir01 root] #/opt/mysql/bin/mysqladmin-u root password 123456
[Root @ linuxsir01 root] #

The above command shows that the mysql database administrator is root and the password is 123456.

2] How do I access the mysql database? Take mysql database administrator root and password 123456 as an example;

[Root @ linuxsir01 root] #/opt/mysql/bin/mysql-uroot-p123456

After the preceding command is output, the following prompt is displayed;

Welcome to the MySQL monitor. Commands end with; or/g.
Your MySQL connection id is 6 to server version: 3.23.58

Type 'help; 'or'/H' for help. type'/C' to clear the buffer.

Mysql>

Note: When operating these commands, you should open the mysqld server. These new siblings have long known about it :)

3] how to operate commands in the database? I think this is all in the mysql manual. I mainly want to pay attention to it. In fact, I cannot have several commands. If you want to learn mysql, it is not difficult. If you have been operating mysql in windows, it is actually the same here. mysql is a cross-platform database, and its usage is the same.

In the mysql database, each command is always at the end of the ";". Some new beginners may forget to enter the ";" and the result cannot be returned. :):)

1] What databases are available in mysql? Code:

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql>

After mysql is installed and the Administrator is set up, the first time we enter the system, we use the show databases; command to view the Database List, we found that there are two databases, mysql and test, which are self-built by the system, it is intended for everyone to practice.

4] how to create and delete a database?

For example, to create a database named linux, run the following command:

Mysql> create database [database name];

Therefore, we should run the following command to create a database named linux

Mysql> create database linux;
Query OK, 1 row affected (0.00 sec)

Is it built ?? It must have been built, because there are OK :)

Check whether there is a linux database?

Code:

mysql> show databases;
+----------+
| Database |
+----------+
| linux    |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

mysql>

How can we delete a database ??
Mysql> drop database [database name];

For example, to delete the created linux database, use the following command;
Mysql> drop database linux;
Query OK, 0 rows affected (0.00 sec)

Is it deleted ??

Code:

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql>

5] how to operate a database? There are many problems. I suggest you read the mysql manual. There are too many things in it. To operate a database, you must first specify a database as the current database. use the use command

Mysql> use [database];

For example, if I want to specify the database linux as the current database, it should be

Mysql> use linux;
Database changed
Mysql>

6] how to back up the database ??

For example, to back up an existing mysql database named linux, run the mysqldump command.

The command format is as follows:

[Root @ linuxsir01 root] #/opt/mysql/bin/mysqldump-uroot-p linux>/root/linux. SQL
Enter password: Enter the Database password here

Through the above command, we need to understand two things: first, back up the database as a database administrator; second, the backup destination is/root, and the backup file name is linux. SQL. In fact, the backup location and file name are determined based on your own situation. The file name can be retrieved by yourself, or the path can be arranged by yourself;

For example, if I want to back up a linux database to/home/beinan and the database name is linuxsir031130. SQL, enter the following command.
[Root @ linuxsir01 root] #/opt/mysql/bin/mysqldump-uroot-p linux>/home/beinan/linuxsir031130. SQL
Enter password: Enter the database password of the database administrator root.

In this way, we can find the backup file linuxsir031130. SQL for the database named linux in mysql under the/home/beinan directory.

To sum up, we must learn to make changes when learning. :):)

5] How to import the backup database to the database?

First, we need to perform the preceding operations, such as adding a Database Administrator (if you have not added a mysql database administrator) and creating a database.

For example, to back up linuxsir031130. SQL IN THE/home/beinan directory and import it to a database named linux, perform the following operations;

[Root @ linuxsir01 root] #/opt/mysql/bin/mysql-uroot-p linux Enter password: Enter the password here

If the machine is good, the database is relatively small, just a few minutes.

6] other commonly used mysql commands;

View status
Mysql> show status;

View Processes

Code:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 16 | root | localhost | NULL | Query   | 0    | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql>

To view a table, you must first specify a database as the current database; for example, a database named linux;

Mysql> use linux;
Mysql> show tables;
Empty set (0.00 sec)

Mysql>

7] Some Supplements to common mysql database commands;

Several common mysql-related management commands

Mysql command: displays and uses the mysql database in basic text. I have mentioned the usage in the previous section, such as logon.

Mysqladmin command, used to create and maintain the mysql database, which has been briefly mentioned earlier;

Isamchk is a database file used to repair, check, and optimize the. ism suffix;

Mysqldump is used to back up the database, which has been described earlier;

Myisamchk is used to fix database files with the. myi suffix;

For example, to check whether there is a problem with the database named linux. myi database table, use the following command;

Stop the mysqld Server
[Root @ linuxsir01 root] #/opt/mysql/share/mysql. server stop

Then execute
[Root @ linuxsir01 root] #/opt/mysql/bin/myisamchk/opt/mysql/var/linux/*. MYI

The command above means to check all. myi files. The database directory is in the/opt/mysql/var/linux/directory.

If any problem occurs, use the-r parameter to fix it.
[Root @ linuxsir01 root] #/opt/mysql/bin/myisamchk-r/opt/mysql/var/linux/*. MYI

6] mysqlshow command: displays the database and table selected by the user
[Root @ linuxsir01 root] #/opt/mysql/bin/mysqlshow-uroot-p [database name]

For example, if I want to view a database named linux, it should be:

[Root @ linuxsir01 root] #/opt/mysql/bin/mysqlshow-uroot-p linux