在Apache, PHP, mysql的体系架构中，MySQL对于性能的影响最大，也是关键的核心部分。对于Discuz!论坛程序也是如此，MySQL的设置是否合理优化，直接 影响到论坛的速度和承载量！同时，MySQL也是优化难度最大的一个部分，不但需要理解一些MySQL专业知识，同时还需要长时间的观察统计并且根据经验 进行判断，然后设置合理的参数。 下面我们了解一下MySQL优化的一些基础，MySQL的优化我分为两个部分，一是服务器物理硬件的优化，二是MySQL自身(my.cnf)的优化。 一、服务器硬件对MySQL性能的影响 ① 磁盘寻道能力（磁盘I/O）,以目前高转速SCSI硬盘(7200转/秒)为例，这种硬盘理论上每秒寻道7200次，这是物理特性决定的，没有办法改变。 MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一，对于日均访问量 在100万PV以上的Discuz!论坛，由于磁盘I/O的制约，MySQL的性能会非常低下！解决这一制约因素可以考虑以下几种解决方案： 使用RAID-0+1磁盘阵列，注意不要尝试使用RAID-5，MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。 ②CPU 对于MySQL应用，推荐使用S.M.P.架构的多路对称CPU，例如：可以使用两颗Intel Xeon 3.6GHz的CPU，现在我较推荐用4U的服务器来专门做数据库服务器，不仅仅是针对于mysql。 ③物理内存对于一台使用MySQL的Database Server来说，服务器内存建议不要小于2GB，推荐使用4GB以上的物理内存，不过内存对于现在的服务器而言可以说是一个可以忽略的问题，工作中遇到了高端服务器基本上内存都超过了16G。 二、 MySQL自身因素 当解决了上述服务器硬件制约因素后，让我们看看MySQL自身的优化是如何操作的。对MySQL自身的优化主要是对其配置文件 my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。 由于my.cnf文件的优化设置是与服务器硬件配置息息相关的，因而我们指定一个假想的服务器硬件环境： 下面，我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明： #vim /etc/my.cnf以下只列出my.cnf文件中[mysqld]段落中的内容，其他段落内容对MySQL运行性能影响甚微，因而姑且忽略。 port = 3306 serverid = 1 socket = /tmp/mysql.sock skip-locking #避免MySQL的外部锁定，减少出错几率增强稳定性。 skip-name-resolve #禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！ back_log = 384 #back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自 己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。 key_buffer_size = 256M #key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！ max_allowed_packet = 4M thread_stack = 256K table_cache = 128K sort_buffer_size = 6M #查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。 read_buffer_size = 4M #读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。 join_buffer_size = 8M #联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。 myisam_sort_buffer_size = 64M table_cache = 512 thread_cache_size = 64 query_cache_size = 64M […]
View Details1、从左开始截取字符串 left（str, length） 说明：left（被截取字段，截取长度） 例：select left（content,200） as abstract from my_content_t 2、从右开始截取字符串 right（str, length） 说明：right（被截取字段，截取长度） 例：select right（content,200） as abstract from my_content_t 3、截取字符串 substring（str, pos） substring（str, pos, length） 说明：substring（被截取字段，从第几位开始截取） substring（被截取字段，从第几位开始截取，截取长度） 例：select substring（content,5） as abstract from my_content_t select substring（content,5,200） as abstract from my_content_t （注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度） 4、按关键字截取字符串 substring_index（str,delim,count） 说明：substring_index（被截取字段，关键字，关键字出现的次数） 例：select substring_index（"blog.jb51.net"，"。"，2） as abstract from my_content_t 结果：blog.jb51 （注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束） 函数简介： SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FORlen) 不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 详情请查阅手册。 实例： 表1：user 表2：jl 期望效果：通过user表jlid字段存储的id值，读取jl表中的相应记录，这里想要读取，jl表中id为1、2的记录，首先想到用in，但是很遗憾由于 jlid字段存储的id值有2个，尽管从形式上符合in(1,2)的格式，但是如果你使用select jl.* from jl where jl.id in(select jlid from user where user.id=1)来查询的话，是不行的，他总是返回id为1的记录。 那么怎么办呢？如果我们能够分别得到1,2中的1和2就行了。好在mysql也提供了字符串截取函数SUBSTRING。 sql句法如下： SELECT […]
View DetailsPostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES，现在已经更名为PostgreSQL，版本 4.2为基础的对象关系型数据库管理系统（ORDBMS）。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性：复杂查询、外键、触发器、视图、事务完整性、MVCC。同样，PostgreSQL 可以用许多方法扩展，比如， 通过增加新的数据类型、函数、操作符、聚集函数、索引。免费使用、修改、和分发 PostgreSQL，不管是私用、商用、还是学术研究使用。 PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统)，它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如 MySQL 和 Firebird)，和专有系统(比如 Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server)之外的另一种选择。 PostgreSQL 不寻常的名字导致一些读者停下来尝试拼读它，特别是那些把SQL拼读为"sequel"的人。PostgreSQL 开发者把它拼读为 "post-gress-Q-L"。它也经常被简略念为 "postgres"。[1] 事实上， PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99，首先，它包括了可以说是目前世界上最丰富的数据类型的支持，其中有些数据类型可以说连商业数据库都不具备， 比如 IP 类型和几何类型等；其次，PostgreSQL 是全功能的自由软件数据库，很长时间以来，PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统（MVCC）、数据完整性检查等特性的唯一的一种自由软件的数据库管理系统。 Inprise 的 InterBase 以及SAP等厂商将其原先专有软件开放为自由软件之后才打破了这个唯一。最后，PostgreSQL拥有一支非常活跃的开发队伍，而且在许多黑客的努力下，PostgreSQL 的质量日益提高。 从技术角度来讲，PostgreSQL 采用的是比较经典的C/S（client/server）结构，也就是一个客户端对应一个服务器端守护进程的模式，这个守护进程分析客户端来的查询请求，生成规划树，进行数据检索并最终把结果格式化输出后返回给客户端。为了便于客户端的程序的编写，由数据库服务器提供了统一的客户端 C 接口。而不同的客户端接口都是源自这个 C 接口，比如ODBC，JDBC，Python，Perl，Tcl，C/C++，ESQL等， 同时也要指出的是，PostgreSQL 对接口的支持也是非常丰富的，几乎支持所有类型的数据库客户端接口。这一点也可以说是 PostgreSQL 一大优点。 PostgreSQL强壮的一个原因源于它的架构。和商业数据库一样，PostgreSQL可以用于C/S(客户/服务器)环境。这对于用户和开发人员有很多好处。 PostgreSQL安装核心是数据库服务端进程。它允许在一个独立服务器上。需要访问存储在数据库中的数据的应用程序必须通过数据库进程。这些客户端程序无法直接访问数据，即使它们和服务程序在同一台机器上。
View DetailsMySQL master/slave配置过程 一般master用来写，表类型采用InnoDB，在并发很多的情况下，InnoDB的表现肯定要比MyISAM强很多;slave用来读，表类型采用MyISAM，一个master可以对应一个或多个slave，一般情况下来说读比写多吧，不过也要根据实际情况来定。 A服务器(WindowsXP): 192.168.1.54 主服务器master B服务器(Linux): 192.168.1.23 副服务器slave A服务器设置 #提示，此处最好用mysql的命令行执行命令，用SQLyog等工具时可能不行 mysql -u root -p mysql>GRANT FILE ON *.* TO backup@192.168.1.23 IDENTIFIED BY '1234'; mysql>GRANT REPLICATION SLAVE ON *.* TO backup@192.168.1.23; mysql>exit; 上面是Master开放一个账号backup密码1234给IP:192.168.1.23有档案处理的权限 停止mysql的服务，防止数据库中增加新的数据，确保复制到slave中的数据是一样的 (此时如果是用mysqldump导出数据的话，则不能停止mysql服务，停止web server就可以了，net stop apache服务名称) 停止mysql mysqladmin -u root -p shutdown 或用net stop mysql服务名称(如mysql) 导出数据库结构及数据 D:\>mysqldump -u root -p ms_test > ms_test.sql Enter password: **** 备份Master所有数据库结构及数据, MySQL是要在stop情况下 在A机器上修改my.ini 在[mysqld]区段内加入参数 log-bin=mysql-bin server-id=1 #sql-bin-update-same binlog-do-db=ms_test 重启A服务器mysql B服务器设置 设定/etc/my.cnf 在[mysqld]区段加入 #added by alex in 08/21/1008 master-host=192.168.1.54 master-user=backup master-password=1234 master-port=3306 server-id=2 master-connect-retry=60 replicate-do-db=ms_test 将A上备份的数据库结构及数据copy到B服务器上，然后将数据库结构及数据导入到B服务器中 因为导出的文件ms_test.sql中不含有创建数据库的sql语句， 一是方法是在B服务器上手动创建数据库 另一种方法是把create database if […]
View Details如下是转的，今天遇到类似问题，按照此方法处理成功了： 补充change的点位到新日志的第一点也是可以的（mysql50--98；mysql51--106；mysql55--107） 今天一台MySQL 服务器突然停电，重启后复制无法启动，总是报如下错误： 080922 10:29:01 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000778' at position 2874108, relay log ' ./relay-bin.003287' position: 2874245 080922 10:29:01 [Note] Slave I/O thread: connected to master 'replication@192.168.0.10:3306', replication started in log 'mysql-b in.000778' at position 2874108 080922 10:29:01 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( se rver_errno=1236) 080922 10:29:01 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master wh en reading data from binary […]
View Details环境 配置Mysql的MasterSlave至少需要两台机器。我这里使用三台虚拟机进行测试。三台机器配置完全一样，MySQL安装的路径也是一样： 第一台：10.1.5.181； Windows 2008 DataCenter + MySQL Community Server 5.6.10.1 第二台：10.1.5.182； Windows 2008 DataCenter + MySQL Community Server 5.6.10.1 第三台：10.1.5.183； Windows 2008 DataCenter + MySQL Community Server 5.6.10.1 第一台10.1.5.181用作master，其他两台用做slave。 配置Master 在10.1.5.181这台服务器上找到MySQL的配置文件my.ini。我的具体路径是在C:\ProgramData\MySQL\MySQL Server 5.6下。 打开配置文件，在最下面添加如下配置： ************************************************************************************ #Master start #日志输出地址 主要同步使用 log-bin=master-bin.log #同步数据库 binlog-do-db=test #主机id 不能和从机id重复 server-id=1 #Master end ************************************************************************************ master的配置比较少，server-id是为这一组master/slave服务器定的唯一id，master/slave服务器中不能重复。在binlog-do-db中填写对象要同步的数据库，如果有多个，用逗号分隔，或再写一行如binlog-do-db=test2。 配置Slave 同样在第二台机器上10.1.5.181找到配置文件my.ini。打开配置文件，在最下面添加如下配置： ***************************************************************************** report-host = 10.1.5.181 report-user = root report-password = root123 log-bin = slave-bin.log replicate-do-db = test server-id = 2 ***************************************************************************** 这里需要添加master的IP，连接master的用户名和密码，生产环境中需要新建一个用户专门来处理replication，这里没有新建用户，用root做测试。端口没有配置，就是使用默认的3306，如果端口有变化，则通过report-port=？来配置。log-bin是记录日志的位置。 然后通过命令start slave来启动mysql的复制功能。如果在start slave过程中出现异常： The server is not configured as slave; fix in config […]
View Details获得当前日期+时间（date + time）函数：now()
|
1 2 3 4 5 6 7 |
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2008-08-08 22:20:46 | +---------------------+ |
获得当前日期+时间（date + time）函数：sysdate() sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。看下面的例子就明白了：
|
1 2 3 4 5 6 7 |
mysql> select now(), sleep(3), now(); +---------------------+----------+---------------------+ | now() | sleep(3) | now() | +---------------------+----------+---------------------+ | 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 | +---------------------+----------+---------------------+ |
sysdate() 日期时间函数，一般情况下很少用到。 MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()
|
1 2 3 4 5 6 7 |
mysql> select current_timestamp, current_timestamp(); +---------------------+---------------------+ | current_timestamp | current_timestamp() | +---------------------+---------------------+ | 2008-08-09 23:22:24 | 2008-08-09 23:22:24 | +---------------------+---------------------+ |
MySQL 日期转换函数、时间转换函数 MySQL Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)
|
1 2 3 4 5 6 7 |
mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); +----------------------------------------------------+ | date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') | +----------------------------------------------------+ | 20080808222301 | +----------------------------------------------------+ |
MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。 MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)
|
1 2 3 4 5 |
select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09 select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09 select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09 select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30 select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30 |
可以看到，str_to_date(str,format) 转换函数，可以把一些杂乱无章的字符串转换为日期格式。另外，它也可以转换为时间。“format” 可以参看 MySQL 手册。 MySQL （日期、天数）转换函数：to_days(date), from_days(days)
|
1 2 |
select to_days('0000-00-00'); -- 0 select to_days('2008-08-08'); -- 733627 |
MySQL （时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)
|
1 2 |
select time_to_sec('01:00:05'); -- 3605 select sec_to_time(3605); -- '01:00:05' |
MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)
|
1 2 3 |
select makedate(2001,31); -- '2001-01-31' select makedate(2001,32); -- '2001-02-01' select maketime(12,15,30); -- '12:15:30' |
MySQL （Unix 时间戳、日期）转换函数
|
1 2 3 4 |
unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp,format) |
下面是示例：
|
1 2 3 4 5 6 7 8 9 |
select unix_timestamp(); -- 1218290027 select unix_timestamp('2008-08-08'); -- 1218124800 select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800 select from_unixtime(1218290027); -- '2008-08-09 21:53:47' select from_unixtime(1218124800); -- '2008-08-08 00:00:00' select from_unixtime(1218169800); -- '2008-08-08 12:30:00' select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008' |
MySQL 日期时间计算函数 MySQL 为日期增加一个时间间隔：date_add()
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
set @dt = now(); select date_add(@dt, interval 1 day); -- add 1 day select date_add(@dt, interval 1 hour); -- add 1 hour select date_add(@dt, interval 1 minute); -- ... select date_add(@dt, interval 1 second); select date_add(@dt, interval 1 microsecond); select date_add(@dt, interval 1 week); select date_add(@dt, interval 1 month); select date_add(@dt, interval 1 quarter); select date_add(@dt, interval 1 year); select date_add(@dt, interval -1 day); -- sub 1 day |
MySQL adddate(), addtime()函数，可以用 date_add() 来替代。下面是 date_add() 实现 […]
View Details网站:bbs.osyunwei.com 程序在:Web服务器192.168.21.129上面 数据库在:MySQL服务器192.168.21.169上面 实现目的:增加一台MySQL备份服务器(192.168.21.168)，做为MySQL服务器(192.168.21.169)的从服务器，这两台MySQL服务器之间实现双机热备。 即:从服务器(192.168.21.168)上面指定的数据库永远与主服务器(192.168.21.169)上面指定的数据库保持同步，并且随着主服务器(192.168.21.169)上面指定的数据库的变化而变化。 系统运维 www.osyunwei.com 温馨提醒:qihang01原创内容版权所有,转载请注明出处及原文链接 环境说明 1、Web服务器 系统:CentOS 6.2 IP:192.168.21.129 Web环境:apache+php 2、MySQL主服务器 系统:CentOS 6.2 IP:192.168.21.169 主机名称:MySQLMaster MySQL版本:mysql-5.5.22 3、MySQL从服务器 系统:CentOS 6.2 IP:192.168.21.168 主机名称:MySQLSlave MySQL版本:mysql-5.5.22 4、客户机 系统:Windows 7 IP:192.168.21.130 备注:作为主从服务器的MySQL版本建议使用同一版本!或者必须保证主服务器的MySQL版本要高于从服务器的MySQL版本(MySQL版本是向下兼容的) ####################################################################################### 教程开始: 一、安装MySQL 说明:在两台MySQL服务器192.168.21.169和192.168.21.168上分别进行如下操作，安装MySQL 5.5.22 1、配置防火墙，开启MySQL默认3306端口 vi /etc/sysconfig/iptables #编辑防火墙配置文件 -A INPUT -m state state NEW -m tcp -p tcp dport 3306 -j ACCEPT /etc/init.d/iptables restart #重启防火墙，使配置生效 2、关闭SELINUX vi /etc/selinux/config #SELINUX=enforcing #注释掉 #SELINUXTYPE=targeted #注释掉 SELINUX=disabled #增加 :wq 保存，关闭。 shutdown -r now重启系统。 3、安装编译工具 yum install wget make apr* autoconf automake curl-devel gcc gcc-c++ zlib-devel openssl openssl-devel pcre-devel gd kernel […]
View Details前言:朋友咨询我说执行简单的update语句失效，症状如下: MySQL> update order_info set province_id=15 ,city_id= 1667 where order_from=10 and order_out_sn= 1407261241xxxx ; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> QQ远程过去，开始check 1，查看数据库的隔离级别: mysql> select @@tx_isolation; + + | @@tx_isolation | + + | REPEATABLE-READ | + + 1 row in set (0.00 sec) mysql> 2，去查看先当前库的线程情况: mysql> show full processlist; + -+ + -+ + -+ + -+ + | Id | User | Host | db | Command | Time | State | Info | + -+ + -+ + -+ + -+ + | 1 | event_scheduler | localhost | NULL | Daemon | 9635385 | Waiting on empty queue | NULL | | 9930577 | business_web | 192.168.1.21:45503 | business_db | Sleep | […]
View Details闲置已久的空间环境配置忘得差不多了，今天得空整理，重置了磁盘重新搭建环境，首先在CentOS 7.0安装MariaDB的数据库，在这里记录下安装过程，以便以后查看。 1、安装MariaDB 安装命令
|
1 |
yum -y install mariadb mariadb-server |
安装完成MariaDB，首先启动MariaDB
|
1 |
systemctl start mariadb |
设置开机启动
|
1 |
systemctl enable mariadb |
接下来进行MariaDB的相关简单配置
|
1 |
mysql_secure_installation |
首先是设置密码，会提示先输入密码 Enter current password for root (enter for none):<–初次运行直接回车 设置密码 Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车 New password: <– 设置root用户的密码 Re-enter new password: <– 再输入一次你设置的密码 其他配置 Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车 Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车, Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车 Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车 初始化MariaDB完成，接下来测试登录
|
1 |
mysql -uroot -ppassword |
完成。 2、配置MariaDB的字符集 文件/etc/my.cnf
|
1 |
vi /etc/my.cnf |
在[mysqld]标签下添加
|
1 2 3 4 5 |
init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake |
文件/etc/my.cnf.d/client.cnf
|
1 |
vi /etc/my.cnf.d/client.cnf |
在[client]中添加
|
1 |
default-character-set=utf8 |
文件/etc/my.cnf.d/mysql-clients.cnf
|
1 |
vi /etc/my.cnf.d/mysql-clients.cnf |
在[mysql]中添加
|
1 |
default-character-set=utf8 |
全部配置完成，重启mariadb
|
1 |
systemctl restart mariadb |
之后进入MariaDB查看字符集
|
1 |
mysql> show variables like "%character%";show variables like "%collation%"; |
显示为 +————————--+—————————-+ | Variable_name […]
View Details