05.sql导入数据库+导出数据库

导入数据库

导入数据库前必须创建一个空数据库！！！

创建空数据库

mysql -uroot -p123456 -A

[root@localhost ~]#  mysql -uroot -p123456 -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 37

Server version: 5.5.60-MariaDB MariaDB Server

 

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

create database info;

MariaDB [(none)]> create database info;

Query OK, 1 row affected (0.00 sec)

 

导入方法一

mysql -uroot -p123456 info < /root/test.sql

注意“<”两次的空格，必须加上！！！

[root@localhost ~]# mysql -uroot -p123456 info < /root/test.sql

 

mysql -uroot -p123456

[root@localhost /]# mysql -uroot -p123456

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 44

Server version: 5.5.60-MariaDB MariaDB Server

 

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

use info;

MariaDB [(none)]> use info;

Database changed

 

show tables;

MariaDB [info]> show tables;

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

| Tables_in_info |

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

| userinfo       |

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

1 row in set (0.00 sec)

 

导入方法二

create database test;

MariaDB [info]> create database test;

Query OK, 1 row affected (0.00 sec)

 

use test;

MariaDB [info]> use test;

Database changed

 

source /root/info.sql

MariaDB [test]> source /root/info.sql

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Query OK, 1 row affected (0.00 sec)

 

show tables;

MariaDB [Test]> show tables;

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

| Tables_in_Test |

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

| userinfo       |

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

1 row in set (0.00 sec)

 

导出数据库

语法：mysqldump –u用户名 –p密码 数据库名>导出数据库的文件名.sql

参数：

-B：导出整个数据库包含建库语句

-A：导出全部数据库

 

导出数据库

mysqldump -uroot -p123456 Test>test.sql

[root@localhost ~]# mysqldump -uroot -p123456 Test>test.sql

 

导出包含建库语句数据库

mysqldump -uroot -p123456 -B Test>info.sql

[root@localhost ~]# mysqldump -uroot -p123456 -B Test>info.sql

 

导出全部数据库

mysqldump -uroot -p123456 -A >all.sql

[root@localhost ~]# mysqldump -uroot -p123456 -A >all.sql

 

导出select结果到文本

[root@localhost /]# mkdir info

[root@localhost /]# chmod 777 info

 

select * from Test.userinfo into outfile '/info/info.bak';

MariaDB [(none)]> select * from Test.userinfo into outfile '/info/info.bak';

Query OK, 3 rows affected (0.01 sec)

 

select * into outfile '/info/infobak.txt' from Test.userinfo;

MariaDB [(none)]> select * into outfile '/info/infobak.txt' from Test.userinfo;

Query OK, 3 rows affected (0.00 sec)