To mysql basic operations _ MySQL

Basic mysql conversion operations Mysql Directory

1. Database Directory

/Var/lib/mysql

2. configuration file

/Usr/share/mysql (mysql. server command and configuration file)

3. related commands

/Usr/bin (commands such as mysqladmin mysqldump)

4. start the script

/Etc/rc. d/init. d/(directory for starting the script file mysql)

Install

# Yum-y install mysql *

# Service mysqld start

# Netstat-tlpn | grep mysql

# Mysql

MySQL does not have a password by default. it is self-evident that the password is added after installation.

#/Usr/bin/mysqladmin-u root password redhat

# Mysql

ERROR 1045: Access denied for user: 'root @ localhost' (Using password: NO)

An error is displayed, indicating that the password has been modified.

Log on with the modified password

# Mysql-u root-p

Enter password: (Enter the modified password redhat)

Welcome to the MySQL monitor. Commands end with; or/g.

Your MySQL connection id is 4 to server version: 4.0.16-standard

Type 'help; 'or'/h' for help. type'/C' to clear the buffer.

Mysql>

You can use the mysqladmin command to change the password or the database to change the password.

Start and stop

1. start

After MySQL is installed, run the following command to start mysql in the/etc/init. d Directory:

#/Etc/init. d/mysqld start

2. stop

#/Usr/bin/mysqladmin-u root-p shutdown

3. automatic start

1) Check whether mysql is in the auto start list

#/Sbin/chkconfig -- list

2) add mysql to the startup service group of the system.

#/Sbin/chkconfig -- add mysqld

3) delete mysql from the startup service group

#/Sbin/chkconfig -- del mysqld

MysqlCommon operations

1. display the database

Mysql> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Mysql |

| Test |

+ -------------------- +

3 rows in set (0.00 sec)

Mysql has just been installed with three databases: information_schema, mysql, and test. The mysql database is very important. it contains MySQL system information. we change the password and add new users. In fact, we use the relevant tables in this database for operations.

2. display tables in the database

Mysql> use mysql; (open the database. to operate on each database, open the database, similar to foxpro)

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-

Database changed

Mysql> show tables;

+ --------------------------- +

| Tables_in_mysql |

+ --------------------------- +

| Columns_priv |

| Db |

| Func |

| Help_category |

| Help_keyword |

| Help_relation |

| Help_topic |

| Host |

| Proc |

| Procs_priv |

| Tables_priv |

| Time_zone |

| Time_zone_leap_second |

| Time_zone_name |

| Time_zone_transition |

| Time_zone_transition_type |

| User |

+ --------------------------- +

17 rows in set (0.01 sec)

3. display the data table structure

Describe table name;

Mysql> describe user;

4. display records in the table

Select * from table name

Mysql> select * from user;

5. create a database

Create database name

Mysql> create database lifang;

Query OK, 1 row affected (0.01 sec)

6. create a table

Use database name

Create table name (field setting list );

For example, if you create a table name in the just-created lifang database, the table has four fields: id (serial number, auto-increment), xm (name), xb (name), and csny (year of birth ).

Mysql> use lifang;

Database changed

Mysql> create table name (id int (3) auto_increment not null primary key, xm char (8), xb char (2), csny date );

Query OK, 0 rows affected (0.01 sec)

You can use the describe command to view the created table structure.

Mysql> describe name

->;

+ ------- + --------- + ------ + ----- + --------- + ---------------- +

| Field | Type | Null | Key | Default | Extra |

+ ------- + --------- + ------ + ----- + --------- + ---------------- +

| Id | int (3) | NO | PRI | NULL | auto_increment |

| Xm | char (8) | YES | NULL |

| Xb | char (2) | YES | NULL |

| Csny | date | YES | NULL |

+ ------- + --------- + ------ + ----- + --------- + ---------------- +

4 rows in set (0.00 sec)

7. add Records

Mysql> insert into name values ('', 'lifang ', 'female', '2017-05-10 ');

Query OK, 1 row affected, 2 warnings (0.00 sec)

Mysql> insert into name values ('', 'fuyun ', 'female', '2017-07-14 ');

Query OK, 1 row affected, 2 warnings (0.00 sec)

Mysql> insert into name values ('', 'xiaodi ', 'female', '2017-12-28 ');

Query OK, 1 row affected, 2 warnings (0.00 sec)

The select command can be used to verify the result.

Mysql> select * from name;

+ ---- + -------- + ------ + ------------ +

| Id | xm | xb | csny |

+ ---- + -------- + ------ + ------------ +

| 1 | lifang | fe | 1984-05-10 |

| 2 | fuying | fe | 1986-07-14 |

| 3 | xiaodi | fe | 1982-12-28 |

+ ---- + -------- + ------ + ------------ +

3 rows in set (0.00 sec)

8. modify records

For example, change the date of birth of xiaodi to 1985-12-28.

Mysql> update name set csny = '2017-12-28 'where xm = 'xiaodi ';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

9. delete records

Mysql> delete from name where xm = 'xiaodi ';

Query OK, 1 row affected (0.00 sec)

10. delete databases and tables

Drop database name

Drop table name

Mysql> drop table name;

Query OK, 1 rows affected (0.00 sec)

Mysql> drop database lifang;

Query OK, 1 rows affected (0.00 sec)

　

Add mysqlUser

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

Example 1: add a user fuyiing password to redhat so that he can log on to any host 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

Mysql> grant select, insert, update, delete on *. * to fuying @ station12 identified by "redhat ";

Query OK, 1 rows affected (0.00 sec)

The added user is very dangerous. if you know the fuying password, you can log on to your MySQL database on any computer on the internet and do whatever you want, for the solution, see example 2.

Example 2: add a user xiaodi whose password is redhat so that the user can only log on to localhost, the database lifang can be queried, inserted, modified, or deleted (localhost refers to the local host, that is, the host where the MySQL database is located), so that the user knows the xiaodi password, he cannot directly access the database from the Internet, and can only operate the lifang library through the MYSQL host.

Mysql> grant select, insert, update, delete on lifang. * to xiaodi @ localhost identified by "redhat ";

Query OK, 1 rows affected (0.00 sec)

If a new user cannot log on to MySQL, run the following command during logon:

# Mysql-u fuying-p-h 192.168.0.12 (-h is followed by the IP address of the host to be logged on)

Backup and Recovery

1. backup

For example, back up the lifang library created in the previous example to the file back_lifang.

[Root @ station12 mysql] # mysqldump-u root-p -- opt lifang> back_lifang

Enter password:

[Root @ station12 mysql] #

2. Restore

[Root @ station12 mysql] # mysql-u root-p lifang <back_lifang

Enter password:

[Root @ station12 mysql] #