MySQL database command line tool

MySQL includes a server process for database management and a series of tools for accessing databases and creating applications:

MySQL: Execute SQL queries in MySQL, or execute SQL commands stored in files;
Mysqlaccess: Manage Users;
Mysqladmin: Manages database servers, including database creation and removal;
Mysqld: the actual MySQL server process;
Mysqldump: dump the definition and content of a database or table to a file;
Mysqlhotcopy: Hot Backup;
Mysqlimport: import data of different file formats to MySQL tables;
Mysqlshow: displays information about the server or any object (database and table.

Mysql_secure_installation: a script used to manage the root password, remotely access and remove temporary (TEST) databases, and temporary users.

Generally, root should be used only to log on to MySQL on the local database. we can add a user 'admin' with super management permissions to achieve remote maintenance.

TIPS:

1. Log On As the root user. (enter mysql-u username-P password in the command line)
2. Execute the following statement:
Mysql> grant all privilages on *. * to admin @ localhost
Identified by 'Password' with grant option;
Mysql> grant all privilages on *. * to admin @ "%
"Identified by 'Password' with grant option;

"%" Is a wildcard that allows the admin user to initiate access from any host. The password is the password set for the admin user.

========================================================== ========

MySQL command line Common commands

First, start and stop the MySQL Service
Net stop MySQL
Net start MySQL

Second, log on to MySQL
Syntax: mysql-u user name-P User Password
Enter the mysql-uroot-p command, press enter and prompt you to enter the password, enter 12345, and then press enter to enter mysql. The MySQL prompt is:
Mysql>
Note: If you are connecting to another machine, you need to add a parameter-H Machine IP address.

Third, add new users
Format: grant permission on database. * To username @ login host identified by "password"
For example, you can add a user user1 with the password password1 so that the user can log on to the machine and have the permission 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 user1 @ localhost identified by "password1 ";
If you want the user to log on to MySQL on any machine, change localhost to "% ".
If you do not want user1 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * To user1 @ localhost identified "";

Step 4: operate databases
Log on to MySQL and run the following commands at the MySQL prompt. Each Command ends with a semicolon.
1. display the Database List.
Show databases;
By default, two databases are available: MySQL and test. MySQL inventory contains the MySQL system and user permission information. We change the password and add users, in fact, this database is actually operated.
2. display the data tables in the database:
Use MySQL;
Show tables;
3. display the data table structure:
Describe table name;
4. Create and delete databases:
Create Database database name;
Drop database database name;
5. Create a table:
Use Database Name;
Create Table Name (Field List );
Drop table name;
6. Clear the table records:
Delete from table name;
7. display the records in the table:
Select * from table name;

Step 5: export and import data
1. Export data:
Mysqldump -- opt test> mysql. Test
Export the database test database to the mysql. Test file, which is a text file
For example, mysqldump-u root-p123456 -- databases dbname> MySQL. dbname
Export the database dbname to the mysql. dbname file.
2. import data:
Mysqlimport-u root-p123456 <MySQL. dbname.
No need to explain it.
3. Import text data to the database:
Field data of text data is separated by the tab key.
Use test;
Load data local infile "file name" into Table table name;

6. Execute external script files

Mysql-U test-p1234 -- database bugfree <D:/greenamp/bugfree. SQL