Grant MySQL account access to specified database and database operations under Linux system

Grant MySQL account access to specified database under Linux system

Requirements: 1, create a database in MySQL mydata2, new MySQL account admin password 1234563, give the account admin to the database MyData have full operation rights ================================== ==================================== operation is as follows: 1, login MySQL database       MYSQ  -uroot  -p    # In the terminal command line input       123456         #输入密码2, show databases;     #显示数据库列表3, create database MyData;     #建立数据库mydata4, insert into Mysql.user (Host,user,password) VALUES (' localhost ', ' admin ', Password (' 123456 ') ));     #新建账户admin, password 1234565, flush privileges;     #刷新系统授权表6, grant all on mydata.* to ' admin ' @ '% ' identified by ' 123456 ' with GRANT option;     #允许账户admin从任何主机连接到数据库mydata至此, Account Admin has full administrative rights on database MyData. ========================================================================   Extensions:      grant all on mydata.* to ' admin ' @ ' 192.168.1.1 ' identified by ' 123456 ' with GRANT option;     #账户admin只能从192.168.1.1 Connect to database mydata       Grant all on mydata.* to ' admin '@ ' localhost ' identified by ' 123456 ' with GRANT option;     #账户admin只能从本机连接到数据库mydata       Update mysql.user set Password=password (1234) where user= " Admin "and host=" localhost ";   #修改账号admin密码为1234       Revoke all on mydata.* from ' admin ' @ '% ';           #禁止用户admin从任何主机访问数据库mydata        Revoke all on mydata.* from ' Admi n ' @ ' 192.168.1.1 ';     #禁止用户admin从192.168.1.1 Access database mydata       Dalete  from  mysql.user   where  user= "admin" and host= "localhost";     #删除用户admin ====================================================================== Note: Similar to how you do in Windows , please test them yourself.  ========================================================================================================= ===========

purpose of the tutorial: Linux MySQL client Create, delete database, import, export database

Using tools: Putty, SECURECRT and other remote Tools

Applicable system: Linux Series Systems

Operation Process:

1.Mysql clients Create and delete databases:

1) Log in to MySQL database:

[Email protected]~]#/usr/local/mysql/bin/mysql-u Root-Penter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 6Server Version:5.1. --Log Source Distribution Copyright (c) -, ., Oracle and/or its affiliates. All rights reserved. Oracle isA registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'To clear the current input statement. mysql>

To operate according to the host internal installation of MySQL, the above command:/usr/local/mysql/bin/mysql-u root-p Enter the correct password to log in to the MySQL database service (MySQL installed in the/usr/local/mysql/ directory)

2) Create an empty database

Mysql>2006  (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection ID:    7** * *1 row affected (0.00  sec) mysql< /c9>> show databases; +--------------------+| Database           |+--------------------+| information_schema | | Daobidao           | | mysql              |+--------------------+ 3 inch Set (0.00 sec)

Above command: CREATE database name;  You can create an empty database, such as the command: Create Database Daobidao; Create an empty database of Daobidao; command: show databases; All databases are displayed.

3) Delete Database

Mysql>2006  (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection ID:    9** * *0 rows affected (0.01  sec) MySQL > show Databases; +--------------------+| Database           |+--------------------+| information_schema | | mysql              |+--------------------+2  inset (0.00 sec)

The above command: drop database name; You can delete one of the databases; For example, command : Drop database Daobidao; Delete Daobidao databases.

2.Mysql client import, export database

1) Import Database [method one]

[[Email protected]~]#/usr/local/mysql/bin/mysql-u root-p Daobidao </root/test.sql    Enter Password:

The above command:/usr/local/mysql/bin/mysql-u root-p the database name to which database you want to import < The database file to import, such as command:/usr/local/mysql/bin/mysql-u root-p da Obidao </root/test.sql Import the database file Test.sql file into the Daobidao database; (but which database needs to be imported to)

2) Import Database [method two]

Mysql>CREATE DATABASE Daobidao; Query OK,1Row affected (0.00sec) MySQL>Use daobidao;database changedmysql> source/root/Test.sql; ERROR2006(HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection ID: -Current Database:daobidao Query OK,0Rows Affected (0.00sec) Query OK,0Rows Affected (0.00sec) Query OK,0Rows Affected (0.00sec)

The above command: 1) Create a database, 2) use the database, 3) import the database file; For example, command: 1) create databases Daobidao; Create an empty database; 2) use Daobidao;  Use of the database; 3) Source/root/test.sql; Import the database file. (but which database needs to be imported to be present)

3) Export the data and table structure of the database

[Email protected]~]#/usr/local/mysql/bin/mysqldump-u root-p daobidao >/root/dbd.sqlenter Password:

Above command:/usr/local/mysql/bin/mysqldump-u root-p need to export the database > export the database file name; for example, command:/usr/local/mysql/bin/mysqldump-u root-p Daobidao >/root/dbd.sql will export the Daobidao database and store it in the/root/dbd.sql file.

4) Export the table structure of the database

[Email protected]~]#/usr/local/mysql/bin/mysqldump-u root-p-D daobidao >/root/dbd.sqlenter Password:

Above command:/usr/local/mysql/bin/mysqldump-u root-p-D need to export the database > export the database file name; for example, command:/usr/local/mysql/bin/mysqldump-u root -p-d Daobidao >/root/dbd.sql will daobidao the database export table structure, stored in the/root/dbd.sql file.

Grant MySQL account access to specified database and database operations under Linux system