时间:2021-07-01 10:21:17 帮助过:4人阅读
使用工具:Putty、SecureCRT等远程工具
适用系统:Linux 系列系统
操作过程:
1.Mysql 客户端创建、删除数据库:
1)登录Mysql数据库:
- [root@DaoBiDao~]# /usr/local/mysql/bin/mysql -u root -<span style="color: #000000;">p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id </span><span style="color: #0000ff;">is</span> <span style="color: #800080;">6</span><span style="color: #000000;">
- Server version: </span><span style="color: #800080;">5.1</span>.<span style="color: #800080;">60</span>-<span style="color: #000000;">log Source distribution
- 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.
- Oracle </span><span style="color: #0000ff;">is</span> a registered trademark of Oracle Corporation and/<span style="color: #000000;">or its
- affiliates. Other names may be trademarks of their respective
- owners.
- 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.
- mysql</span>>
要根据主机内部安装mysql的情况操作,以上命令:/usr/local/mysql/bin/mysql -u root -p 回车,输入正确密码即可登录mysql数据库服务(mysql安装在/usr/local/mysql/目录下)
2)创建空数据库
- mysql><span style="color: #000000;"> create database daobidao;
- ERROR </span><span style="color: #800080;">2006</span><span style="color: #000000;"> (HY000): MySQL server has gone away
- No connection. Trying to reconnect...
- Connection id: </span><span style="color: #800080;">7</span><span style="color: #000000;">
- Current database: </span>*** NONE ***<span style="color: #000000;">
- Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
- mysql</span>><span style="color: #000000;"> show databases;
- </span>+--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | daobidao |
- | mysql |
- +--------------------+
- <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)删除数据库
- mysql><span style="color: #000000;"> drop database daobidao;
- ERROR </span><span style="color: #800080;">2006</span><span style="color: #000000;"> (HY000): MySQL server has gone away
- No connection. Trying to reconnect...
- Connection id: </span><span style="color: #800080;">9</span><span style="color: #000000;">
- Current database: </span>*** NONE ***<span style="color: #000000;">
- Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.01</span><span style="color: #000000;"> sec)
- mysql</span>><span style="color: #000000;"> show databases;
- </span>+--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- +--------------------+
- <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)导入数据库 [方法一]
- [root@DaoBiDao~]# /usr/local/mysql/bin/mysql -u root -p daobidao < /root/<span style="color: #000000;">test.sql
- 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)导入数据库 [方法二]
- mysql><span style="color: #000000;"> create database daobidao;
- Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
- mysql</span>><span style="color: #000000;"> use daobidao;
- Database changed
- mysql</span>> source /root/<span style="color: #000000;">test.sql;
- ERROR </span><span style="color: #800080;">2006</span><span style="color: #000000;"> (HY000): MySQL server has gone away
- No connection. Trying to reconnect...
- Connection id: </span><span style="color: #800080;">17</span><span style="color: #000000;">
- Current database: daobidao
- Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
- Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
- Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
- ····························</span>
以上命令:1)创建一个数据库,2)使用该数据库,3)导入数据库 文件;例如命令:1)create database daobidao; 创建一个空数据库; 2)use daobidao; 使用该数据库; 3) source /root/test.sql; 导入数据库文件。(但需要导入到哪个数据库必须要存在)
3)导出数据库的数据和表结构
- [root@DaoBiDao~]#/usr/local/mysql/bin/mysqldump -u root -p daobidao > /root/<span style="color: #000000;">dbd.sql
- 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)导出数据库的表结构
- [root@DaoBiDao~]#/usr/local/mysql/bin/mysqldump -u root -p -d daobidao > /root/<span style="color: #000000;">dbd.sql
- 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账户访问指定数据库和数据库操作
标签: