MySQL及相关套件操作说明

MySQL及相关套件操作说明一、达成目标相关设置完成后，能够用excel刷数据，数据库内数据定时更新。二、相关组件1、MySQL数据库、mysql-connector-odbc2、NavicatforMySQL3、ETL工具、jdk、jre三、MySQL安装与配置1、工具\原料a.操作系统b.MySQL安装文件2、方法步骤a.MySQL安装文件分为两种，一种是msi格式的，一种是zip格式的。如果是msi格式的可以直接点击安装，按照它给出的安装提示进行安装（相信大家的英文可以看懂英文提示），一般MySQL将会安装在C:\ProgramFiles\MySQL\MySQLServer5.6该目录中；zip格式是自己解压，解压缩之后其实MySQL就可以使用了，但是要进行配置。b.解压之后可以将该文件夹改名，放到合适的位置，个人建议把文件夹改名为MySQLServer5.6，放到C:\ProgramFiles\MySQL路径中。当然你也可以放到自己想放的任意位置。c.配置环境变量：我的电脑-属性-高级-环境变量选择PATH,在其后面添加:你的mysqlbin文件夹的路径(如:C:\ProgramFiles\MySQL\MySQLServer5.6\bin)PATH=.......;C:\ProgramFiles\MySQL\MySQLServer5.6\bin(注意是追加,不是覆盖)d.配置完环境变量之后先别忙着启动mysql，我们还需要修改一下配置文件（如果没有配置，之后启动的时候就会出现图中的错误哦！:错误2系统找不到文件），mysql-5.6.1X默认的配置文件是在C:\ProgramFiles\MySQL\MySQLServer5.6\my-default.ini，或者自己建立一个my.ini文件，在其中修改或添加配置（如图）：[mysqld]basedir=C:\ProgramFiles\MySQL\MySQLServer5.6（mysql所在目录）datadir=C:\ProgramFiles\MySQL\MySQLServer5.6\data（mysql所在目录\data）e.5.以管理员身份运行cmd（一定要用管理员身份运行，不然权限不够），输入：cdC:\ProgramFiles\MySQL\MySQLServer5.6\bin进入mysql的bin文件夹(不管有没有配置过环境变量，也要进入bin文件夹，否则之后启动服务仍然会报错误2)输入mysqld-install(如果不用管理员身份运行，将会因为权限不够而出现错误：Install/RemoveoftheServiceDenied!)安装成功f.安装成功后就要启动服务了，继续在cmd中输入:netstartmysql（如图）,服务启动成功！此时很多人会出现错误，请看注意：注意：这个时候经常会出现错误2和错误1067。如果出现“错误2系统找不到文件”，检查一下是否修改过配置文件或者是否进入在bin目录下操作，如果配置文件修改正确并且进入了bin文件夹，需要先删除mysql（输入mysqld-remove）再重新安装（输入mysqld-install）；如果出现错误1067，那就是配置文件修改错误，确认一下配置文件是否正确。g.服务启动成功之后，就可以登录了，如图，输入mysql-uroot-p（第一次登录没有密码，直接按回车过）,登录成功！3、注意事项1、默认账户“root”是没有密码的，需要密码可以用语句设置，也可以安装navicatformysql后用其设置密码，建议用第二种方法，简单明了不易出错。2、初次使用的账户权限有限，通常会赋予默认账户较高的权限。打开cmd输入：cdC:\ProgramFiles\MySQL\MySQLServer5.6\bin回车（cdmysql根目录\bin，如果根目录不在C盘，需再输入：e:（所在盘符:）），再输入：mysql-u账户-p密码回车，此时已登录MySQL，再输入:Grantallprivilegeson*.*to‘账户名’@’%’identifiedby‘密码’;(注意分号要保留)，表示赋予指定账户所有数据库的所有权限，权限可以重复赋予，比如先赋予“开单明细”数据的的全部权限，再赋予”test.累计开单明细”的select权限，方便数据库的管理。3、MySQL比较依赖索引，所以最好给比较大的表加上索引，提高查询效率。4、MySQL默认的日期数据类型是“timestamp”，默认情况下对于导入的空日期数据会自动更新为当前系统日期，对于大部分数据而言这种设置是很不必要的，通常将数据类型改成“datetime”或者将“timestamp”的默认值改为“NULL”并且将“根据当前时间戳更新”取消勾选(在NavicatforMySQL里面设置，右键数据表-设计表)。5、my.ini文件设置注意要取消开头的井号和空格，否则无法生效。四、NavicatforMySQL安装与配置1、安装NavicatforMySQL是绿色安装，解压即可直接运行。2、连接数据库初次使用需要建立连接。输入ip端口帐号密码即可直接连接，如果连接失败可能是MySQL服务没有启动，端口默认是3306，root是MySQL默认帐号，如果连接本地库ip可以填localhost。3、创建数据库右键点击连接名称选择新建数据库，信息填写如图，保险起见字符集用utf8，否则数据容易乱码。4、数据库常用操作主要用于抽取数据、保存代码作为计划功能的节点。制定定时工作计划，可以选择已保存的查询代码作为工作节点，其功能与SQLServer的作业类似。图中新建计划之后选择了两个已保存的查询作为节点。点击[设置计划任务]，在弹窗的计划选项卡中点击[新建]可以设置计划启动时间，启动后会按顺序执行代码五、ETL工具配置、安装与使用1、无须安装，直接可用，点击文件内的Spoon.bat即可启动2、将mysql-connector-java-5.1.39-bin.jar该文件放入ETL根目录的lib文件夹下，否则无法连接mysql。3、安装jdk及配置环境变量a.jdk下载好后双击安装包即可安装，无特别需求默认设置即可。b.右键点击计算机-属性-高级系统设置-高级选项卡，点击环境变量，需要配置三个属性path、PANTAHO_JAVA_HOME、calsspath（只配置path和classpath也可以，配置方法略有不同，但是不容已出错）PANTAHO_JAVA_HOME：在弹窗的用户变量下面点击新建变量名写“PANTAHO_JAVA_HOME”，变量值写jdk的安装路径，例如：C:\ProgramFiles\Java\jdk1.8.0_91Path：在弹窗的系统变量里面找到path打开，在最后面追加”.%PENTAHO_JAVA_HOME%\bin”calsspath：在弹窗的系统变量下面点击新建，变量名写“classpath”，变量值写“.%PENTAHO_JAVA_HOME%\lib\dt.jar;.%PENTAHO_JAVA_HOME%\lib\tools.jar”4、安装jre，默认安装即可。5、ETL工具主要分为两部分：转换和作业，转换可以作为一次输入输出的节点，作业可以按顺序执行转换。6、转换：在左侧边栏主对象树选项卡下双击转换即可新建转换，核心对象下面选择输入下的表输入，拖动到工作区即可新建表输入节点，同样在输出下拖动表输出到工作区可新建表述出节点。二者配置类似，只介绍表输出双击表输出节点，再弹窗中配置下列信息：【步骤名称】：按需填写【数据库连接】：点击新建，已有连接点击编辑，输入【连接名称】，【连接类型】按实际选择，我的目标是MySQL，选择MySQL，【连接方式】默认，【设置】如实填写，UseResultStreamingCursor取消勾选，【高级】选项卡明明参数新增“characterEncoding”对应值填写“utf8”，否则乱码，表输入如果是SQLServer可以不设置此参数，设置完成后可点击测试是否设置成功。【目标表】：点击浏览，选择需写入数据的目标表即可。【剪裁表】：勾选表示先清空目标表在写入，不勾选表示追加。其他的可以默认将鼠标放在表输入节点上会向下弹出提示窗口，点击第四个按钮生成目标流向，在点击表输出完成转换设置。7、作业，在左侧边栏主对象树选项卡下双击作业即可新建作业目前我们所需要的任务都是转换完成的，所以在作业里面主要的都是转换节点作业需要一个开始和一个结束节点，即和，作为作业的起始和结束，否则作业正常启动也无法正常结束。根据需要执行的转换数量加入转换节点，鼠标放在节点上自动弹出步骤连接按钮，依次连接开始-转换-结束。配置转换按钮。双击转换节点，在【转换文件名】右侧点击，选择已保存的转换文件，确定即可。最后在左上角点击保存到指定位置，确保制作定时任务可以找到，保存文件的拓展明是.kjb。8、定时任务制作。定时任务是用系统自带的【任务计划程序】定时调用配置好的批处理文件来完成定时作业。批处理文件制作：新建一个.txt文件，输入以下信息其中”E:\data-integration”是ETL的根目录，“\panbat”是我自己创建存放转换和作业的文件夹，“mysql_update.kjb”是保存的作业文件，“panlog.log”是日志文件，如果制定路径的文件不存在，运行程序后会自动创建。也可以不执行作业而只执行一个转换，只要把“Kitchen”改成“Pan”，执行文件改成保存的转换文件即可，保存之后修改拓展名为“.bat”。在控制面板中打开任务计划程序，在页面右侧找到【创建基本任务…】并单击，根据提示填写即可，其中在启动程序环节选择选择制作好的批处理文件之后，在【起始于】项目中填写“E:\data-integration\panbat\”,即批处理文件所在的文件夹，之后完成计划任务设置。选中已设置完成的任务可以在右侧点击【启用】测试是否设置成功。9、注意事项：a.ETL工具在设置表输入的时候尽量选择表和运算量小的视图以提高传输效率，b.表输出的目标表和表输入的目标表的字段名、文本数据类型要一直，避免数字、日期转换成数字文本和日期文本，或者反转，会大幅降低传输效率。c.ETl传输也会受电脑配置、网速限制，要控制传输的数据量。六、Excel与MySQL连接Excel抽取MySQL数据通过设置好的数据库接口连接指定的数据表，抽取的数据可以直接生成透视表，也可以生成表格。1、Excel连接MySQL获取数据需要对应的数据库接口，安装程序是“mysql-connector-odbc-5.3.6-win32.msi”，64位系统也是安装32位的文件，否则连接不了数据库。2、安装好之后需要创建数据连接接口。打开“C:\Windows\SysWOW64\odbcad32.exe”,点击【添加】，找到“MySQLODBC5.xUnicodeDriver”，点击完成，弹窗配置如下：【DataSourceName】:连接名称，创建连接接口只能连接指定的某一个数据库，当需要连接多个数据库的时候最好规范连接名称，例如：mysql5.17，指连接ip尾号是5.17的数据库。方便抽取数据的时候查找指定数据库。【Description】：连接描述，随意填写，对连接无影响。【TCP/IPServer】:数据库所在电脑的IP地址【Port】：MySQL数据库的数据库接口，默认是3306，如果有修改，填写修改后的接口。【NamedPipe】：可不填。【User】：MySQL数据的账户名称，账户通常是高权限的账户，方便抽取数据，如果要限制权限，可以填写已设置好权限的账户名。【Pssword】：数据库账户密码【Database】：可指定数据库的某个子库，默认为全部子库。【Details】：点开之后有很多选项，默认即可，但是【CharacterSet】须选择“gbk”，与excel的编码方式相同，否则容易乱码，“gb2312”也可以，但是“gbk”支持的字符