Common commands for the CentOS system to operate MySQL

The origin of MySQL name MySQL is a small relational database management system, MySQL is widely used in small and medium-sized web sites on the Internet. Because of its small size, fast speed, low total cost of ownership, especially the open source, many small and medium-sized web sites in order to reduce the total cost of ownership of the site chose MySQL as the site database. Common commands for working with MySQL in the CentOS system.

1. Common MySQL commands in the CentOS system
Create database name; Create a database
Use DatabaseName; Select Database
Drop database name deletes databases directly and does not alert
Show tables; Show Table
Describe TableName; Detailed description of the table
Add distinct in Select to remove duplicate fields
Mysqladmin drop DatabaseName You are prompted before you delete the database.

Show current MySQL version and current date
Select version (), current_date;

2. The CentOS system modifies the root password in MySQL:
Shell>mysql-u root-p
mysql> Update user Set Password=password ("Xueok654123″) where user= ' root ';
Mysql> flush Privileges//Refresh Database
Mysql>use dbname; Open the database:
Mysql>show databases; Show all databases
Mysql>show tables; Displays all tables in the database MySQL: use MySQL first;
Mysql>describe user; Displays column information for the user table in the MySQL database);

3. Grant in the CentOS system

Create a full superuser who can connect to the server from anywhere, but must use a password something do this mysql> grant all privileges on * * to [email protected] 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 [email protected] identified by ' something ' with GRANT OPTION;
GRANT all privileges on * * to [e-mail protected] "%" identified by ' something ' with GRANT OPTION;

Remove Authorization:
Mysql> revoke all privileges on * * FROM [email protected] "%";
mysql> Delete from user where user= "root" and host= "%";
mysql> flush Privileges;

Create a user custom login on a specific client it363.com to access a specific database fangchandb
MySQL >grant Select, insert, UPDATE, Delete, Create,drop on fangchandb.* to [e-mail protected] it363.com identified by ' passwd

To rename a table:
mysql > ALTER table t1 rename T2;

4. Mysqldump in CentOS system

Backing Up the database
shell> mysqldump-h host-u root-p dbname >dbname_backup.sql

Recovering a Database
shell> mysqladmin-h myhost-u root-p Create dbname
shell> mysqldump-h host-u root-p dbname < Dbname_backup.sql

If you only want to unload the build instruction, the command is as follows:
Shell> mysqladmin-u root-p-D databasename > A.sql

If you only want to unload the SQL command that inserts the data, and you do not need to create a table command, the command is as follows:
shell> mysqladmin-u root-p-t databasename > A.sql

So what should I do if I only want the data and I don't want any SQL commands?
mysqldump-t./Phptest Driver

Only the-t parameter is specified to unload the plain text file, which represents the directory where the data is unloaded./represents the current directory, that is, the same directory as mysqldump. If you do not specify a driver table, the data for the entire database is unloaded. Each table generates two files, one for the. sql file, which contains the build table execution. 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.

The shell can be used to type the redirection utility to complete this work. For example, if you have queries in file My_file.sql, you can execute these queries as follows:

If you want to write the build statement in advance in Sql.txt:
MySQL > mysql-h myhost-u root-p Database < Sql.txt

This is the basic common command for working with MySQL in the CentOS system.

Common commands for the CentOS system to operate MySQL