Mysql数据库主从心得整理_百通达网络 -Powered By 专注个人学习

1.Slave_SQL_Running: No mysql同步故障解决方法首先停掉Slave服务

mysql>slave stop;

到主服务器查看主机状态

mysql>show master status;

到Slave服务器上执行手动同步：

mysql> change master to 
> master_host='master_ip',
> master_user='user', 
> master_password='pwd', 
> master_port=3307, 
> master_log_file='mysql-bin.000020', 
> master_log_pos=135617781;

启动slave服务

mysql>slave start;

查看日志一些命令

show master status\G

RESET MASTER  #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER 
RESET SLAVE   #从机运行，清除日志同步位置标志，并重新生成master.info

主库master配置

1、主库的配置 1.1．mysql5.0以下版本的配置修改主库mysql配置配置文件，在[mysqld]段添加以下内容：

server-id = 1
log-bin=/home/mysql/logs/binlog/bin-log
max_binlog_size = 500M
binlog_cache_size = 128K
binlog-do-db = adb
binlog-ignore-db = mysql
log-slave-updates

1.2. mysql5.0以上版本的配置修改主库mysql配置配置文件，在[mysqld]段添加以下内容：

server-id = 1
log-bin=/home/mysql/logs/binlog/bin-log
max_binlog_size = 500M
binlog_cache_size = 128K
binlog-do-db = adb
binlog-ignore-db = mysql
log-slave-updates
expire_logs_day=2
binlog_format="MIXED"

在mysql的replicatin环境中master/slave常用参数

master所有参数

1.log-bin=mysql-bin

//控制master的是否开启binlog记录功能；二进制文件最好放在单独的目录下，这不但方便优化、更方便维护。重新命名二进制日志很简单，只需要修改[mysqld]里的 log_bin选项，这里有一点需要注意，如下例子：

log_bin=/home/mysql/binlog/binlog.log [root@localhost ~]# ll /home/mysql/binlog total 8 -rw-rw---- 1 mysql mysql 98 Mar 7 17:24 binlog.000001 -rw-rw---- 1 mysql mysql 33 Mar 7 17:24 binlog.index [root@localhost ~]#

从上面的例子可以看到，我要重新调整logbin的路径为“/home/mysql/binlog”，但我log_bin的设置却有些不同,这里需要注意两点 1.1).目录的文件夹命名不能有空格 1.2).指定目录时候一定要以*.log结尾，即不能仅仅指定到文件夹的级别，否则在重启mysql时会报错。

2.server-id=1

//每个server服务的标识，在master/slave环境中，此变量一定要不一样

expire_logs_days=15

//通过此来实现master自动删除binlog

4.innodb_flush_log_at_trx_commit=1

//此单数表示在事务提交时，处理重做日志的方式；此变量有三个可选值0，1，2 0：当事务提交时，并不将事务的重做日志写入日志文件，而是等待每秒刷新一次 1：当事务提交时，将重做日志缓存的内容同步写到磁盘日志文件，为了保证数据一致性，在replication环境中使用此值。 2：当事务提交时，将重做日志缓存的内容异步写到磁盘日志文件（写到文件系统缓存中）建议必须设置innodb_flush_log_at_trx_commit=1

5.sync_binlog=1

//此参数表示每写缓冲多少次就同步到磁盘；sync_binlog=1表示同步写缓冲和磁盘二进制日志文件，不使用文件系统缓存，在使用innodb事务引擎时，在复制环境中，为了保证最大的可用性，都设置为“1”，但会对影响io的性能。及时设置为“1”，也会有问题发生，假如当二进制日志写入磁盘，但事务还没有commit，这个时候宕机，当服务再次起来的恢复的时候，无法回滚以及记录到二进制日志的未提交的内容；这个时候就会造成master和slave数据不一致，这时就需要参数innodb_support_xa=1来保证。建议必须设置

6.innodb_support_xa=1

//此参数与XA事务有关，它保证了二进制日志和innodb数据文件的同步，保证复制环境中数据一致性。建议必须设置

7.binlog-do-db=skate_db

//只记录指定数据库的更新到二进制日志中

8.binlog-do-table=skate_tab

//只记录指定表的更新到二进制日志中

9.binlog-ignore-db=skate_db

//忽略指定数据库的更新到二进制日志中

10.log_slave_updates=1

//此参数控制slave数据库是否把从master接受到的log并在本slave执行的内容记录到slave的二进制日志中，在级联复制环境中（包括双master环境），这个参数是必须的

11.binlog_format=statement|row|mixed

//控制以什么格式记录二进制日志的内容，默认是mixed

12.max_binlog_size

//master的每个二进制日志文件的大小，默认1G

13.binlog_cache_size

//所有未提交的事务都会咱被记录到一个缓存或临时文件中，待提交时，统一同步到二进制日志中，此变量是基于session的，每个会话开启一个binlog_cache_size大小的缓存。通过变量“Binlog_cache_disk_use”和“Binlog_cache_use”来设置binlog_cache_size的大小。说明： Binlog_cache_disk_use：使用临时文件写二进制日志的次数 Binlog_cache_use：使用缓冲记写二进制的次数

14.auto_increment_increment=2 //增长的步长 auto_increment_offset=1 //起始位置 //在双master环境下可以防止键值冲突

slave所用参数

1.server-id=2

//和master的含义一样，如上

2.log-bin=mysql-bin

//和master的含义一样，如上

3.relay-log=relay-bin

//中继日志文件的路径名称

relay-log-index=relay-bin

//中继日志索引文件的路径名称

log_slave_updates=1

//和master的含义一样，如上

6.read_only=1

//使数据库只读，此参数在slave的复制环境和具有super权限的用户不起作用，对于复制环境设置read_only=1非常有用，它可以保证slave只接受master的更新，而不接受client的更新。 mysq> set global read_only=1

7.skip_slave_start

//使slave在mysql启动时不启动复制进程，mysql起来之后使用 start slave启动，建议必须

8.replicate-do-db

//只复制指定db

9.replicate-do-table

//只复制指定表

replicate-ingore-table

//忽略指定表

replicate_wild_do_table=skatedb.% //模糊匹配复制指定db

12.auto_increment_increment=2 auto_increment_offset=1

//和master含义一样，参考如上

13。log_slow_slave_statements

//在slave上开启慢查询日志，在query的时间大于long_query_time时，记录在慢查询日志里

14.max_relay_log_size

//slave上的relay log的大小，默认是1G

15.relay_log_info_file

//中继日志状态信息文件的路径名称

relay_log_purge

//当relay log不被需要时就删除，默认是on， SET GLOBAL relay_log_purge=1

17.replicate-rewrite-db=from_name->to_name //数据库的重定向，可以把分库汇总到主库便于统计分析

一个普通的配置

server-id  =  1
log-bin = /mydata/binlogs/master-bin
 binlog-do-db=db_test
reset master RESET MASTER  #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER
show slave status\G

server-id  =  2
relay-log = /mydata/relaylogs/relay-bin
CHANGE MASTER TO MASTER_USER='tom', MASTER_HOST='172.16.41.1', MASTER_PASSWORD='qazwsx123';
show slave status\G

代码如下    复制代码
1.mysql> reset slave; # 重点就是这行
2.mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; # 请依照自行环境设定
3.mysql> start slave; # 就正常了.
4.?: 重新设置 slave, MASTER_LOG_FILE 和 MASTER_LOG_POS 会被清空, 所以需要重新设置.

MariaDB [(none)]> CREATE USER 'tom'@'172.16.41.2' IDENTIFIED BY 'qazwsx123';
MariaDB [(none)]> REVOKE ALL PRIVILEGES ,GRANT OPTION FROM 'tom'@'172.16.41.2';
MariaDB [(none)]> GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ,REPLICATION SLAVE ON *.* TO 'tom'@'172.16.41.2';
MariaDB [(none)]> FLUSH PRIVILEGES

操作命令：

代码如下:

show binlog events in 'mysql-bin.000016' limit 10;
reset master 删除所有的二进制日志
flush logs  产生一个新的binlog日志文件
show master logs; 或者 show binary logs; 查看二进制文件列表和文件大小

代码如下:

./mysqlbinlog --start-datetime="2012-05-21 15:30:00" --stop-datetime="2012-05-21 16:40:00" /binlog/mysql-bin.000005 > a.log

要想通过日志恢复数据库，在你的 my.cnf 文件里应该有如下的定义，log-bin=mysql-bin，这个是必须的 binlog-do-db=db_test ，这个是指定哪些数据库需要日志，每行一个，如果不指定的话默认就是所有数据库. 代码如下:
```
[mysqld]
log-bin=mysql-bin
binlog-do-db=db_test
binlog-do-db=db_test2 
```

3.删除二进制日志:

代码如下:

mysql> reset master  (清空所有的二进制日志文件)
purge master logs to 'mysql-bin.000006';  (删除mysql-bin.000006之前的二进制日志文件)
purge master logs before '2007-08-10 04:07:00'  (删除该日期之前的日志)

在my.cnf 配置文件中[mysqld]中添加: 代码如下:

expire_logs_day=3  设置日志的过期天数,过了指定的天数,会自动删除

4.下面就是恢复操作了

特别提示，mysql每次启动都会重新生成一个类似 mysql-bin.000003 的文件，如果你的mysql每天都要重新启动一次的话，这时候你就要特别注意不要选错日志文件了。技巧1: 你可以通过–one-database 参数选择性的恢复单个数据库，example在下面，爽吧。

mysqlbinlog –stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001  | mysql -u root -pmypwd –one-database db_test

技巧2: 如果已经使用过

/usr/local/mysql5/bin/mysqlbinlog –start-date="005-04-20 9:55:00" /var/data/mysql5/mysql-bin.0* > /home/db/tt.sql 类似的语句将日志导成了ASCII文本文件，那么你就可以直接在phpmyadmin里执行这个文件,因为它本身就是一个标准的sql文件，比如想让文件里面的某些语句不执行，找到它们删除即可，然后再放进去执行。

技巧3:

mysqlbinlog –stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001  | mysql -u root -pmypwd –one-database db_test

这是把mysql-bin.000001这个二进制文件里的内容转换成ASCII文件(也就是sql语句)，直接通过管道操作符 | 传输给 mysql这个程序，然后过滤掉其它数据库的语句，只在db_test里执行。

要想确定当前的二进制日志文件的文件名，输入下面的MySQL语句：代码如下:

SHOW BINLOG EVENTS

指定恢复时间对于MySQL 4.1.4，可以在 mysqlbinlog 语句中通过 –start-date 和 –stop-date 选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2005年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：

代码如下:

mysqlbinlog –stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001  | mysql -u root -pmypwd

该命令将恢复截止到在 –stop-date 选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：

代码如下:

mysqlbinlog –start-date="2005-04-20 10:01:00" /var/log/mysql/mysql-bin.000001  | mysql -u root -pmypwd

指定恢复位置

也可以使用mysqlbinlog的选项 –start-position 和 –stop-position 来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：

代码如下:

mysqlbinlog –start-date="2005-04-20" –stop-date="2005-04-20" /var/log/mysql/mysql-bin.000001 > /tmp/mysql_restore.sql

该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

代码如下:

mysqlbinlog –stop-position="368312" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd
mysqlbinlog –start-position="368315"/var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd

上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。

博客

Mysql数据库主从心得整理

在mysql的replicatin环境中master/slave常用参数

操作命令：

admin

热门文章

标签云

友情链接