当前位置:Gxlcms > 数据库问题 > Linux系统下授权MySQL账户访问指定数据库和数据库操作

Linux系统下授权MySQL账户访问指定数据库和数据库操作

时间:2021-07-01 10:21:17 帮助过:4人阅读

使用工具:Putty、SecureCRT等远程工具

适用系统:Linux 系列系统

操作过程:

1.Mysql 客户端创建、删除数据库:

1)登录Mysql数据库:

  1. [root@DaoBiDao~]# /usr/local/mysql/bin/mysql -u root -<span style="color: #000000;">p
  2. Enter password:
  3. Welcome to the MySQL monitor. Commands end with ; or \g.
  4. Your MySQL connection id </span><span style="color: #0000ff;">is</span> <span style="color: #800080;">6</span><span style="color: #000000;">
  5. Server version: </span><span style="color: #800080;">5.1</span>.<span style="color: #800080;">60</span>-<span style="color: #000000;">log Source distribution
  6. Copyright (c) </span><span style="color: #800080;">2000</span>, <span style="color: #800080;">2011</span>, Oracle and/<span style="color: #000000;">or its affiliates. All rights reserved.
  7. Oracle </span><span style="color: #0000ff;">is</span> a registered trademark of Oracle Corporation and/<span style="color: #000000;">or its
  8. affiliates. Other names may be trademarks of their respective
  9. owners.
  10. Type </span><span style="color: #800000;">‘</span><span style="color: #800000;">help;</span><span style="color: #800000;">‘</span> or <span style="color: #800000;">‘</span><span style="color: #800000;">\h</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">for</span> help. Type <span style="color: #800000;">‘</span><span style="color: #800000;">\c</span><span style="color: #800000;">‘</span><span style="color: #000000;"> to clear the current input statement.
  11. mysql</span>>

 

 

要根据主机内部安装mysql的情况操作,以上命令:/usr/local/mysql/bin/mysql -u root -p 回车,输入正确密码即可登录mysql数据库服务(mysql安装在/usr/local/mysql/目录下)

2)创建空数据库

 
  1. mysql><span style="color: #000000;"> create database daobidao;
  2. ERROR </span><span style="color: #800080;">2006</span><span style="color: #000000;"> (HY000): MySQL server has gone away
  3. No connection. Trying to reconnect...
  4. Connection id: </span><span style="color: #800080;">7</span><span style="color: #000000;">
  5. Current database: </span>*** NONE ***<span style="color: #000000;">
  6. Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
  7. mysql</span>><span style="color: #000000;"> show databases;
  8. </span>+--------------------+
  9. | Database |
  10. +--------------------+
  11. | information_schema |
  12. | daobidao |
  13. | mysql |
  14. +--------------------+
  15. <span style="color: #800080;">3</span> rows <span style="color: #0000ff;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800080;">0.00</span> sec)

 

以上命令:create database 库名; 就可以创建一个空的数据库,例如命令:create database daobidao;  创建一个daobidao的空数据库;命令:show databases; 显示所有的数据库。

3)删除数据库

  1. mysql><span style="color: #000000;"> drop database daobidao;
  2. ERROR </span><span style="color: #800080;">2006</span><span style="color: #000000;"> (HY000): MySQL server has gone away
  3. No connection. Trying to reconnect...
  4. Connection id: </span><span style="color: #800080;">9</span><span style="color: #000000;">
  5. Current database: </span>*** NONE ***<span style="color: #000000;">
  6. Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.01</span><span style="color: #000000;"> sec)
  7. mysql</span>><span style="color: #000000;"> show databases;
  8. </span>+--------------------+
  9. | Database |
  10. +--------------------+
  11. | information_schema |
  12. | mysql |
  13. +--------------------+
  14. <span style="color: #800080;">2</span> rows <span style="color: #0000ff;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800080;">0.00</span> sec)

 

 

以上命令:drop database 库名; 就可以删除一个数据库;例如命令:drop database daobidao; 就删除了daobidao 数据库。

2.Mysql客户端导入、导出数据库

1)导入数据库 [方法一]

 
  1. [root@DaoBiDao~]# /usr/local/mysql/bin/mysql -u root -p daobidao < /root/<span style="color: #000000;">test.sql
  2. Enter password:</span>

 

以 上命令:/usr/local/mysql/bin/mysql -u root -p 需要导入到哪个数据库的数据库名 < 需要导入的数据库文件;例如命令:/usr/local/mysql/bin/mysql -u root -p daobidao < /root/test.sql   将数据库文件test.sql文件导入到daobidao数据库中; (但需要导入到哪个数据库必须要存在)

2)导入数据库 [方法二]

 
  1. mysql><span style="color: #000000;"> create database daobidao;
  2. Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
  3. mysql</span>><span style="color: #000000;"> use daobidao;
  4. Database changed
  5. mysql</span>> source /root/<span style="color: #000000;">test.sql;
  6. ERROR </span><span style="color: #800080;">2006</span><span style="color: #000000;"> (HY000): MySQL server has gone away
  7. No connection. Trying to reconnect...
  8. Connection id: </span><span style="color: #800080;">17</span><span style="color: #000000;">
  9. Current database: daobidao
  10. Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
  11. Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
  12. Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
  13. ····························</span>

 

以上命令:1)创建一个数据库,2)使用该数据库,3)导入数据库 文件;例如命令:1)create database daobidao; 创建一个空数据库; 2)use daobidao; 使用该数据库; 3) source /root/test.sql;  导入数据库文件。(但需要导入到哪个数据库必须要存在)

3)导出数据库的数据和表结构

 
  1. [root@DaoBiDao~]#/usr/local/mysql/bin/mysqldump -u root -p daobidao > /root/<span style="color: #000000;">dbd.sql
  2. Enter password:</span>

 

以上命令:/usr/local/mysql/bin /mysqldump -u root -p 需要导出的数据库 > 导出存放的数据库文件名; 例如命令:/usr/local/mysql/bin/mysqldump -u root -p daobidao > /root/dbd.sql 将daobidao 数据库导出,存放在/root/dbd.sql文件。

4)导出数据库的表结构

 
  1. [root@DaoBiDao~]#/usr/local/mysql/bin/mysqldump -u root -p -d daobidao > /root/<span style="color: #000000;">dbd.sql
  2. Enter password:</span>

 

以上命令:/usr/local/mysql/bin /mysqldump -u root -p -d 需要导出的数据库 > 导出存放的数据库文件名; 例如命令:/usr/local/mysql/bin/mysqldump -u root -p -d daobidao > /root/dbd.sql 将daobidao 数据库导出表结构,存放在/root/dbd.sql文件。

Linux系统下授权MySQL账户访问指定数据库和数据库操作

标签:

人气教程排行