分类 MYSQL 下的文章

数据库建立索引常用的规则如下：
1、表的主键、外键必须有索引； 
2、数据量超过300的表应该有索引； 
3、经常与其他表进行连接的表，在连接字段上应该建立索引； 
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 
5、索引应该建在选择性高的字段上； 
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

- 阅读剩余部分 -

MAC安装完MySQL后在终端无法和Linux一样简单的用 "msyql" 登入管理，可以通过以下设置简化。

MySQL安装的位置在 /usr/local/mysql

设置一个别名即可：

$alias mysql='/usr/local/mysql/bin/mysql'

现在就可以和Linux一样通过命令管理，如：

$mysql -u root -p

下载：wget http://mysqltuner.pl/ -O mysqltuner.pl

运行：perl mysqltuner.pl 结果：

>>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 5.5.41-0+wheezy1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 1M (Tables: 11)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 11

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
[OK] Slow queries: 0% (0/113)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
[!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 25% (54 on disk / 213 total)
[OK] Thread cache hit rate: 97% (1 created / 42 connections)
[OK] Table cache hit rate: 24% (52 open / 215 opened)
[OK] Open file limit used: 4% (48/1K)
[OK] Table locks acquired immediately: 100% (62 immediate / 62 locks)
[OK] InnoDB buffer pool / data size: 128.0M/1.2M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)

If you host a large database that can't be split up, and your server doesnt have the resources to handle it, you can setup mysql to be run on an external server. This shouldn't be needed too often, as usually, you can just move entire user accounts to another server to ease the load. But in the case of one large database using up the whole server, then you dont' have much choice.

Note that this guide does not transfer any databases over. It's generally a good idea to do this before you add users. Also, mysql will continue to run on your local DA.. so existing databases and scripts should continue to function, but users will not be able to control them through DA.

1)The first step is to install mysql onto the remote server. DA is not require on this server, as it is just used for mysql and nothing else. I won't go into the detail on how to do this (use google).

2) The next step is to setup a user that DA can connect to on the remote mysql server. The default DA uses is da_admin, so staying with that name is a good idea, but not required.
You can use this guide to setup the da_admin user on your server: help.directadmin.com/item.php?id=45. The username and password you setup have to be set in the /usr/local/directadmin/conf/mysql.conf file for DA to use.
On a related note, if the remote server is on a LAN IP, and the connecting IP to that remote server will not be your server IP, but rather some other IP such as a 192.168.x.x type of IP then you can use this guide to add an access host by default instead of the server IP: directadmin.com/features.php?id=818.

Note that the above guide will only grants da_admin on the "localhost", meaning you have to already be on that remote mysql server to use the account. We'll need to add another acccess hsot (ip) to allow the DA server to connect to it. Basically, you just run the "GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost" command again, but you change localhost to the IP of your DA server,

GRANT ALL PRIVILEGES ON *.* TO da_admin@1.2.3.4 WITH GRANT OPTION;
FLUSH PRIVILEGES;

we don't need the "identified by 'pass'" again, because the password is already from adding it the first time.

3) At this point.. the remote mysql database should be setup and ready to accept DA to use it. Test it out by logging into your DA machine via ssh, and type:

mysql -uda_admin -p --host=4.3.2.1

where 4.3.2.1 is the IP of your mysql server. If you can't connect, go back over your settings again.. also make sure that port 3306 is open on the remote box.

4)Now.. the easier part is to tell DA to use the remote server. Edit:
/usr/local/directadmin/conf/mysql.conf
add the line:
host=4.3.2.1
where 4.3.2.1 is the IP of your remote server. Make sure there is a newline character at the end of the line.. (Eg, press enter)

5) That should be it. Go into DA and check the mysql section of your user level to see if you get any errors. If not, try adding a database to make sure it works.

6) The only cleanup task would then be to fix up phpMyAdminto also connect to the remote host.
Edit /var/www/html/phpMyAdmin/config.inc.php
Find this line:

$cfg['Servers'][$i]['host']          = 'localhost'; // MySQL hostname or IP address

and change it to

$cfg['Servers'][$i]['host']          = '4.3.2.1'; // MySQL hostname or IP address

官方文档：http://help.directadmin.com/item.php?id=140

The following mini script is used to convert existing database tables to UTF-8. Upload the script to your account as "convert.php" and modify the database connection parameters and the character set, then execute the script.

To execute the script, you simply would visit the script in any web browser. If you upload the file to your public_html folder you'd visit "http://your-domain.com/convert.php".

Don't forget to replace your-domain.com with your actual domain name. Also, to get your languages to work on your site the collation will need to be utf8.

Code to convert your database to UTF-8

<?php  
     
// Fill in your Server, User, Database, Password, and Collation configuration below   
$db_server = 'localhost';   
$db_user = 'database user';   
$db_password = 'password';   
$db_name = 'database name';   
$char_set = 'new character set';  


// Adds the header information
header('Content-type: text/plain');  


// Connects to the MySQL database    
$connection = mysql_connect($db_server, $db_user, $db_password) or die(mysql_error() );      
$db = mysql_select_db($db_name) or die( mysql_error() ); 


// Runs the SQL query on teh database     
$sql = 'SHOW TABLES'; $result = mysql_query($sql) or die( mysql_error() ); 


// Runs a loop that finds all collations within the database and changes it to the new collation    
   while ( $row = mysql_fetch_row($result) )   {     
      $table = mysql_real_escape_string($row[0]);  
      $sql = "ALTER TABLE $table CONVERT TO CHARACTER SET $char_set COLLATE utf8_general_ci";     
      mysql_query($sql) or die( mysql_error() );       
      print "$table changed successfully.\n";  
   }    


// Update the Collation of the database itself  
$sql = "ALTER DATABASE CHARACTER SET $char_set;";  
mysql_query($sql) or die( mysql_error());     
print "Database collation has been updated successfully.\n";     


// close the connection to the database  
mysql_close($connection);     


?>
Note! You can use this script to change the database to any character set you wish. You need to define the character set in the script to change character sets:
$char_set = 'character set';

You will need the change the utf8_general_ci to match the character set you defined in the step above. So, if you want to change the character set to "Hebrew" you'd change the line to:

$sql = "ALTER TABLE $table CONVERT TO CHARACTER SET $char_set COLLATE hebrew_general_ci";

转载：http://www.inmotionhosting.com/support/website/databases/how-to-convert-a-database-to-utf-8

CREATE DATABASE information_schema DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ciSQL 查询:
-- -- ? information_schema -- CREATE DATABASE information_schema DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
MySQL 返回：
#1044 - Access denied for user 'xxx'@'localhost' to database 'information_schema'

phpmyadmin导出的数据库里有创建数据库的语句，而空间没有创建数据库权限，所以出错。常见于没有进入特定数据库导出导致。

删除导出的sql文件里的创建数据库语句：

-- phpMyAdmin SQL Dump
-- version 2.11.6
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2014 年 09 月 01 日 15:46
-- 服务器版本: 5.0.51
-- PHP 版本: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET

@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS

*/;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--------------------------------------------------------
这中间的部分删除...
--
-- 表的结构 alerts
--------------------------------------------------------

保存，然后再进phpmyadmin导入数据就没有问题了。

本文参考：http://www.bxl.me/1598.html

在wdcp的后台里,是可以备份这个数据库的,但目前是没有恢复或自动恢复的,那要恢复时,怎么办?
备份目录在
/www/backup/mysql下
要恢复的话,直接解压就可以

tar zxvf dbname.tar.gz -C /www/wdlinux/mysql/var/

这样就可以了,什么都不用操作
dbname.tar.gz为你要恢复的数据库备份文件
当然,这个是默认情况下
有些时候,还需要修改下权限,修改这个解压的目录的权限为mysql
如

chown -R mysql.mysql /www/wdlinux/mysql/var/dbname

这样修改的原因,主要是有移动过目录或上传或下载等原因,改变了原文件的权限,所以需要修改,默认备份恢复,是可以不用修改这个

同样的情况,如果迁移服务器,也可以打包上传解压,也可以直接上传整个目录到/www/wdlinux/mysql/var下
这样操作的话,一定要改权限,否则会启动不了,又或是对数据库无法写操作

操作完上面后,别忘了重起一下数据库,否则可能也不会生效,如

service mysqld restart

备注
这个直接操作数据库目录文件的方法,仅限于在使用mysql版本相同或相近时可以,否则可能会有其它一些问题
此时,可以使用mysqldump,phpmyadmin导出,再导入的方法

原文连接:http://www.wdlinux.cn/bbs/thread-3795-1-1.html

一查询数值型数据:
SELECT * FROM tb_name WHERE sum > 100;
查询谓词:>,=,<,<>,!=,!>,!<,=>,=<

二查询字符串
SELECT * FROM tb_stu WHERE sname = '小刘'
SELECT * FROM tb_stu WHERE sname like '刘%'
SELECT * FROM tb_stu WHERE sname like '%程序员'
SELECT * FROM tb_stu WHERE sname like '%PHP%'

三查询日期型数据
SELECT * FROM tb_stu WHERE date = '2011-04-08'
注:不同数据库对日期型数据存在差异: ：
(1)MySQL:SELECT * from tb_name WHERE birthday = '2011-04-08'
(2)SQL Server:SELECT * from tb_name WHERE birthday = '2011-04-08'
(3)Access:SELECT * from tb_name WHERE birthday = #2011-04-08#

四查询逻辑型数据
SELECT * FROM tb_name WHERE type = 'T'
SELECT * FROM tb_name WHERE type = 'F'
逻辑运算符:and or not

五查询非空数据
SELECT * FROM tb_name WHERE address <>'' order by addtime desc
注:<>相当于PHP中的!=

六利用变量查询数值型数据
SELECT * FROM tb_name WHERE id = '$_POST[text]'
注:利用变量查询数据时，传入SQL的变量不必用引号括起来，因为PHP中的字符串与数值型数据进行连接时，程序会自动将数值型数据转变成字符串，然后与要连接的字符串进行连接

七利用变量查询字符串数据
SELECT * FROM tb_name WHERE name LIKE '%$_POST[name]%'
完全匹配的方法"%%"表示可以出现在任何位置

八查询前n条记录
SELECT * FROM tb_name LIMIT 0,$N;
limit语句与其他语句，如order by等语句联合使用，会使用SQL语句千变万化，使程序非常灵活

九查询后n条记录
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n

十查询从指定位置开始的n条记录
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $_POST[begin],$n
注意:数据的id是从0开始的

十一查询统计结果中的前n条记录
SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num

十二查询指定时间段的数据
SELECT 要查找的字段 FROM 表名 WHERE 字段名 BETWEEN 初始值 AND 终止值
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

十三按月查询统计数据
SELECT * FROM tb_stu WHERE month(date) = '$_POST[date]' ORDER BY date ;
注：SQL语言中提供了如下函数，利用这些函数可以很方便地实现按年、月、日进行查询
year(data):返回data表达式中的公元年分所对应的数值
month(data):返回data表达式中的月分所对应的数值
day(data):返回data表达式中的日期所对应的数值

十四查询大于指定条件的记录
SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;

十五查询结果不显示重复记录
SELECT DISTINCT 字段名 FROM 表名 WHERE 查询条件
注:SQL语句中的DISTINCT必须与WHERE子句联合使用，否则输出的信息不会有变化 ,且字段不能用*代替

十六NOT与谓词进行组合条件的查询
(1)NOT BERWEEN … AND … 对介于起始值和终止值间的数据时行查询 可改成 <起始值 AND >终止值
(2)IS NOT NULL 对非空值进行查询
(3)IS NULL 对空值进行查询
(4)NOT IN 该式根据使用的关键字是包含在列表内还是排除在列表外，指定表达式的搜索，搜索表达式可以是常量或列名，而列名可以是一组常量，但更多情况下是子查询

十七显示数据表中重复的记录和记录条数
SELECT name,age,count(*) ,age FROM tb_stu WHERE age = '19' group by date

十八对数据进行降序/升序查询
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 DESC 降序
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 ASC 升序
注:对字段进行排序时若不指定排序方式，则默认为ASC升序

十九对数据进行多条件查询
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段1 ASC 字段2 DESC …
注意:对查询信息进行多条件排序是为了共同限制记录的输出，一般情况下，由于不是单一条件限制，所以在输出效果上有一些差别。

二十对统计结果进行排序
函数SUM([ALL]字段名) 或 SUM([DISTINCT]字段名),可实现对字段的求和，函数中为ALL时为所有该字段所有记录求和,若为DISTINCT则为该字段所有不重复记录的字段求和
如：SELECT name,SUM(price) AS sumprice FROM tb_price GROUP BY name

SELECT * FROM tb_name ORDER BY mount DESC,price ASC

二十一单列数据分组统计
SELECT id,name,SUM(price) AS title,date FROM tb_price GROUP BY pid ORDER BY title DESC
注:当分组语句group by排序语句order by同时出现在SQL语句中时，要将分组语句书写在排序语句的前面，否则会出现错误

二十二多列数据分组统计
多列数据分组统计与单列数据分组统计类似
SELECT *，SUM(字段1*字段2) AS (新字段1) FROM 表名 GROUP BY 字段 ORDER BY 新字段1 DESC
SELECT id,name,SUM(price*num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC
注：group by语句后面一般为不是聚合函数的数列，即不是要分组的列

二十三多表分组统计
SELECT a.name,AVG(a.price),b.name,AVG(b.price) FROM tb_demo058 AS a,tb_demo058_1 AS b WHERE a.id=b.id GROUP BY b.type;

摘自：http://bbs.csdn.net/topics/390407669

mysql中如果我们开启bin-log日志全在mysql目录发现大量的mysql-bin.000001,mysql-bin.000002等，如果多了会发现占很大的空间，下面我来介绍mysql-bin日志关闭与删除方法。

日记删除
首先使用root权限账户登入mysql
#mysql -u root -pxxxxx
执行：mysql> reset master;
完成后退出：exit;

关闭日记或定时删除
编辑 my.cnf 注释 log-bin=mysql-bin 重启mysql即可
如果需要定时删除则 expire_logs_days = 3
意思为3天删除

  1. 1
  2. 2