Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to deploy and switch documents between master and slave servers of MySQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

How to deploy documents for MySQL database master-slave server

1. Make sure that the version of MySQL installed on the master server and slave server is the same.

two。 Set up a connection account for the slave server on the master server

MysqlGRANTREPLICATIONSLAVE,SUPER,RELOADON*.*TO'username'@10.1.1.4'IDENTIFIEDBY'use .

Master server IP:10.1.1.3

IP:10.1.1.4 from the server

1. Make sure that the version of MySQL installed on the master server and slave server is the same.

two。 Set up a connection account for the slave server on the master server

Mysql > GRANTREPLICATIONSLAVE,SUPER,RELOADON*.*TOIDENTIFIEDBY'userpassword'

3. Execute FLUSHTABLESWITHREADLOCK to lock the table

Mysql > FLUSHTABLESWITHREADLOCK

4. Keep the client running and issue FLUSHTABLES statements to keep the read lock valid. If you exit the client program, the lock is released. Enter the data directory of the primary server and execute the command:

Shell > tar-cvf/tmp/mysql-snapshot.tar.shell > tar-xvf/tmp/mysql-snapshot.tar

Read the current binary log name (File) and offset value (Position) on the primary server and record it:

Mysql > SHOWMASTERSTATUS; | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | | mysql-bin.003 | 73 | test | manual,mysql | after taking a snapshot and recording the log name and offset, you can re-enable write activity on the master server:

Mysql > UNLOCKTABLES

5. Ensure that the [mysqld] section of the my.cnf file on the primary server host includes a log_bin option

[mysqld] Log_bin=mysql-binserver-id=1

6. Stop the server used for the slave server and add the following line to its my.cnf file:

[mysqld] server-id=2

7. If you make a binary backup of the data of the master server, copy it to the data directory of the slave server before starting the slave server.

Make sure that the permissions on these files and directories are correct. The user running the server MySQL must be able to read and write files as they would on the primary server.

8. Start the slave server with the-- skip-slave-start option so that it does not immediately attempt to connect to the master server.

9. Execute the following statement on the slave server:

Mysql > CHANGEMASTERTOMASTER_HOST='10.1.1.3',MASTER_USER='username',MASTER_PASSWORD='userpassword',MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=recorded_log_position

9. Start the slave server thread:

Mysql > STARTSLAVE

10. Verify that the deployment is successful

Mysql > showslavestatus\ Groubles1.rowboxes SlaveSlaveInstruments IOV StateVal WaitingformastertosendeventMasterly HostVera 10.1.1.3 Masterpieces UserVOV repairslaveMasterpieces PortVOL3306Connectfolds RetryWare 60Masterpieces Logogical FileVesmysqlFlynBin.0058Readers Masterpieces Logogical Posplace 27324573Relayworthy Logistic FileVariations cLY11- 111CorrelayWhen LableReplicateTableReplicateRetilleDBReplicateDostDBlReplicateReplicateReplicateReplicateDelixqlReplicateTableReplicateReplicateRetilleTableReplicateReplicateTableTableReplicateReplicateTableReplicateReplicateWildflowers TableReplicateReplicateReplicateWildflowers TableReplicateReplicateWildfolk IgnoremenTableReplicateReplicatewild Wildhands Ignoreride LableLastspot ErastrorrorlSkipfake Countermono 0Execcentric Logistic Logspace PosMold 27324573RelayayayayOBLASE LogicateUntilleReplicateReplicateTableReplicateReplicateTableReplicateReplicatewild 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:No1rowinset (0.00sec)

When both Slave_IO_Running and Slave_SQL_Running display Yes, synchronization is successful.

How to switch documents between MySQL Database Master and Slave Server

1. Make sure that all statements in the relay log have been processed from the slave server. Mysql > STOPSLAVEIO_THREAD.

two。 Then check the output of the SHOWPROCESSLIST statement until you see Hasreadallrelaylog.

3. When these are done from the server, they can be reconfigured to a new setting.

4. On the slave server that is promoted to the master server, issue STOPSLAVE and RESETMASTER and RESETSLAVE operations.

5. Then restart the mysql service.

6. RESETMASTER on the primary server. Then the CHANGEMASTERTOMASTER_HOST='10.1.1.4',MASTER_USER='rep_slave',MASTER_PASSWORD='userpassword'; switch is complete.

This is the end of the content of "how to deploy and switch documents from MySQL database master-slave server". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.