Mysql common operation commands

# mysql common operation commands #

1. Install mysql

Yum install mysql mysql-server

/ etc/init.d/mysqld start # # enable mysqld service

two。 Setup and login

Mysql_secure_installation # # after installing mysql for the first time, you can use this command to initially set up mysql.

Mysql-uroot-predhat # # Log in to the mysql database locally (ps-aux | grep mysql kill-9)

Mysqladmin-uroot-predhat password westos # # modify the local mysql,root password

Mysqladmin-uroot-predhat-h 172.25.8.1 password westos # # modify the remote 172.25.8.1mysql server, root password

3. Operation command

Library operation:

Show databases; # # display database

Use mysql; # # enter the database (if Database changed appears by pressing enter key, the operation is successful!

Show tables; # # display tables in the database

Desc user; # # View the structure of user

Flush privileges; # # refresh database information

Select host,user,password from user; # # query the host,user,password field in user table

Create database westos; # # create westos database

Use westos; # # enter the database westos

Table operation:

Create table linux (# # create table, table name linux, field username,password

Username varchar (15) not null

Password varchar (15) not null)

Select * from mysql.user; # # query all the contents of the user table under the mysql library

Alter table linux add age varchar (4); # # add age fields to the linux table

Desc linux; # # View the structure of linux table

ALTER TABLE linux DROP age # # Delete the age field from the linux table

ALTER TABLE linux ADD age VARCHAR (4) AFTER username # # add field age after username field in linux table

Desc linux; # # View the structure of linux table

Insert into linux values ('user1',18,'passwd1'); # # insert username=user1,age=18,password=password1 into linux table

Update linux set password='passwd2' where username= "user1"; # # Update the password of user1 in the linux table to password2

Delete from linux where username='user1'; # # Delete all the contents of user1 in the linux table

Select * from linux; # # can be viewed

User Management:

CREATE USER hjy@localhost identified by 'westos'; # # create a local user hjy and add the password westos, which is encrypted by default

CREATE USER hee@'%' identified by 'redhat'; # # creating a user hee,% means that this account can be logged in from any host

Select host,User,Password from user; # # query the host,user,password field in user table

Grant select on *. * to user1@localhost identified by 'passwd1'; # # authorizes user1 with a password of passwd1 and can only query the contents of the database locally

Grant all on mysql.* to user2@'%' identified by 'passwd2'; # # authorizes user2 with a password of passwd2, can log in to mysql from any remote host and can operate on the mysql database at will (% changed to ip to specify this ip login)

FLUSH PRIVILEGES; # # reload Authorization Table

SHOW GRANTS FOR hjy@localhost; # # View user authorization

REVOKE DELETE,UPDATE,INSERT on mysql.* from hjy@localhost; # # revoke a user's DELETE,UPDATE,INSERT permission to mysql

REVOKE all on mysql.* from hjy@localhost; # # revoke all user permissions

DROP USER hjy@localhost; # # deleting a user

Backup

/ var/lib/mysql

Mysqldump-uroot-predhat-- all-databases # # command to back up all data

Mysqldump-uroot-predhat mysql > mysql.bak # # backup mysql library is imported to mysql.bak

Mysql-uroot-predhat-e "create database westos;" # # create a database

Mysql-uroot-predhat westos < mysql.bak # # restore mysql.bak to westos library

Password recovery

/ etc/init.d/mysqld stop

Mysqld_safe-- skip-grant-tables & # # skip the grant-tables authorization table and log in to the local mysql database without authentication

Update mysql.user set password=password ('westos') where user='root'; # # Update the password of the conditional root user in the mysql.user table is westos

/ etc/init.d/mysql restart # # restart nysql