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

Editor to share with you how to achieve MySQL master-slave synchronous configuration in the CentOS server environment. I hope you will gain something after reading this article. Let's discuss it together.

This paper gives an example of how to configure MySQL master-slave synchronization in CentOS server environment. Share with you for your reference, the details are as follows:

I. Environment

Host:

Master operating system: centos 5.3

IP:192.168.1.222

MySQL version: 5.0.77

Slave:

Slave operating system: centos 5.3

IP:192.168.1.220

MySQL version: 5.0.77

Second, create a database

Log in to the master machine and the slave machine respectively

Mysql:mysql-u root-p

Create the 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=1log-bin=logbinlog-do-db=repl / / databases that need to be synchronized. If there is no row, it means to synchronize all databases binlog-ignore-db=mysql / / ignored databases

Add a synchronization account for the slave machine on the mastermachine

Grant replication slave on *. * to 'repl'@'192.1681.220' 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=2master-host=192.168.1.222master-user=replmaster-password=123456master-port=3306master-connect-retry=60replicate-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.

IV. Problems that arise

When I execute the command start slave, the system prompts me

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), can't figure out what the problem is.

Later, it was found that slave had been turned on by default and had to be turned off before opening.

Execution

Slave stop

Re-execution

The copy code is as follows:

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

And then execute

Slave start

Execute at this time.

Show slave status\ G

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.000003Slave_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: 01 row in set (0.00 sec)

At this point, 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 replDatabase changedmysql > create table test (id int,name char (10)); Query OK, 0 rows affected (0.00 sec) mysql > insert into test values (1 row affected sec) mysql > insert into test values; 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 changedmysql > select * from test;+-+-+ | id | name | +-+-+ | 1 | zaq | | 1 | xsw | +-+-+ 2 rows in set (0.00 sec)

Indicates that the configuration has been successful.

After reading this article, I believe you have a certain understanding of "how to achieve MySQL master-slave synchronization configuration method in CentOS server environment". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.