Mysql master-slave replication _ MySQL

Mysql master-slave replication bitsCN.com I. environment description
Master Database server ip address: 192.168.0.2
Slave Database server ip: 192.168.0.3
Mysql on the master-slave server has been completely installed, but is put into production, so no data is generated.
II. master-slave replication configuration
1. master configuration of the master database server
(1) authorize the slave server slave
Mysql> grant replication slave on *. * to slave@192.168.0.3 identified by "123 ";
(2) view the master status
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000003 | 400 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Write down the file and position, which will be used to configure slave later. (File is the mysql log file, position is the log clue number, and the server will copy from this position)
2. slave Database server slave configuration
(1) modify the server id of slave to avoid duplication with other mysql databases.
Vi/etc/my. cnf
Server-id = 2
(2) execute SQL statement synchronization
Mysql> change master to master_host = 192.168.0.2, master_user = slave, master_password = 123, master_log_file = mysql-bin.000003, master_log_pos = 400;
(3) start the slave synchronization function
Mysql> start slave;
(4) view the slave status
Mysql> show slave status G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 548
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 399
Relay_Master_Log_File: mysql-bin.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: 548
Relay_Log_Space: 554
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
1 row in set (0.02 sec)

ERROR:
No query specified
Note: Slave_IO_Running: Yes
Slave_ SQL _Running: Yes when both IO and SQL processes are Yes, master and slave can perform master-slave replication.
III. verification test
1. create a test table on the master (root user)
Mysql> use test
Mysql> create table tbs (id int );
Mysql> insert into tbs values (1 );
2. check whether the replication is successful on slave (root user)
Mysql> use test
Mysql> show tables;

Mysql> select * from tbs;
+ ------ +
| Id |
+ ------ +
| 1 |
+ ------ +
1 row in set (0.00 sec)
Copied successfully! BitsCN.com