MySQL5.1复制参数binlog_format (转)

本文转自:http://lincolnhuang.wordpress.com/2011/05/05/mysql5-1%E5%A4%8D%E5%88%B6%E5%8F%82%E6%95%B0binlog_format/ 感谢作者!

一、简介
MySQL 5.1 中,在复制方面的改进就是引进了新的复制技术:基于行的复制。
简言之,这种新技术就是关注表中发生变化的记录,而非以前的照抄 binlog 模式。
从 MySQL 5.1.12 开始,可以用以下三种模式来实现:
基于SQL语句的复制(statement-based replication, SBR)
基于行的复制(row-based replication, RBR)
混合模式复制(mixed-based replication, MBR)。
相应地,binlog的格式也有三种:STATEMENT,ROW,MIXED。
SBR 模式是默认的。

在运行时可以动态低改变binlog的格式,除了以下几种情况:
1. 存储过程或者触发器中间
2. 启用了NDB
3. 当前会话试用 RBR 模式,并且已打开了临时表

如果binlog采用了 MIXED 模式,那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式。
1. 当DML语句更新一个NDB表时
2. 当函数中包含 UUID() 时
3. 2个及以上包含 AUTO_INCREMENT 字段的表被更新时
4. 行任何 INSERT DELAYED 语句时
5. 用 UDF 时
6. 视图中必须要求使用 RBR 时,例如创建视图是使用了 UUID() 函数

二、设置方法
设定主从复制模式的方法非常简单,只要在以前设定复制配置的基础上,再加一个参数:
binlog_format=”STATEMENT”
binlog_format=”ROW”
binlog_format=”MIXED”

当然了,也可以在运行时动态修改binlog的格式。例如:
mysql> SET SESSION binlog_format = ‘STATEMENT’;
mysql> SET SESSION binlog_format = ‘ROW’;
mysql> SET SESSION binlog_format = ‘MIXED’;

mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
mysql> SET GLOBAL binlog_format = ‘ROW’;
mysql> SET GLOBAL binlog_format = ‘MIXED’;

三、优缺点
现在来比较以下 SBR 和 RBR 两种模式各自的优缺点
SBR 的优点:
1. 历史悠久,技术成熟
2. binlog文件较小
3. binlog中包含了所有数据库更改信息,可以据此来审核数据库的安全等情况
4. binlog可以用于实时的还原,而不仅仅用于复制
5. 主从版本可以不一样,从服务器版本可以比主服务器版本高

SBR 的缺点:
1. 不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候。
2. 调用具有不确定因素的 UDF 时复制也可能出问题
3. 使用以下函数的语句也无法被复制:
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)
4. INSERT … SELECT 会产生比 RBR 更多的行级锁
5. 复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时,需要比 RBR 请求更多的行级锁
6. 对于有 AUTO_INCREMENT 字段的 InnoDB表而言,INSERT 语句会阻塞其他 INSERT 语句
7. 对于一些复杂的语句,在从服务器上的耗资源情况会更严重,而 RBR 模式下,只会对那个发生变化的记录产生影响
8. 存储函数(不是存储过程)在被调用的同时也会执行一次 NOW() 函数,这个可以说是坏事也可能是好事
9. 确定了的 UDF 也需要在从服务器上执行
10. 数据表必须几乎和主服务器保持一致才行,否则可能会导致复制出错
11. 执行复杂语句如果出错的话,会消耗更多资源

RBR 的优点:
1. 任何情况都可以被复制,这对复制来说是最安全可靠的
2. 和其他大多数数据库系统的复制技术一样
3. 多数情况下,从服务器上的表如果有主键的话,复制就会快了很多
4. 复制以下几种语句时的行锁更少:
* INSERT … SELECT
* 包含 AUTO_INCREMENT 字段的 INSERT
* 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
5. 执行 INSERT,UPDATE,DELETE 语句时锁更少
6. 从服务器上采用多线程来执行复制成为可能

RBR 的缺点:
1. binlog 大了很多
2. 复杂的回滚时 binlog 中会包含大量的数据
3. 主服务器上执行 UPDATE 语句时,所有发生变化的记录都会写到 binlog 中,而 SBR 只会写一次,这会导致频繁发生binlog 的并发写问题
4. UDF 产生的大 BLOB 值会导致复制变慢
5. 无法从 binlog 中看到都复制了写什么语句
6. 当在非事务表上执行一段堆积的SQL语句时,最好采用 SBR 模式,否则很容易导致主从服务器的数据不一致情况发生

另外,针对系统库 mysql 里面的表发生变化时的处理规则如下:
1. 如果是采用 INSERT,UPDATE,DELETE 直接操作表的情况,则日志格式根据 binlog_format 的设定而记录
2. 如果是采用 GRANT,REVOKE,SET PASSWORD 等管理语句来做的话,那么无论如何都采用 SBR 模式记录
注:采用 RBR 模式后,能解决很多原先出现的主键重复问题

innodb_log_file_size参数调整要慎之又慎!!!

如果innodb_log_file_size以前是256M,现在要调整到512M,那么更改配置后,你将无法启动mysql,这个参数调整特别是有数据时需要慎之又慎!!!发这篇个文章主要是为了提醒我自己,以前吃过这方面的亏,还好当时是测试服务器。

 

那万一碰到后怎么办呢?

先改回去试试,能成功启动的话再导出数据做备份。再:

要STOP服务先,然后再删除原来的文件………
打开/var/lib/mysql
删除ib_logfile0, ib_logfile1……..ib_logfilen
再开启选项,成功启动。

mysql 5.1.57主备配置文件备忘

mysql 5.1跟mysql 5.0有很多配置参数已经更改了,甚至最新的mysql 5.1.57和早一些的mysql 5.1,比如mysql 5.1.1x之类的,都有不少配置参数的更改,真是搞不懂为什么?其实实现的功能基本是一致的。

贴上最近做的一个Mysql 5.1.57主备的安装和配置在这里,备忘:

我这两台机器的内存都很大,每台48G。不过目前的数据量还很小。

安装:

# yum install gcc gcc-c++
# groupadd mysql
# useradd –shell /sbin/nologin -g mysql mysql
# tar zxvf mysql-5.1.57.tar.gz
# cd mysql-5.1.57

# ./configure –prefix=/data1/app/services/mysql51
            –localstatedir=/data1/app/services/mysql51/data
            –with-charset=utf8
            –with-collation=utf8_general_ci
            –with-extra-charsets=gb2312,gbk,utf8
            –with-plugins=max-no-ndb
            –with-unix-socket-path=/data1/app/tmp/mysql.sock
            –with-mysqld-user=mysql
            –enable-local-infile
            –enable-assembler
            –with-client-ldflags=-all-static
            –with-mysqld-ldflags=-all-static
            –enable-thread-safe-client

# make

# make install

 

配置文件:

主:

[mysqld]
datadir=/data1/app/services/mysql51/data
socket=/data1/app/tmp/mysql.sock
pid-file=/data1/app/tmp/mysqld.pid
user=mysql

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

character-set-server = utf8
collation-server = utf8_general_ci
default-storage-engine=INNODB

skip-name-resolve
skip-external-locking
key_buffer = 1024M
max_allowed_packet = 1M
table_cache = 32512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 32M
thread_cache_size = 32
query_cache_size = 32M
tmp_table_size = 1G

# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 16
lower_case_table_names = 1
max_connections = 1200

slow_query_log_file = /data1/app/log/mysqld-slow.log
slow_query_log = 1
log-queries-not-using-indexes
long_query_time = 1
wait_timeout = 86400

log-bin = mysql-bin
server-id = 1
expire-logs-days = 7
#binlog-do-db = db1

#binlog-do-db = db2

innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 256M
innodb_file_per_table
innodb_open_files = 65535
innodb_lock_wait_timeout = 50
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

binlog_format = mixed
transaction-isolation = READ-UNCOMMITTED

[mysqld_safe]
log-error=/data1/app/log/mysqld.log

 

备:

[mysqld]
datadir=/data1/app/services/mysql51/data
socket=/data1/app/tmp/mysql.sock
pid-file=/data1/app/tmp/mysqld.pid
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

character-set-server = utf8
collation-server = utf8_general_ci
default-storage-engine=INNODB

skip-name-resolve
skip-external-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 32512
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 16M
thread_cache_size = 32
query_cache_size = 16M
tmp_table_size = 1G
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 16
lower_case_table_names = 1
max_connections = 1200

slow_query_log_file = /data1/app/log/mysqld-slow.log
slow_query_log = 1
log-queries-not-using-indexes
long_query_time = 1
wait_timeout = 86400

log-bin = mysql-bin
relay-log = mysql-relay-log
relay-log-index = mysql-relay-index
log_slave_updates = 1

server-id = 2
#master-host = 10.0.0.32
#master-user = repl
#master-password = password
#master-port = 3306
#master-connect-retry = 10
expire-logs-days = 7
replicate-do-db = db1
replicate-do-db = db2
#replicate-ignore-db=test
report-host = 10.0.0.33

innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 256M
innodb_file_per_table
innodb_open_files = 65535
innodb_lock_wait_timeout = 50
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

binlog_format = mixed
transaction-isolation = READ-UNCOMMITTED

[mysqld_safe]
log-error=/data1/app/log/mysqld.log

 

备机的主服务器配置信息不再配置在my.cnf内了,启动后执行以下SQL配置:

 CHANGE MASTER TO MASTER_HOST=’10.0.0.32′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’password’,
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10;

 

 

另外关于innodb_buffer_pool_size参数,今天看了一下这篇文章:

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

主要讲的是关于innodb_buffer_pool_size参数的配置的,大致就是两个方向:

1. 如果mysql innodb的数据量小于物理内存,那么innodb_buffer_pool_size配置为数据量大小+10%,然后再考虑下数据增长,适当调整。

2. 如果mysql innodb的数据量小于物理内存,那么innodb_buffer_pool_size配置应该尽量大,这样可以最大限度的增加缓冲,减少IO。

但是配置时需要注意用物理内存减去其它方面的内存开销,这些开销有:

1) 操作系统的内存开销,这个包括一些系统进程,页表,socket连接的缓冲等一般留个1G左右就够了。

2) innodb正常情况下还有大约8%的开销,主要用在每个缓存页帧的描述、adaptive hash等数据结构,如果不是安全关闭,启动时还要恢复的话,还要另开大约12%的内存用于恢复,两者相加就有差不多21%的开销。

3)数据库连接方面的内存开销,下面有计算公式。

4) query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer等的开销。

5)其它应用程序的内存开销,如果有的话。

在这种情况下,用物理内存减去上面几个方面的内存开销。同时参考用Mysql 5.1手册上的计算公式来确定这个值:

innodb_buffer_pool_size

+ key_buffer_size

+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)

+ max_connections*2MB

每个线程使用一个堆栈(通常是2MB,但在MySQL AB二进制分发版里只有256KB)并且在最坏的情况下也使用sort_buffer_size + read_buffer_size附加内存。

另个还有两个注意的地方:

1)避免双重缓冲,innodb用了innodb_buffer_pool,操作系统层面还有一个缓冲。这样就双重缓冲了,没有必要,反而加大了系统的负载,速度也慢了。可以配置innodb_flush_method = O_DIRECT跳过操作系统的缓冲,直接写入磁盘。

2)避免操作系统对mysql进程做页面交换。如果物理内存不够的时候,操作系统会把当前进程使用的内存先存到磁盘的虚拟内存,然后把空出来的内存给新的需要的进程使用。这方面需要保持有足够的内存,在linux上不想用虚拟内存,可以配置内核参数:

# vim /etc/sysctl.conf

vm.swappiness = 5

# sysctl -p

默认是60,这个数字越高,使用虚拟内存的机率也越高,我设置5,意思意思就行了。需要注意的是,就算设成0也不表示不使用虚拟内存了,物理内存不够时,依然会用到的。

 

MySQL 主从同步错误(error)解决(转)

1出现错误(error)提示

 

Slave I/O: error connecting to master ‘backup@192.168.1.x:3306’ – retry-time: 60  retries: 86400, Error_code: 1045

解决方法(fāng fǎ)

从服务器上删除掉所有二进制日志文件包括一个数据(data)目录下master.info文件和hostname-relay-bin开头文件

master.info::记录(record)了Mysql主服务器上日志文件和记录(record)位置连接密码(code) 

 2出现错误(error)提示

 

Error reading packet from server: File ‘/home/mysql/mysqlLog/log.000001’ not found (Errcode: 2) ( server_errno=29)

解决方案:

由于主服务器运行了一段时间产生了二进制文件而slave是从log.000001开始读取删除主机二进制文件包括log.index文件

3错误(error)提示如下

 

Slave SQL: Error ‘Table ‘xxxx’ doesn’t exist’ on query. Default database: ‘t591’. Query: ‘INSERT INTO `xxxx`(type,post_id,browsenum) SELECT type,post_id,browsenum FROM xxxx WHERE hitdate=’20090209”, Error_code: 1146

解决方法(fāng fǎ)

由于slave没有此table表添加这个表使用slave start 就可以继续同步

 

4错误(error)提示如下

 

Error ‘Duplicate entry ‘1’ for key 1′ on query. Default database: ‘movivi1’. Query: ‘INSERT INTO `v1vid0_user_samename` VALUES(null,1,’123′,’11’,’4545′,’123′)’

 

 

Error ‘You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ ‘ at line 1’ on query. Default database: ‘club’. Query: ‘INSERT INTO club.point_process ( GIVEID, GETID, POINT, CREATETIME, DEMO ) VALUES ( 0, 4971112, 5, ‘2010-12-19 16:29:28’, ‘

1 row in set (0.00 sec)

 Mysql > Slave statusG;

显示:Slave_SQL_Running 为 NO

解决方法(fāng fǎ):

Mysql > stop slave;

Mysql > set global sql_slave_skip_counter =1 ;

Mysql > start slave;

5错误(error)提示如下

# show slave statusG;

 

Master_Log_File: mysql-bin.000029

Read_Master_Log_Pos: 3154083

Relay_Log_File: c7-relay-bin.000178

Relay_Log_Pos: 633

Relay_Master_Log_File: mysql-bin.000025

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB: club

Replicate_Ignore_DB: 

Replicate_Do_Table: 

Replicate_Ignore_Table: 

Replicate_Wild_Do_Table: 

Replicate_Wild_Ignore_Table: 

Last_Errno: 1594

Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.

Skip_Counter: 0

Exec_Master_Log_Pos: 1010663436

这个问题(problem)原因是主数据(data)库突然停止或问题(problem)终止更改了mysql-bin.xxx日志slave服务器找不到这个文件需要找到同步点和日志文件然后chage master即可

解决方法(fāng fǎ):

 

 change master to 

 master_host=’211.103.156.198′,

 master_user=’同步帐号’, 

 master_password=’同步密码(code)’, 

 master_port=3306, 

 master_log_file=’mysql-bin.000025‘, 

 master_log_pos=1010663436;

6错误(error)提示如下

 

Error ‘Unknown column ‘qdir’ in ‘field list” on query. Default database: ‘club’. Query: ‘insert into club.question_del (id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime,banzhu_uid,banzhu_uname,del_cause,qdir) select id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime,’1521859′,’admin0523′,’无意义回复’,qdir from club.question where id=7330212′

1 row in set (0.00 sec)

这个错误(error)就说club.question_del 表里面没有qdir这个字段 造成加上就可以了~

在主mysql : 里面查询 Desc club.question_del; 

在 错误(error)从服务器上执行 : alter table question_del add qdir varchar(30) not null;

该文章收集于互联网,如有侵权,请联系删除!

Performance Dashboard运行错误

Microsoft SQL Server 2005 Performance Dashboard Reports 是一组运行于 SQL Server 2005 SP2 Management Studio 及更高版本中的 Reporting Services 报表文件。他几乎统计了sql server 2005各个方面的报表,如cpu,内存,IO,未使用索引的查询等等,通过这些报表,SQL Server 用户可以更容易确定性能问题发生的时间以及可能存在的原因。这样可以更快地解决问题。

 

下载地址:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22602

 

今天运行 Performance Dashboard报了一个错,之前所有安装过程都是挺正常的,但执行“performance_dashboard_main.rdl”报表时出现“两个datetime 列的差别导致了运行时溢出。”错误。

解决办法:

1) 修改setup.sql脚本中的procedure MS_PerfDashboard.usp_Main_GetSessionInfo的内容中的“convert(bigint, datediff(ms, login_time, getdate()))”为“convert(bigint,datediff(dd,login_time,getdate()))*24*60*60*1000”

2) 将recent_cpu.rdl中的“convert(bigint, datediff(ms, login_time, getdate()))”都换成“convert(bigint,datediff(dd,login_time,getdate()))*24*60*60*1000”就可以了 

然后再执行setup.sql重新安装,再次运行Performance Dashboard,没有错误了。

 

 

rpm包安装时如何更新安装路径?

如果rpm包已经做好,但在安装的时候想修改默认路径,则可以:

rpm -ivh –prefix=/opt/usr xxx.rpm
又或者同时修改多个路径:

rpm -ivh –relocate=/usr=/opt/usr –relocate=/etc=/usr/etc xxx.rpm


查看一个未安装的rpm包中包含那些文件:

rpm -qlp ****.rpm 

rpm -ivh [install_options] package
以下的options是可完成:
        –prefix <path>;
               This   sets   the   installation   prefix to <path>; for
               relocatable packages.

        –relocate <oldpath>;=<newpath>;
               For relocatable packages, translates the files that
               would be put in <oldpath>; to <newpath>;.

        –badreloc
               To   be   used   in   conjunction with –relocate, this
               forces the relocation even   if   the   package   isn’t
               relocatable.

        –noorder
               Don’t reorder the packages for an install. The list
               of packages would normally be reordered to   satisfy
               dependancies.