随着网站的运行Mysql数据库也越来越大，备份问题也日益凸显，使用phpmyadmin导出或导入时可能会因为超时文件过大等原因无法导入或导出。下面VPS侦探说一下几个解决方法： MySQL数据库导出 方法1:mysqldump命令 执行命令： /usr/local/mysql/bin/mysqldump -u 用户名 -p 数据库名 > 文件名 如果数据库用户名有密码密码，执行后会提示输入密码。如果数据库用户名没有密码，就将命令中的“-p”参数删除。 注意：推荐使用MySQL root帐号进行操作，因为有些数据库可能设置了只能对应的用户进行操作。 方法2:phpmyadmin phpmyadmin支持将数据库文件直接备份到配置文件指定的目录，默认安装的lnmp使用了修改后的配置，可以将数据库直接备份到/home/wwwroot/phpmyadmin/save/ 下面，然后用户通过http或者ftp等方式下载即可。暂时没有测试过比较大的数据库，lnmp的用户可以测试一下给我反馈。 方法3：第三方软件 如：帝国备份王，专门为MYSQL大数据的备份与导入而设计的稳定高效软件,系统采用分卷备份与导入,理论上可备份任何大小的数据库。据网上的反馈看还是不错的，安装使用方法。 MySQL数据库导入 方法1：mysql命令 执行命令： /usr/local/mysql/bin/mysql -u 用户名 -p 数据库名 < 文件名 参数与mysqldump的使用一样。 注意：导入的数据库名需要已经存在。 方法2：phpmyadmin phpmyadmin可以将位于/home/wwwroot/phpmyadmin/upload/ 导入到指定的数据库里，可以上传数据库备份文件到/home/wwwroot/phpmyadmin/upload/这个目录进行导入。 方法3：第三方软件 如：帝国备份王。用了才知道好。
开发lamp程序，对mysql数据库的导入导出是经常的事情，我就遇到这个问题，不能很方便的将数据库导入导出。今天整理了两条比较实用的命令，轻松搞定导入导出问题。 首先是导出命令 1.导出数据库 mysqldump db_name -uroot -p > /path/db_name.sql 2.导出数据库中某个表 mysqldump db_name tb_name -uroot -p > /path/db_tb_name.sql 再次是导入命令 1.导入刚才备份的数据库sql文件 mysql db_name -uroot -p < /path/db_tb_name.sql 如果你是服务器操作，再现上两条命令 打包数据库备份文件，对文件压缩了，再进行传输 tar zcvf db_name.sql.tar.gz db_name.sql 传输到指定机器后，再解包 tar zxvf db_name.sql.tar.gz
在 MySQL 中有 4 种不同的日志, 分别为错误日志, 查询日志和慢查询日志, 二进制日志. 默认情况下, 为尽量减少 IO 损耗, 系统只打开错误日志. 若需要复制, 就必须要打开二进制日志. 错误日志 错误日志在 MySQL 数据库中很重要, 它记录着 MySQL 启动和停止, 以及服务器在运行过程中发生的任何错误的相关信息. 配置 如果配置文件 my.cnf 没有指定 log_eror, 则错问日志默认文件名为 hostname.err, 存放于 datadir 目录中. mysql> SHOW VARIABLES LIKE ‘log_error’; +—————+——-+ | Variable_name | Value | +—————+——-+ | log_error | | +—————+——-+ 1 row in set (0.00 [...]
介绍： Query Cache保存了SELECT语句的完整结果集，如果缓存命中，那么会跳过解析，预处理，优化，执行等步骤，直接返回结果给客户端。SELECT查询中使 用过的表也会被缓存，如果表发生了变化，那么和此表相关的SELECT查询缓存将会失效，这种方式有些粗暴，毕竟表的改变不一定对SELECT查询结果有 影响，但是这种简单的方式对复杂的系统还是很重要的。 如何命中： MySQL检查缓存的方法很简单，就是去查找一个表，查询的键为用当前SELECT语句，数据库名称，客户端协议的版本，以及其他因素生成的一个哈希值。 MySQL会精确地使用客户端传过来的SELECT查询语句以及其他数据，两条语句中只要字符大小写，空格，注释有一点点不同，那么MySQL就认 为这是两个不同的查询。所以在书写SQL语句时候要保持统一的风格，因为这不仅可以使代码更加的统一，无形中还提高了性能，何乐而不为呢？ 以下两条语句MySQL会认为是不同的查询： SELECT * FROM t WHERE …. select * from t where …. 不能被缓存的查询： MySQL对结果不确定的查询不会进行缓存，因为这个本来就无法缓存。如果查询语句中存在NOW()等类似的返回值不确定的函数调用，那么不能被缓存，除 了这些函数以外，用户自定义函数，存储函数，用户自定义变量，临时表，MySQL系统表等如果出现在SELECT语句中，这个查询结果集也不能被 MySQL缓存；子查询，视图，存储过程以及准备语句(Prepared Statement)也不可以被缓存。 Query Cache 是以客户端请求提交的 Query 为对象来处理的，只要客户端请求的是一个 Query，无论这个 Query 是一个简单的单表查询还是多表 Join，亦或者是带有子查询的复杂 SQL，都被当作成一个 Query，不会被分拆成多个 Query 来进行 Cache。所以，存在子查询的复杂 Query 也只会产生一个Cache对象，子查询不会产生单独的Cache内容。UNION[ALL] 类型的语句也同样如此。 缓存的开销： 首先SELECT查询要在缓存中查找对应的缓存结果集；其次如果这个查询是可以被缓存的，并且这个查询结果还没有被缓存，那么需要把此次查询的结果集写入到缓存中；最后，如果对表进行写操作的话，除了基本的IO操作之外，还要把和此表相关的查询缓存失效。 查询缓存可以减少结果集产生的时间，但它不会减少结果集发送到客户端的时间，而这个时间可能往往是性能的瓶颈。 最适合使用缓存的查询是那种需要很多资源来产生结果，但是不需要很多的空间来存储结果的查询，这种查询在存储，返回，以及失效等方面的开销都很小，比如COUNT()操作就适合用查询缓存。 如果对一张表进行比较平均的读写操作，那么查询缓存就不会起很大的作用了。因为写入数据不停地使缓存失效，然后查询又不停地把结果集进行缓存，这种情况下给系统根本带来不了多大的性能提升。 内存管理： MySQL把结果集缓存在内存中，内存池的大小为query_cache_size设置的值减去自身需要的40KB。需要缓存查询结果的时候，服务器会为 查询分配一块内存空间，这个空间的大小至少为query_cache_min_res_unit的值。服务器会把查询结果集保存在这个内存块中，如果这个 块满了之后还有结果没有保存，那么服务器就会再分配一个内存块来保存余下的结果，如果保存完成之后，发现这个内存块还有空间没被使用，那么系统会把这剩下 的空间合并到缓存的可用空间中去，以供下次分配内存块时候使用。 如果有两个并发的查询在进行，并且其结果都是可以被缓存的，那么系统会分配两个内存块分别对其结果进行缓存，如果这两个结果集缓存完毕之后，分配的 [...]
MySQL优化不是一劳永逸的工作，而是一个持久战。其中慢查询日志的分析是一个重要手段，以前我总是手动大概看看，不过这实在不是长久之计，今天试用了一下mysqldumpslow和mysqlsla，感觉效率高多了。 mysqldumpslow mysqldumpslow是官方提供的perl脚本，所以你也不用费劲巴拉的安装了，只要有mysql的环境基本就能用： mysqldumpslow -s c 其中参数（-s c）的意思就是按照各种慢查询条数排序。不过mysqldumpslow有一个挺不爽的缺点，就是查询的结果只有一些抽象的SQL，没有可供实际运行的SQL例子。 mysqldumpslow, mysql官方提供的慢查询日志分析工具. 输出图表如下: 主要功能是, 统计不同慢sql的 出现次数(Count), 执行最长时间(Time), 累计总耗费时间(Time), 等待锁的时间(Lock), 发送给客户端的行总数(Rows), 扫描的行总数(Rows), 用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示). mysqlsla mysqlsla是 hackmysql.com推出的一款日志分析工具(该网站还维护了 mysqlreport, mysqlidxchk 等比较实用的mysql工具) 整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等. 安装方法如下： tar zxf http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz cd mysqlsla-2.03 perl Makefile.PL make make install 使用起来很简单： /path/to/mysqlsla slow.log 格式说明如下: [...]
在mysql的配置文件 /etc/mysql/my.cnf 里添加以下两行 log_slow_queries = /var/log/slowlog/3000.log //如果需要，开启慢查询 long_query_time = 2 //慢查询设置的时间，超过此时间就记录 便可以记录查询时间过长的sql语句，从而sql语句进行优化 上面的2是2秒，当然可以改成小数，真正记当用时较长的sql词句
Mysql操作 启动：net start mysql; 进入：mysql -u root -p/mysql -h localhost -u root -p databaseName; 列出数据库：show databases; 选择数据库：use databaseName; 列出表格：show tables； 显示表格列的属性：show columns from tableName； 建立数据库：source fileName.txt; 匹配字符：可以用通配符_代表任何一个字符，％代表任何字符串; 增加一个字段：alter table tabelName add column fieldName dateType; 增加多个字段：alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType; 多行命令输入:注意不能将单词断开;当插入或更改数据时，不能将字段的字符串展开到多行里，否则硬回车将被储存到数据中; 增加一个管理员帐户：grant all on *.* to user@localhost identified by “password”; 每条语句输入完毕后要在末尾填加分号’;’，或者填加’\g’也可以； [...]
Mysql的备份方式有:mysqldump、mysqlhotcopy、BACKUP TABLE 、SELECT INTO OUTFILE，又或者备份二进制日志（binlog），还可以是直接拷贝数据文件和相关的配置文件。MyISAM表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。Innodb 所有的表都保存在同一个数据文件 ibdata1中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用mysqldump。 1.mysqldump备份 mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。 示例:mysqldump -uroot -p database table > /home/jobs/back.sql mysqldump也可做增量备份,mysqldump相关参数网上较多,就不在此一一赘述了 2.mysqlhotcopy备份 mysqlhotcopy 是一个 PERL 程序。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上。 mysqlhotcopy 只能用于备份 MyISAM，并且只能运行在 类Unix 和 NetWare 系统上。 mysqlhotcopy 支持一次性拷贝多个数据库，同时还支持正则表达。 示例： root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root-p=123456 database /tmp (把数据库目录 database 拷贝到 /tmp下) root#/usr/local/mysql/bin/mysqlhotcopy [...]
基本概念 定义： 二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。 作用： 1。二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。 2。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。 不良影响： 运行服务器时若启用二进制日志则性能大约慢1%。 如何启动： 通过 –log-bin=file选项可以启用 (更改my.ini文件) 日志位置 >>如果没有指定文件名,则Mysql使用hostname-bin文件. >>如果指定了相对路径,则假定该路径相对于数据目录 >>Mysql在文件名后添加了数字索引.所以该文件最后的形式为filename.number 如果你在日志名中提供了扩展名(例如，–log-bin=file_name.extension)，则扩展名被悄悄除掉并忽略。 更换策略： 使用索引来循环文件,在以下条件将循环至下一个索引 1。服务器重启 2。服务器被更新 3。日志到达了最大日志长度 max_binlog_size 4。日志被刷新 mysql> flush logs; 工具介绍： shell>>mysqlbinlog [option] binlogFile> newfile 如： D:\mysql\log>mysqlbinlog binlog.000001 > 1.txt 一个例子： log-bin=”D:/mysql/log/binlog” 那么，在该文件夹下就会有文件D:/mysql/log/binlog.000001等 常见问题 1.如何清除binlog >>>使用下面的两个命令 PURGE {MASTER | BINARY} LOGS TO ‘log_name’ //log_name不会被清除 PURGE {MASTER | BINARY} [...]
mysql让存储结果分页，用于复杂查询。 似乎讨论分页的人非常少，难道大家都沉迷于limit m,n? 在有索引的情况下，limit m,n速度足够，可是在复杂条件搜索时， where somthing order by somefield+somefield mysql会搜遍数据库，找出“所有”符合条件的记录，然后取出m,n条记录。 如果你的数据量有几十万条，用户又搜索一些非常通俗的词， 然后要依次读最后几页重温旧梦。。。 mysql该非常悲壮的不停操作硬盘。 所以，能试着让mysql也存储分页，当然要程式配合。（这里只是提出一个设想，欢迎大家一起讨论） asp的分页：在asp系统中有recordset对象来实现分页，不过大量数据放在内存中，而且不知道什么时候才失效(请asp高手指点). sql数据库分页：用存储过程+游标方式分页，具体实现原理不是非常清晰，设想如果用一次查询就得到需要的结果，或是id集，需要后续页时只要按照结果中的ids读出相关记录。这样只要非常小的空间保留本次查询的所有ids. (sql中的查询结果不知道怎样清晰过期垃圾?) 这样，能让mysql模拟存储分页机制： 1. select id from $table where $condition order by $field limit $max_pages*$count; 查询符合条件的ids. 限定最大符合条件的记录数量，也能不加。 2. 因为php在执行结束后所有变量都要lost，所以能考虑: 方案a. 在mysql建立临时表，查询结果用一个时间或随机数作为唯一标志插入。 其中建立page1~pagen个字段，每个字段保存该页中需要的ids, 这样一个id对一条记录. 方案b. 如果打开session,也能放在session中保存，实际上是放在文件中保存。 建立一个$ids数组，$ids[1]~$ids[$max_pages]. 考虑到有时候用户会开几个 窗口同时查询，要为$ids做一个唯一标志，避免查询结果相互覆盖。二维数组 和$$var都是好办法。 3. 在每页页的请求中，直接找到对应的ids，中间以”,”间隔： select * from $table where id in ($ids); [...]
