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