mysql数据库同步debug
mysqlmysql 数据库同步数据库同步 debugdebug数据库的同步一直是个很重要的问题，也是一个难题，所幸 mysql 提供了多种方法可以用来同步在本文中我先给出 mysql 本身自带的方式 master-slave 方式，详尽的步骤如下：1、prepare# 需要修改主、从服务器的 my.cnf 文件#1) mastermysql> grant replication client,replication slave on *.* to repl'192.168.0.* identified by xxxxxx=# 主服务器# Replication Master Server (default)# binary logging is required for replicationlog-bin=/mysql/bin-loglog-bin-index=/mysql/bin-log.index#binlog_cache_size = 1Mbinlog_do_db = test1binlog_do_db = test2#binlog_ignore_db =# required unique id between 1 and 232 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 2370=2) slavemysql> grant replication client,replication slave on *.* to repl'192.168.0.* identified by xxxxxx=# 从服务器# 来运棋牌 http:/www.411196.com# required unique id between 2 and 232 - 1# (and different from the master)# defaults to 2 if master-host is set# but will not function as a slave if omittedserver-id = 2379# The replication master for this slave - requiredmaster-host = 192.168.0.240# The username the slave will use for authentication when connecting# to the master - requiredmaster-user = repl# The password the slave will authenticate with when connecting to# the master - requiredmaster-password = xxxxxx# The port the master is listening on.# optional - defaults to 3306master-port = 3306# binary logging - not required for slaves, but recommended#log-bin=/mysql/log/slavebin-logmaster-info-file = /mysql/log/master.inforelay-log-info-file = /mysql/log/relay-log.inforeplicate_do_db = test1replicate_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,1064slave_skip_errors = 1062relay-log = /mysql/log/relay-logrelay-log-index = /mysql/log/relay-log.index=# below step will start the work #=cd /opt/mysql/binln -s /opt/mysql/share/mysql/mysql.server mysqlctl=2、stop mysql1) master: mysqlctl stop mysql >show master status Gmysql >reset mastermysqlctl stop4、start slaves mysqlmysqlctl startps auxww|grep mysqlmysql >stop slavemysql >show slave status Gmysql >reset slave;mysqlctl stop5、start masters mysqlmysqlctl startmysql >show master status Gmysql >flush tables with read lockmysql >show master status G# record the bin-log and positon #6、synchronization masters datas to slaversync -avP “masters datas” “slaves datas directory”7、start slaves mysqlmysqlctl start skip-slave-startmysql >show slave status Gmysql >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:slaves file and pos must be the same with with masters #8、mastermysql >unlock tables; 棋牌评测网 http:/www.77884.net9、slavemysql >show slave status G# Note: Seconds_behind_master=0# Slave_IO_Running=YES,Slave_SQL_Running=YES10、checking the slaves database updated real-time whether or not ?
