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

This article introduces you how to operate mysql in the CentOS system, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Common MySQL commands in CentOS system

Create database name; creates a database

Use databasename; Select Database

Drop database name deletes the database directly without reminding you

Show tables; display table

Detailed description of the describe tablename; table

Add distinct to select to remove duplicate fields

Mysqladmin drop databasename is prompted before deleting the database.

Displays the current mysql version and current date

Select version (), current_date

2. CentOS system modifies the password of root in mysql:

Shell > mysql-u root-p

Mysql > update user set password=password ("xueok654123") where user='root'

Mysql > flush privileges / / refresh the database

Mysql > use dbname; Open the database:

Mysql > show databases; shows all databases

Mysql > show tables; displays all the tables in the database mysql: use mysql; first and then

Mysql > describe user; displays the column information of the user table in the mysql database)

3. Grant in CentOS system

Create a full superuser that can connect to the server from anywhere, but you must use a password something to do this mysql > grant all privileges on *. * to user@localhost identified by 'something' with

Add new users

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

GRANT ALL PRIVILEGES ON *. * TO monty@localhost IDENTIFIED BY 'something' WITH GRANT OPTION

GRANT ALL PRIVILEGES ON *. * TO monty@ "%" IDENTIFIED BY 'something' WITH GRANT OPTION

Delete Authorization:

Mysql > revoke all privileges on *. * from root@ "%"

Mysql > delete from user where user= "root" and host= ""

Mysql > flush privileges

Create a user custom to log in to a specific client it363.com and access a specific database fangchandb

Mysql > grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by 'passwd'

Rename the table:

Mysql > alter table T1 rename T2

4. Mysqldump in CentOS system

Backup database

Shell > mysqldump-h host-u root-p dbname > dbname_backup.sql

Restore the database

Shell > mysqladmin-h myhost-u root-p create dbname

Shell > mysqldump-h host-u root-p dbname

Mysqladmin-u root-p-d databasename > a.sql

If you only want to uninstall the sql command that inserts the data, and you don't need the table creation command, the command is as follows:

Shell > mysqladmin-u root-p-t databasename > a.sql

So what if I just want data and don't want any sql commands?

Mysqldump-T. / phptest driver

Where the plain text file can be unloaded only if the-T parameter is specified, which represents the directory where the data was unloaded, and. / represents the current directory, that is, the same directory as mysqldump. If you do not specify the driver table, the data from the entire database will be unloaded. Each table generates two files, one of which is a .sql file that contains the execution of the table creation. The other is a .txt file that contains only data and no sql instructions.

5. You can store the query in a file and tell mysql to read the query from the file instead of waiting for keyboard input.

You can use the shell to type the redirect utility to do this. For example, if you have queries in the file my_file.sql, you can execute them as follows:

If you want to write the table statement in advance in sql.txt:

Mysql > mysql-h myhost-u root-p database < sql.txt

About how to operate mysql in the CentOS system to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.