mysql数据库同步debug的示例分析

共计 3622 个字符，预计需要花费 10 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 mysql 数据库同步 debug 的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

mysql 的同步一直是个很重要的问题，也是一个难题，所幸提供了多种方法可以用来同步

在本文中我先给出 mysql 本身自带的方式 master-slave 方式，详尽的步骤如下：

1、prepare

### 需要修改主、从服务器的 my.cnf 文件 ###

1) master

mysql grant replication client,replication slave on *.* to lsquo;repl rsquo;@ 192.168.0.* rsquo; identified by lsquo;xxxxxx rsquo;

=========================================================================

### 主服务器 ###

# Replication Master Server (default)

# binary logging is required for replication

log-bin=/mysql/bin-log

log-bin-index=/mysql/bin-log.index

#binlog_cache_size = 1M

binlog_do_db = test1

binlog_do_db = test2

#binlog_ignore_db =

# 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 = 2370

=========================================================================

2) slave

mysql grant replication client,replication slave on *.* to lsquo;repl rsquo;@ 192.168.0.* rsquo; identified by lsquo;xxxxxx rsquo;

=========================================================================

### 从服务器 ###

# 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 = 2379

#

# The replication master for this slave – required

master-host = 192.168.0.240

#

# The username the slave will use for authentication when connecting

# to the master – required

master-user = repl

#

# The password the slave will authenticate with when connecting to

# the master – required

master-password = xxxxxx

#

# The port the master is listening on.

# optional – defaults to 3306

master-port = 3306

#

# binary logging – not required for slaves, but recommended

#log-bin=/mysql/log/slavebin-log

master-info-file = /mysql/log/master.info

relay-log-info-file = /mysql/log/relay-log.info

replicate_do_db = test1

replicate_do_db = test2

#replicate_ignore_db = hellip;

#replicate_do_table =

#replicate_ignore_table =

#replicate_wild_do_table =

#replicate_wild_ignore_table = hellip;

#replicate_wild_ignore_table = temp\_subpost\_%

#replicate_rewrite_db=-

# 1062: dup key entry

# 1064: sql syntax

#slave_skip_errors = 1062,1064

slave_skip_errors = 1062

relay-log = /mysql/log/relay-log

relay-log-index = /mysql/log/relay-log.index

=========================================================================

### below step will start the work ###

=========================================================================

cd /opt/mysql/bin

ln -s /opt/mysql/share/mysql/mysql.server mysqlctl

=========================================================================

2、stop mysql

1) master: mysqlctl stop ps auxww|grep mysql

2) slave: mysqlctl stop ps auxww|grep mysql

3、start master rsquo;s mysql

mysqlctl start

ps auxww|grep mysql

mysql flush tables;

mysql show master status \G

mysql reset master

mysqlctl stop

4、start slave rsquo;s mysql

mysqlctl start

ps auxww|grep mysql

mysql stop slave

mysql show slave status \G

mysql reset slave;

mysqlctl stop

5、start master rsquo;s mysql

mysqlctl start

mysql show master status \G

mysql flush tables with read lock

mysql show master status \G

## record the bin-log and positon ##

6、synchronization master rsquo;s datas to slave

rsync -avP“master rsquo;s datas”“slave rsquo;s datas directory”

7、start slave rsquo;s mysql

mysqlctl start ndash;skip-slave-start

mysql show slave status \G

mysql change master to

– MASTER_HOST= rsquo;master_host_name rsquo;,

– MASTER_USER= rsquo;replication_user_name rsquo;,

– MASTER_PASSWORD= rsquo;replication_password rsquo;,

– MASTER_LOG_FILE= rsquo;recorded_log_file_name rsquo;,

– MASTER_LOG_POS=recorded_log_position;

## Note:slave rsquo;s file and pos must be the same with with master rsquo;s ##

8、master

mysql unlock tables;

9、slave

mysql show slave status \G

## Note: Seconds_behind_master=0

## Slave_IO_Running=YES,Slave_SQL_Running=YES

10、checking the slave rsquo;s database updated real-time whether or not ??

以上是“mysql 数据库同步 debug 的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！