Basic MySQL operations and troubleshooting

Account maintenance and restoration of the MySQL System

* 1. Case Description

In programming and development, it is critical to involve the database permission maintenance module. Once a problem occurs, it is difficult to troubleshoot and solve the problem. This article describes the knowledge of Mysql Data permissions, from basic to in-depth, from comprehensiveness to detail. At the end of the article, we will sort out the permission management problems encountered at work.

* 2. Case Analysis and Solution

The following are my frequently used commands in MySQL development and maintenance and their explanations:

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;

1: Use the show statement to find out the current database on the server:

Mysql> show databases;

2. Create a database named mysqldata

Mysql> Create Database mysqldata;

3. Select the database you created

Mysql> Use mysqldata; (when you press the Enter key to see database changed, the operation is successful !)

4: view the tables in the current database

Mysql> show tables;

5. Create a database table

Mysql> Create Table mytable (name varchar (20), sex char (1 ));

6: display the table structure:

Mysql> describe mytable;

7. Add records to the table

Mysql> insert into mytable values ("hyq", "M ");

8: load data into database tables in text mode (for example, D:/mysql.txt)

Mysql> load data local infile "D:/mysql.txt" into Table mytable;

9: import the. SQL file command (for example, D:/MySQL. SQL)

Mysql> use database;

Mysql> source D:/MySQL. SQL;

10: delete a table

Mysql> drop table mytable;

11: Clear the table

Mysql> Delete from mytable;

12: Update table data

Mysql> Update mytable set sex = "F" where name = 'hyq ';

Posted on happytian read (6) Comments (0) EDIT favorites to 365key

13. Back up the database

Mysqldump-u root Database Name> XXX. Data

14: Example 2: connect to MySQL on the remote host

Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3. Exit MySQL command: exit (Press ENTER)

The MySQL permission system involves two concepts:
1: Authentication-> determine whether the user is allowed to connect to the Database Server
2: Authorization-> determine whether the user has sufficient permissions to execute query requests.
Obviously, we can see from the above that, if the authentication fails, which authorization cannot be performed. Here we need to pay attention to two tables, namely the user table and the DB table in the MySQL database.
The User table is unique to some extent, because it is the only table that works in the authentication and authorization phases of permission requests, it is also the only permission table for storing MySQL Server-related permissions. In the authentication phase, it is only responsible for authorizing the user to access the MySQL server, and determining the maximum number of connections and the maximum number of concurrent connections per hour. In the authorization phase, the user determines whether the user authorized to access the server is granted the global permission to operate the database, and determines the maximum number of queries and updates per hour for the user.
DB tables are used to grant permissions to each user for each database. You can view the DB fields.
User and permission management commands:
Create user: used to create a new user account (this command is available from version 5.0). No permissions are assigned when this user is created, after creation, you must use the grant command to assign the corresponding permissions to the user.
Eg: create user guest @ localhost identified by '201312 ';
Grant select on mydb. * to guest @ localhost;
Drop User: delete a user account (note that only accounts without any permissions can be deleted before version 4.1.1, and any accounts can be deleted after version 5.0.2)
Eg: Drop user guest;
Rename User: rename a user account.
GRANT: used to manage access permissions, that is, granting permissions to user accounts. Of course, it can also create a new user account.
Eg: grant select, insert, update, delete on new_db. * to guest @ '%' identified by '123 ';
Grant permission on database. Table to user @ access mode identified by password
Grant select on mydb. * to guest @ localhost identified by '20140901 ';
BTW: If you need an account with a blank password or no password, you must first run the create USER command and then run
Grant. Perform the following operations:
Grant all privileges on mydb. * to visitor @ '%'; the visitor is not created in the User table of the database.
The 1133 error "can't find any matching row in the User table" will occur ". Grant can only create
An account with a password.
Revoke: delete an account. For more information, see the MySQL documentation.

* 3. Experience summary, preventive measures, and normative suggestions

The following is a summary of some problems I encountered in using MySQL. I only introduced the general idea, and the details should be further supported.

1. MySQL Database Remote Access Authorization

1. Change table
It may be that your account is not allowed to log on remotely, but only on localhost. At this time, you only need to log in to MySQL on the computer of localhost, and change the "host" entry in the "user" table in the "MySQL" database to "%" from "localhost"
Mysql-u root-P
Mysql> use MySQL;
Mysql> Update user set host = '%' where user = 'root ';
Mysql> select host, user from user;

2. Authorization Law
Run the following command on the machine where MySQL is installed:
1. D: \ mysql \ bin \> mysql-H localhost-u Root
// You can access the MySQL server.
2. mysql> grant all privileges on *. * To 'root' @ '%' with grant option
// Grant data access permissions to any host
For example, if you want myuser to use mypassword to connect to the MySQL server from any host.
Grant all privileges on *. * To 'myuser' @ '%' identified by 'mypassword' wi
Th grant option;
If you want to allow myuser to connect to the MySQL server from a host whose IP address is 192.168.1.6, and use mypassword as the password
Grant all privileges on *. * To 'myuser' @ '192. 168.1.3 'identified
'Mypassword' with grant option;
3. mysql> flush privileges
// The modification takes effect.
4. mysql> exit
Log out of the MySQL server, so that you can log on to any other host as a root user.

2. MySQL changes the root user password

2.1 shut down the MySQL process in the system and run the following command: killall-term mysqld

2.2 run the following command to start MySQL without checking its permissions. Mysqld_safe-Skip-grant-Table &,

2.3 use a blank password to log on to MYSQL as the root user. Mysql-u Root

2.4 modify the password of the root user. Mysql> Update mysql. User SET Password = PASSWORD ('newpassword') where user = 'root'; mysql> flush privileges; mysql> quit

2.5. Restart.