MySQL Master and slave configuration

MySQL has an IO thread from the library that takes binlog from the main library to write locally. Another SQL thread is responsible for performing these local logs, enabling command replay;
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-ignore-db=mysql ignored databases
# master-connect-retry=60 If a primary server is found to be disconnected from the server, the time difference (in seconds) to reconnect
#replicate-DO-DB=REPL//Synchronized database (the name of the database that needs to be backed up), do not write the bank means synchronize all databases

Then restart the MySQL slave machine

Enter MySQL in the slave machine
Mysql>start slave;
Mysql>show slave status\g;
If the slave_io_running, slave_sql_running status is yes, the setting is successful.






Iv. problems that arise
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

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 status\g
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 | Za