mysql在linux64位下安装步骤完整版

一一 下载说明下载说明 前期的一些准备说明 参考 MySQL 发展史 Mysql 不同版本 说明 MySQL 分为 Community Server 和 Enterprise Edition 其中 Community Server 可以直接从 mysql 的官网下载 Enterprice Edition 只能从 Oracle edelivery 上下载 不过 Edelivery 现在已经屏蔽了中国的 IP 如果国内的用户一般不能从 edelivery 上下载 需要 翻墙出去才能下载 1 1 官方文档上有关官方文档上有关 MySQL 安装文件类型说明 安装文件类型说明 Linux supports anumber of different solutions for installing MySQL The recommended method isto use one of the distributions from Oracle If you choose this method thereare three options available 1 Installingfrom a generic binary package in tar gz format See Section 2 2 Installing MySQL from Generic Binaries on Unix Linux for moreinformation 2 Extractingand compiling MySQL from a source distribution For detailed instructions see Section 2 9 InstallingMySQL from Source 3 Installingusing a pre compiled RPM package For more information on using the RPMsolution see Section 2 5 1 Installing MySQL from RPM Packages on Linux 这里讲了 3 种方式来安装 MySQL 数据库 我这里用最简单的 RPM 包来安装 As analternative you can use the native package manager within your Linuxdistribution to automatically download and install MySQL for you Nativepackage installations can take of the download and dependencies required to runMySQL but the MySQL version will often be some way behind the currentlyavailable release You will also normally be unable to install developmentalreleases as these are not usually made available in the native repository Formore information on using the native package installers see Section 2 5 2 Installing MySQL on Linux using Native Package Manager Note For many Linux installations you will want to set up MySQL to be started automatically when your machinestarts Many of the native package installations perform this operation foryou but for source binary and RPM solutions you may need to set this upseparately The required script mysql server can be found in the support filesdirectory under the MySQL installationdirectory or in a MySQL source tree You can install itas etc init d mysql for automatic MySQL startup and shutdown See Section 2 10 1 2 Starting and Stopping MySQL Automatically 这里要注意一个问题 就是设置 MySQL 的自启动 如果使用 native package 来安 装 会自动设置为自启动 如果使用其他的方式 如 source binary 和 RPM 就需要单 独的来设置自启动 1 2 下载安装包下载安装包 Community Server 下载地址 这里只需要下载 linux64 位下 mysql5 5 33 的服务端和客户端安装包 MySQL server 5 5 33 1 linux2 6 x86 64 rpm MySQL client 5 5 33 1 linux2 6 x86 64 rpm 二二 安装安装 MySQL 2 1 安装说明安装说明 1 MySQL 的安装包有很多个 作用也不同 在大多数情况下 只需要安装 MySQL server 和 MySQL client 其他的包根据需要来安装 2 The server RPM places data underthe var lib mysql directory The RPM also creates a login accountfor a user named mysql if one does not exist to use for running theMySQL server and creates the appropriate entries in etc init d tostart the server automatically at boot time This means that if you haveperformed a previous installation and have made changes to its startup script you may want to make a copy of the script so that you do not lose it when youinstall a newer RPM 3 During RPM installation a usernamed mysql and a group named mysql are created on thesystem This is done using the useradd groupadd and usermod commands Those commands require appropriateadministrative privileges which is required for locally managed users andgroups as listed in the etc passwd and etc groupfiles by theRPM installation process being run by root 在 MySQL 安装时 会创建 mysql 用户和 mysql 组 4 MySQL 的相关目录说明 usr bin Client programs and scripts mysqladmin mysqldump 等命令 usr sbin The mysqld server var lib mysql Log files databases 这个目录就是数据库的目录 usr share info Manual in Info format usr share man Unix manual pages usr include mysql Include header files usr lib mysql Libraries usr share mysql Miscellaneous support files including error messages character set files sample configuration files SQLfor database installation mysql server 命令及配置文件 usr share sql bench Benchmarks etc rc d init d 启动脚本文件 mysql 的目录 2 2 安装安装 将下载的安装包拷贝至 home mysql 目录下 安装 Server 端 root localhost mysql rpm ivh MySQL server 5 5 33 1 linux2 6 x86 64 rpm Preparing 100 1 MySQL server 100 PLEASE REMEMBER TO SET A PASSWORD FOR THEMySQL root USER To do so start the server then issue thefollowing commands usr bin mysqladmin u root password new password usr bin mysqladmin u root h rac2password new password Alternatively you can run usr bin mysql secure installation which will also give you the option ofremoving the test databases and anonymous user created bydefault This is strongly recommended for productionservers See the manual for more instructions Please report any problems with the usr bin mysqlbugscript root rac2 software 安装客户端 root localhost mysql rpm ivh MySQL client 5 5 33 1 linux2 6 x86 64 rpm Preparing 100 1 MySQL client 100 2 3 安装完成后参数设置安装完成后参数设置 2 3 1 修改密码 连接 MySQL 报错 root localhost mysql mysql ERROR 2002 HY000 Can t connect to localMySQL server through socket var lib mysql mysql sock 2 启动 MySQL 后 在连正常 root localhost etc init d mysql start Starting MySQL OK root localhost mysql Welcome to the MySQL monitor Commands end with or g Your MySQL connection id is 1 Server version 5 5 15 MySQL CommunityServer GPL Copyright c 2000 2010 Oracle and or itsaffiliates All rights reserved Oracle is a registered trademark of OracleCorporation and or its affiliates Other names may be trademarksof their respective owners Type help or h for help Type c toclear the current input statement mysql 注意这里 因为 MySQL 默认没有密码 所以这里我们没有输入密码就直接连上了 修改密码 这个是安装 Server 中提示的 root localhost usr bin mysqladmin u root password root root localhost mysql ERROR 1045 28000 Access denied for user root localhost using password NO 修改密码之后 就提示要使用密码了 root localhost mysql u root p Enter password Welcome to the MySQL monitor Commands end with or g Your MySQL connection id is 5 Server version 5 5 15 MySQL CommunityServer GPL Copyright c 2000 2010 Oracle and or itsaffiliates All rights reserved Oracle is a registered trademark of OracleCorporation and or its affiliates Other names may be trademarks of theirrespective owners Type help or h for help Type c toclear the current input statement mysql 输入密码后成功连接 2 3 2 查看 MySQL 端口 3306 这个是默认端口 root localhost netstat nat grep 3306 tcp 0 0 192 168 3 100 32842 192 168 3 100 3306 TIME WAIT tcp 0 0 3306 LISTEN 2 3 3 设置 mysql 远程连接 a 查看 mysql 的用户 mysql select host user password from mysql user where user root b 设置允许所以地址访问 mysql update mysql user set host where user root mysql FLUSH PRIVILEGES c 设置 3306 端口允许访问 要访问 3306 可以用两种方式 一个就是让防火墙开放 3306 端口 另一个是关闭防火 墙 推荐使用开放 3306 端口 一 开放 3306 端口 方法 1 iptables I INPUT i eth0 p tcp dport 3306 j ACCEPT iptables I OUTPUT o eth0 p tcp sport 3306 j ACCEPT 对应阻止 3306 端口的命令为 iptables I INPUT i eth0 p tcp dport 3306 j DROP iptables I OUTPUT o eth0 p tcp sport 3306 j DROP 然后保存 etc rc d init d iptables save 方法 2 修改 etc sysconfig iptables 文件 增加如下一行 A RH Firewall 1 INPUT m state state NEW m tcp p tcp dport 3306 j ACCEPT 然后重启防火墙 service iptables restart 二 关闭防火墙 两种方式 1 重启后生效 chkconfig iptables off 对应的开启防火墙命令为 chkconfig iptables on 2 即时生效 但重启后防火墙会再次启动 service iptables stop 对应的开启防火墙命令为 service iptables start 经过这三步的设置之后 就可以在远程连接 mysql 了 2 3 4 设置 mysql 不区分大小写 Linux 下的 mysql 安装完之后默认 区分表名的大小写 不区分列名的大小写 改变表名的大小写区分规则的方法 用 root 帐号登录 在 etc f 或 etc mysql f 中的 mysqld 后添加 lower case table names 1 重启 MySQL 服 务 若设置成功 则不再区分表名的大小写 注意 如果在 etc 或 etc mysql 找不到 f 需要从其他地方拷贝过来 因为使用 rpm 安装 mysql 时 需要手工拷贝 f 具体操作 到 usr share mysql 下找到 cnf 文件 拷贝其中一个到 etc 并改名为 f 中 命 令如下 root localhost mysql cp usr share mysql my f etc f my f 是为了小型数据库而设计的 不应该把这个模型用于含有一些常用 项目的数据库 my f 是为中等规模的数据库而设计的 my f 是为专用于一个 SQL 数据库的计算机而设计的 my f 是为企业中的数据库而设计的 拷贝完成后 进到 etc 目录下 执行 vi f 查看该文件 root localhost etc vi f 按下 i 键进入编辑模式 找到 mysqld 后添加 lower case table names 1 并且将 max allowed packet 改为 50M 添加后 按下 esc 键 输入 wq 保存退出 再重启 mysql 服务即可 如图 2 3 5 修改 mysql5 5 默认编码 mysql 数据库的默认编码并不是 utf 8 安装 mysql 后 启动服务并登陆 使用 show variables like character 命令可查看 mysql 数据库的默认编码 由上图可见 database 和 server 的字符集使用了 latin1 编码方式 不支持中文 即存 储中文时会出现乱码 以下是命令行修改为 utf 8 编码的过程 以支持中文 1 关闭 mysql 服务 2 打开 f 后 在文件内的 mysqld 下增加如下两行设置 并保存退出 character set server utf8 init connect SET NAMES utf8 3 重新启动 mysql 服务 service mysql start 至此 完成修改默认编码 登陆 mysql 后重新使用 show variable like character 获 得的输出效果如下 2 3 6 创建存储过程时报错 创建存储过程时 报错 出错信息 ERROR 1418 HY000 This function has none of DETERMINISTIC NO SQL or READS SQL DATA in its declaration and binary logging is enabled you might want to use the less safe log bin trust function creators variable 原因 这是我们开启了 bin log 我们就必须指定我们的函数是否是 1 DETERMINISTIC 不确定的 2 NO SQL 没有 SQl 语句 当然也不会修改数据 3 READS SQL DATA 只是读取数据 当然也不会修改数据 4 MODIFIES SQL DATA 要修改数据 5 CONTAINS SQL 包含了 SQL 语句 其中在 function 里面 只有 DETERMINISTIC NO SQL 和 READS SQL DATA 被支持 如果我们开启 了 bin log 我们就必须为我们的 function 指定一个参数 解决方法 在 f 配置文件中添加 log bin trust function creators 1 修改 f 的方法参见 2 3 5 修改 mysql5 5 默认编码 添加完成后重启 mysql 服务即可 2 3 7 设置开机自启动 查看 MySQL 开机自启动设置 root rac2 chkconfig list grep mysql mysql 0 off 1 off 2 on 3 on 4 on 5 on 6 off 这里的数字分别代表 Linux 启动的不同模式 3 是命令行 5 是窗口 关闭开机自启动 root rac2 chkconfig mysql off root rac2 chkconfig list grep mysql mysql 0 off 1 off 2 off 3 off 4 off 5 off 6 off 启用开机自启动 root rac2 chkconfig mysql on root rac2 chkconfig list grep mysql mysql 0 off 1 off 2 on 3 on 4 on 5 on 6 off root rac2 将 Mysql 从 chkconfig 服务里删除 root rac2 chkconfig del mysql root rac2 chkconfig list grep mysql 将 Mysql 添加到 chkconfig 里 root rac2 chkconfig add mysql root rac2 chkconfig list grep mysql mysql 0 off 1 off 2 on 3 on 4 on 5 on 6 off 2 4 数据库操作数据库操作 root rac2 mysql u root p Enter password Welcome to the MySQL monitor Commands end with or g Your MySQL connection id is 6 Server version 5 5 15 MySQL CommunityServer GPL Copyright c 2000 2010 Oracle and or itsaffiliates All rights reserved Oracle is a registered trademark of OracleCorporation and or its affiliates Other names may be trademarksof their respective owners Type help or h for help Type c toclear the current input statement 查看已经存在的数据库 这几个是安装时自动创建的 mysql show databases Database information schema mysql performance schema test 4 rows in set 0 00 sec 使用 mysql 数据库 mysql use mysql Reading table information for completion oftable and column names You can turn off this feature to get aquicker startup with A Database changed 查看 mysql 数据库下面的表 mysql show tables Tables in mysql columns priv db event func general log help category help keyword help relation help topic host ndb binlog index plugin proc procs priv proxies priv servers slow log tables priv time zone time zone leap second time zone name time zone transition time zone transition type user 24 rows in set 0 01 sec 创建数据库 Dave mysql