MYSQL安装部署、参数设置、备份恢复、监控规范.doc

中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 1 / 35 MYSQL 安装部署、参数设置、备份、恢复、安装部署、参数设置、备份、恢复、 监控规范监控规范 普元信息技术股份有限公司普元信息技术股份有限公司 2014 年年 10 月月 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 2 / 35 目录目录 1 1概述概述 3 1.1背景3 1.2目的3 1.3范围4 2 2硬件准备硬件准备 4 3 3实施实施 4 3.1软件准备4 3.2MYSQL 安装部署.4 3.2.1安装包准备4 3.2.2安装服务端和客户端5 3.2.3修改密码5 3.3MYSQL 使用.5 3.4MYSQL 参数配置.6 3.5MYSQL 备份恢复.11 3.5.1使用图形化辅助工具备份11 3.5.2使用 MySQL 自带的命令行方式备份11 3.5.3从数据库导出数据库文件备份12 3.5.4从外部文件导入数据到数据库中恢复13 3.5.5关于导入文件大小限制问题的解决13 3.6MYSQL 监控.13 3.6.1慢查询13 3.6.2连接数14 3.6.3索引缓存大小16 3.6.4临时表19 3.6.5Table Cache 相关优化.20 3.6.6进程使用情况21 3.6.7查询缓存23 3.6.8排序使用情况26 3.6.9文件打开数（Open_files）27 3.6.10表锁情况.27 3.6.11表扫描情况.28 3.6.12常见 mysql 监控指标.30 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 3 / 35 1 1概述概述 1.11.1背景背景 随着 OSS 域各个系统的建设，系统间数据共享的需求也越来越多。目前，贵州移动的 网管系统在总部规范指导下，按需逐年建设而成的。这些系统实现对通信网络和业务平台 的管理，支撑配置管理、故障监控、指标分析、网络优化、例行维护、指挥调度等工作。 但是，面向网元和网络的分专业网管，难以支持以客户感知为核心、面向端到端业务实现 的运维管理新要求。越来越多的跨系统应用需要同时来自多个生产系统的数据进行支撑， 实际生产分析场景需要将多类型、多专业的性能、质量等数据的进行集中管理，并建立模 型关联，准实时、非实时分析需求并存。 CMOSS2.0 明确提出了数据集成和共享平台的技术架构要求。包括服务总线和数据总线。 数据集成和共享平台作为所有系统的交互桥梁，能够实现全面支持 CMOSS 规范中的所有共 享模式，提升系统稳定性及业务吞吐量；提供基于标准模型的数据共享服务，以解决数据 开放共享问题。建设效果直接影响到中国移动的 IT 业务能力和 IT 功能支撑能力。 根据集团 CMOSS2.0 技术规范的要求，结合贵州移动在网管领域对数据共享的具体需 求。贵州移动规划建立网络数据共享平台，通过对生产系统数据准实时的采集，将数据汇 总到数据共享平台上。利用目前业界最新的数据分析和处理技术，按照不同的业务视角利 用采集到数据，为企业运营提供技术决策依据。同时，由于统计分析使用的数据和生产运 营的数据分离，可以较少对正式系统的影响，从而保障生产系统运营更加稳定、可靠。 由于数据来自各个专业系统，需要面对不同的厂商、不同的技术架构。在数据共享平 台的建设过程中，存在数据标准化、服务标准化、大数据处理等问题。这些问题是数据集 成和共享平台建设成功的关键，需要重点解决。 1)对现有网管数据从生产到消费的全流程梳理，形成网管数据血缘关系和数据地图，以 作为网管数据统一建模、集成共享的基础； 2)根据数据地图，建立可扩展的、面向底层数据源和上层应用灵活适配的网管数据模型， 作为数据共享中心模型标准。 3)研究海量数据共享技术和模式，对数据库共享、数据编排、服务共享等数据共享技术 和模式进行评估，制定符合网络数据中心的共享技术标准。 4)Hadoop 是当前大数据处理常用的技术，但 Hadoop NoSQL 和多接点的特点，为应用开 发和平台维护带来一定的难道。通过技术服务，对 Hadoop 应用开发提供参考标准和技术支 持，研究并制定 Hadoop 平台的维护标准。 1.21.2目的目的 1)形成对网管数据地图与数据模型标准的梳理，作为在建数据共享中心的规范与指导； 2)研究海量数据的处理与分析技术、模式，为后续进一步建立针对大数据的数据共享中 心提供可行方案与指导。 为了达到上述目标，本项目分解为四个子项目，各子项目工作内容如下： 1)1)网络数据地图研究子项目网络数据地图研究子项目 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 4 / 35 对现有网管数据从生产到消费的全流程梳理，形成网管数据血缘关系和数据地图， 以作为网管数据统一建模、集成共享的基础； 2)2) 网络数据共享模型标准研究子项目网络数据共享模型标准研究子项目 根据数据地图，建立可扩展的、面向底层数据源和上层应用灵活适配的网管数据 模型，作为数据共享中心模型标准。 3)3) 网络数据共享技术标准子项目网络数据共享技术标准子项目 研究海量数据共享技术和模式，对数据库共享、数据编排、服务共享等数据共享 技术和模式进行评估，制定符合网络数据中心的共享技术标准。 4)4) 网络数据共享平台维护标准子项目网络数据共享平台维护标准子项目 数据共享平台采用 Hadoop 架构，多种技术共存，方案相对复杂，NoSQL 和多节点 的特点，为应用开发和平台维护带来一定的难道。通过技术服务，对应用开发提 供参考标准和技术支持，研究并制定平台的维护标准。 1.31.3范围范围 该文档是用于指导贵州移动网络共享平台 MYSQL 安装部署、参数设置、备份、恢复、 监控的规范。 2 2硬件准备硬件准备 设备用途设备用途 操作操作 系统系统 设备设备 数量数量 设备设备 数量数量 主机名主机名 IPIP 说明说明 2 CXNDSP_HDP_01 CXNDSP_HDP_02 控制服务器 Hadoop 服 务器 Centos6.2 Centos6.2 16 14 CXNDSP_HDP_03 CXNDSP_HDP_04 . CXNDSP_HDP_16 数据节点 3 3实施实施 3.13.1软件准备软件准备 操作系统，HADOOP 软件正确安装。 3.23.2 MYSQLMYSQL 安装安装部署部署 Mysql 作为 HIVE 的元数据存储数据库，只安装在 hadoop 的 Master 节点。 .1安装包准备安装包准备 官网下载： /downloads/ 或者使用 CentOS6.2 自带 mysql，版本：mysql-5.1.52-1.el6_0.1.x86_64 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 5 / 35 .2安装服务端和客户端安装服务端和客户端 .3修改密码修改密码 安装后系统默认创建系统用户“mysql” ，可修改 mysql 的密码； 设置 mysql 的默认管理员 root 的密码为 rootmysql，注意这里的 root 是 mysql 数据 库的用户。 usr/bin/mysqladmin -u root password rootmysql 登陆 mysql ： mysql -u root p 3.33.3 MYSQLMYSQL 使用使用 创建数据库: create database hivemdb default character set utf8 collate utf8_general_ci; 查看当前数据库 ：show databases; 切换到 hivedb 数据库：use hivemdb; 创建新用户： create user hive localhost identified by hive; 用户授权： GRANT ALL PRIVILEGES ON *.* TO hive% IDENTIFIED BY hive WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO hivelocalhost IDENTIFIED BY hive WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO hiveGZHDS05 IDENTIFIED BY hive WITH GRANT OPTION; 重载授权表： flush privileges; 新用户登陆： mysql -u hive p （要关闭防火墙要关闭防火墙） 设置随系统启动： 查看是否在自动启动列表 chkconfig -list|grep mysql 把 MySQL 添加到你系统的启动服务组里 chkconfig -add mysql 把 MySQL 从启动服务组里面删除 chkconfig -del mysql 其他用法 查看 mysql 状态：service mysqld status 启动 mysql：service mysqld start 查看所有表：Show tables； 查看 mysql 安装路径：mysql show variables like %basedir%; 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 6 / 35 环境 hadoopGZHDS03 bin$ ls -l /usr/bin/mysql -rwxr-xr-x. 1 root root 339432 Jun 25 2011 /usr/bin/mysql 可执行文件/usr/bin/mysql，允许其他用户可读和可执行。将/usr/bin 加入到 Path 环境变量，就让 mysql 成为可执行命令：PATH=/bin:/usr/lib64/qt- 3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/hado op/bin 3.43.4 MYSQLMYSQL 参数配置参数配置 MySQL 的参数配置，直接影响到 DB 的速度和承载量！ MySQL 也是优化难度最大的一个部分，不但需要理解一些 MySQL 专业知识，同时还 需要长时间的观察统计并且根据经验进行判断，然后设置合理的参数。 详细的参数配置说明如下： #cat f # MySQL client library initialization. client port = 3306 socket = /tmp/mysql.sock character-set-server = utf8 # The MySQL server mysqld init_connect = set names utf8 /设定连接 mysql 数据库时使用 utf8 编码，以让 mysql 数 据库以 utf8 运行 show variables like character%G; 可查询 mysql 字符相关 init_connect = SET autocommit=0 /关闭自动提交模式，就认为用户总是以事务方式操 作 character-set-server = utf8 port = 3306 socket = /tmp/mysql.sock basedir = /opt/justone/mysqldM/mysql datadir = /opt/justone/mysqldM/mysql/data skip-locking /避免 MySQL 的外部锁定，减少出错几率增强稳定性。 #skip-networking /开启该选项即彻底关闭 MySQL 的 TCP/IP 连接方式，如果 WEB 服 务器是以远程连接的方式访问 MySQL 数据库服务器则不要开启该选项！否则将无法正常 连接！ skip_name_resolve /禁止 MySQL 对外部连接进行 DNS 解析，使用这一选项可以消除 MySQL 进行 DNS 解析的时间。如果开启该选项，则所有远程主机连接授权都要使用 IP 地 址方式，否则 MySQL 将无法正常处理连接请求！ #back_log = 384 /监听队列中所能保持的连接数即保存了在 MySQL 连接管理器线程处 理之前的连接。 back_log 参数的值指出在 MySQL 暂时停止响应新请求之前的短时间内多少个请求可 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 7 / 35 以被存在堆栈中。 如果一个短时间内有很多连接，则需要增大该参数的值，来指定到来的 TCP/IP 连接的 侦听队列的大小。 不同的操作系统在这个队列大小上有它自己的限制。试图设定 back_log 高于你的操作 系统的限制将是无效的。 默认值为 50。对于 Linux 系统推荐设置为小于 512 的整数。系统值由 ulimit -a 查看。 max_connections = 2000 /允许的同时客户的数量。增加该值即增加 mysqld 要求的文件 描述符的数量。 注：该值过小，客户端会经常有 Too many connections 错误。 wait_timeout=10 /指定一个请求的最大连接时间，对于 4GB 左右内存的服务器可以设 置为 5-10。 max_connect_errors = 500 /如同一主机有超出该参数值个数的中断错误连接，则该主机 将被禁止连接。 如需对该主机进行解禁，执行：FLUSH HOST; table_open_cache = 2048 /所有线程打开表的数量 max_allowed_packet = 16M /一个查询语句包的最大尺寸,信息交换中使用信息包的允许 大小(如：导入表) 用客户端工具如 sqlyog 异地 cp 数据库出错时，可考虑加大此值。 max_heap_table_size = 256M /HEAP 数据表(内存表)的最大长度(默认设置是 16M); 超过这个长度的 HEAP 数据表将被存入一个临时文件而不是驻留在内存里。 sort_buffer_size = 512K /查询排序时所能使用的缓冲区大小。 该参数对应的分配内存是每连接独占,如有 100 个连接，实际分配的排序缓冲区大小为 100512K50MB。 所以，对于内存在 4GB 左右的服务器推荐设置为 6-8M。 join_buffer_size = 1M /联合查询操作所能使用的缓冲区大小，该参数对应的分配内存 也是每连接独享. thread_cache_size = 8 /可以复用的保存在线程缓存中的线程的数量.内存 G*8 即 2G 设 为 16 数据库的每一个连接都要使用自己的线程。线程创建需要时间，所以如果这个连接关 闭时并不需要关闭这个线程，服务器会把它保存在自己的线程缓存中，以便下一个连接使 用。 thread_concurrency = 8 /该参数取值为服务器逻辑 CPU 数量2 bulk_insert_buffer_size = 8M /指定 MyISAM 类型数据表表使用特殊的树形结构的缓 存。 使用整块方式(bulk)能够加快插入操作( INSERT SELECT, INSERT VALUES (), (), , 和 LOAD DATA INFILE) 的速度和效率。该参数限制每个线程使用的树形结构缓 存大小，如果设置为 0 则禁用该加速缓存功能。注意：该参数对应的缓存操作只能用户向 非空数据表中执行插入操作！默认值为 8MB。 query_cache_size = 64M /指定 MySQL 查询缓冲区的大小。 可以通过在 MySQL 控制台执行以下命令观察： SHOW VARIABLES LIKE %query_cache%; SHOW STATUS LIKE Qcache%; Qcache_lowmem_prunes 的值非常大，则表明经常出现缓冲不够的情况； Qcache_hits 的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 8 / 35 率，那么可以考虑不用查询缓冲； Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。 default-storage-engine = InnoDB /新数据表的默认数据表类型 默认设置是 MyISAM lower_case_table_names = 1 /MySQL 实现不区分大小写 transaction_isolation = REPEATABLE-READ /设置所有连接的默认事务隔离级 tmp_table_size = 256M /临时 HEAP 数据表的最大长度 默认设置是 32M; 超过这个长度的临时数据表将被转换为 MyISAM 数据表并存入一个 临时文件。 slow_query_log = 1 log = /opt/justone/mysqldM/mysql/logs/mysql.log long_query_time = 2 log-slow-queries = /opt/justone/mysqldM/mysql/logs/slowquery.log # Replication related settings server-id = 1 /设定为 master log-bin=mysql-bin /产生的 log 以 mysql-bin 开头 binlog_cache_size = 8M /为 binary log 指定在查询请求处理过程中 SQL 查询语句使用 的缓存大小。 如果频繁应用于大量、复杂的 SQL 表达式处理，则应该加大该参数值以获得性能提升。 binlog_format=mixed /日志格式,亦可自定义。 InnoDB 和 MyISAM 类型区别 InnoDB 和 MyISAM 是许多人在使用 MySQL 时最常用的两个表类型，这两个表类型 各有优劣，视具体应用而定。 基本的差别为：MyISAM 类型不支持事务处理等高级处理，而 InnoDB 类型支持。 MyISAM 类型的表强调的是性能，其执行数度比 InnoDB 类型更快，但是不提供事务 支持，而 InnoDB 提供事务支持已经外部键等高级数据库功能。 InnoDB 注意的地方： 1.InnoDB 不支持 FULLTEXT 类型的索引。 2.InnoDB 中不保存表的具体行数，也就是说，执行 select count(*) from table 时， InnoDB 要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数 即可。注意的是，当 count(*)语句包含 where 条件时，两种表的操作是一样的。 3.对于 AUTO_INCREMENT 类型的字段，InnoDB 中必须包含只有该字段的索引，但 是在 MyISAM 表中，可以和其他字段一起建立联合索引。 4.DELETE FROM table 时，InnoDB 不会重新建立表，而是一行一行的删除。 5.LOAD TABLE FROM MASTER 操作对 InnoDB 是不起作用的，解决方法是首先把 InnoDB 表改成 MyISAM 表，导入数据后再改成 InnoDB 表，但是对于使用的额外的 InnoDB 特性（例如外键）的表不适用。 6.InnoDB 表的行锁也不是绝对的，如果在执行一个 SQL 语句时 MySQL 不能确定要扫 描的范围，InnoDB 表同样会锁全表，例如 update table set num=1 where name like “?a%” # MyISAM Specific options key_buffer_size = 32M /索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读 和多重写) 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 9 / 35 索引块是缓冲的并且被所有的线程共享。如果你使它太大，系统将开始换页并且真的 变慢了。 默认数值是 8388600(8M)，MySQL 主机有 2GB 内存，可设为 402649088(400MB)。 注意：该参数值设置的过大反而会是服务器整体效率降低！ read_buffer_size = 256K /读查询操作所能使用的缓冲区大小，该参数对应的分配内存 也是每连接独享. read_rnd_buffer_size = 256K /针对按某种特定顺序(如 ORDER BY 子句)输出的查询结 果(默认 256K) 加速排序操作后的读数据，提高读分类行的速度。 myisam_sort_buffer_size = 128M /myisam 引擎的 sort_buffer_size myisam_max_sort_file_size = 10G /类似于上 myisam_recover /自动检查和修复无法正确关闭 MyISAM 表 #skip-innodb /去掉 innodb 支持 #skip-bdb /去掉 bdb 事务型表支持 # INNODB Specific options * innodb_additional_mem_pool_size = 16M /InnoDB 用来存储数据字典和其他内部数据结 构的内存池大小。 应用程序里的表越多就应该分配越多的内存，如果 innodb 用光了这个内存就会向系统 内存要。 并且写入警告日志,根据 MySQL 手册，对于 2G 内存的机器，推荐值是 20M。 缺省值是 1M。通常不用太大，只要够用就行，与表结构的复杂度有关系。 #innodb_buffer_pool_size = 6G /指定大小的内存来缓冲数据和索引。 对于单独的 MySQL 数据库服务器，最大可以把该值设置成物理内存的 80%。 根据 MySQL 手册，对于 2G 内存的机器，推荐值是 1G（50%） innodb_buffer_pool_size = 512M innodb_data_file_path = ibdata1:10M:autoextend /用来容纳 InnoDB 为数据表的表空间: 可能涉及一个以上的文件; 每一个表空间文件的最大长度都必须以字节(B)、兆字节 (MB)或千兆字节(GB)为单位给出; 表空间文件的名字必须以分号隔开; 最后一个表空间文件还可以带一个 autoextend 属 性和一个最大长度(max:n)。 例如，ibdata1:1G;ibdata2:1G:autoextend:max:2G 的意思是: 表空间文件 ibdata1 的最大长度是 1GB，ibdata2 的最大长度也是 1G，但允许它扩充到 2GB。 除文件名外，还可以用硬盘分区的设置名来定义表空间，此时必须给表空间的最大初 始长度值加上 newraw 关键字做后缀，给表空间的最大扩充长度值加上 raw 关键字做后缀 (例如/dev/hdb1:20Gnewraw 或/dev/hdb1:20Graw); MySQL 4.0 及更高版本的默认设置是 ibdata1:10M:autoextend。 innodb_file_io_threads = 4 /IO 操作(硬盘写操作)的最大线程个数(默认设置是 4)。 innodb_file_per_table = 1 /为每一个新数据表创建一个表空间文件而不是把数据表都集 中保存在中央表空间里 如果系统中表的个数不多，并且没有超大表，使用该参数可以使得各个表之间的,维护 相对独立。 innodb_thread_concurrency = 16 /InnoDB 驱动程序能够同时使用的最大线程个数(默认 设置是 8)。 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 10 / 35 innodb_flush_log_at_trx_commit = 1 /InnoDB 记录日志的方式。 如果设置为 1，则每个事务提交的时候，MySQL 都会将事务日志写入磁盘。 如果设置为 0 或者 2，则大概每秒中将日志写入磁盘一次。 实际测试发现，该值对插入数据的速度影响非常大 设置为 2 时插入 10000 条记录只需要 2 秒，设置为 0 时只需要 1 秒，而设置为 1 时则 需要 229 秒。 建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。 在存在丢失最近部分事务的危险的前提下，可以把该值设为 0。 innodb_log_buffer_size = 8M /日志缓存的大小 默认的设置在中等强度写入负载以及较短事务的情况下，一般可以满足服务器的性能 要求。 如果更新操作峰值或者负载较大就应该加大这个值。8-16M 即可。 innodb_log_file_size = 256M /日志组中每个日志文件的大小在高写入负载尤其是大数 据集的情况下很重要。 这个值越大性能就越高，但恢复时时间会加长。默认是 5M。 Javaeye 推荐 innodb_log_file_size = 64M 需要注意的是 修改完以后要 STOP 服务接着删除原来的日志 ib_logfile0 和 ib_logfile1，然后启动服务. 整体性能分析报告 show engine innodb statusG; innodb_log_files_in_group = 3 /日志组中的日志文件数目,推荐使用 3 innodb_max_dirty_pages_pct = 90 /最大脏页的百分数 当系统中脏页所占百分比超过这个值，INNODB 就会进行写操作以把页中的已更新数 据写入到磁盘文件中。 innodb_flush_method = O_DIRECT /InnoDB 日志文件的同步办法(仅适用于 UNIX/Linux 系统)。 O_DIRECT 跳过了操作系统的文件系统 Disk Cache，让 MySQL 直接读写磁盘。 innodb_lock_wait_timeout = 120 /事务获得资源超时设置,默认 50s 如果某个事务在等待 n 秒(s)后还没有获得所需要的资源，就使用 ROLLBACK 命令放 弃这个事务。 这项设置对于发现和处理未能被 InnoDB 数据表驱动程序识别出来的死锁条件有着重 要的意义。 但是不能对表锁导致的死锁进行自动监测。 mysqldump quick /不缓冲查询，直接导出至 stdout；使用 mysql_use_result()做它。 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项 max_allowed_packet = 16M mysql no-auto-rehash /不自动补齐命令，设置 auto-rehash 为自动补齐。默认 TAB 补齐 #safe-updates /仅仅允许使用键值的 UPDATE 和 DELETE myisamchk /MyISAM 表维护的一个非常实用的工具。 可以使用 myisamchk 实用程序来获得有关数据库表的信息或检查、修复、优化他们。 myisamchk 适用 MyISAM 表(对应.MYI 和.MYD 文件的表)。 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 11 / 35 key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M mysqlhotcopy interactive-timeout /把一个数据传输的最大时间量设置为默认的 28800 秒 mysqld_safe open-files-limit = 8192 /每个进程的可打开文件数量. 确认你已经将系统限制设定的足够高 #ulimit -HSn 65536 3.53.5 MYSQLMYSQL 备份恢复备份恢复 .1使用图形化辅助工具使用图形化辅助工具备份备份 首先需要安装 MySQL GUI Tools v5.0，它是一个可视化界面的 MySQL 数据库管理控制 台，提供了四个非常好用的图形化应用程序，方便数据库管理和数据查询。这些图形化管 理工具可以大大提高数据库管理、备份、迁移和查询效率，即使没有丰富的 SQL 语言基础 的用户也可以应用自如。它们分别是： MySQL Migration Toolkit：数据库迁移 MySQL Administrator：MySQL 管理器 MySQL Query Browser：用于数据查询的图形化客户端 MySQL Workbench：DB Design 工具 .2使用使用 MySQLMySQL 自带的命令行方式自带的命令行方式备份备份 MySQL 导入导出.sql 文件： 步骤如下： 一.MySQL 的命令行模式的设置： 桌面-我的电脑-属性-环境变量-新建- PATH=“;pathMySQLbin;”其中 path 为 MySQL 的安装路径。 二.简单的介绍一下命令行进入 MySQL 的方法： 1.C:MySQL -h hostname -u username -p 按 ENTER 键，等待然后输入密码。这里 hostname 为服务器的名称，如 localhost，username 为 MySQL 的用户名，如 root。 进入命令行后可以直接操作 MySQL 了。 2.简单介绍一下 MySQL 命令： MySQL-CREATE DATABASE dbname;/创建数据库 MySQL-CREATE TABLE tablename;/创建表 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 12 / 35 MySQL-SHOW DATABASES;/显示数据库信息，有那些可用的数据库。 MySQL-USE dbname;/选择数据库 MySQL-SHOW TABLES;/显示表信息，有那些可用的表 MySQL-DESCRIBE tablename;/显示创建的表的信息 .3从数据库导出数据库文件从数据库导出数据库文件备份备份 1.将数据库 mydb 导出到 e:MySQLmydb.sql 文件中： 打开开始-运行-输入 cmd 进入命令行模式 c:MySQLdump -h localhost -u root -p mydb e:MySQLmydb.sql 然后输入密码，等待一会导出就成功了，可以到目标文件中检查是否成功。 2.将数据库 mydb 中的 mytable 导出到 e:MySQLmytable.sql 文件中： c:MySQLdump -h localhost -u root -p mydb mytablee:MySQLmytable.sql 3.将数据库 mydb 的结构导出到 e:MySQLmydb_stru.sql 文件中： c:MySQLdump -h localhost -u root -p mydb -add-drop-table e:MySQLmydb_stru.sql /-h localhost 可以省略，其一般在虚拟主机上用 命令行导出和导入 MySQL 数据库 备份 MySQL 数据库的命令 mysqldump -hhostname -uusername -ppassword databasename backupfile.sql 备份 MySQL 数据库为带删除表的格式 备份 MySQL 数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动 删除原有数据库。 mysqldump -add-drop-table -uusername -ppassword databasename backupfile.sql 直接将 MySQL 数据库压缩备份 mysqldump -hhostname -uusername -ppassword databasename | gzip backupfile.sql.gz 备份 MySQL 数据库某个(些)表 mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 backupfile.sql 同时备份多个 MySQL 数据库 mysqldump -hhostname -uusername -ppassword databases databasename1 databasename2 databasename3 multibackupfile.sql 仅仅备份数据库结构 mysqldump no-data databases databasename1 databasename2 databasename3 structurebackupfile.sql 备份服务器上所有数据库 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 13 / 35 mysqldump all-databases allbackupfile.sql 还原 MySQL 数据库的命令 mysql -hhostname -uusername -ppassword databasename MySQL -h localhost -u root -p mydb2 show variables like %slow%; +-+-+ | Variable_name | Value | +-+-+ | log_slow_queries （已废弃） | OFF | | slow_launch_time | 2 | 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 14 / 35 | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/twf13-slow.log | +-+-+ 4 rows in set (0.00 sec) mysql show global status like %slow%; +-+-+ | Variable_name | Value | +-+-+ | Slow_launch_threads | 0 | | Slow_queries | 0 | +-+-+ 2 rows in set (0.00 sec) 配置中关闭了记录慢查询，执行时间超过 2 秒的即为慢查询，系统显示有 0 个慢查询， 你可以分析慢查询日志，找出有问题的 SQL 语句，慢查询时间 不宜设置过长，否则意义不 大，最好在 5 秒以内，如果你需要微秒级别的慢查询，可以考虑给 MySQL 打补丁： /docs/wiki/release:start，记得找对应的版本。 打开慢查询日志可能会对系统性能有一点点影响，如果你的 MySQL 是主-从结构，可 以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又 小。 （消耗的主要是 CPU） mysqldumpslow 查询慢查询日志 .2连接数连接数 经常会遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量 确实很高，MySQL 服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种 情况是 MySQL 配 置文件中 max_connections 值过小： max_connections Max_used_connections Connections max_user_connections back_log max_connect_errors max_connections：整个 MySQL 允许的最大连接数； 这个参数主要影响的是整个 MySQL 应用的并发处理能力，当系统中实际需要的连接量 大于 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 15 / 35 max_ connections 的情况下，由于 MySQL 的设置限制，那么应用中必然会产生连接请 求的等待， 从而限制了相应的并发量。所以一般来说，只要 MySQL 主机性能允许，都是将该参数 设置的尽可能大一点。一般来说 500 到 800 左右是一个比较合适的参考值 当然这建立在机器能支撑的情况下，因为如果连接数越多，介于 MySql 会为每个连接 提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以 过conn%通配符查看当前状态的连接数量，以定夺该值的大小 mysql show variables like max_connections; +-+-+ | Variable_name | Value | +-+-+ | max_connections | 151 | +-+-+ 1 row in set (0.00 sec) 这台 MySQL 服务器最大连接数是 151，然后查询一下服务器响应的最大连接数： mysql show global status like Max_used_connections; +-+-+ | Variable_name | Value | +-+-+ | Max_used_connections | 1 | +-+-+ 查看当前连接数 mysql show global status like Connections; +-+-+ | Variable_name | Value | +-+-+ | Connections | 2 | MySQL 服务器过去的最大连接数是 1，没有达到服务器连接数上限 151，应该没有出现 1040 错误，比较理想的设置是： Max_used_connections / max_connections * 100% 85%（好像和上面有冲突） 最大连接数占上限连接数的 85%左右，如果发现比例在 10%以下，MySQL 服务器连接数 上限设置的过高了。 max_user_connections：每个用户允许的最大连接数；是针对于单个用户的连接限制。 在一般情况下我们可能都较少使用这个限制，只有在一些专门提供 MySQL 数据存储服务， 中国移动通信集团贵州有限公司 2014 年网络部网管数据集成与共享标准技术服务项目 16 / 35 或者是提供虚拟主机服务的应用中可能需要用到。 back_log 是要求 MySQL 能有的连接数量。当主要 MySQL 线程在一个很短时间内得到非 常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且 启动一个新 线程。back_log 值指出在 MySQL 暂时停止回答新请求之前的短时间内多少个请求可以被存 在堆栈中。如果期望在一个短时间内有很多连接， 你需要增加它。也就是说，如果 MySql 的连接数据达到 max_connections 时，新来的请求将会被存在堆栈中，以等待某一连接释 放资源，该堆栈 的数量即 back_log，如果等待连接的数量超过 back_log，将不被授予连 接资源。另外，这值（back_log）限于您的操作系统对到来的 TCP/IP 连接的侦听队列的 大小。你的操作系统在这个队列大小上有它自己的限制（可以检查你的 OS 文档找出这个变 量的最大值） ，试图设定 back_log 高于你的操作系统的限制将是无效的。 mysql show variables like back_log; +-+-+ | Variable_name | Value | +-+-+ | back_log | 50 | +-+-+ max_connect_errors max_connect_errors 默认值为 10，如果受信帐号错误连接次数达到 10 则自动堵塞， 需要 flush hosts 来解除。如果你得到象这样的一个错误： Host hostname is blocked because of many connection errors. Unblock with mysqladmin flush-hosts 这意味着，mysqld 已经得到了大量(max_connect_errors)的主机hostname的在中 途被中断了的连接请求。在 max_connect_errors 次失败请求后，mysqld 认定出错了(象来 字一个黑客的攻击)，并且阻止该站点进一步的连接，直到某人执行命令 mysqladmin flush-hosts。 内网连接的话，建议设置在 10000 以上，已避免堵塞，并定期 flush hosts。 connect_timeout 指定 MySQL 服务等待应答一个连接报文的最大