Mysql database synchronization debug_MySQL

Source: Internet
Author: User
Mysql database synchronization debug bitsCN.com

Database synchronization has always been a very important issue and a difficult issue. Fortunately, mysql provides a variety of methods for synchronization.

In this article, I will first provide the mysql built-in master-slave method, detailed steps are as follows:

1. prepare

### My. cnf file of the master and slave servers to be modified ###

1) master

Mysql> grant replication client, replication slave on *. * to 'repl' @ '2017. 192. 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 = 1 M

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

Servers-id = 2370

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

2) slave

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

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

### Slave 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

Servers-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

# 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 = 4152,1064

Slave_skip_errorrs = 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. 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

-> 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 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 ??

This article is from "small steps and continuous steps !" Blog

BitsCN.com

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.