Shulou(Shulou.com)06/01 Report--

How to set up MySQL master-slave replication? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Setup details:

Master server: 192.168.1.10

Slave server: 192.168.1.20

Database: mydb

1. Set up the MySQL master server

Create a mysql account with REPLICATION SLAVE privileges on the primary server, and the replication client will connect to the master.

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl_user'@'192.168.1.20' IDENTIFIED BY' secretpassword';mysql > FLUSH PRIVILEGES

There are block write statements on all tables, so do not make changes after backup.

Mysql > use mydb;mysql > FLUSH TABLES WITH READ LOCK;mysql > exit

Edit the mysql configuration file and add the following code under the [mysqld] section.

# vim / etc/ my.cnf[mysqld] log-bin=mysql-binbinlog-do-db=mydbserver-id=1innodb_flush_log_at_trx_commit=1sync_binlog=1

Restart the master mysql server for the changes to take effect.

# service mysqld restart

Use the following command to check the current binary log file name (File) and current offset (Position) values.

Mysql > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000002 | 107 | mydb | | +-+

The above output shows that the current binary uses mysql-bin.000002 with an offset value of 107. Make a note of these values for use on the secondary server.

Back up the database and copy it to slave mysql server.

# mysqldump-u root-p mydb > mydb.sql# scp mydb.sql 192.168.1.20:/opt/

After the backup is complete, remove the READ LOCK from the table so that changes can be made.

Mysql > UNLOCK TABLES

two。 Set up MySQL Slave Server

Edit the salve mysql configuration file and add the following values under the [mysqld] section.

# vim / etc/ my.cnf[mysqld] server-id=2replicate-do-db=mydb

Server-id is always a non-zero value. These values are never similar to other master and slave servers.

Restart mysql slave server. If you have already configured replication, use-skip-slave-start at startup and do not connect to the primary server immediately.

# / etc/init.d/mysqld restart

Use the following command to set option values on the secondary server.

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.10',-> MASTER_USER='repl_user',-> MASTER_PASSWORD='secretpassword',-> MASTER_LOG_FILE='mysql-bin.000002',-> MASTER_LOG_POS=107

Finally, start the slave thread

Mysql > SLAVE START

Check the status of the slave server.

Mysql > show slave status Gateway * 1. Row * * Slave_IO_State: Master_Host: 192.168.1.15 Master_User: repl_user Master_ Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 107 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: mydb Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 107 Relay_Log_Space: 107 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_ SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 row in set (0.00 sec) mysql >

MySQL master-slave replication has been successfully configured on your system and working mode.

Thank you for reading! After reading the above, do you have a general idea of how to set up MySQL master-slave replication? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.