Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of mysql database synchronization debug, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The synchronization of mysql has always been a very important problem and a difficult problem. Fortunately, it provides a variety of methods for synchronization.

In this article, I first give the way that mysql itself comes with master-slave. The detailed steps are as follows:

1 、 prepare

# need to modify the my.cnf files of master and slave servers #

1) master

Mysql > grant replication client,replication slave on *. * to 'repl'@'192.168.0.*' identified by' xxxxxx'

=

# Master server #

# 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 'repl'@'192.168.0.*' identified by' xxxxxx'

=

# from server #

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

# replicate_do_table =

# replicate_ignore_table =

# replicate_wild_do_table =

# replicate_wild_ignore_table = …

# replicate_wild_ignore_table = temp\ _ subpost\ _%

# replicate_rewrite_db=- >

# 1062: dup key entry

# 1064: sql syntax

# slave_skip_errors = 1062 and 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's mysql

Mysqlctl start

Ps auxww | grep mysql

Mysql > flush tables

Mysql > show master status\ G

Mysql > reset master

Mysqlctl stop

4 、 start slave's mysql

Mysqlctl start

Ps auxww | grep mysql

Mysql > stop slave

Mysql > show slave status\ G

Mysql > reset slave

Mysqlctl stop

5 、 start master'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's datas to slave

Rsync-avP "master's datas"slave's datas directory"

7 、 start slave's mysql

Mysqlctl start-skip-slave-start

Mysql > show slave status\ G

Mysql > change master to

-> MASTER_HOST='master_host_name'

-> MASTER_USER='replication_user_name'

-> MASTER_PASSWORD='replication_password'

-> MASTER_LOG_FILE='recorded_log_file_name'

-> MASTER_LOG_POS=recorded_log_position

# # Note:slave's file and pos must be the same with with master'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's database updated real-time whether or not??

The above is all the contents of the article "sample Analysis of mysql Database synchronization debug". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.