MySQL master-slave synchronization configuration in centos

MySQL master-slave synchronization configuration in centos I. Environment HOST: master Operating System: centos5.3IP: 192.168.1.222MySQL version: 5.0.77 slave: slave Operating System: centos5.3IP: 192.168.1.220MySQL version: 5.0.77 II. Create a database to log on to mysql on the master and Server Load balancer respectively: mysql-uroot

MySQL master/slave synchronization configuration in centos I. Environment HOST: master Operating System: centos 5.3 IP: 192.168.1.222 MySQL version: 5.0.77 slave machine: slave Operating System: centos 5.3 IP: 192.168.1.220 MySQL version: 5.0.77 II. Create a database to log on to mysql on the master and slave respectively: mysql-u root

MySQL master-slave synchronization configuration in centos
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

2. Create a database

Log on to mysql on the master and slave respectively: mysql-u root-p

Create database repl;

Iii. configurations of master and slave

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

Add the following fields in the [mysqld] configuration section:

Server-id = 1

Log-bin = log

Binlog-do-db = repl // the database to be synchronized. If no row exists, all databases are synchronized.

Binlog-ignore-db = mysql // ignored Database

Add a synchronization account for the Server Load balancer instance on the master machine
Grant replication slave on *. * to 'repl' @ '192. 100' identifiedby '123 ';

Restart the mysql service of the master machine: service mysqld restart

Use the show master status Command to view logs

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 on the server Load balancer instance.

Add the following content in the [mysqld] Field

Server-id = 2

Master-host = 192.168.1.222

Master-user = repl

Masters-password = 123456

Master-port = 3306

Master-connect-retry = 60

Replicate-do-db = repl // the database to be synchronized. If this row is left blank, all databases are synchronized.

Restart the mysql instance of the Server Load balancer instance.

Log on to mysql on the server Load balancer instance

Mysql> start slave;

Mysql> show slave statusG;
If the status of Slave_IO_Running and Slave_ SQL _Running is Yes, the setting is successful.

Iv. Problems

Question 1: When I run the start slave command, the system prompts

ERROR 1200 (HY000): The server is not configured as slave; fixin config file or with change master,

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

Or question 2 Slave_ SQL _Running: No

Later, we found that the slave has been enabled by default. We need to disable it before enabling it.

Execute slave stop;

Run again

Change master tomaster_host = '192. 168.1.222 ', master_user = 'repl', master_password = '000000', master_log_file = 'Log. 100', master_log_pos = 98;

Then execute slave start;

Then execute show slave statusG.

Shown 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.20.03

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 229

Relay_Master_Log_File: log.20.03

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)

The two red lines are displayed, indicating that the configuration is successful.

5. test whether the Master/Slave servers can be synchronized

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

Mysql> use repl

Database changed

Mysql> create table test (id int, namechar (10 ));

Query OK, 0 rows affected (0.00 sec)

Mysql> insert into test values (1, 'zaq ');

Query OK, 1 row affected (0.00 sec)

Mysql> insert into test values (1, 'xsw ');

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 whether synchronization is performed on the slave server.

Mysql> use repl;

Database changed

Mysql> select * from test;

+ ------ +

| Id | Name |

+ ------ +

| 1 | zaq |

| 1 | xsw |

+ ------ +

2 rows in set (0.00 sec)

Solution II:
Mysql> slave stop;
Mysql> set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1;
Mysql> slave start;

Slave_IO_Running: No

1. mysql> reset slave; # focus on this line
2. mysql> change master to master_host = '2017. 168.1.199 ', master_user = 'apk _ service', master_password = '000000', master_log_file = 'mysql-bin.000042', master_log_pos = 123456; # Set it according to your own Environment
3. mysql> start slave; # It Will Be normal.