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

In this issue, the editor will bring you about how to configure mysql master-slave synchronization in linux. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Steps to configure mysql master-slave synchronization under linux

I. mainframe environment

Host:

Master operating system: rhel6.0

IP:172.16.0.100

MySQL version: 5.1.47

Slave: www.2cto.com

Slave operating system: rhel6.0

IP:172.16.0.200

MySQL version: 5.1.47

Second, create

Log in to mysql-u root-p of Mastermachine and slave machine respectively.

Create a database: create database repl

Third, the related configuration of master machine and slave machine

1. Modify the mysql configuration file my.cnf in the master machine, which is located in the / etc directory.

Add the following fields to the [mysqld] configuration section

Server-id=1

Log-bin=mysql-bin

Binlog-do-db=repl / / databases that need to be synchronized. If there is no row, it means that all databases are synchronized.

Binlog-ignore-db=mysql / / ignored database

Add a synchronization account for the slave machine on the mastermachine

Grant replication slave on *. * to 'replication'@'172.16.0.200' identified by' 123456'

Restart the mysql service of MasterCard: service mysqld restart

Use the show master status command to check the log

Mysql > show master status

+-- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-- +

| | log.000003 | 98 | repl | mysql | |

+-- +

1 row in set (0.00 sec)

2. Modify the mysql configuration file in the slave machine

Also add the following under the [mysqld] field

Server-id=2 www.2cto.com

Master-host=172.16.0.100

Master-user=repl

Master-password=123456

Master-port=3306

Master-connect-retry=60

Replicate-do-db=repl / / synchronized database. Not writing this line means synchronizing all databases.

Then restart the mysql of the slave machine

Enter the mysql in the slave machine

Mysql > start slave

Mysql > show slave status\ G

If the status of Slave_IO_Running and Slave_SQL_Running is Yes, the setting is successful.

Execute show slave status\ G at this time

The display is as follows:

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.222

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: log.000003

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 229

Relay_Master_Log_File: log.000003

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

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

1 row in set (0.00 sec)

Www.2cto.com

Slave_IO_Running: Yes Slave_SQL_Running: Yes

The above two lines in the message are Yes, indicating that the configuration is successful.

Fifth, test whether the master and slave servers can be synchronized

Create a new table on the primary server, which must be under repl data

Mysql > use repl

Database changed

Mysql > create table test (id int,name char (10))

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test values (1meme Zaq')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test values (1)

Query OK, 1 row affected (0.00 sec)

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | zaq |

| | 1 | xsw |

+-+ +

2 rows in set (0.00 sec)

Check from the server to see if it is synchronized

Mysql > use repl

Database changed

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | zaq |

| | 1 | xsw |

+-+ +

2 rows in set (0.00 sec)

Indicates that the configuration has been successful.

IV. Problems that arise

1. When the command start slave is executed, the system prompts

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Execute show slave status; and prompt Empty set (0.00 sec)

Reason: slave has been enabled by default. It should be turned off and then enabled.

Execute slave stop

Re-execution

Change master to master_host='172.16.0.200',master_user='repl',master_password='123456', master_log_file='log.000003', master_log_pos=98

2. Error message: Slave_IO_Running:NO

Information in mysql's error log: www.2cto.com

Slave I retries O: error connecting to master 'replication@172.16.0.100:3306'-retry-time: 60 retries: 86400, Error_code: 1045

Solution method

1. The login password must be set when creating slave remote connection access in the main mysql.

two。 Stop the mysqld service on slave, delete all binary log files from the server, including master.info files in a data directory and files at the beginning of hostname-relay-bin, and then start the mysqld service on slave.

Master.info:: records the log file on the Mysql master server and the location of the record and the password for the connection.

Slave_SQL_Running: No on 3.slave, indicating that a table definition is incorrect

Solution:

Empty drop to remove all existing tables on master and slave, and then import new data from the master library.

This is how to configure mysql master-slave synchronization in the linux shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, 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.