《mysql数据库同步debug》由会员分享,可在线阅读,更多相关《mysql数据库同步debug(5页珍藏版)》请在金锄头文库上搜索。
1、mysqlmysql 数据库同步数据库同步 debugdebug数据库的同步一直是个很重要的问题,也是一个难题,所幸 mysql 提供了多种方法可以用来同步在本文中我先给出 mysql 本身自带的方式 master-slave 方式,详尽的步骤如下:1、prepare# 需要修改主、从服务器的 f 文件#1) mastermysql grant replication client,replication slave on *.* to repl192.168.0.* identified by xxxxxx=# 主服务器# Replication Master Server (default
2、)# 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 maste
3、r if omittedserver-id = 2370=2) slavemysql grant replication client,replication slave on *.* to repl192.168.0.* identified by xxxxxx=# 从服务器# 来运棋牌 http:/# 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 i
4、f 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 - requi
5、redmaster-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
6、_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,1064
7、slave_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、
8、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 maste
9、rs 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_l
10、og_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:/9、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 ?