存档

文章标签 ‘mysql’

Mysql INNODB存储引擎表损坏修复方法(转)

2017年12月10日 没有评论

设你正在运行使用InnoDB表格的MySQL,糟糕的硬件设备,驱动程序错误,内核错误,不幸的电源故障或某些罕见的MySQL错误使你的InnoDB表空间被损坏了。
在这种情况下,InnoDB的一般会出现这样的输出:

InnoDB: Database page corruption on disk ora failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384bytes):
… 这里省略很多二进制和十六进制编码…
080703 23:46:16 InnoDB: Page checksum 587461377,prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form storedchecksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: Database page corruption on disk or a failed

mysqldump导出库时报错如下:
“mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `; at row:6880″
或是操作对应表时,也会报错。这时数据库会重启。
当时想到的是在修复之前保证数据库正常,不是这么异常的无休止的重启。

使用innodb_force_recovery =1,正如你所看到的,即使日志文件中有校验失败的记录,但CHECK TABLE还是说表格是正确的。
这意味着你不能太依赖CHECKTABLE在InnoDB上执行的结果。
所以就修改了配置文件的一个参数:innodb_force_recovery
innodb_force_recovery影响整个InnoDB存储引擎的恢复状况。默认为0,表示当需要恢复时执行所有的,
innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。
当设置参数值大于0后,可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
因为错误日志里面提示出现了坏页,导致数据库崩溃,所以这里把innodb_force_recovery 设置为1,忽略检查到的坏页。
重启数据库之后,找到错误信息出现的表

因为后者可以通过使用OPTIMIZE TABLE命令来修复,但这和更难以恢复的表格目录(table dictionary)被破坏的情况来说要好一些。

操作步骤:

修改my.cnf 配置文件,添加innodb_force_recovery = 1运行InnoDB,然后重启mysql 。

1,建议一个备份表,T_MinuteStore_BAK,使用MYISAM存储引擎,建表语句如下:

CREATE TABLE `T_MinuteStore_BAK` (
`T_MinuteStore_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘行标识’,
`siteID` int(11) DEFAULT NULL COMMENT ‘站点标识’,
`genTime` datetime DEFAULT NULL COMMENT ‘监测时间’,
`itemID` int(11) DEFAULT NULL COMMENT ‘监测项目’,
`value` varchar(255) DEFAULT NULL COMMENT ‘监测值’,
`flag` varchar(255) DEFAULT NULL COMMENT ‘异常标志’,
`filterFlag` bit(1) DEFAULT NULL COMMENT ‘过滤标志’,
PRIMARY KEY (`T_MinuteStore_ID`)
) ENGINE=MYISAM AUTO_INCREMENT=188995 DEFAULT CHARSET=utf8 COMMENT=’监测分钟数据–5分钟一组数据’;

2,把T_MinuteStore表中的数据导入到新表T_MinuteStore_BAK.

insert into T_MinuteStore_BAK select * from T_MinuteStore;

提示报错: 2013 (HY000): Lost connection TO MySQL server during query
你可能想对数据表进行扫描直到第一个被损坏的行,然后从MyISAM表中得到结果?
不幸的是,运行之后的T_MinuteStore_BAK表格是只有一部份数据。
这里查看T_MinuteStore_BAK表中,主键 T_MinuteStore_ID 从最小值到160861,可以看出是被顺序插入的数据。
那么现在就好办了,说明从16081之后,有损坏的数据行。
下面作下插入数据测试,看看大约有多少行数据损坏。
insert into T_MinuteStore_BAK select * from T_MinuteStore where T_MinuteStore_ID>161000;
执行命令后,还是报错。
最后测试;
161500160861
此主键之间的数据,有损坏,其它数据恢复正常。总计100万行数据,只丢了几百行数据,完全可以承受。
3, 删除掉原表: drop table T_MinuteStore;注释掉innodb_force_recovery 之后,重启Mysql。
4,重命名 T_MinuteStore_BAK:

rename table T_MinuteStore_BAK to T_MinuteStore;
5,最后将表T_MinuteStore修改回存储引擎:

alter table T_MinuteStore engine = innodb;

最后将数据库数据导出,测试导出成功,未发现问题。备份好数据,然后更换好硬盘,重装好Mysql ,将数据导入。
测试程序,一切正常。

分类: 解决方案 标签: , ,

解决在phpmyadmin中执行sql语句出现的错误:Unknown storage engine ‘InnoDB’

2016年10月3日 没有评论

问题:phpmyadmin报错——Unknown storage engine ‘InnoDB’

解决方法:解决方法:
1.关闭MySQL数据库
   2.修改my.ini文件,把skip-innodb这行注释掉
   3.打开MySQL数据库
当然把innodb改成MyISAM也行

原因:没有开启MySQL InnoDB存储引擎。

关于innodb引擎的资料:
事务型数据库的首选引擎,支持ACID事务,支持行级锁定。InnoDB是为处理巨大数据量时的最大性能设计。InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同,比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上。InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。

  简介   InnoDB 给 MySQL 提供了具有事务(transaction)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)、多版本并发控制(multi-versioned concurrency control)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行级锁(locking on row level),提供与 Oracle 类似的不加锁读取(non-locking read in SELECTs)。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation),因为 InnoDB 的行级锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。   在技术上,InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

MySQL

InnoDB 把数据和索引存放在表空间里,可能包含多个文件,这与其它的不一样,举例来说,在 MyISAM 中,表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小,可也可以每个表使用各自独立的表空间,只需要启用选项 innodb_file_per_table。   在 MySQL 的源代码中,从 3.23.34a 开始包含 InnoDB 表引擎,并在 MySQL -Max 的二进制版本中激活。

分类: Linux, 解决方案 标签: ,

利用mysql二进制日志进行mysql数据库的恢复(转)

2016年9月4日 没有评论

对于需要确保mysql数据库完整性的网站,mysql的二进制日志至关重要。比如你做了mysql数据库的主从备份,你认为这应该能确保mysql从数据库是最新的内容了,这的确没错,但有一种情况,比如你误删除了数据,或者被黑客入侵而清除了数据库,这时mysql从服务器也会跟主数据库同步,这是你不想看到的,但数据确实没了。但是如果你每天进行了数据库的完整备份,而且mysql的二进制日志还在的话,这将有可能恢复最新的数据。下面介绍方法。

1、开启二进制日志
在使用二进制日志恢复数据库时,请确保你已经开启二进制日志,检查my.cnf文件是否存在log-bin=mysql-bin。

2、定期完整备份
你还需要间隔一定的时间做数据库的完整备份,只有这样你才有可能快速的恢复最新数据。

3、查看备份时间
查看你备份的时间,如dedecmsv57gbksp1.sql,取最后一行:– Dump completed on 2012-11-08 11:11:48。

4、导出二进制日志
暂时关闭网站,防止新数据写入,导出二进制日志:

mysqlbinlog mysql-bin.000001 -d dedecmsv57gbksp1 –start-datetime=”2012-11-08 11:11:48″ >binlog.sql

5、调整binlog.sql
编辑binlog.sql,删除误操作的语句。

6、导入数据
重建数据库dedecmsv57gbksp1,导入dedecmsv57gbksp1.sql,接着导入binlog.sql。

MySQL删除数据库时的错误error dropping database

2016年1月8日 没有评论

由于在1月2号更新了下数据库,却出现了数据库已存在的提示信息。于是就进入命令行窗口drop database hkvps,可是却得到了ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./hkvps’, errno: 39)的错误信息。

解决方法:删除/var/lib/mysql/hkvps下面的所有文件,不要删除hkvps本身。然后再通过命令行drop database就可以了。

分类: 解决方案 标签: ,

mysql更改tmp路径

2016年1月5日 没有评论

因为mysql经常要把文件定入tmp目录,而tmp的目录为/tmp,为了提高性能,所以考虑更改tmpdir目录为/run/shm,即使用内存来存储。

1、打开/etc/mysql/my.cnf
更改tmpdir = /tmp为

tmpdir = /run/shm
2、更改/etc/apparmor.d/usr.sbin.mysqld添加:

/run/shm/* rw
3、重启mysql和apparmor

1
2
/etc/init.d/apparmor restart
/etc/init.d/mysql restart
分类: 解决方案 标签: ,

MySQL优化经验 key_buffer_size,query_cache_size,table_cache(转)

2015年12月10日 没有评论

同时在线访问量继续增大 对于1G内存的服务器明显感觉到吃力严重时甚至每天都会死机 或者时不时的服务器卡一下 这个问题曾经困扰了我半个多月MySQL使用是很具伸缩性的算法,因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。

安装好mysql后,配制文件应该在/usr/local/mysql/share/mysql目录中,配制文件有几个,有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境,当然需要有不同的配制文件了。

一般的情况下,my-medium.cnf这个配制文件就能满足我们的大多需要;一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了,使用mysqladmin variables extended-status –u root –p 可以看到目前的参数,有3个配置参数是最重要的,即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只对MyISAM表起作用,

key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。一般我们设为16M,实际上稍微大一点的站点 这个数字是远远不够的,通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。 或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql,以下的状态值都是本人通过phpmyadmin获得的实例分析:

这个服务器已经运行了20天

key_buffer_size – 128M
key_read_requests – 650759289
key_reads – 79112

比例接近1:8000 健康状况非常好

另外一个估计key_buffer_size的办法 把你网站数据库的每个表的索引所占空间大小加起来看看以此服务器为例:比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大。

从4.0.1开始,MySQL提供了查询缓冲机制。使用查询缓冲,MySQL将SELECT语句和查询结果存放在缓冲区中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。

通过调节以下几个参数可以知道query_cache_size设置得是否合理

Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks

Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲

我设置:

query_cache_size = 32M
query_cache_type= 1

得到如下状态值:

Qcache queries in cache 12737 表明目前缓存的条数
Qcache inserts 20649006
Qcache hits 79060095  看来重复查询率还挺高的
Qcache lowmem prunes 617913 有这么多次出现缓存过低的情况
Qcache not cached 189896   
Qcache free memory 18573912  目前剩余缓存空间
Qcache free blocks 5328 这个数字似乎有点大 碎片不少
Qcache total blocks 30953

如果内存允许32M应该要往上加点

table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

对于有1G内存的机器,推荐值是128-256。

笔者设置table_cache = 256

得到以下状态:

Open tables 256
Opened tables 9046

虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,已经运行了20天,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值 那就要考虑增大table_cache。

如果你不需要记录2进制log 就把这个功能关掉,注意关掉以后就不能恢复出问题前的数据了,需要您手动备份,二进制日志包含所有更新数据的语句,其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外,如果做同步复制( Replication )的话,也需要使用二进制日志传送修改情况。

log_bin指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字引,每次启动服务时,都会重新生成一个新的二进制文件。此外,使用log-bin-index可以指定索引文件;使用binlog-do-db可以指定记录的数据库;使用binlog-ignore-db可以指定不记录的数据库。注意的是:binlog-do-db和binlog-ignore-db一次只指定一个数据库,指定多个数据库需要多个语句。而且,MySQL会将所有的数据库名称改成小写,在指定数据库时必须全部使用小写名字,否则不会起作用。

关掉这个功能只需要在他前面加上#号

#log-bin

开启慢查询日志( slow query log ) 慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询,如果需要,还可以记录不使用索引的记录。下面是一个慢查询日志的例子:

开启慢查询日志,需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。

log_slow_queries指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值,缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的参数,它指示记录不使用索引的查询。笔者设置long_query_time=10

笔者设置:

sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
interactive_timeout=120
thread_concurrency = 4

参数说明:

back_log

要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。

max_connections

并发连接数目最大,120 超过这个值就会自动恢复,出了问题能自动解决

thread_cache

没找到具体说明,不过设置为32后 20天才创建了400多个线程 而以前一天就创建了上千个线程 所以还是有用的

thread_concurrency

#设置为你的cpu数目x2,例如,只有一个cpu,那么thread_concurrency=2
#有2个cpu,那么thread_concurrency=4
skip-innodb
#去掉innodb支持

代码:

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M – 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the “–help” option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
#socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
#socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
net_buffer_length = 16K
myisam_sort_buffer_size = 1M
max_connections=120
#addnew config
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
skip-innodb
skip-bdb
skip-name-resolve
join_buffer_size=512k
query_cache_size = 32M
interactive_timeout=120
long_query_time=10
log_slow_queries= /usr/local/mysql4/logs/slow_query.log
query_cache_type= 1
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 4

#end new config
# Don’t listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the “enable-named-pipe” option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin

# required unique id between 1 and 2^32 – 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) –
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , , by quoted strings and
# by the master’s port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST=’125.564.12.1′, MASTER_PORT=3306,
# MASTER_USER=’joe’, MASTER_PASSWORD=’secret’;
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables’ values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 – 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave – required
#master-host =
#
# The username the slave will use for authentication when connecting
# to the master – required
#master-user =
#
# The password the slave will authenticate with when connecting to
# the master – required
#master-password =
#
# The port the master is listening on.
# optional – defaults to 3306
#master-port =
#
# binary logging – not required for slaves, but recommended
#log-bin

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 – 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

补充

优化table_cachetable_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。对于有1G内存的机器,推荐值是128-256。

案例1:该案例来自一个不是特别繁忙的服务器table_cache – 512open_tables – 103opened_tables – 1273uptime – 4021421 (measured in seconds)该案例中table_cache似乎设置得太高了。在峰值时间,打开表的数目比table_cache要少得多。

案例2:该案例来自一台开发服务器。table_cache – 64open_tables – 64opened-tables – 431uptime – 1662790 (measured in seconds)虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。案例3:该案例来自一个upderperforming的服务器table_cache – 64open_tables – 64opened_tables – 22423uptime – 19538该案例中table_cache设置得太低了。虽然运行时间不到6小时,open_tables达到了最大值,opened_tables的值也非常高。这样就需要增加table_cache的值。优化key_buffer_sizekey_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。对于1G内存的机器,如果不使用MyISAM表,推荐值是16M(8-64M)。

案例1:健康状况key_buffer_size – 402649088 (384M)key_read_requests – 597579931key_reads – 56188案例2:警报状态key_buffer_size – 16777216 (16M)key_read_requests – 597579931key_reads – 53832731案例1中比例低于1:10000,是健康的情况;案例2中比例达到1:11,警报已经拉响。

mysql服务性能优化—my.cnf配置说明详解(16G内存)

2015年12月3日 没有评论

MYSQL服务器my.cnf配置文档详解
硬件:内存16G

[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit = 10240

back_log = 600
#在MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。

max_connections = 3000
#MySQL允许最大的进程连接数,如果经常出现Too Many Connections的错误提示,则需要增大此值。

max_connect_errors = 6000
#设置每个主机的连接请求异常中断的最大次数,当超过该次数,MYSQL服务器将禁止host的连接请求,直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。

table_cache = 614
#指示表调整缓冲区大小。# table_cache 参数设置表高速缓存的数目。每个连接进来,都会至少打开一个表缓存。#因此, table_cache 的大小应与 max_connections 的设置有关。例如,对于 200 个#并行运行的连接,应该让表的缓存至少有 200 × N ,这里 N 是应用可以执行的查询#的一个联接中表的最大数量。此外,还需要为临时表和文件保留一些额外的文件描述符。
# 当 Mysql 访问一个表时,如果该表在缓存中已经被打开,则可以直接访问缓存;如果#还没有被缓存,但是在 Mysql 表缓冲区中还有空间,那么这个表就被打开并放入表缓#冲区;如果表缓存满了,则会按照一定的规则将当前未用的表释放,或者临时扩大表缓存来存放,使用表缓存的好处是可以更快速地访问表中的内容。执行 flush tables 会#清空缓存的内容。一般来说,可以通过查看数据库运行峰值时间的状态值 Open_tables #和 Opened_tables ,判断是否需要增加 table_cache 的值(其中 open_tables 是当#前打开的表的数量, Opened_tables 则是已经打开的表的数量)。即如果open_tables接近table_cache的时候,并且Opened_tables这个值在逐步增加,那就要考虑增加这个#值的大小了。还有就是Table_locks_waited比较高的时候,也需要增加table_cache。

external-locking = FALSE
#使用–skip-external-locking MySQL选项以避免外部锁定。该选项默认开启

max_allowed_packet = 32M
#设置在网络传输中一次消息传输量的最大值。系统默认值 为1MB,最大值是1GB,必须设置1024的倍数。

sort_buffer_size = 2M
# Sort_Buffer_Size 是一个connection级参数,在每个connection(session)第一次需要使用这个buffer的时候,一次性分配设置的内存。
#Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。例如:500个连接将会消耗 500*sort_buffer_size(8M)=4G内存
#Sort_Buffer_Size 超过2KB的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。
#技术导读 http://blog.webshuo.com/2011/02/16/mysql-sort_buffer_size/
#dev-doc: http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html
#explain select*from table where order limit;出现filesort
#属重点优化参数

join_buffer_size = 2M
#用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

thread_cache_size = 300
# 服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。设置规则如下:1GB 内存配置为8,2GB配置为16,3GB配置为32,4GB或更高内存,可配置更大。

thread_concurrency = 8
# 设置thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那么thread_concurrency的应该为4; 2个双核的cpu, thread_concurrency的值应为8
#属重点优化参数

query_cache_size = 64M
## 对于使用MySQL的用户,对于这个变量大家一定不会陌生。前几年的MyISAM引擎优化中,这个参数也是一个重要的优化参数。但随着发展,这个参数也爆露出来一些问题。机器的内存越来越大,人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一下 query_cache_size的工作原理:一个SELECT查询在DB中工作后,DB会把该语句缓存下来,当同样的一个SQL再次来到DB里调用时,DB在该表没发生变化的情况下把结果从缓存中返回给Client。这里有一个关建点,就是DB在利用Query_cache工作时,要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时,Query_cache里的数据又怎么处理呢?首先要把Query_cache和该表相关的语句全部置为失效,然后在写入更新。那么如果Query_cache非常大,该表的查询结构又比较多,查询语句失效也慢,一个更新或是Insert就会很慢,这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统,该参数不适合分配过大。而且在高并发,写入量大的系统,建议把该功能禁掉。
#重点优化参数(主库 增删改-MyISAM)

query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小,缺省为1M

query_cache_min_res_unit = 2k
#默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费
#查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
#如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。
#查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小,要不就是碎片太多。
#查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

default-storage-engine = MyISAM
#default_table_type = InnoDB

thread_stack = 192K
#设置MYSQL每个线程的堆栈大小,默认值足够大,可满足普通操作。可设置范围为128K至4GB,默认为192KB。

transaction_isolation = READ-COMMITTED
# 设定默认的事务隔离级别.可用的级别如下:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED-读未提交2.READ COMMITTE-读已提交3.REPEATABLE READ -可重复读4.SERIALIZABLE -串行

tmp_table_size = 256M
# tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增加 tmp_table_size 值。如果超过该值,则会将临时表写入磁盘。
max_heap_table_size = 256M
long_query_time = 2
log_long_format
log-slow-queries=/data/3306/slow-log.log
#log-bin = /data/3306/mysql-bin
log-bin
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M

expire_logs_days = 7
key_buffer_size = 2048M
#批定用于索引的缓冲区大小,增加它可以得到更好的索引处理性能,对于内存在4GB左右的服务器来说,该参数可设置为256MB或384MB。

read_buffer_size = 1M
# MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

read_rnd_buffer_size = 16M
# MySql的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。

bulk_insert_buffer_size = 64M
#批量插入数据缓存大小,可以有效提高插入效率,默认为8M

myisam_sort_buffer_size = 128M
# MyISAM表发生变化时重新排序所需的缓冲

myisam_max_sort_file_size = 10G
# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).
# 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)

myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
# 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.
# 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.

myisam_recover
#自动检查和修复没有适当关闭的 MyISAM 表
skip-name-resolve
lower_case_table_names = 1

server-id = 1

innodb_additional_mem_pool_size = 16M
#这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小,类似于Oracle的library cache。这不是一个强制参数,可以被突破。

innodb_buffer_pool_size = 2048M
# 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大,并且不会暴增,那么无需把 innodb_buffer_pool_size 设置的太大了

innodb_data_file_path = ibdata1:1024M:autoextend
#表空间文件 重要数据

innodb_file_io_threads = 4
#文件IO的线程数,一般为 4,但是在 Windows 下,可以设置得较大。

innodb_thread_concurrency = 8
#服务器有几个CPU就设置为几,建议用默认设置,一般为8.

innodb_flush_log_at_trx_commit = 2
# 如果将此参数设置为1,将在每次提交事务后将日志写入磁盘。为提供性能,可以设置为0或2,但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写入日志文件,而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志,但日志文件每次刷新到磁盘一次。

innodb_log_buffer_size = 16M
#此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据.MySQL开发人员建议设置为1-8M之间

innodb_log_file_size = 128M
#此参数确定数据日志文件的大小,以M为单位,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间

innodb_log_files_in_group = 3
#为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3M

innodb_max_dirty_pages_pct = 90
#推荐阅读 http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html
# Buffer_Pool中Dirty_Page所占的数量,直接影响InnoDB的关闭时间。参数innodb_max_dirty_pages_pct 可以直接控制了Dirty_Page在Buffer_Pool中所占的比率,而且幸运的是innodb_max_dirty_pages_pct是可以动态改变的。所以,在关闭InnoDB之前先将innodb_max_dirty_pages_pct调小,强制数据块Flush一段时间,则能够大大缩短 MySQL关闭的时间。

innodb_lock_wait_timeout = 120
# InnoDB 有其内置的死锁检测机制,能导致未完成的事务回滚。但是,如果结合InnoDB使用MyISAM的lock tables 语句或第三方事务引擎,则InnoDB无法识别死锁。为消除这种可能性,可以将innodb_lock_wait_timeout设置为一个整数值,指示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)

innodb_file_per_table = 0
#独享表空间(关闭)

[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
log-error=/data/3306/mysql_oldboy.err
pid-file=/data/3306/mysqld.pid

#补充
#wait_timeout = 10
#指定一个请求的最大连接时间,对于4GB左右的内存服务器来说,可以将其设置为5-10。
#skip_networking
#开启该选可以彻底关闭MYSQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MYSQL数据库服务器的,则不要开启该选项,否则将无法正常连接。

#log-queries-not-using-indexes
将没有使用索引的查询也记录下来

双机高可用、负载均衡、MySQL(读写分离、主从自动切换)架构设计(转)

2015年8月5日 没有评论

架构简介

前几天网友来信说帮忙实现这样一个架构:只有两台机器,需要实现其中一台死机之后另一台能接管这台机器的服务,并且在两台机器正常服务时,两台机器都能用上。于是设计了如下的架构。

此架构主要是由keepalived实现双机高可用,维护了一个外网VIP,一个内网VIP。正常情况时,外网VIP和内网VIP都绑定在server1服务器,web请求发送到server1的nginx,nginx对于静态资源请求就直接在本机检索并返回,对于php的动态请求,则负载均衡到server1和server2。对于SQL请求,会将此类请求发送到Atlas MySQL中间件,Atlas接收到请求之后,把涉及写操作的请求发送到内网VIP,读请求操作发送到mysql从,这样就实现了读写分离。

当主服务器server1宕机时,keepalived检测到后,立即把外网VIP和内网VIP绑定到server2,并把server2的mysql切换成主库。此时由于外网VIP已经转移到了server2,web请求将发送给server2的nginx。nginx检测到server1宕机,不再把请求转发到server1的php-fpm。之后的sql请求照常发送给本地的atlas,atlas把写操作发送给内网VIP,读操作发送给mysql从,由于内网VIP已经绑定到server2了,server2的mysql同时接受写操作和读操作。

当主服务器server1恢复后,server1的mysql自动设置为从,与server2的mysql主同步。keepalived不抢占server2的VIP,继续正常服务。

架构要求

要实现此架构,需要三个条件:
1、服务器可以设置内网IP,并且设置的内网IP互通;
2、服务器可以随意绑定IDC分配给我们使用的外网IP,即外网IP没有绑定MAC地址;
3、MySQL服务器支持GTID,即MySQL-5.6.5以上版本。

环境说明

server1
eth0: 10.96.153.110(对外IP)
eth1: 192.168.1.100(对内IP)
server2
eth0: 10.96.153.114(对外IP)
eth1: 192.168.1.101(对内IP)
系统都是CentOS-6。

对外VIP: 10.96.153.239
对内VIP: 192.168.1.150

hosts设置

/etc/hosts:
192.168.1.100 server1
192.168.1.101 server2

Nginx PHP MySQL Memcached安装

这几个软件的安装推荐使用EZHTTP来完成。

解决session共享问题

php默认的session存储是在/tmp目录下,现在我们是用两台服务器作php请求的负载,这样会造成session分布在两台服务器的/tmp目录下,导致依赖于session的功能不正常。我们可以使用memcached来解决此问题。
上一步我们已经安装好了memcached,现在只需要配置php.ini来使用memcached,配置如下,打开php.ini配置文件,修改为如下两行的值:
session.save_handler = memcache
session.save_path = “tcp://192.168.1.100:11211,tcp://192.168.1.101:11211″
之后重启php-fpm生效。

Nginx配置

Server1配置
http {
[...]
upstream php-server {
server 192.168.1.101:9000;
server 127.0.0.1:9000;
keepalive 100;
}
[...]
server {
[...]
location ~ \.php$ {
fastcgi_pass php-server;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
[...]
}
[...]
}

Server2配置
http {
[...]
upstream php-server {
server 192.168.1.100:9000;
server 127.0.0.1:9000;
keepalive 100;
}
[...]
server {
[...]
location ~ \.php$ {
fastcgi_pass php-server;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
[...]
}
[...]
}
这两个配置主要的作用是设置php请求的负载均衡。

MySQL配置

mysql util安装
我们需要安装mysql util里的主从配置工具来实现主从切换。
cd /tmp
wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.3.tar.gz
tar xzf mysql-utilities-1.5.3.tar.gz
cd mysql-utilities-1.5.3
python setup.py build
python setup.py install

mysql my.cnf配置
server1:
[mysql]
[...]
protocol=tcp
[...]
[...]
[mysqld]
[...]
# BINARY LOGGING #
log-bin = /usr/local/mysql/data/mysql-bin
expire-logs-days = 14
binlog-format= row
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency =true
master-info-repository=TABLE
relay-log-info-repository=TABLE
server-id=1
report-host=server1
report-port=3306
[...]
server2:
[mysql]
[...]
protocol=tcp
[...]
[mysqld]
[...]
# BINARY LOGGING #
log-bin = /usr/local/mysql/data/mysql-bin
expire-logs-days = 14
binlog-format= row
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency =true
master-info-repository=TABLE
relay-log-info-repository=TABLE
server-id=2
report-host=server2
report-port=3306
[...]
这两个配置主要是设置了binlog和启用gtid-mode,并且需要设置不同的server-id和report-host。

开放root帐号远程权限
我们需要在两台mysql服务器设置root帐号远程访问权限。
mysql> grant all on *.* to ‘root’@’192.168.1.%’ identified by ‘Xp29at5F37′ with grant option;
mysql> grant all on *.* to ‘root’@'server1′ identified by ‘Xp29at5F37′ with grant option;
mysql> grant all on *.* to ‘root’@'server2′ identified by ‘Xp29at5F37′ with grant option;
mysql> flush privileges;

设置mysql主从
在任意一台执行如下命令:
mysqlreplicate –master=root:Xp29at5F37@server1:3306 –slave=root:Xp29at5F37@server2:3306 –rpl-user=rpl:o67DhtaW
# master on server1: … connected.
# slave on server2: … connected.
# Checking for binary logging on master…
# Setting up replication…
# …done.

显示主从关系
mysqlrplshow –master=root:Xp29at5F37@server1 –discover-slaves-login=root:Xp29at5F37
# master on server1: … connected.
# Finding slaves for master: server1:3306
# Replication Topology Graph
server1:3306 (MASTER)
|
+— server2:3306 – (SLAVE)

检查主从状态
mysqlrplcheck –master=root:Xp29at5F37@server1 –slave=root:Xp29at5F37@server2
# master on server1: … connected.
# slave on server2: … connected.
Test Description Status
—————————————————————————
Checking for binary logging on master [pass]
Are there binlog exceptions? [pass]
Replication user exists? [pass]
Checking server_id values [pass]
Checking server_uuid values [pass]
Is slave connected to master? [pass]
Check master information file [pass]
Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]
Checking slave delay (seconds behind master) [pass]
# …done.

Keepalived配置

keepalived安装(两台都装)
yum -y install keepalived
chkconfig keepalived on

keepalived配置(server1)
vi /etc/keepalived/keepalived.conf
vrrp_sync_group VG_1 {
group {
inside_network
outside_network
}
}

vrrp_instance inside_network {
state BACKUP
interface eth1
virtual_router_id 51
priority 101
advert_int 1
authentication {
auth_type PASS
auth_pass 3489
}
virtual_ipaddress {
192.168.1.150/24
}
nopreempt
notify /data/sh/mysqlfailover-server1.sh
}

vrrp_instance outside_network {
state BACKUP
interface eth0
virtual_router_id 50
priority 101
advert_int 1
authentication {
auth_type PASS
auth_pass 3489
}
virtual_ipaddress {
10.96.153.239/24
}
nopreempt
}

keepalived配置(server2)
vrrp_sync_group VG_1 {
group {
inside_network
outside_network
}
}

vrrp_instance inside_network {
state BACKUP
interface eth1
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 3489
}
virtual_ipaddress {
192.168.1.150
}
notify /data/sh/mysqlfailover-server2.sh
}

vrrp_instance outside_network {
state BACKUP
interface eth0
virtual_router_id 50
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 3489
}
virtual_ipaddress {
10.96.153.239/24
}
}
此keepalived配置需要注意的是:
1、两台server的state都设置为backup,server1增加nopreempt配置,并且server1 priority比server2高,这样用来实现当server1从宕机恢复时,不抢占VIP;
2、server1设置notify /data/sh/mysqlfailover-server1.sh,server2设置notify /data/sh/mysqlfailover-server2.sh,作用是自动切换主从
/data/sh/mysqlfailover-server1.sh脚本内容:
#!/bin/bash

sleep 10
state=$3
result=`mysql -h127.0.0.1 -P3308 -uroot -pXp29at5F37 -e ‘show slave status;’`
[[ "$result" == "" ]] && mysqlState=”master” || mysqlState=”slave”

if [[ "$state" == "MASTER" ]];then
if [[ "$mysqlState" == "slave" ]];then
mysqlrpladmin –slave=root:Xp29at5F37@server1:3308 failover
fi

elif [[ "$state" == "BACKUP" ]];then
if [[ "$mysqlState" == "master" ]];then
mysqlreplicate –master=root:Xp29at5F37@server2:3308 –slave=root:Xp29at5F37@server1:3308 –rpl-user=rpl:o67DhtaW
fi
fi

sed -i ‘s/proxy-read-only-backend-addresses.*/proxy-read-only-backend-addresses = 192.168.1.150:3308/’ /usr/local/mysql-proxy/conf/my.cnf
mysql -h127.0.0.1 -P2345 -uuser -ppwd -e “REMOVE BACKEND 2;”
/data/sh/mysqlfailover-server2.sh脚本内容:
#!/bin/bash

sleep 10
state=$3
result=`mysql -h127.0.0.1 -P3308 -uroot -pXp29at5F37 -e ‘show slave status;’`
[[ "$result" == "" ]] && mysqlState=”master” || mysqlState=”slave”

if [[ "$state" == "MASTER" ]];then
if [[ "$mysqlState" == "slave" ]];then
mysqlrpladmin –slave=root:Xp29at5F37@server2:3308 failover
fi

elif [[ "$state" == "BACKUP" ]];then
if [[ "$mysqlState" == "master" ]];then
mysqlreplicate –master=root:Xp29at5F37@server1:3308 –slave=root:Xp29at5F37@server2:3308 –rpl-user=rpl:o67DhtaW
fi
fi

sed -i ‘s/proxy-read-only-backend-addresses.*/proxy-read-only-backend-addresses = 192.168.1.150:3308/’ /usr/local/mysql-proxy/conf/my.cnf
mysql -h127.0.0.1 -P2345 -uuser -ppwd -e “REMOVE BACKEND 2;”

Atlas设置

atlas安装
到这里下载最新版本,https://github.com/Qihoo360/Atlas/releases
cd /tmp
wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
rpm -i Atlas-2.2.1.el6.x86_64.rpm

atlas配置
cd /usr/local/mysql-proxy/conf
cp test.cnf my.cnf
vi my.cnf
调整如下参数,
proxy-backend-addresses = 192.168.1.150:3306
proxy-read-only-backend-addresses = 192.168.1.101:3306
pwds = root:qtyU1btXOo074Itvx0UR9Q==
event-threads = 8
注意:
proxy-backend-addresse设置为内网VIP
proxy-read-only-backend-addresses设置为server2的IP
root:qtyU1btXOo074Itvx0UR9Q==设置数据库的用户和密码,密码是通过/usr/local/mysql-proxy/bin/encrypt Xp29at5F37生成。
更详细参数解释请查看,Atlas配置详解。

启动atlas
/usr/local/mysql-proxy/bin/mysql-proxy –defaults-file=/usr/local/mysql-proxy/conf/my.cnf
之后程序里配置mysql就配置127.0.0.1:1234就好。

部署atlas自动维护脚本
在两台机器都部署此脚本,并添加定时任务(如每2分钟运行一次)我们把脚本放在/data/sh/auto_maintain_atlas.sh,脚本内容为:
#!/bin/bash

count=`mysql -N -h127.0.0.1 -P2345 -uuser -ppwd -e “select * from backends;” | wc -l`

if [[ "$count" == "1" ]];then
result=`mysql -hserver1 -P3308 -uroot -pXp29at5F37 -e ‘show slave status\G’`
if echo “$result” | grep Slave_IO_State;then
slaveIP=192.168.1.100
else
result=`mysql -hserver2 -P3308 -uroot -pXp29at5F37 -e ‘show slave status\G’`
slaveIP=192.168.1.101
fi

slaveIORunning=`echo “$result” | awk -F’:’ ‘/Slave_IO_Running:/{print $2}’`
slaveSQLRunning=`echo “$result” | awk -F’:’ ‘/Slave_SQL_Running:/{print $2}’`
SlaveSQLRunning_State=`echo “$result” | awk -F’:’ ‘/Slave_SQL_Running_State:/{print $2}’`
if [[ "$slaveIORunning" =~ "Yes" && "$slaveSQLRunning" =~ "Yes" && "$SlaveSQLRunning_State" =~ "Slave has read all relay log" ]];then
mysql -h127.0.0.1 -P2345 -uuser -ppwd -e “add slave ${slaveIP}:3308;”
fi
fi
为什么需要这个脚本呢?假设目前mysql主服务器在s1,s1宕机后,s2接管VIP,接着删除atlas中设置的slave backend,其mysql提升为主。过一段时间后,s1从宕机中恢复,这时候s1的mysql自动切换为从,接着删除atlas中设置的slave backend,开始连接s2的mysql主同步数据。到这个时候我们发现,已经不存在读写分离了,所有的sql都发送给了s2的mysql。auto_maintain_atlas.sh脚本就派上用场了,此脚本会定时的检查主从是否已经同步完成,如果完成就自动增加slave backend,这样读写分离又恢复了,完全不需要人工干预。

server1主宕机测试

测试keepalived是否工作正常
我们来模拟server1宕机。
在server1上执行shutdown关机命令。
此时我们登录server2,执行ip addr命令,输出如下:
1: lo: mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:81:9d:42 brd ff:ff:ff:ff:ff:ff
inet 10.96.153.114/24 brd 10.96.153.255 scope global eth0
inet 10.96.153.239/24 scope global secondary eth0
inet6 fe80::20c:29ff:fe81:9d42/64 scope link
valid_lft forever preferred_lft forever
3: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:81:9d:4c brd ff:ff:ff:ff:ff:ff
inet 192.168.1.101/24 brd 192.168.1.255 scope global eth1
inet 192.168.1.150/32 scope global eth1
inet6 fe80::20c:29ff:fe81:9d4c/64 scope link
valid_lft forever preferred_lft forever
我们看到对外VIP 10.96.153.239和对内IP 192.168.1.150已经转移到server2了,证明keepalived运行正常。

测试是否自动切换了主从
登录server2的mysql服务器,执行show slave status;命令,如下:
mysql> show slave status\G
Empty set (0.00 sec)
我们发现从状态已经为空,证明已经切换为主了。

测试server1是否抢占VIP
为什么要测试这个呢?如果server1恢复之后抢占了VIP,而我们的Atlas里后端设置的是VIP,这样server1启动之后,sql的写操作就会向server1的mysql发送,而server1的mysql数据是旧于server2的,所以这样会造成数据不一致,这个是非常重要的测试。
我们先来启动server1,之后执行ip addr,输出如下:
1: lo: mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:f1:4f:4e brd ff:ff:ff:ff:ff:ff
inet 10.96.153.110/24 brd 10.96.153.255 scope global eth0
inet6 fe80::20c:29ff:fef1:4f4e/64 scope link
valid_lft forever preferred_lft forever
3: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:f1:4f:58 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.100/24 brd 192.168.1.255 scope global eth1
inet6 fe80::20c:29ff:fef1:4f58/64 scope link
valid_lft forever preferred_lft forever
我们看到,server1并没有抢占VIP,测试正常。不过另人郁闷的是,在虚拟机的环境并没有测试成功,不知道为什么。

测试server2的atlas是否已经删除slave backend
我们测试这个是为了保证atlas已经没有slave backend,也就是没有从库的设置了,否则当server1恢复时,有可能会把读请求发送给server1的mysql,造成读取了旧数据的问题。

[root@server1 ~]# mysql -h127.0.0.1 -P2345 -uuser -ppwd
mysql> select * from backends;
+————-+——————–+——-+——+
| backend_ndx | address | state | type |
+————-+——————–+——-+——+
| 1 | 192.168.1.150:3308 | up | rw |
+————-+——————–+——-+——+
1 rows in set (0.00 sec)
如果看到只有一个后端,证明运作正常。

测试server1 mysql是否设置为从
serve1恢复后,登录server1的mysql服务器,执行show slave status;命令,如下:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Opening tables
Master_Host: server1
Master_User: rpl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 48405991
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: yes

测试是否自动恢复读写分离
server1恢复后一段时间,我们可以看是读写分离是否已经恢复。

[root@server1 ~]# mysql -h127.0.0.1 -P2345 -uuser -ppwd
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select * from backends;
+————-+——————–+——-+——+
| backend_ndx | address | state | type |
+————-+——————–+——-+——+
| 1 | 192.168.1.150:3308 | up | rw |
| 2 | 192.168.1.100:3308 | up | ro |
+————-+——————–+——-+——+
2 rows in set (0.00 sec)

我们看到server1已经被添加为slave backend了。这表示已经成功恢复读写分离。

XAMPP php版本历史

2015年1月18日 没有评论

XAMPP版本历史如图

http://code.stephenmorley.org/articles/xampp-version-history-apache-mysql-php/

XAMPP Apache MySQL PHP 5 PHP 4
1.8.1 2.4.3 5.5.27 5.4.7
1.8.0 2.4.2 5.5.25a 5.4.4
1.7.7 2.2.21 5.5.16 5.3.8
1.7.5 2.2.21 5.5.15 5.3.8
1.7.4 2.2.17 5.5.8 5.3.5
1.7.3 2.2.14 5.1.41 5.3.1
1.7.2 2.2.12 5.1.37 5.3.0
1.7.1 2.2.11 5.1.33 5.2.9
1.7.0 2.2.11 5.1.30 5.2.8
1.6.8 2.2.9 5.0.67 5.2.6 4.4.9
1.6.7 2.2.9 5.0.51b 5.2.6 4.4.8
1.6.6a 2.2.8 5.0.51a 5.2.5 4.4.8
1.6.6 2.2.8 5.0.51 5.2.5 4.4.8 (RC2)
1.6.5 2.2.6 5.0.51 5.2.5 4.4.7
1.6.4 2.2.6 5.0.45 5.2.4 4.4.7
1.6.3a 2.2.4 5.0.45 5.2.3 4.4.7
1.6.3 2.2.4 5.0.54 5.2.3 4.4.7
1.6.2 2.2.4 5.0.41 5.2.2 4.4.7
1.6.1 2.2.4 5.0.37 5.2.1 4.4.6
1.6.0a 2.2.4 5.0.33 5.2.1 4.4.5
1.6.0 2.2.3 5.0.33 5.2.1 4.4.5

分类: Linux 标签: , , ,

Windows下的PHP安装文件线程安全和非线程安全的区别

2014年11月25日 没有评论

从2000年10月20日发布的第一个Windows版的PHP3.0.17开始的都是线程安全的版本,这是由于与Linux/Unix系统是采用 多进程的工作方式不同的是Windows系统是采用多线程的工作方式。如果在IIS下以CGI方式运行PHP会非常慢,这是由于CGI模式是建立在多进程 的基础之上的,而非多线程。一般我们会把PHP配置成以ISAPI的方式来运行,ISAPI是多线程的方式,这样就快多了。但存在一个问题,很多常用的 PHP扩展是以Linux/Unix的多进程思想来开发的,这些扩展在ISAPI的方式运行时就会出错搞垮IIS。而用线程安全版本的话顶多只是搞跨某个 线程,而不会影响到整个IIS的安全。

当然在IIS下CGI模式才是 PHP运行的最安全方式,但CGI模式对于每个HTTP请求都需要重新加载和卸载整个PHP环境,其消耗是巨大的。为了兼顾IIS下PHP的效率和安全, 有人给出了FastCGI的解决方案。FastCGI可以让PHP的进程重复利用而不是每一个新的请求就重开一个进程。同时FastCGI也可以允许几个 进程同时执行。这样既解决了CGI进程模式消耗太大的问题,又利用上了CGI进程模式不存在线程安全问题的优势。

因此,如果是使用ISAPI的方式来运行PHP就必须用Thread Safe(线程安全)的版本;而用FastCGI模式运行PHP的话就没有必要用线程安全检查了,用None Thread Safe(NTS,非线程安全)的版本能够更好的提高效率。
因此,如果是使用ISAPI的方式来运行PHP就必须用Thread Safe(线程安全)的版本;而用FastCGI模式运行PHP的话就没有必要用线程安全检查了,用None Thread Safe(NTS,非线程安全)的版本能够更好的提高效率。
附:德问相关问题摘录
下载PHP安装文件时,我看到有两种不同的二进制文件,像是非线程安全(Non Thread Safe)和线程安全(Thread Safe),比如该页面所列:http://windows.php.net/download/。这个是什么意思,之间有什么区别?

这个主要是针对web server 而言,在windows环境下,如果你使用的web server 是apchae 或者 iis 7以下版本,则应该选择线程安全的安装文件,而如果你使用Fast-cgi模式时,可以选择非线程安全,因为 web sever 本身能保证线程安全。
当然还有二进制文件编译时所使用的编译器:vc9 (vs系列) vc6(gcc)

如楼上所言,是针对web server的,部分web server在处理应用请求的时候是用多线程而非多进程的方式处理,线程方式因为涉及到共享寄存器和内存,所以很容易出错,这个时候程序就需要花一些额外的经历去处理寄存器中的数据一致性,即保证线程安全。
所以是否采用线程安全主要看你的web server所采用的PHP请求处理方式,如果是多线程处理,那么请选择线程安全的,否则选择非线程安全的,如楼上所说Fast-cgi方式可选择非线程安全的