概述一般生产环境需要定期做一次mysql数据库主备切换演练。这里简单记录一下,后面会演示一些备份恢复演练。1、查看主库状态showprocesslist;Masterhassentallbinlogtoslave;waitingforbinlogtobeupdatedshowmasterstatus\G2,查看从库状态showprocesslist;showslavestatus\G3,停止从库的IO_THREAD线程stopslaveIO_THREAD;showprocesslist;showslavestatus\G4,从库切换到主库stopslave;resetmastershow;resetslavestatall;\G5,激活账号SELECTDISTINCTCONCAT('User:''',user,'''@''',host,''';')ASqueryFROMmysql.user;GRANTREPLICATIONSLAVEON*.*TO'repl'@'%'IDENTIFIEDBY'xxx;FLUSHPRIVILEGES;6.将原主库切换为从库(由原主库执行)resetmaster;重置奴隶;CHANGEMASTERTOMASTER_HOST='1xxx',MASTER_USER='repl',MASTER_PASSWORD='xxx,MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=589;7、查看主库SHOWPROCESSLIST;showmasterstatus\G8,启动从库SHOWPROCESSLIST;startslave;showslavestatus\G9,MySQL主从测试9.1插入数据9.1.1创建测试表并插入数据mysql>createtabletest123(idint(4));QueryOK,0rowsaffected(0.01sec)mysql>insertintotest123values(1);QueryOK,1rowaffected(0.00sec)mysql>insertintotest123values(2);QueryOK,1rowaffected(0.00sec)mysql>insertintotest123values(3);QueryOK,1rowaffected(0.00sec)mysql>insertintotest123values(4);QueryOK,1rowaffected(0.00sec)9.1.2从数据库查询:9.2删除数据9.2.1从主库中删除表:mysql>droptabletest123;9.2.2从数据库查询:
