MySQL Command Line[mysql命令行常用命令]

作者:计算机专家

数据库迁移是我们经常可遇到的问题,对于少量的数据,迁移基本上不会有什么问题。生产环境中,有以下情况需要做迁移工作:

前文已经提到过 MySQL 采用 LOAD DATA 导入文本文件,本节将对 MySQL 导入/导出数据进行介绍,毕竟数据的操作和管理都离不开与外界的大量信息交换。

 

磁盘空间不够。比如一些老项目,选用的机型并不一定适用于数据库。随着时间的推移,硬盘很有可能出现短缺; 业务出现瓶颈。比如项目中采用单机承担所有的读写业务,业务压力增大,不堪重负。如果 IO 压力在可接受的范围,会采用读写分离方案; 机器出现瓶颈。机器出现瓶颈主要在磁盘 IO 能力、内存、CPU,此时除了针对瓶颈做一些优化以外,选择迁移是不错的方案; 项目改造。某些项目的数据库存在跨机房的情况,可能会在不同机房中增加节点,或者把机器从一个机房迁移到另一个机房。再比如,不同业务共用同一台服务器,为了缓解服务器压力以及方便维护,也会做迁移。

导出数据

MySQL 中你可以使用 SELECT...INTO OUTFILE 语句来简单的导出数据到文本文件上。

 

MySQL迁移通常使用的有三种方法:

使用 SELECT ... INTO OUTFILE 语句导出数据

以下实例中我们将数据表 EMPLOYEE 数据导出到 /tutorials.txt 文件中:

mysql> SELECT * FROM EMPLOYEE
    -> INTO OUTFILE '/tutorials.txt';

你可以通过命令选项来设置数据输出的指定格式,以下实例为导出 CSV 格式:

mysql> SELECT * FROM EMPLOYEE INTO OUTFILE '/tutorials.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY 'rn';

SELECT ... INTO OUTFILE 语句有以下属性:

  • LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作,SELECT句法。为了将一个数据库的数据写入一个文件,使用SELECT ... INTO OUTFILE,为了将文件读回数据库,使用LOAD DATA INFILE。
  • SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上,因此您必须拥有FILE权限,才能使用此语法。
  • 输出不能是一个已存在的文件。防止文件数据被篡改。
  • 你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
  • 在UNIX中,该文件被创建后是可读的,权限由MySQL服务器所拥有。这意味着,虽然你就可以读取该文件,但可能无法将其删除。

MySql下载地址:www.mysql.org

1、数据库直接导出,拷贝文件到新服务器,在新服务器上导入。

导出表作为原始数据

mysqldump 是 MySQL 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本,其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。
使用mysqldump导出数据需要使用 --tab 选项来指定导出文件指定的目录,该目标必须是可写的。以下实例将数据表 EMPLOYEE 导出到 /tmp 目录中:

$ mysqldump -u root -p --no-create-info 
            --tab=/tmp pi EMPLOYEE
password ******

 

2、使用第三方迁移工具。

导出 SQL 格式的数据

导出SQL格式的数据到指定文件,如下所示:

$ mysqldump -u root -p pi EMPLOYEE > database_dump.txt
password ******

如果你需要导出整个数据库的数据,可以使用以下命令:

$ mysqldump -u root -p pi > database_dump.txt
password ******

如果需要备份所有数据库,可以使用以下命令(该方法可用于实现数据库的备份策略):

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

第一招、mysql服务的启动和停止

3、数据文件和库表结构文件直接拷贝到新服务器,挂载到同样配置的MySQL服务下。

导入数据

MySQL中可以使用两种简单的方式来导入MySQL导出的数据。

net stop mysql

第一种方案的优点:会重建数据文件,减少数据文件的占用空间,兼容性最好,导出导入很少发生问题,需求灵活。缺点:使用传统导出导入时间占用长。

使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ,将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定LOCAL关键词,则表明从客户主机上按路径读取文件。如果没有指定,则文件在服务器上按路径读取文件。

更多选项包含分隔符、导入的列等
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY 'rn';

mysql> LOAD DATA LOCAL INFILE 'dump.txt'
 -> INTO TABLE mytbl (b, c, a);

net start mysql

第二种方案的优点:设置完成后传输无人值守,自动完成。缺点:不够灵活,设置繁琐,传输时间长,异常后很难从异常的位置继续传输。

使用 mysqlimport 导入数据

mysqlimport客户端提供了LOAD DATA INFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOAD DATA INFILE子句。从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令:

$ mysqlimport -u root -p --local database_name dump.txt
password *****

mysqlimport命令可以指定选项来设置指定格式,命令语句格式如下:

$ mysqlimport -u root -p --local --fields-terminated-by=":" 
   --lines-terminated-by="rn"  database_name dump.txt
password *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序:

$ mysqlimport -u root -p --local --columns=b,c,a 
    database_name dump.txt
password *****

 

第三种方案的优点:时间占用短,文件可断点传输,操作步骤少。缺点:新旧服务器中MySQL版本及配置必须相同,可能引起未知问题。

小结

采用本节介绍的方法能够实现 MySQL 的数据导出和导入,也能够组合起来实现不同数据库之间的数据拷贝、转移等。

至此,《MySQL学习笔记》系列就结束了。学习笔记以列表的形式记录了 MySQL 的常用管理和操作命令,希望在后续的实践中能够以手册形式多多发挥作用……

 

假如数据库迁移是因为业务瓶颈或项目改造等需要变动数据表结构的(比如分区分表),我们便只能使用第一种方法了。

第二招、登陆mysql

使用MySQL的SELECT INTO OUTFILE 、LOAD DATA INFILE快速导出导入数据

mysql -u用户名-p用户密码

LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。MySQL官方文档也说明了,该方法比一次性插入一条数据性能快20倍。

mysql -uroot -p, 回车後提示你输入密码,输入12345,然後回车即可进入到mysql中了,mysql的提示符是:

当用户一前一后地使用SELECT ... INTO OUTFILELOAD DATA INFILE将数据从一个数据库写到一个文件中,然后再从文件中将它读入数据库中时,两个命令的字段和行处理选项必须匹配。否则,LOAD DATA INFILE 将不能正确地解释文件内容。

mysql> 注意,如果是连接到另外的机器上,则需要加入一个参数-h机器IP

下面是一个项目的例子,MySQL由windows平台迁移到Linux平台,数据总量12G

 

Windows平台导出数据:

第三招、增加新用户

tables.txt是保存数据表名称的文件,通过从文件中读取数据表名称,循环导出所有表:如果过程中摄及到分表,可根据分表规则修改导出的sql语句和批处理代码,非常灵活。

grant 权限on 数据库.* to 用户名@登录主机identified by "密码"

@echo off & setlocal enabledelayedexpansionfor /f %%i in (tables.txt) do ( set table=%%i echo "dump table -- !table! --" mysql -uroot -p12345678 codetc_old -e "SELECT * INTO OUTFILE 'F:/MySQL/Uploads/!table!.txt' FIELDS TERMINATED BY ',' FROM !table!")pause

增加一个用户user1密码为password1,让其可以在本机上登录, 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql,然後键入以下命令:grant select,insert,update,delete on *.* to [email protected] Identified by "password1";如果希望该用户能够在任何机器上登陆mysql,则将localhost改为"%"。

Linux平台导入数据:

如果你不想user1有密码,可以再打一个命令将密码去掉。grant select,insert,update,delete on mydb.* to [email protected] identified by "";

#!/bin/bashwhile read linedo mysql -uroot -p12345678 codetc_new -e "LOAD DATA INFILE '/var/lib/mysql-files/$line.txt' INTO TABLE $line FIELDS TERMINATED BY ','"done  tables.txt

 

数据导入之前需在新机器上创建表结构,12G的数据导出用时3分钟左右,导入用时4分钟左右

 

总结

第四招: 操作数据库登录到mysql中,然後在mysql的提示符下运行下列命令,每个命令以分号结束。

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。如果你想了解更多相关内容请查看下面相关链接

 

显示数据库列表。

show databases; 缺省有两个数据库:mysql和test。mysql库存放着mysql的系统和用户权限信息,我们改密码和新增用户,实际上就是对这个库进行操作。

显示库中的数据表:

use mysql; show tables;

显示数据表的结构:

describe 表名;

建库与删库:

create database 库名; drop database 库名;

建表:

use 库名; create table 表名(字段列表); drop table 表名;

清空表中记录:

delete from 表名;

显示表中的记录:

select * from 表名;

 

 

第五招、导出和导入数据

导出数据:

mysqldump --opt test > mysql.test 即将数据库test数据库导出到mysql.test文件,後者是一个文本文件如:mysqldump -u root -p123456 --databases dbname > mysql.dbname 就是把数据库dbname导出到文件mysql.dbname中。

导入数据:

mysqlimport -u root -p123456 < mysql.dbname。不用解释了吧。

将文本数据导入数据库:

文本数据的字段数据之间用tab键隔开。use test; load data local infile "文件名" into table 表名;

本文由杏彩发布,转载请注明来源

关键词: