Shulou(Shulou.com)06/01 Report--

This article mainly introduces three kinds of linux command query mysql, which has a certain reference value, friends who need can refer to it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Linux commands query mysql: 1, the command to start mysql [mysqladmin start]; 2, the command to restart mysql [mysqladmin restart]; 3, the command to close mysql [mysqladmin shutdown].

The linux command queries mysql as follows:

1. The command to start mysql: mysqladmin start

/ ect/init.d/mysql start (previous is the installation path of mysql)

2. Command to restart mysql: mysqladmin restart

/ ect/init.d/mysql restart (previous is the installation path of mysql)

3. The command to close mysql: mysqladmin shutdown

/ ect/init.d/mysql shutdown (previous is the installation path of mysql)

4. Connect the mysql on this machine:

Cd mysql\ bin

Mysql-uroot-p

Enter password

Exit the mysql command: exit

5. Modify the mysql password:

Mysqladmin-u username-p old password password new password

Or

Mysql Command Line SET PASSWORD FOR root=PASSWORD ("root")

6. Add new users

Grant select on database. * to user name @ login host identified by "password"

Eg: add a user's test password to 123, so that he can log in on any host and have the authority to query, insert, modify and delete all databases. First use the root user to connect to mysql, and then type the following command:

Grant select,insert,update,delete on *. * to "Identified by" 123"

7. Operations related to mysql database

You must first log in to mysql, all at the mysql prompt, and each command ends with a semicolon

Show database list: show databases

Show the data table in the library: use mysql; / / Open the library

Show tables

Display the structure of the data table: describe table name

Build the database: create database library name

Table creation: use library name

Create table table name (list of field settings)

Delete library and delete table: drop database library name

Drop table table name

Clear the record in the table: delete from table name

Show the records in the table: select * from table name

8. Modification of code

To change the encoding format of the entire mysql:

When you start mysql, the mysqld_safe command line joins

-- default-character-set=gbk

To change the encoding format of a library: enter the command at the mysql prompt

Alter database db_name default character set gbk

9. Import and export of data

Text data is transferred to the database

The format that the text data should conform to: the field data is separated by the tab key, and the null value is used instead. Example:

1 name duty 2006-11-23

Data input command load data local infile "file name" into table table name

10. Export databases and tables

Mysqldump-- opt news > news.sql (back up all the tables in the database news to a news.sql file, where news.sql is a text file with any file name. )

Mysqldump-- opt news author article > author.article.sql (back up the author table and the article table in the database news to the author.article.sql file, where author.article.sql is a text file with any file name. )

Mysqldump-- databases db1 db2 > news.sql (back up the databases dbl and db2 to a news.sql file, where news.sql is a text file with any file name. )

Mysqldump-h host-u user-p pass-- databases dbname > file.dump

Is to import the database dbname with the name user and password pass on host into the file file.dump

Mysqldump-- all-databases > all-databases.sql (back up all databases to an all-databases.sql file, where all-databases.sql is a text file with any file name. )

11. Import data

Mysql

Source news.sql; (executed under the mysql command to import tables)

12. Connect MySQL

Format: mysql-h host address-u user name-p user password

Example 1: connect to the MYSQL on this computer.

First of all, open the DOS window, then enter the directory mysqlbin, and then type the command mysql-uroot-p. Enter prompts you to enter the password. If you have just installed MYSQL, the superuser root does not have a password, so you can enter the MYSQL directly by entering the enter. The prompt for MYSQL is: mysql >.

Example 2: connect to the MYSQL on the remote host. Suppose the IP of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Type the following command:

Mysql-h210.110.110.110-uroot-pabcd123

(note: U and root do not have to add spaces, and so do others)

Exit the MYSQL command: exit (enter).

14. Change the password

Format: mysqladmin-u username-p old password password new password

Example 1: add a password ab12 to root. First enter the directory mysqlbin under DOS, and then type the following command:

Mysqladmin-uroot-password ab12

Note: since root does not have a password at the beginning, the-p old password can be omitted.

Example 2: change the password of root to djg345.

Mysqladmin-uroot-pab12 password djg345

15. Add new users. (note: commands in MySQL environment, so they are followed by a semicolon as a command Terminator)

Format: grant select on database. * to user name @ login host identified by\ "password\"

Example 1. Add a user's test1 password to abc, so that he can log in on any host and have the authority to query, insert, modify and delete all databases. First use the root user to connect to MySQL, and then type the following command:

Grant select,insert,update, delete on *. * to test1@\ "%\" Identified by\ "abc\"

But the increase in the number of users in example 1 is very dangerous, if you want someone who knows the password of test1, then he can log in to your MySQL database on any computer on internet and can do whatever he or she wants with your data.

Example 2, add a user's test2 password to abc, so that he can only log in on localhost, and can query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MySQL database is located), so that even if the user knows the test2 password, he can not access the database directly from the internet, but only through the web page on the MySQL host.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by\ "abc\"

If you don't want test2 to have a password, you can issue another command to eliminate the password.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by\ "\"; Thank you for reading this article carefully. I hope it will be helpful for everyone to share three kinds of linux commands to query mysql content. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.