Category Archives: Mysql

Mysql数据库仅运行本机连接

为了安全性的原因,可能需要把Mysql设置为近可以通过127.0.0.1才能连接.

1. 首先更改my.cnf,绑定127.0.0.1

bind-address  = 127.0.0.1

这样只能本机访问,如果改成外网地址则外网可以访问mysql端口.

2. 添加iptables防火墙,阻止外部对3306端口的连接

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j DROP

如果想要开启的话:

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

Mysql创建用户并赋予查询权限

方法如下,不过在改密码的时候报错,只有默认空密码:

#
# Connect to the local database server as user root
# You will be prompted for a password.
#
mysql -h localhost  -u root -p

#
# Now we see the 'mysql>' prompt and we can run
# the following to create a new database for Paul.
#
mysql> create database pauldb;
Query OK, 1 row affected (0.00 sec)

#
# Now we create the user paul and give him full 
# permissions on the new database
mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to paul@localhost;
Query OK, 0 rows affected (0.00 sec)

#
# Next we set a password for this new user
#
mysql> set password for paul = password('mysecretpassword');
Query OK, 0 rows affected (0.00 sec)

#
# Cleanup and ext
mysql> flush privileges;
mysql> exit;

EasyPHP安装WHMCS错误

网站上自己整合的WHMCS template有些问题,准备在电脑上装一个EasyPHP环境修改一下自己做的template,但是安装whmcs后,登陆admin一直报错:

Login Failed. Please Try Again.
Your IP has been logged and admins notified of this
failed login attempt.

让我很郁闷,重新安装了N次都是这样,突然想到在安装的过程中有报错:

Errors Occured

Please open a ticket with the debug information below for support

File: upgrade330.sql
Line 15 - Field 'fromname' doesn't have a default value
Line 16 - Field 'fromname' doesn't have a default value
Errors Occured

Please open a ticket with the debug information below for support

File: upgrade340.sql
Line 16 - Field 'fromname' doesn't have a default value
Line 17 - Field 'fromname' doesn't have a default value
Line 18 - Field 'fromname' doesn't have a default value
Line 30 - Field 'value' doesn't have a default value
Errors Occured

Please open a ticket with the debug information below for support

File: upgrade341.sql
Line 6 - Field 'fromname' doesn't have a default value
Errors Occured

Please open a ticket with the debug information below for support

File: upgrade380.sql
Line 24 - Incorrect integer value: '' for column 'id' at row 1

以为问题不大就当是就没有理会,是不是这里的原因?

后来才发现原来的EasyPHP新版的mysql默认开启了Strict Mode,造成WHMCS安装报错,在php.ini中修改

#sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
sql-mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

参考这里:http://www.vk8.org/archives/405.html

Mysql复杂查询时“表别名”的用处

比如说我有如下的一些数据:

解释一下:projectId是指某个项目的ID,qiantaiId是在这个项目中某个人的ID,但是一个人在某个项目中可能会做好几次,问题是如何用sql统计某个项目中究竟有多少人参与?

第一,这个sql肯定要有项目Id,where projectId=XX是肯定有的,qiantaiId只以类分,所以要group by qiantaiId,我第一想法是这样的:

select count(*)  from votes where projectId=5 group by qiantaiId

但是很遗憾,得到的结果是这样的:



虽然很遗憾,但是离我的目标不远了,至少的确是2行数据。那么就需要在查询结果中再计算,想到了如下sql:

SELECT count(*) from(select count(*)  from votes where projectId=5 group by qiantaiId)

但是mysql又报错了:

#1248 - Every derived table must have its own alias

原来mysql在复杂查询时需要表别名,这还真不知道,更改sql如下OK:

SELECT count(*) from(select count(*)  from votes where projectId=5 group by qiantaiId) as t1

忘记Mysql密码更改Mysqld密码

安装Virtualizor很快就完了,但是mysql的root密码是随机的,就在Virtualizor的控制面板中改了mysql的root密码,没想到Virtualizor连进都进不去了,只有更改手工更改mysql密码了.

1.停止Mysqld服务

# service mysqld stop 

2. 使用 “–skip-grant-tables”参数重新启动mysqld

# mysqld_safe --skip-grant-tables &

2. 手工改密码

# mysql -u root
mysql> use mysql
mysql> update user set password=password('admin123') where user='root';
mysql> flush previleges;
mysql> quit

4. 重启mysqld服务

#service mysqld start

SQL外键

在学习数据库基本原理的时候知道有外键的存在,是为了保证数据的完备性,不过一直用mysql的时候没有用过外键,直到今天看到Yii的Demo中,user和post表都在创建的时候设定了外键和约束:

CREATE TABLE tbl_user
(
	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(128) NOT NULL,
	password VARCHAR(128) NOT NULL,
	salt VARCHAR(128) NOT NULL,
	email VARCHAR(128) NOT NULL,
	profile TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE tbl_post
(
	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(128) NOT NULL,
	content TEXT NOT NULL,
	tags TEXT,
	status INTEGER NOT NULL,
	create_time INTEGER,
	update_time INTEGER,
	author_id INTEGER NOT NULL,
	CONSTRAINT FK_post_author FOREIGN KEY (author_id)
		REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

mysql默认的MyISAM表类型是不支持外键的,只有选择InnoDB才能使用外键.到底是否需要在SQL中设置外键?首先外键并不是类似于索引的东西,能够加快查询,只是为了保证在user被删除的时候,user的所有post也均被删除.这样的工作完全可以由程序来完成,并且有时候即使删除了user,残留的post也可能是有用的.

总的来说,这个外键是可有可无的,不设置完全没有问题.数据库范式真是一个操蛋的玩意.

CentOS上优化Mysqld

刚刚安装的Mysqld启动后会占用大量的内存,其实根本不用这么多,因为mysql默认加载了很多不需要的服务,不如说如果数据库仅仅是查询的话,innodb之类的引擎根本就不用加载。

#vim /etc/my.cnf

[mysqld]
port = 3306
socket=/var/lib/mysql/mysql.sock
skip-locking

key_buffer = 16K
query_cache_limit = 256K
query_cache_size = 4M
max_allowed_packet = 1M
table_cache = 8

max_connections = 16
thread_concurrency = 2

sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

skip-bdb
skip-innodb

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

这样修改后mysqld启动后只会占用5MB以内的内存,如果你的内存比较大或者使用的是独立主机而不是VPS的话,可以把参数调整的更大一些,加快mysql的查询速度。