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

This article mainly explains "the method of master-slave replication of Mysql building". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the method of Mysql master-slave replication".

Main library: 192.168.1.1

Slave library: 192.168.1.2

Location of data files: / data/datafile

Database to be synchronized: repdb

one。 Set up MASTER

1. Modify my.cnf

# vi / etc/my.cnf

Server-id = 1

Log-bin

Binlog-ignore-db=mysql

two。 Give SLAVE permission account and allow users to LOAD TABLE and LOAD DATA on SLAVE

Mysql > GRANT FILE,SELECT,REPLICATION SLAVE ON *. * TO user_rep@192.168.1.2 IDENTIFIED BY 'rep123'

3. Lock the main database table

Mysql > FLUSH TABLES WITH READ LOCK

4. Display the main library information

Record File and Position, slave library settings will be used

Mysql > SHOW MASTER STATUS G

* * 1. Row *

File: mysql-bin.000030

Position: 391156558

Binlog_Do_DB:

Binlog_Ignore_DB:

5. Open another terminal and package the main library

/ data/datafile/ # tar cvf repdb.tar repdb

two。 Set up SLAVE

1. Transfer to the main library package and unpack it

/ data/datafile/ # scp 192.168.1.1:/data/datafile/repdb.tar.

/ data/datafile # tar xvf repdb.tar

two。 Unlock the main library table

Mysql > UNLOCK TABLES

3. View modify repdb folder permissions

/ data/datafile # chown mysql:mysql repdb-R

4. Modify my.cnf

# vi / etc/my.cnf

# slave

Server-id=2

Master-host=192.168.1.1

Master-user=user_rep

Master-password=rep123

Master-port=3306

Replicate-do-db=repdb

5. Verify connection to MASTER

# mysql-h292.168.1.1-uuser_rep-prep123

Mysql > show grants

+-- +

| | Grants for user_rep@192.168.1.2 |

+-- +

| | GRANT SELECT, FILE, REPLICATION SLAVE ON *. * TO user_rep@192.168.1.2 IDENTIFIED BY |

PASSWORD'* 9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C' |

+-- +

6. Log in to mysql locally

Set the File that connects to MASTER MASTER_LOG_FILE as the main library

Position with MASTER_LOG_POS as the main library

Mysql > CHANGE MASTER TO

MASTER_HOST='192.168.1.1'

MASTER_USER='user_rep'

MASTER_PASSWORD='rep123'

MASTER_LOG_FILE='mysql-bin.000030'

MASTER_LOG_POS=391156558

7. Start the SLAVE service

Mysql > slave start

8. Log in to mysql locally to view SLAVE status

Mysql > SHOW SLAVE STATUS G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.1

Master_User: user_rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000001

Read_Master_Log_Pos: 98

Relay_Log_File: relay.000003

Relay_Log_Pos: 232

Relay_Master_Log_File: binlog.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

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: 98

Relay_Log_Space: 232

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: 0

The values of both the Slave_IO_Running and Slave_SQL_Running columns are "Yes", indicating that both the Slave's Icano and SQL threads are running properly.

The master-slave library has been built successfully. [@ more@]

Launch the problem from the library SLAVE

The CHANGE MASTER and SLAVE services cannot be started due to some erroneous operations. The system reports an error as follows:

Could not initialize master info structure; more error messages can be found in the MySQL error log.

Unable to initialize master info structure; the MySQL error log records more detailed error messages.

Solution:

1. Check the MySQL error log to see the reason.

For example, what is the last Position of synchronization?

In many cases, the service cannot be started because the synchronization recognized by mysql always stays on the previous Position.

two。 View master.info and relay-log.info

Master.info records information related to MASTER

fourteen

Mysql-bin.000030

391156558

192.168.1.1

User_rep

Rep123

3306

sixty

0

Relay-log.info records current synchronization log information

two hundred and thirty five

Mysql-bin.000030

391156558

3. Stop the myslq service and delete master.info and relay-log.info

# service mysql stop

/ data/datafile/ # rm master.info

/ data/datafile/ # rm relay-log.info

4. Start the mysql service

# service mysql start

5. Restart CHANGE MASTER and restart the SLAVE service.

The problem should be solved.

At this point, I believe you have a deeper understanding of the "Mysql master-slave replication method". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.