MySQL master-Slave synchronization configuration under CentOS

MySQL master-Slave synchronization configuration under CentOS
First, the 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

Second, create a database

Log in to master machine and slave machine separately mysql:mysql–u root–p

Creating a database: Create databases Repl;

Third, the Master machine and slave machine related configuration

1, modify the master machine MySQL configuration file my.cnf, the file in/etc directory

Add the following fields to the [mysqld] configuration segment

Server-id=1

Log-bin=log

BINLOG-DO-DB=REPL//The database that needs to be synchronized, if there is no bank, that means synchronizing all the databases

Binlog-ignore-db=mysql//Ignored database

Add a sync account to the slave machine on the master machine
Grant Replication Slave on * * to ' repl ' @ ' 192.1681.220 ' identified by ' 123456 ';

Restart the master machine's MySQL service: Service mysqld restart

See log status with Show Master Status command

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 in the [Mysqld] field

server-id=2

master-host=192.168.1.222

Master-user=repl

master-password=123456

master-port=3306

Master-connect-retry=60

REPLICATE-DO-DB=REPL//synchronization of the database, does not write the bank means that all databases are synchronized

Then restart the MySQL slave machine

Enter MySQL in the slave machine

Mysql>start slave;

Mysql>show slave statusg;
If the slave_io_running, slave_sql_running status is yes, the setting is successful.

Iv. problems that arise

Question 1 When I execute the start slave command, the system prompts

ERROR HY000: The server is not configured as slave; Fix in config file or with change MASTER to,

Executes show slave status, and the empty set (0.00 sec), wondering where the problem is

or question 2 Slave_sql_running:no

Later found that the original slave has been turned on by default, to first turn off and then open

Execute slave stop;

Re-execute

Change Master to master_host= ' 192.168.1.222 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' Log.000003 ', master_log_pos=98;

then execute slave start;

Then execute show slave STATUSG

Shown below:

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)

Shows the two lines in red, indicating the configuration was successful.

V. Test whether the master and slave server can synchronize

Create a new table above the primary server, 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 (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 to see if it's synced from the server.

mysql> use REPL;

Database changed

Mysql> select * from test;

+------+------+

| ID | name |

+------+------+

| 1 | Zaq |

| 1 | XSW |

+------+------+

2 rows in Set (0.00 sec)

MySQL master-Slave synchronization configuration under CentOS