建设网站制作汉狮团队,vs 网站开发教程,做网站的过程,义乌网站建设工作室目前环境如下#xff1a;master server IP:172.17.61.131slave server IP:172.17.61.132mysql version: mysql-5.7.21-linux目标#xff1a;计划内的主从复制正常切换1.切换之前需要检查slave的同步状态#xff0c;对master进行锁表。slave server: 确保Slave_IO_Running和S…目前环境如下master server IP:172.17.61.131slave server IP:172.17.61.132mysql version: mysql-5.7.21-linux目标计划内的主从复制正常切换1.切换之前需要检查slave的同步状态对master进行锁表。slave server: 确保Slave_IO_Running和Slave_SQL_Running都为YESmysql show slave status \G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.17.61.131Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql_bin.000036Read_Master_Log_Pos: 154Relay_Log_File: slave_relay_bin.000003Relay_Log_Pos: 367Relay_Master_Log_File: mysql_bin.000036Slave_IO_Running: YesSlave_SQL_Running: Yes...1 row in set (0.00 sec)master server进行锁表操作由于我是测试环境没有应用所以锁表很快。2.在flush tables with read lock成功获得锁之前必须等待所有语句执行完成(包括SELECT)。所以如果有个慢查询在执行或者一个打开的事务或者其他进程拿着表锁flush tables with read lock就会被阻塞直到所有的锁被释放。mysql flush tables with read lock;Query OK, 0 rows affected (0.01 sec)3.接着要确保master所有的binlog已同步到slave并且slave都已apply了所有的binlog。maser server:mysql show processlist;-----------------------------------------------------------------------------------------------------------------------------------------| Id | User | Host | db | Command | Time | State | Info |-----------------------------------------------------------------------------------------------------------------------------------------| 2 | repl | 172.17.61.132:60079 | NULL | Binlog Dump | 1907 | Master has sent all binlog to slave; waiting for more updates | NULL || 3 | root | localhost | NULL | Query | 0 | starting | show processlist |-----------------------------------------------------------------------------------------------------------------------------------------2 rows in set (0.00 sec)slave server:mysql show processlist;---------------------------------------------------------------------------------------------------------------------------| Id | User | Host | db | Command | Time | State | Info |---------------------------------------------------------------------------------------------------------------------------| 1 | system user | | NULL | Connect | 1918 | Slave has read all relay log; waiting for more updates | NULL || 2 | system user | | NULL | Connect | 1918 | Waiting for master to send event | NULL || 4 | root | localhost | NULL | Query | 0 | starting | show processlist |---------------------------------------------------------------------------------------------------------------------------3 rows in set (0.00 sec)4.停止slave的进程slave sever:mysql STOP SLAVE IO_THREAD;Query OK, 0 rows affected (0.02 sec)mysql show processlist;---------------------------------------------------------------------------------------------------------------------------| Id | User | Host | db | Command | Time | State | Info |---------------------------------------------------------------------------------------------------------------------------| 1 | system user | | NULL | Connect | 2039 | Slave has read all relay log; waiting for more updates | NULL || 4 | root | localhost | NULL | Query | 0 | starting | show processlist |---------------------------------------------------------------------------------------------------------------------------2 rows in set (0.00 sec)5.修改slave的my.cnf重启mysql[mysqld]socket /usr/local/mysql/mysql.sockcharacter_set_server utf8init_connect SET NAMES utf8basedir /usr/local/mysqldatadir /u01/mysqlsocket /u01/mysql/mysql.socklog-error /u01/log/mysql/mysql_3306.errpid-file /u01/mysql/mysqld.pidlower_case_table_names 1sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONsecure-file-priv /u01/backupserver-id10001log_bin /u01/mysql/mysql_bin#skip-grant-tablesinnodb_flush_log_at_trx_commit1sync_binlog1#relay-log/u01/mysql/slave_relay_binexpire_logs_days10read_only0#relay_log_recoveryon#relay_log_info_repositoryTABLEmax_binlog_size1073741824#autocommitoff#long_query_time15#slow_query_logon[rootqht132 ~]# service mysql restartShutting down MySQL.. [ OK ]Starting MySQL. [ OK ]需要开启原slave的bin_log,关闭relay_log*关闭read_only5.提升slave为mastermysql stop slave;Query OK, 0 rows affected (0.00 sec)mysql reset master;Query OK, 0 rows affected (0.00 sec)mysql reset slave all;Query OK, 0 rows affected (0.01 sec)mysql show variables like read_only;----------------------| Variable_name | Value |----------------------| read_only | OFF |----------------------1 row in set (0.00 sec)mysql show master status \G*************************** 1. row ***************************File: mysql_bin.000001Position: 154Binlog_Do_DB:Binlog_Ignore_DB:Executed_Gtid_Set:1 row in set (0.00 sec)在新的master上建立同步帐户并赋予同步权限(由于我为了省事之前主从配置的时候将61网段的权限都赋予给了repl所以这一步我也可以不做)mysql grant replication slave on *.* to repl172.17.61.%;Query OK, 0 rows affected (0.01 sec)6.修改原master的my.cnf重启mysql[mysqld]socket /usr/local/mysql/mysql.sockcharacter_set_server utf8init_connect SET NAMES utf8basedir /usr/local/mysqldatadir /u01/mysqlsocket /u01/mysql/mysql.socklog-error /u01/log/mysql/mysql_3306.errpid-file /u01/mysql/mysqld.pidlower_case_table_names 1sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONsecure-file-priv /u01/backupserver-id10000#log_bin /u01/mysql/mysql_bin#skip-grant-tables#innodb_flush_log_at_trx_commit1#sync_binlog1expire_logs_days10read_only1relay_log_recoveryonrelay_log_info_repositoryTABLE#max_binlog_size1073741824#autocommitoff#long_query_time15#slow_query_logon关闭原master的binlog开启relay_log*打开read_only状态[rootqht131 ~]# service mysql restartShutting down MySQL.. [ OK ]Starting MySQL. [ OK ]7.将master切换为slavemysql reset master;Query OK, 0 rows affected (0.00 sec)mysql change master to- master_host172.17.61.132,- master_userrepl,- master_passwordrepl,- master_log_filemysql_bin.000001,- master_log_pos154;Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql start slave;Query OK, 0 rows affected (0.01 sec)master_log_file和master_log_pos需指定为新master切换后显示的起始位置检查一下新slave的状态确保slave_io_running和slave_sql_running都为YES。mysql show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.17.61.132Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql_bin.000001Read_Master_Log_Pos: 360Relay_Log_File: qht131-relay-bin.000002Relay_Log_Pos: 526Relay_Master_Log_File: mysql_bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 360Relay_Log_Space: 734Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 10001Master_UUID: 744cfcde-3a9b-11e8-b299-000c2900d025Master_Info_File: /u01/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1 row in set (0.00 sec)8.最后进行测试新master:mysql create table t3 ( c1 int);Query OK, 0 rows affected (0.05 sec)新slave:mysql use l5mDatabase changedmysql show tables;---------------| Tables_in_l5m |---------------| t1 || t2 || t3 || test_emp || tt |---------------5 rows in set (0.00 sec)mysql show create table t3\G*************************** 1. row ***************************Table: t3Create Table: CREATE TABLE t3 (c1 int(11) DEFAULT NULL) ENGINEInnoDB DEFAULT CHARSETutf81 row in set (0.00 sec)至此测试完毕转自mysql5.7 主从复制的正常切换 - CSDN博客https://blog.csdn.net/jolly10/article/details/79877564
